How to copy a BigQuery dataset from one region to another

Use the BigQuery Transfer Service

Lak Lakshmanan
Mar 29, 2020 · 1 min read

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.

Google Cloud - Community

Google Cloud community articles and blogs

Google Cloud - Community

A collection of technical articles and blogs published or curated by Google Cloud Developer Advocates. The views expressed are those of the authors and don't necessarily reflect those of Google.

Lak Lakshmanan

Written by

Data Analytics & AI @ Google Cloud

Google Cloud - Community

A collection of technical articles and blogs published or curated by Google Cloud Developer Advocates. The views expressed are those of the authors and don't necessarily reflect those of Google.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store