How to copy a BigQuery dataset from one region to another

Use the BigQuery Transfer Service

Lak Lakshmanan
Google Cloud - Community

--

The usual way to copy BigQuery datasets and tables is to use bq cp:

bq cp source_project:source_dataset.source_table \
dest_dataset.dest_table

Unfortunately, the copy command doesn’t support cross-region copies, only copies within the same region.

To do a cross region copy, you can use the BigQuery Transfer Service.

First, create the destination dataset:

bq mk --location eu ch10eu

Then, create a transfer config whose data source is cross_region_copy:

bq mk --transfer_config --data_source=cross_region_copy \
--params='{"source_dataset_id": "iowa_liquor_sales", "source_project_id": "bigquery-public-data"}' \
--target_dataset=ch10eu --display_name=liquor \
--schedule_end_time="$(date -v +1H -u +%Y-%m-%dT%H:%M:%SZ)"

Because the data transfer service is meant for routine copies, this will repeat every 24 hours. In my example, I set the end time to be 1-hour from now, so that the transfer happens only once.

Please star this issue if you’d like bq cp to support cross-region copies.

--

--

Lak Lakshmanan
Google Cloud - Community

articles are personal observations and not investment advice.