Wsrtjtyk

I often want to load one day's worth of data into a date-partitioned BigQuery table, replacing any data that's already there. I know how to do this for 'old-style' data partitioned tables (the ones that have a _PARTITIONTIME field) but don't know how to do this with the new-style date-partitioned tables (which use a normal date/timestamp column to specify the partitioning because they don't allow one to use the $ decorator.

Let's say I want to do this on my_table. With old-style date-partitioned tables, I accomplished this using a load job that utilized the $ decorator and the WRITE_TRUNCATE write disposition -- e.g., I'd set the destination table to be my_table$20181005.

However, I'm not sure how to perform the equivalent operation using a DML. I find myself performing separate DELETE and INSERT commands. This isn't great because it increases complexity, the number of queries, and the operation isn't atomic.

I want to know how to do this using the MERGE command to keep this all contained within a single, atomic operation. However I can't wrap my head around the MERGE command's syntax and haven't found an example for this use case. Does anyone know how this should be done?

The ideal answer would be a DML statement that selected all columns from source_table and inserted it into the 2018-10-05 date partition of my_table, deleting any existing data that was in my_table's 2018-10-05 date partition. We can assume that source_table and my_table have the same schemas, and that my_table is partitioned on the day column, which is of type DATE.

because they don't allow one to use the $ decorator

But they do--you can use table_name$YYYYMMDD when you load into column-based partitioned table as well. For example, I made a partitioned table:

$ bq query --use_legacy_sql=false "CREATE TABLE tmp_elliottb.PartitionedTable (x INT64, y NUMERIC, date DATE) PARTITION BY date"

Then I loaded into a specific partition:

$ echo "1,3.14,2018-11-07" > row.csv

$ bq "tmp_elliottb.PartitionedTable$20181107" ./row.csv

I tried to load into the wrong partition for the input data, and received an error:

$ echo "1,3.14,2018-11-07" > row.csv

$ bq "tmp_elliottb.PartitionedTable$20181105" ./row.csv

Some rows belong to different partitions rather than destination partition 20181105

I then replaced the data for the partition:

$ echo "2,0.11,2018-11-07" > row.csv

$ bq "tmp_elliottb.PartitionedTable$20181107" ./row.csv

Yes, you can use MERGE as a way of replacing data for a partitioned table's partition, but you can also use a load job.