-1

I am trying to creating an external table with Partition below is the reference image i am using.

enter image description here

Here is what i am intending to do :

I have files flowing into this folder: enter image description here

I need to query the external table based on the date :

eg :

select * from where _PartitionDate ='';

My specific query is what should i fill in the GCS bucket & source Data partitioning fields.

Thank you.

rohith g
  • 33
  • 4

3 Answers3

1

According to the documentation that Guillaume provided [1], you should click on the Source data partitioning box and provide the following link there:

gs://datalake-confidential-redacted/ExternalTable_Data/

Also, the Table type should be External table.

Once that is fixed, you should be able to create the table. I have reproduced the issue on my own and it is working.

[1] - https://cloud.google.com/bigquery/docs/hive-partitioned-queries-gcs#hive-partitioning-options

Malaman
  • 175
  • 4
0

This part of the documentation should help you. You need to check the Source data partitioning and then to fill in your prefix URI such as

gs://datalake-confidential-redacted/ExternalTable_Data/{dt:DATE}

And then, use this dt field as any field in your queries

SELECT *
FROM `externale-table`
WHERE dt = "2020-01-10"
guillaume blaquiere
  • 66,369
  • 2
  • 47
  • 76
  • Thank you so much for the response, I have tried inputting the same parameter into the prefix URI & it fails with the error as below: Invalid source URI: gs:///bigstore/datalake-confidential-redacted/ExternalTable_Data/{dt:DATE} – rohith g Jan 14 '21 at 13:49
  • there is 3/ after the gs:, is it normal? – guillaume blaquiere Jan 14 '21 at 14:22
  • Thank you so much for your kind response, It is only gs://datalake-confidential-redacted/ExternalTable_Data/{dt:DATE} – rohith g Jan 14 '21 at 14:28
  • Created a teraform script for the above. It is cleared. There is a issue with custom Wizard. – rohith g Jan 14 '21 at 18:10
0

Custom Wizard has an issue with this approch. Once we used Teraform scripts it has been successful. It mandates a need to mark HIVE partition to custom & once the date column is created it is added as column into the table. there by allowing to query.

rohith g
  • 33
  • 4