How can I migrate an ingestion time partitioned table between regions in BigQuery?

0
votes

I want to migrate my ingestion time partitioned table from one region to another (using custom Python scripts), but when I extract and load them, they all fall into today's partition, as this is when they are ingested into the table.

How can I make sure the new table contains the same ingestion time partition structure as the original?

1
google-bigquery

1 Answers

1
votes

You can extract specific partitions within a table using the Python client library. So, instead of extracting the entire table, you can specify which partition you want with the decorator syntax (project.dataset.table$YYYYMMDD for daily or project.dataset.table$YYYYMMDDHH for hourly ingestion partitioned tables) and then simply load them per partition using the same decorator format.

Here is some code;

table_partition = client.get_table("my-project.my_dataset.mytable$20200727")
job_config = bigquery.job.ExtractJobConfig()
job_config.compression = bigquery.Compression.GZIP
job_config.destination_format = (bigquery.DestinationFormat.NEWLINE_DELIMITED_JSON)
extract_job = client.extract_table(table_partition, "gs://my-bucket", location="US", job_config=job_config)
job_result = extract_job.result()

You can find a list of all your ingestion time partitions using this query:

SELECT DISTINCT _PARTITIONTIME AS pt
FROM `my-project.my_dataset.mytable`