Loading hive-partitioned ORC data into bigquery

user_10780906 0
Viewed 726

I have hive data partitioned by ts_date and orid and below is the sample cloud storage url for the files

myhivepartionedbucket/crunched-logs/mrs-suds-sloaders/loader-log/output/{year}/{month}/{Day}/{Hour}/{Min}/ts_date={yyyy-mm-dd}/orid={orid}/file.orc

Below is the cloud storage actual url in production

myhivepartionedbucket/crunched-logs/mrs-suds-sloaders/loader-log/output/2020/08/06/00/30/ts_date=2020-08-05/orid=6691/part-202008060030.orc

This kind of structure will be there for all the days for each month with 24hrs separated by each hour for all the orid's

Now we want to schedule to load this kind of data to big query with partition by ts_date and clustering by orid.

I have tried on the console for one file without partition, I'm able to load the data, but how can I schedule and load this kind of data regularly with partition by ts_date and cluster by orid.

I'm new to GCP and not enough understanding from docs provided by google so can somebody guide or give me ideas to run on UI or program in python

Thanks in advance

google-cloud-storage google-bigquery google-cloud-platform hive
1 Answers

You can use a wildcard with a common prefix, like:

myhivepartionedbucket/crunched-logs/mrs-suds-sloaders/loader-log/output/*.orc

To load all your files containing your ORC data.

Make sure to enable Source Data Partitioning when you load ORC data through the console.

The console allows you to set several parameters including partitioning and clustering. However, in order to partition by column(s), is required that the schema inferred from the ORC file has at least one column of DATE or TIMESTAMP type, otherwise, this option is not available when loading your ORC data. Since you're planning to partition using a column detected through your URI path (ts_date) this option may not be available, so my suggestion would be to load it as a regular table and then creating the partitioned/clustered table from query results of the regular table.

Finally, once you have your partitioned/clustered table, you can use it as destination of BigQuery Data Transfer Service for Cloud Storage, which allows you to schedule recurring data loads from Cloud Storage to BigQuery.

user_54315020
Related