how to convert an old style partitioned table to a new style partitioned table easily

Question

I have a partitioned table with name like mytable_* where the suffix denote date.

I would now like to convert this to the new way that tables are to be partitioned in bigquery, ie. with the _partitiondate column, etc.

I was thinking of creating the new table's schema based on the old one, and then to insert data into it, but I am not sure how to put the date value (that is the suffix) of the old table into the _partitiondate field.

I suppose, that this post https://stackoverflow.com/questions/38993877/migrating-from-non-partitioned-to-partitioned-tables is the answer? I was also thinking that I'll probably have to repopulate with an airflow job, etc. — Joyce, Feb 09 '19 at 00:53

score 2 · Accepted Answer · answered Feb 09 '19 at 00:59

If you have previously created date-sharded tables, you can convert the entire set of related tables into a single ingestion-time partitioned table by using the partition command in the bq command-line tool. The date-sharded tables must use the following naming convention: [TABLE]_YYYYMMDD. For example, mytable_20160101, ... , mytable_20160331.

For this you should use bq partition command like in example below

bq --location=[LOCATION] partition --time_partitioning_type=DAY --time_partitioning_expiration [INTEGER] [PROJECT_ID]:[DATASET].[SOURCE_TABLE]_ [PROJECT_ID]:[DATASET].[DESTINATION_TABLE]

You can see more details / options in below articles

Converting date-sharded tables into ingestion-time partitioned tables

and

bq partition

Is there any way of being able to add clustering during the repartition? — Joyce, Mar 08 '19 at 22:29

how to convert an old style partitioned table to a new style partitioned table easily

1 Answers1