Load bulk data into BigQuery partitioned table with different partitions-based data

0
votes

I want to load CSV data into BigQuery partitioned table.My CSV file containing different partitioned data.How can i load entire CSV file to BigQuery partitioned table so that particular csv partitioned data goes to respective partition in BigQuery.

2
google-bigquery

2 Answers

0
votes

You can load your entire CSV data into temporary staging table and then use queries of type

bq query --destination_table=table$20170115 
  "SELECT * FROM temp WHERE part='2017-01-15'"

To copy appropriate data into their partitions.

0
votes

Currently, BigQuery DOES NOT support partitioning based on field(s) in data!
So, obviously you cannot load whole CSV and expect it to be partitioned.
Hopefully this will be changed soon - you can track respective feature request

Meantime, some of your options are:

  • process your csv file outside of BigQuery to split one file to respective dates (one csv per date) and then load them one-by-one into respective partition of (in advance prepared partitioned) table

or

  • load whole csv as is into BigQuery and then process / select one-by-one day into respective partition of (still in advance prepared partitioned) table

Depends on your particular case one or another option can be better for you - you to decide.
From BigQuery cost point of view - first option seems cheaper to me