Copying datasets

You can copy a dataset within a region or from one region to another, without extracting, moving, and reloading data into BigQuery. You can copy the dataset one time or on a customized recurring schedule.

Quotas and limitations

BigQuery dataset copies are subject to the following quotas and limitations.

Copy quotas

Copying a dataset requires one copy job for each table in the dataset.

The following limits apply to BigQuery jobs for copying tables. The limits apply to jobs created by using the bq command-line tool, Cloud Console, or the copy-type jobs.insert API method. All copy jobs count toward this limit, whether they succeed or fail.

Limit Default Notes
Copy jobs per destination table per day See Table operations per day.
Copy jobs per day 100,000 jobs Your project can run up to 100,000 copy jobs per day.
Cross-region copy jobs per destination table per day 100 jobs Your project can run up to 100 cross-region copy jobs for a destination table per day.
Cross-region copy jobs per day 2,000 jobs Your project can run up to 2,000 cross-region copy jobs per day.

The following limits apply to copying datasets:

Limit Default Notes
Maximum number of tables in the source dataset 20,000 tables A source dataset can have up to 20,000 tables.
Maximum number of tables that can be copied per run to a destination dataset in the same region 20,000 tables Your project can copy 20,000 tables per run to a destination dataset that is in the same region.
Maximum number of tables that can be copied per run to a destination dataset in a different region 1,000 tables Your project can copy 1,000 tables per run to a destination dataset that is in a different region. For example, if you configure a cross-region copy of a dataset with 8,000 tables in it, then BigQuery Data Transfer Service automatically creates eight runs in a sequential manner. The first run copies 1,000 tables. Twenty-four hours later, the second run copies 1,000 tables. This process continues until all tables in the dataset are copied, up to the maximum of 20,000 tables per dataset.

For more information, see Copy jobs.

General limitations

  • You must create the destination dataset before you can create a transfer configuration for the dataset copy.

  • For each dataset copy configuration, you can have one active copy at a time. Additional transfer runs are queued.

Data type limitations

  • Copying views is not supported.
  • Copying external tables is not supported.
  • Copying storage in a streaming buffer is not supported.

  • Support for copying tables encrypted with customer-managed keys varies if you are copying in the same region or across regions.

In-region

Copying encrypted tables, including tables encrypted with customer-managed keys (CMEK), is supported for dataset copying within the same region.

Cross-region

Copying tables with the default encryption across regions is supported. Copying tables encrypted with customer-managed keys (CMEK) across regions is not currently supported. CMEK-encrypted tables are skipped when copying tables to the destination dataset.

Destination dataset limitations

Datasets with customer-managed keys (CMEK) cannot be used as a destination when copying across regions. However, a table with CMEK is allowed as a destination when copying within regions.

Table limitations

  • Copying partitioned tables is currently supported. However, appending data to a partitioned table is not supported.

  • If a table exists in the source dataset and the destination dataset, and it has not changed since the last successful copy, it is skipped. This is true even if the Override destination table box is checked.

  • When truncating tables, the dataset copy does not detect any changes made to the destination dataset before beginning the copy. All the data in the destination dataset is refreshed (overwritten). Both tables and schema are overwritten.

    • If you intend to overwrite the destination table when copying by using the overwrite_destination_table flag, both tables must have the same partitioning schema.

Before you begin

Before you create a dataset copy:

  • Dataset copying uses features of the BigQuery Data Transfer Service. In the Google Cloud project for your destination dataset, verify that you have completed all actions required to enable the BigQuery Data Transfer Service.
  • Create a BigQuery dataset as the destination dataset, in the same region or a different region from your source dataset. You specify a location for storing your BigQuery data when you create a dataset. Not all regions are supported yet for dataset copying (see Supported regions). The dataset name must be unique per project.
  • Locate the ID of your source dataset that you want to copy, and the source project's ID.
  • If you intend to set up transfer run notifications for Pub/Sub, you must have pubsub.topics.setIamPolicy permissions. Pub/Sub permissions are not required if you just set up email notifications. For more information, see BigQuery Data Transfer Service run notifications.
  • If you intend to overwrite the destination table when copying by using the overwrite_destination_table flag, both tables must have the same partitioning schema.
  • Grant Identity and Access Management (IAM) roles that give users the necessary permissions to perform each task in this document.

Required permissions

To create a dataset copy, you need the following IAM permissions:

  • To create the copy transfer, you need the following on the project:

    • bigquery.transfers.update
    • bigquery.jobs.create

  • On the source dataset, you need the following:

    • bigquery.datasets.get

  • On the destination dataset, you need the following:

    • bigquery.datasets.get
    • bigquery.datasets.update
    • bigquery.tables.create

The predefined IAM role roles/bigquery.admin includes the permissions that you need in order to create a dataset copy.

For more information on IAM roles in BigQuery, see Predefined roles and permissions or the IAM permissions reference.

Setting up a dataset copy

To create a dataset copy:

Console

Option 1: Use the Copy Dataset icon.

  1. Go to the BigQuery page in the Cloud Console.

    Go to the BigQuery page

  2. Select the dataset name of the source dataset that you want to copy.

  3. Click the Copy Dataset icon.

    Copy Dataset icon.

  4. In the Copy dataset dialog, select the project ID and destination dataset ID. The project and dataset can be in different regions, but not all regions are supported for cross-region dataset copying.

  5. Dataset names within a project must be unique.

  6. Optionally, check the Overwrite destination table box if you want to refresh (overwrite) all the data in the destination dataset. Both tables and schema are overwritten.

    Copy dataset dialog.

  7. Consider deleting the old dataset to avoid additional storage costs.

Option 2: Use the Transfers button.

  1. Go to the BigQuery page in the Cloud Console.

    Go to the BigQuery page

  2. Click Transfers.

  3. Click + CREATE A TRANSFER.

  4. On the Create Transfer page:

    • In the Source type section, for Source, choose Dataset Copy.

      Transfer source.

    • In the Transfer config name section, for Display name, enter a name for the transfer such as My Transfer. The transfer name can be any value that lets you easily identify the transfer if you need to modify it later.

      Transfer name.

    • In the Schedule options section, for Schedule, leave the default value (Start now) or click Start at a set time.

      • For Repeats, choose an option for how often to run the transfer. Options include:

        • Daily (default)
        • Weekly
        • Monthly
        • Custom
        • On-demand

        If you choose an option other than Daily, additional options are available. For example, if you choose Weekly, an option appears for you to select the day of the week. If you choose Custom, a Cron-like time specification is expected, for example every 12 hours. The shortest allowed period is 12 hours.

      • For Start date and run time, enter the date and time to start the transfer. If you choose Start now, you can't modify this field.

        Transfer schedule.

    • For Destination dataset, choose the dataset you created to store your data, in a different region.

    • For Source dataset, input the name of the dataset you want to copy.

    • For Source project, input the ID of the project your source dataset is in.

    • (Optional) Check the Overwrite destination table box if you want to refresh (overwrite) all data in the destination dataset. Both tables and schema are overwritten.

      New dataset copy.

    • (Optional) In the Notification options section:

      • Click the toggle to enable email notifications. When you enable this option, the transfer administrator receives an email notification when a transfer run fails.
      • For Select a Pub/Sub topic, choose your topic name or click Create a topic to create one. This option configures Pub/Sub run notifications for your transfer.

      Pub/Sub topic.

  5. Click Save.

  6. Consider deleting the old dataset to avoid additional storage costs.

bq

Enter the bq mk command and supply the transfer creation flag --transfer_config. The following flags are also required:

  • --project_id
  • --data_source
  • --target_dataset
  • --display_name

  • --params

    bq mk --transfer_config --project_id=PROJECT_ID --data_source=cross_region_copy --target_dataset=DATASET --display_name=NAME --params='PARAMETERS'

Replace the following:

  • PROJECT_ID: your Google Cloud project ID. If --project_id isn't specified, the default project is used.
  • DATASET: the BigQuery target dataset for the transfer configuration.
  • NAME: the display name for the copy job or transfer configuration. The transfer name can be any value that lets you easily identify the transfer if you need to modify it later.
  • PARAMETERS: contains the parameters for the created transfer configuration in JSON format. For example: --params='{"param":"param_value"}'. For dataset copying, you must supply the source_dataset_id, the source_project_id, and optionally the overwrite_destination_table parameters.

Parameters for a dataset copy configuration are:

  • source_dataset_id: the ID of the source dataset you want to copy.
  • source_project_id: the ID of the project your source dataset is in.
  • (Optional) overwrite_destination_table: Include this parameter if you want to truncate the tables of a previous copy and refresh all the data.

For example, the following command creates a dataset copy configuration named My Transfer with a target dataset named mydataset and a project with the ID of myproject.

bq mk --transfer_config --project_id=myproject --data_source=cross_region_copy --target_dataset=mydataset --display_name='My Dataset Copy' --params='{"source_dataset_id":"123_demo_eu","source_project_id":"mysourceproject","overwrite_destination_table":"true"}'

For additional information on the bq mk command, see Command-line tool reference.

API

Use the projects.locations.transferConfigs.create method and supply an instance of the TransferConfig resource.

Java

Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation. 

import com.google.api.gax.rpc.ApiException;
import com.google.cloud.bigquery.datatransfer.v1.CreateTransferConfigRequest;
import com.google.cloud.bigquery.datatransfer.v1.DataTransferServiceClient;
import com.google.cloud.bigquery.datatransfer.v1.ProjectName;
import com.google.cloud.bigquery.datatransfer.v1.TransferConfig;
import com.google.protobuf.Struct;
import com.google.protobuf.Value;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

// Sample to copy dataset from another gcp project
public class CopyDataset {

  public static void main(String[] args) throws IOException {
    // TODO(developer): Replace these variables before running the sample.
    final String destinationProjectId = "MY_DESTINATION_PROJECT_ID";
    final String destinationDatasetId = "MY_DESTINATION_DATASET_ID";
    final String sourceProjectId = "MY_SOURCE_PROJECT_ID";
    final String sourceDatasetId = "MY_SOURCE_DATASET_ID";
    Map<String, Value> params = new HashMap<>();
    params.put("source_project_id", Value.newBuilder().setStringValue(sourceProjectId).build());
    params.put("source_dataset_id", Value.newBuilder().setStringValue(sourceDatasetId).build());
    TransferConfig transferConfig =
        TransferConfig.newBuilder()
            .setDestinationDatasetId(destinationDatasetId)
            .setDisplayName("Your Dataset Copy Name")
            .setDataSourceId("cross_region_copy")
            .setParams(Struct.newBuilder().putAllFields(params).build())
            .setSchedule("every 24 hours")
            .build();
    copyDataset(destinationProjectId, transferConfig);
  }

  public static void copyDataset(String projectId, TransferConfig transferConfig)
      throws IOException {
    try (DataTransferServiceClient dataTransferServiceClient = DataTransferServiceClient.create()) {
      ProjectName parent = ProjectName.of(projectId);
      CreateTransferConfigRequest request =
          CreateTransferConfigRequest.newBuilder()
              .setParent(parent.toString())
              .setTransferConfig(transferConfig)
              .build();
      TransferConfig config = dataTransferServiceClient.createTransferConfig(request);
      System.out.println("Copy dataset created successfully :" + config.getName());
    } catch (ApiException ex) {
      System.out.print("Copy dataset was not created." + ex.toString());
    }
  }
}

Python

Before trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Python API reference documentation.

Install the Python client for the BigQuery Data Transfer API with pip install google-cloud-bigquery-datatransfer. Then create a transfer configuration to copy the dataset. 
from google.cloud import bigquery_datatransfer

transfer_client = bigquery_datatransfer.DataTransferServiceClient()

destination_project_id = "my-destination-project"
destination_dataset_id = "my_destination_dataset"
source_project_id = "my-source-project"
source_dataset_id = "my_source_dataset"
transfer_config = bigquery_datatransfer.TransferConfig(
    destination_dataset_id=destination_dataset_id,
    display_name="Your Dataset Copy Name",
    data_source_id="cross_region_copy",
    params={
        "source_project_id": source_project_id,
        "source_dataset_id": source_dataset_id,
    },
    schedule="every 24 hours",
)
transfer_config = transfer_client.create_transfer_config(
    parent=transfer_client.common_project_path(destination_project_id),
    transfer_config=transfer_config,
)
print(f"Created transfer config: {transfer_config.name}")

Viewing and refreshing dataset copy jobs

You can see progress and view details of a dataset copy under Transfers.

Console

Transfers button console.

View transfer details console.

Dataset copy refresh runs are scheduled to run with the schedule you specify, which is configurable. The default is every 24 hours. Click the More menu and then Refresh Now if you want to request a new run for immediate execution.

Refresh dataset copy button.

Pricing

There is no charge for dataset copying during the beta period.

At general availability, data copied between regions is billed at the same rates as pricing for Compute Engine network egress between regions.

BigQuery sends compressed data for copying across regions, so the GB billed might be less than the size of your dataset.

For more information, see BigQuery pricing.

Supported regions

There are two types of locations:

  • A region is a specific geographic place, such as London.

  • A multi-region is a large geographic area, such as the United States, that contains two or more geographic places.

Datasets can be copied from region to region, single region to multi-region, multi-region to single region, or multi-region to multi-region.

Not all regions are currently supported for dataset copying. You can create dataset copies in regions where the BigQuery Data Transfer Service is currently supported, as shown below.

Regional locations

Region description Region name Notes
Americas
Iowa us-central1 leaf icon Low CO2
Las Vegas us-west4
Los Angeles us-west2
Montréal northamerica-northeast1 leaf icon Low CO2
Northern Virginia us-east4
Oregon us-west1 leaf icon Low CO2
Salt Lake City us-west3
São Paulo southamerica-east1 leaf icon Low CO2
South Carolina us-east1
Toronto northamerica-northeast2
Europe
Belgium europe-west1 leaf icon Low CO2
Finland europe-north1 leaf icon Low CO2
Frankfurt europe-west3
London europe-west2
Netherlands europe-west4
Warsaw europe-central2
Zürich europe-west6 leaf icon Low CO2
Asia Pacific
Delhi asia-south2
Hong Kong asia-east2
Jakarta asia-southeast2
Melbourne australia-southeast2
Mumbai asia-south1
Osaka asia-northeast2
Seoul asia-northeast3
Singapore asia-southeast1
Sydney australia-southeast1
Taiwan asia-east1
Tokyo asia-northeast1

Multi-regional locations

Multi-region description Multi-region name
Data centers within member states of the European Union1 EU
Data centers in the United States US

1 Data located in the EU multi-region is not stored in the europe-west2 (London) or europe-west6 (Zürich) data centers.

Dataset security

To control access to datasets in BigQuery, see Controlling access to datasets. For information about data encryption, see Encryption at rest.

What's next

  • For more information about using transfers, including getting information about a transfer configuration, listing transfer configurations, and viewing a transfer's run history, see Working with transfers.