2

I have two Big Query tables, t1 and t2, both of which contain _PARTITIONTIME columns.

My goal is to insert rows from t2 into t1 in such a way that maintains the _PARTITIONTIME

Is there a way of INSERTing the _PARTITIONTIME as a selected variable?

Or is there a way of retrospectively defining the _PARTITIONTIME column after the rows have been inserted?

billett
  • 432
  • 1
  • 5
  • 13

2 Answers2

2

Currently you can write to partitions using the partition decorators

[TABLE_NAME]$20160501

however there is a high request feature that is in progress, that will allow you to pick a column and use that as partition day.

In order to write to 20 different partition you need to create at least 20 different queries and writes as explained here

bq query --allow_large_results --replace --noflatten_results \
--destination_table 'mydataset.temps$20160101' \
'SELECT stn,temp from [bigquery-public-data:noaa_gsod.gsod2016] WHERE mo="01" AND da="01" limit 100'

bq query --allow_large_results --replace --noflatten_results \
--destination_table 'mydataset.temps$20160102' \
'SELECT stn,temp from [bigquery-public-data:noaa_gsod.gsod2016] WHERE mo="01" AND da="02" limit 100'

More solutions are addressed here: Migrating from non-partitioned to Partitioned tables

Pentium10
  • 204,586
  • 122
  • 423
  • 502
2

I don't know when this have been implemented, but it works!

When you use a DML statement to add rows to an ingestion-time partitioned table, you can specify the partition to which the rows should be added. You reference the partition using the _PARTITIONTIME pseudo column.

INSERT INTO PROJECT_ID.DATASET.t2 (_PARTITIONTIME, field1, field2) 
SELECT _PARTITIONTIME, field1, field2
FROM PROJECT_ID.DATASET.t1

https://cloud.google.com/bigquery/docs/using-dml-with-partitioned-tables#inserting_data_into_ingestion-time_partitioned_tables

Sergiiko
  • 319
  • 1
  • 10