[firestore-bigquery-export] Migrate unpartitioned table to partitioned table

[nwparker]

[REQUIRED] Extension name

This feature request is for extension: _ firestore-bigquery-export

What feature would you like to see?

The ability to migrate a unpartitioned table to a partitioned table

#581 introduced the ability to create a new streaming import for a partitioned table. However, updating an older extension configuration to set the partitioning has no effect.

I am hoping for a way to re-configure my existing imports to have the existing and future data partitioned

How would you use it?

To partition my existing data

[dackers86]

hi @nwparker

It is my understanding that there is currently no function to update or convert an existing table, this functionality is not available in BigQuery.

A possible solution would be to migrate to a new table, this can be done by running a new instance of the extension - and then backfilling / migrating the data into newly created partitioned table via

https://stackoverflow.com/questions/50384721/how-to-convert-a-non-partitioned-table-into-a-partitioned-one

[nwparker]

Here are complete steps that should work. Though ideally this can be automated.

1. Update plugin to use partitioning
2. Copy the table (to a new temporary table) with the correct partitioning CREATE TABLE dataset.temp_table PARTITION BY DATE(timestamp) AS SELECT * FROM dataset.orig_table
3. Delete the original table (it is not possible to rename it)
4. Rename the new table to the original table ALTER TABLE dataset.temp_table RENAME TO orig_table

[i14h]

lets keep this issue here and see if there's enough interest by users to actually build a script for it, otherwise the steps provided by @nwparker can be used to perform the migration