Migrating to Clustered Table

Question

I have a table in BigQuery that is partitioned by day and I would like to move all rows to another table in BigQuery that is partitioned by day and also clustered by a couple fields. I know I can do this with a DML INSERT statement, but it's expensive. Is there a cheaper workaround? Thanks!

Answer 1

Lazy migration, for the cost of a full table scan

CREATE TABLE x_new
PARTITION BY y
CLUSTER BY z
AS
SELECT * 
FROM x_old

Free migration, by exporting

Step 1:

CREATE TABLE x_new
PARTITION BY y
CLUSTER BY z
AS
SELECT * 
FROM x_old
LIMIT 0

Step 2: Export table x_old to GCS (Avro recommended).

Step 3: Load exported files from GCS into x_new.