CODEWITHSUNDEEP
google-bigquery
Rogier Werschkull
Rogier Werschkull

Reputation: 744

BigQuery: change date partitioned table to ingestion time partitioned table

I have an BigQuery date partitioned table that I want to convert to an ingestion time partitioned table (partitioned on _PARTITIONTIME), using the current date partitioning to feed into _PARTITIONTIME. How can I do this?

WHY? Because only ingestion partitioned tables can be incrementally loaded to using BigQuery's scheduled query functionality (by using the @rundate parameter as partition decorator)

Upvotes: 2

Views: 4262

Answers (1)

Deen酱
Deen酱

Reputation: 146

One option is to disable the scheduled query first and copy the column-based partitioned table to a ingestion-time partitioned table. Then re-enable the scheduled query. Please follow steps:

  1. Disable the scheduled query through the BigQuery UI: disable option on scheduled query
  2. Create a new ingestion-time partitioned table (called ingestion_time_partitioned) and copy the column-based partitioned table (called table_column_partitioned) to the new table (ingestion_time_partitioned).
  3. Edit the scheduled query to write to the new ingestion-time partitioned table (ingestion_time_partitioned). Please remember to re-enable the scheduled query and remove the partition field (which is used for column-based partition).

Copying from column-based partitioned table to a ingestion-time partitioned table will correctly map the column-based partition to the ingestion-time-based partition. And copy job on BigQuery is free. For more information about copying partitioned tables, please see https://cloud.google.com/bigquery/docs/managing-partitioned-tables#copying_partitioned_tables

Upvotes: 2

Related Questions