# Partition and cluster an existing BigQuery table

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](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 `common` dataset where it creates the stored procedures needed.
    
* You probably want to change the region (I have used `region-eu` in the code). The script makes use of the `INFORMATION_SCHEMA` and this needs a qualifier ([https://cloud.google.com/bigquery/docs/information-schema-intro#syntax](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 RENAME` followed by `DROP TABLE` of 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](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#alter_table_rename_to_statement)).