BigQuery date partition from column

Question

I'm trying to understand how one would go about to load a date partitioned table from a csv file containing data for multiple days. I guess I'm looking for a similar feature relational databases offer with their partitioning on a specific column which takes care of loading the record into the correct partition automatically.

It seems that with bigquery the only way to assign a partition dynamically is by using a partition decorator which would require me to load every record sequentially from the CSV (as the day could be different for every row) or first write an etl to split the csv to separate files by day.

Am I missing something here?

Answer 1

Now Google has introduced a feature for Partition by field. You can use any Date or Datetime type field for partiton

Answer 2

Currently BigQuery doesn't support partitioning on a specific column, but this is a highly requested feature vote here and here.

As you say the only way to load data in a partition is to use the partition decorator. For this you either process your CSV outside into days and load each day into it's own partition using separate load jobs for each partition.

Or you load the full CSV unpartitioned, then later create the scripting for partitions by individual query->write results to dedicated partition as described in details here.