Cloud Intelligence™
How to Copy Data Inside BigQuery
This page is also available in Deutsch, Español, Français, Italiano, 日本語, and Português.
About Joshua Fox
Joshua Fox has been a software architect in innovative technology companies for 20 years. Now, he advises tech startups and growth companies on architecture and cost optimization for Google Cloud Platform and Amazon Web Services; also publishing articles, and speaking to cloud engineers.
He has a PhD from Harvard University and a BA in math from Brandeis.
My personal pageCopying data between your own tables in BigQuery has become much easier in recent years. But there are still many ways to do it, each with different limitations that can get confusing. I’ll describe each, with pluses and minuses.
Copying across data regions used to be complicated.
PerfectScale™ for Kubernetes
Ready to optimize?
Get your free Kubernetes savings analysis
Copy through Storage
You could copy from BigQuery to Cloud Storage, then back again to BigQuery in the other region. This not only has that additional complicating step, but it can get expensive too. You also had to orchestrate it yourself, for example, with Composer for scheduling, buffering, and parallelization.
Dataset Copying
Recently, the Dataset Copying feature has made this much easier. The feature works across regions and is free, except for network costs. Scheduling and related features are built-in.
Your cloud bill shouldn't be a mystery
Optimization, automation, expertise. In one platform.
Intra-region copying with `bq` _, the Job API, or Copy Tables_
Copying inside a region has always been easier, and best of all, free. Within the region, you can run bq cpfrom the command line, code against the Job API, or “Copy Tables” in the Cloud Console lets you do this as well.
Scheduled Queries
The Scheduled Queries feature is another way to copy inside a region. Though not free, it is quite flexible, allowing you to write any SELECT statement for insertion to a target table. And as the name suggests, it has scheduling built-in.
Of the approaches I mentioned, only bq cpand Scheduled Queries support one common use case, daily incremental backup of an ingestion-time partitioned table. Only these can copy just the last day’s partition and preserve this as a partition in the target table.
Here are your choices in table form:
