title: Unable to query BigQuery external table on partitioned data with spark
body: <p>I was trying to create an external table on the partitioned data in GCS written from a spark job with date partitioned in PARQUET format.</p>
  
  <p>The data is the GCS bucket is as shown the picture .</p>
  
  <p><a href="https://i.stack.imgur.com/exp5z.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/exp5z.png" alt="enter image description here"></a></p>
  
  <p>I created an external table with table definition</p>
  
  <pre><code>  "hivePartitioningOptions": {
      "mode": "AUTO",
      "sourceUriPrefix": "gs://transaction_data_bucket_for_bigquery/trx_data"
    },
    "sourceFormat": "PARQUET",
    "sourceUris": [
      "gs://transaction_data_bucket_for_bigquery/trx_data/*"
    ]
  }
  </code></pre>
  
  <p>with command </p>
  
  <pre><code>bq mk --external_table_definition=/tmp/table_def &lt;project&gt;:&lt;dataset&gt;.sample_trx_external
  </code></pre>
  
  <p>When i try to query the table, i am getting a strange error.</p>
  
  <pre><code>Partition keys should be invariant from table creation across all partitions, with the number of partition keys held constant with invariant names. Expected 0 partition keys ([]), but 1 ([transaction_date]) were encountered along path /bigstore/transaction_data_bucket_for_bigquery/trx_data/transaction_date=2016-01-01.; Cannot add hive partitioning to table &lt;data_set&gt;.sample_trx_external -- table creation from underlying uri failed.. Underlying error: Partition keys should be invariant from table creation across all partitions, with the number of partition keys held constant with invariant names. Expected 0 partition keys ([]), but 1 ([transaction_date]) were encountered along path /bigstore/transaction_data_bucket_for_bigquery/trx_data/transaction_date=2016-01-01..
  </code></pre>
  
  <p>Unfortunately i couldn't able to decipher the message. There are only 1 day of transactions written to the GCS bucket . </p>
  
  <p>When i tried with CUSTOM mode definition </p>
  
  <pre><code>{
    "hivePartitioningOptions": {
      "mode": "CUSTOM",
      "sourceUriPrefix": "gs://transaction_data_bucket_for_bigquery/trx_data/{transaction_date:DATE}"
    },
    "sourceFormat": "PARQUET",
    "sourceUris": [
      "gs://transaction_data_bucket_for_bigquery/trx_data/*"
    ]
  }
  </code></pre>
  
  <p>i got a slightly different error </p>
  
  <pre><code>Partition keys should be invariant from table creation across all partitions, with the number of partition keys held constant with invariant names. Expected 1 partition keys ([transaction_date]), but 0 ([) were encountered along path /bigstore/transaction_data_bucket_for_bigquery/trx_data.; Cannot add hive partitioning to table &lt;data_Set&gt;.sample_trx_external_2 -- table creation from underlying uri failed.. Underlying error: Partition keys should be invariant from table creation across all partitions, with the number of partition keys held constant with invariant names. Expected 1 partition keys ([transaction_date]), but 0 ([) were encountered along path /bigstore/transaction_data_bucket_for_bigquery/trx_data..
  </code></pre>
  
  <p>Got struck here and any suggestion would be a great help . </p>