Manage datasets

Console

1. Enable the BigQuery Data Transfer Service for your destination dataset.

   Enable the BigQuery Data Transfer Service API

2. Ensure that you have the required roles.

   If you intend to set up transfer run notifications for Pub/Sub (Option 2 later in these steps), then you must have the pubsub.topics.setIamPolicy permission.

   If you only set up email notifications, then Pub/Sub permissions are not required. For more information, see the BigQuery Data Transfer Service run notifications.

3. Create a BigQuery dataset in the same region or a different region from your source dataset.

Option 1: Use the BigQuery copy function

To create a one-time transfer, use the BigQuery copy function:

1. Go to the BigQuery page.

   Go to BigQuery

2. In the Explorer panel, expand your project and select a dataset.

3. In the Dataset info section, click content_copy Copy, and then do the following:

   1. In the Dataset field, either create a new dataset or select an existing dataset ID from the list.

      Dataset names within a project must be unique. The project and dataset can be in different regions, but not all regions are supported for cross-region dataset copying.

      In the Location field, the location of the source dataset is displayed.

   2. Optional: To overwrite both the data and schema of the destination tables with the source tables, select the Overwrite destination tables checkbox. Both the source and destination tables must have the same partitioning schema.

   3. To copy the dataset, click Copy.

Option 2: Use the BigQuery Data Transfer Service

To schedule recurring copies and configure email or Pub/Sub notifications, use the BigQuery Data Transfer Service in the Google Cloud console of the destination project:

1. Go to the Data transfers page.

   Go to Data transfers

2. Click Create a transfer.

3. In the Source list, select Dataset Copy.

4. In the Display name field, enter a name for your transfer run.

5. In the Schedule options section, do the following:

   1. For Repeat frequency, choose an option for how often to run the transfer:

      If you select Custom, enter a custom frequency—for example, every day 00:00. For more information, see Formatting the schedule.

   2. For Start date and run time, enter the date and time to start the transfer. If you choose Start now, this option is disabled.

6. In the Destination settings section, select a destination dataset to store your transfer data. You can also click CREATE NEW DATASET to create a new dataset before you select it for this transfer.

7. In the Data source details section, enter the following information:

   1. For Source dataset, enter the dataset ID that you want to copy.
   2. For Source project, enter the project ID of your source dataset.

8. To overwrite both the data and schema of the destination tables with the source tables, select the Overwrite destination tables checkbox. Both the source and destination tables must have the same partitioning schema.

9. In the Service Account menu, select a service account from the service accounts associated with your Google Cloud project. You can associate a service account with your transfer instead of using your user credentials. For more information about using service accounts with data transfers, see Use service accounts.

   • If you signed in with a federated identity, then a service account is required to create a transfer. If you signed in with a Google Account, then a service account for the transfer is optional.
   • The service account must have the required roles.

10. Optional: In the Notification options section, do the following:

    • To enable email notifications, click the toggle. When you enable this option, the owner of the transfer configuration receives an email notification when a transfer run fails.
    • To enable Pub/Sub notifications, click the toggle, and then either select a topic name from the list or click Create a topic. This option configures Pub/Sub run notifications for your transfer.

11. Click Save.

bq

1. Enable the BigQuery Data Transfer Service for your destination dataset.

2. Ensure that you have the required roles.

3. To create a BigQuery dataset, use the bq mk command with the dataset creation flag --dataset and the location flag:

   bq mk \
     --dataset \
     --location=LOCATION \
     PROJECT:DATASET

   Replace the following:

   • LOCATION: the location where you want to copy the dataset
   • PROJECT: the project ID of your target dataset
   • DATASET: the name of the target dataset

4. To copy a dataset, use the bq mk command with the transfer creation flag --transfer_config and the --data_source flag. You must set the --data_source flag to cross_region_copy. For a complete list of valid values for the --data_source flag, see the transfer-config flags in the bq command-line tool reference.

   bq mk \
     --transfer_config \
     --project_id=PROJECT \
     --data_source=cross_region_copy \
     --target_dataset=DATASET \
     --display_name=NAME \
    --service_account_name=SERCICE_ACCOUNT \
     --params='PARAMETERS'

   Replace the following:

   • NAME: the display name for the copy job or the transfer configuration

   • SERVICE_ACCOUNT: the service account name used to authenticate your transfer. The service account should be owned by the same project_id used to create the transfer and it should have all of the required permissions.

   • PARAMETERS: the parameters for the transfer configuration in the JSON format

     Parameters for a dataset copy configuration include the following:

     • source_dataset_id: the ID of the source dataset that you want to copy
     • source_project_id: the ID of the project that your source dataset is in
     • overwrite_destination_table: an optional flag that lets you truncate the tables of a previous copy and refresh all the data

     Both the source and destination tables must have the same partitioning schema.

   The following examples show the formatting of the parameters, based on your system's environment:

   • Linux: use single quotes to enclose the JSON string–for example:

     '{"source_dataset_id":"mydataset","source_project_id":"mysourceproject","overwrite_destination_table":"true"}'
     

   • Windows command line: use double quotes to enclose the JSON string, and escape double quotes in the string with a backslash–for example:

     "{\"source_dataset_id\":\"mydataset\",\"source_project_id\":\"mysourceproject\",\"overwrite_destination_table\":\"true\"}"
     

   • PowerShell: use single quotes to enclose the JSON string, and escape double quotes in the string with a backslash–for example:

     '{\"source_dataset_id\":\"mydataset\",\"source_project_id\":\"mysourceproject\",\"overwrite_destination_table\":\"true\"}'
     

   For example, the following command creates a dataset copy configuration that's named My Transfer with a target dataset that's 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 Transfer' \
     --params='{
         "source_dataset_id":"123_demo_eu",
         "source_project_id":"mysourceproject",
         "overwrite_destination_table":"true"
         }'

API

1. Enable the BigQuery Data Transfer Service for your destination dataset.

2. Ensure that you have the required roles.

3. To create a BigQuery dataset, call the datasets.insert method with a defined dataset resource.

4. To copy a dataset, use the projects.locations.transferConfigs.create method and supply an instance of the TransferConfig resource.

Java

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",