BigQuery Create External Table on partitioned Data

[Kiran Arshewar]

Hi Guys,

I am naive to GCP I am trying to find a solution to use the gcs location to load the data in BigQuery, 

location: gs://bucket/prefix/yyyymmdd=20180101/filename.csv

file header: order_id,city,transaction_id

I want the table in BigQuery to be  order_id,city,transaction_id, *yyyymmdd*

current architecture is creating an external hive table in hdfs is there similar way to do in BigQuery

Thanks,

Kiran

[Csaba Kassai]

Hi Kiran,

one solution is to create an external table in BQ, where the source is gs://bucket/prefix/*. The process is described here: https://cloud.google.com/bigquery/external-data-cloud-storage

Then you can extract the date from the  _FILE_NAME pseudo column in the external table using  a query with some string function like REGEXP_EXTRACT. 

Regards, 

Csaba

--
You received this message because you are subscribed to the Google Groups "cloud-composer-discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cloud-composer-di...@googlegroups.com.
To post to this group, send email to cloud-compo...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/cloud-composer-discuss/50143197-2b5c-423e-95a3-f462210aa70f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Sudarshan Thanekar]

Kiran,

Do you have any other way? Because we are working parquet files and Bigquery doesn't allow external table for parquet files..

To view this discussion on the web visit https://groups.google.com/d/msgid/cloud-composer-discuss/CAO4aYDziKJ8D7GafsFR1X3mA5sPhb%2BO%2Bvv3S28QtgGMme3Xutw%40mail.gmail.com.

[Kiran Arshewar]

No I don’t  have other sol as of now, we have csv and parquet files. 

Privileged/Confidential Information may be contained in this message. If you are
not the addressee indicated in this message (or responsible for delivery of the
message to such person), you may not copy or deliver this message to anyone. In
such case, you should destroy this message and kindly notify the sender by reply
email. Please advise immediately if you or your employer does not consent to email
for messages of this kind. Opinions, conclusions and other information in this
message that do not relate to the official business of Group M Worldwide LLC and/or
other members of the GroupM group of companies shall be understood as neither given
nor endorsed by it. GroupM is the global media investment management arm of WPP.
For more information on our business ethical standards and Corporate Responsibility
policies please refer to WPP's website at http://www.wpp.com/WPP/About/