BigQuery Write Truncate with a partitioned table causes loss of partition information?

Question

We have recently partitioned most of our tables in BigQuery using the following method:

1. Run a Dataflow pipeline which reads a table and writes the data to a new partitioned table.

2. Copy the partitioned table back to the original table using a copy job with write truncate set.

Once complete the original table is replaced with the data from the newly created partitioned table, however, the original table is still not partitioned. So I tried the copy again, this time deleting the original table first and it all worked.

The problem is it takes 20 minutes to copy our partitioned table which would cause downtime for our production application. So is there any way of doing write truncate with a partitioned table replacing a non-partitioned table without causing any downtime? Or will we need to delete the table first in order to replace it?

Answer 1

Sorry but you cannot change a non-partitioned table to partitioned, or vice versa. You will have to delete and re-create the table.

Couple of workarounds I can think of:

1. Keep both tables while you're migrating your queries to the partitioned table. After all queries are migrated you delete the original, non-partitioned table.
2. If you are using Standard Sql, you can replace the original table with a view on top of the partitioned table. Deleting and replacing the original table with a view should be very quick. And partition pruning should still work on top of the view so you're only charged for the queried partitions. Partition pruning might not work for legacy sql.