Sometimes it so happens that we create or are using a table with data that is non-partitioned but we need to convert this into a partitioned table. A typical use-case is old tables that start accumulate data over time. Quite often, we need the same data with extra partitions (and/or clusters), and the rest we can use DDL commands (such as updating metadata).
I’ve created a simple helper script that comes in handy for just that: https://github.com/raghuveer-s/example-code/blob/main/partition-bq-table/partition-bq-table.sql
The script does try to preserve some table and column metadata before partitioning, cluster, and copying the data into a new table. For the most part, you should be able to just change the variable names and use it.
Some final points to consider:
There are some assumptions baked in the script, such as the existence of
commondataset where it creates the stored procedures needed.You probably want to change the region (I have used
region-euin the code). The script makes use of theINFORMATION_SCHEMAand this needs a qualifier (https://cloud.google.com/bigquery/docs/information-schema-intro#syntax). Note for my future self: This really should be parameterized.If you want to preserve the same table name for the partitioned table, add a couple of commands for changing table name with
ALTER TABLE RENAMEfollowed byDROP TABLEof the old table. Note that, changing a table name does come with some limitations in BigQuery (https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#alter_table_rename_to_statement).