Working with Transfers

After creating a transfer using the BigQuery Data Transfer Service, you can:

  • Get information about a transfer configuration
  • List transfer configurations
  • View a transfer's run history
  • View transfer run details such as log messages
  • Update a transfer
  • Set up a backfill
  • Update credentials
  • Disable a transfer
  • Delete a transfer

Getting information about a transfer configuration

After you create a transfer, you can get information about the transfer's configuration. The configuration includes the transfer's Resource Name and the values you supplied when you created the transfer.

Getting information about a transfer requires bigquery.transfers.get permissions. The following predefined IAM roles include bigquery.transfers.get permissions:

  • bigquery.admin
  • bigquery.user

A user granted the bigquery.transfers.get permissions can view information about all transfers in a project.

For more information on IAM roles in BigQuery, see Access Control.

To get information about a transfer configuration:

Web UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Transfers.

  3. On the Transfers page, click the appropriate transfer in the list.

  4. The transfer configuration appears on the properties page above the Run History. The following example shows the configuration properties for a Google AdWords transfer.

    Transfer configuration

Command-line

Enter the bq show command and provide the transfer configuration's resource name. The --format flag can be used to control the output format.

bq show --format=prettyjson --transfer_config [RESOURCE_NAME]

Where:

  • [RESOURCE_NAME] is the transfer's Resource Name (also referred to as the transfer configuration).

For example, enter the following command to display transfer configuration projects/myproject/locations/us/transferConfigs/1234a123-1234-1a23-1be9-12ab3c456de7.

bq show --format=prettyjson --transfer_config projects/myproject/locations/us/transferConfigs/1234a123-1234-1a23-1be9-12ab3c456de7

API

Use the projects.locations.transferConfigs.get method and supply the transfer configuration using the name parameter.

Listing transfer configurations

Listing transfer configurations in a project requires bigquery.transfers.get permissions. The following predefined IAM roles include bigquery.transfers.get permissions:

  • bigquery.admin
  • bigquery.user

A user granted the bigquery.transfers.get permissions can list all transfers in a project.

For more information on IAM roles in BigQuery, see Access Control.

To list transfer configurations in a project:

Web UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Transfers. The transfer configurations appear on the Transfers page grouped by location.

Command-line

To list all transfer configurations for a project by location, enter the bq ls command and supply the --transfer_location and --transfer_config flags. You can also supply the --project_id flag to specify a particular project. If --project_id isn't specified, the default project is used. The --format flag can be used to control the output format.

To list transfer configurations for particular data sources, supply the --filter flag.

To view a particular number of transfer configurations in paginated format, supply the --max_results flag to specify the number of transfers.

bq ls --transfer_config --transfer_location=[LOCATION] --project_id=[PROJECT_ID] --max_results=[INTEGER] --filter=dataSourceIds:[DATA_SOURCES]

Where:

  • [LOCATION] is the location of the transfer configurations. The location is specified when you create a transfer.
  • [PROJECT_ID] is your project ID.
  • [INTEGER] is the number of results to show per page.
  • [DATA_SOURCES] is one of the following:
    • adwords
    • dcm_dt (DoubleClick Campaign Manager)
    • dfp_dt (DoubleClick for Publishers)
    • youtube_channel
    • youtube_content_owner

Examples:

Enter the following command to display all transfer configurations in the US for your default project. The output is controlled using the --format flag.

bq ls --format=prettyjson --transfer_location=us --transfer_config

Enter the following command to display all transfer configurations in the US for project ID myproject.

bq ls --transfer_config --transfer_location=us --project_id=myproject

Enter the following command to list the 3 most recent transfer configurations.

bq ls --transfer_config --transfer_location=us --project_id=myproject --max_results=3

Enter the following command to list transfer configurations filtered to show only these data sources: AdWords and DoubleClick Campaign Manager.

bq ls --transfer_config --transfer_location=us --project_id=myproject --filter=dataSourceIds:dcm_dt,adwords

API

Use the projects.locations.transferConfigs.list method and supply the project ID using the parent parameter.

Viewing the run history

As your scheduled transfers are run, a run history is kept for each transfer configuration that includes successful transfer runs and transfer runs that fail. Transfer runs more than 90 days old are automatically deleted from the run history.

Viewing the run history for a transfer configuration requires bigquery.transfers.get permissions. The bigquery.user predefined IAM role includes bigquery.transfers.get permissions.

For more information on IAM roles in BigQuery, see Access Control.

To view the run history for a transfer configuration:

Web UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Transfers.

  3. On the Transfers page, click the appropriate transfer in the list.

  4. On the properties page, the run history appears below the transfer properties.

    Run history

  5. (Optional) Uncheck Show only latest run per day to see all transfer runs.

The status of the transfer run is indicated graphically:

Icon Run status
Run success icon The transfer run was successful.
Run failure icon The transfer run failed.
Run warning icon The transfer run was successful, but warnings were encountered. The run is not considered problematic unless the resulting tables appear abnormal.

Command-line

To list transfer runs for a particular transfer configuration, enter the bq ls command and supply the --transfer_location and --transfer_config flags. You can also supply the --project_id flag to specify a particular project. If [RESOURCE_NAME] doesn't contain project information, the --project_id value is used. If --project_id isn't specified, the default project is used. The --format flag can be used to control the output format.

To view a particular number of transfer runs, supply the --max_results flag.

bq ls --transfer_run --max_results=[INTEGER] --transfer_location=[LOCATION] --transfer_config [RESOURCE_NAME]

Where:

  • [INTEGER] is the number of results to return.
  • [LOCATION] is the location of the transfer configurations. The location is specified when you create a transfer.

  • [PROJECT_ID] is your project ID.
  • [RESOURCE_NAME] is the transfer's Resource Name (also referred to as the transfer configuration).

Examples:

Enter the following command to display the 3 latest runs for transfer configuration projects/myproject/locations/us/transferConfigs/1234a123-1234-1a23-1be9-12ab3c456de7. The output is controlled using the --format flag.

bq ls --format=prettyjson --transfer_run --max_results=3 --transfer_location=us --transfer_config projects/myproject/locations/us/transferConfigs/1234a123-1234-1a23-1be9-12ab3c456de7

API

Use the projects.locations.transferConfigs.list method and specify the project ID using the parent parameter.

Viewing transfer run details and log messages

When a transfer run appears in the run history, you can view the run details including log messages, warnings and errors, the run name, and the start and end time.

Viewing transfer run details requires bigquery.transfers.get permissions. The bigquery.user predefined IAM role includes bigquery.transfers.get permissions.

For more information on IAM roles in BigQuery, see Access Control.

To view transfer run details:

Web UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Transfers.

  3. On the Transfers page, click the appropriate transfer in the list.

  4. On the properties page, the run history appears below the transfer properties. Click a run to examine the details.

  5. In the run details, note the Run Name and any error messages. This information is needed if you contact Google Cloud Support. The run details also include log messages and warnings.

    Run details

Command-line

Enter the bq show command and provide the transfer run's run name using the --transfer_run flag. The --format flag can be used to control the output format.

bq show --format=prettyjson --transfer_run [RUN_NAME]

Where:

  • [RUN_NAME] is the transfer run's Run Name. You can retrieve the run name using the bq ls command.

Examples:

Enter the following command to display details for transfer run projects/myproject/locations/us/transferConfigs/1234a123-1234-1a23-1be9-12ab3c456de7.

bq show --format=prettyjson --transfer_run projects/myproject/locations/us/transferConfigs/1234a123-1234-1a23-1be9-12ab3c456de7

Enter the bq ls command with the --transfer_log flag to list transfer log messages for a transfer run.

bq ls --transfer_log --max_results=[INTEGER] --message_type=messageTypes:[MESSAGE_TYPE] [RUN_NAME]

Where:

  • [INTEGER] is the number of log messages to return.
  • [MESSAGE_TYPE] is the type of log message to view:
    • INFO
    • WARNING
    • ERROR
  • [RUN_NAME] is the transfer run's Run Name. You can retrieve the run name using the bq ls command.

Examples:

Enter the following command to view the first 2 log messages for transfer run projects/myproject/locations/us/transferConfigs/1234a123-1234-1a23-1be9-12ab3c456de7.

bq ls --transfer_log --max_results=2 projects/myproject/locations/us/transferConfigs/1234a123-1234-1a23-1be9-12ab3c456de7

Enter the following command to view only error messages for transfer run projects/myproject/locations/us/transferConfigs/1234a123-1234-1a23-1be9-12ab3c456de7.

bq ls --transfer_log --max_results=2 --message_type=messageTypes:ERROR projects/myproject/locations/us/transferConfigs/1234a123-1234-1a23-1be9-12ab3c456de7

API

Use the projects.transferConfigs.runs.transferLogs.list method and supply the transfer run's Run Name using the parent parameter.

Updating a transfer

Once the transfer is added, you can edit the transfer. You can edit any of the fields populated during transfer creation except for the Source:

  • Destination dataset
  • Display Name
  • Any of the parameters specified for the specific transfer type

Updating a transfer requires bigquery.transfers.update permissions. The bigquery.admin predefined IAM role includes bigquery.transfers.update permissions.

For more information on IAM roles in BigQuery, see Access Control.

To update a transfer:

Web UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Transfers.

  3. On the Transfers page, click the appropriate transfer in the list.

  4. Click Edit to update the transfer configuration.

  5. When you have finished making changes, click Save.

Command-line

Enter the bq update command, provide the transfer configuration's resource name using the --transfer_config flag, and supply the --display_name, --target_dataset, --refresh_window_days, or --params flags.

bq update --display_name='[NAME]' --target_dataset=[DATASET] --params='[PARAMETERS]' --refresh_window_days=[INTEGER] --transfer_config [RESOURCE_NAME]

Where:

  • --display_name is the display name for the transfer configuration.
  • --target_dataset is the target dataset for the transfer configuration.
  • --params contains the parameters for the transfer configuration in JSON format. For example: --params='{"param":"param_value"}'. The following parameters are editable:
    • Google AdWords: customer_id
    • DoubleClick Campaign Manager: bucket and network_id
    • DoubleClick for Publishers: bucket and network_code
    • YouTube Channel: page_id and table_suffix
    • YouTube Content Owner: content_owner_id and table_suffix
  • [INTEGER] is a value from 0 to 30. For information on setting the refresh window, see the documentation for your transfer type.
  • [RESOURCE_NAME] is the transfer's Resource Name (also referred to as the transfer configuration).

Examples:

Enter the following command to update the display name, target dataset, refresh window, and parameters for AdWords transfer projects/myproject/locations/us/transferConfigs/1234a123-1234-1a23-1be9-12ab3c456de7.

bq update --display_name='My changed transfer' --target_dataset=mydataset2 --refresh_window=3 --params='{"customer_id":"123-123-5678"}' --transfer_config projects/myproject/locations/us/transferConfigs/1234a123-1234-1a23-1be9-12ab3c456de7

API

Use the projects.transferConfigs.patch method and supply the transfer run's Run Name using the transferConfig.name parameter.

Set up a backfill

You can manually initiate data backfills at any time. In addition to source limits, the BigQuery Data Transfer Service supports a maximum of 180 days per backfill request. Simultaneous backfill requests are not supported.

When backfilling large date ranges, break your backfill requests into 180 day chunks and wait for the previous backfill request to finish before creating another one.

For information on how much data is available for backfill, see the documentation for your data source:

Scheduling a backfill requires bigquery.transfers.update permissions. The bigquery.admin predefined IAM role includes bigquery.transfers.update permissions.

For more information on IAM roles in BigQuery, see Access Control.

To schedule a backfill:

Web UI

  1. Go to the BigQuery web UI. BigQuery web UI
  2. Click Transfers.
  3. On the Transfer page, click the appropriate transfer.
  4. Click Schedule Backfill.

    Schedule backfill

  5. In the Schedule Run dialog:

    • For Data Start Date, enter or choose the appropriate date in MM/DD/YYYY format.
    • For Data End Date, enter or choose the appropriate date in MM/DD/YYYY format.
    • Click Ok.

    Schedule backfill

Command-line

Enter the bq mk command, supply the --transfer_run flag, and specify the --start_time and --end_time.

bq mk --transfer_run --start_time='[START_TIME]' --end_time='[END_TIME]' [RESOURCE_NAME]

Where:

  • [START_TIME] and [END_TIME] are timestamps that end in Z or contain a valid time zone offset. For example:

  • 2017-08-19T12:11:35.00Z

  • 2017-05-25T00:00:00+00:00

  • [RESOURCE_NAME] is the transfer's Resource Name (also referred to as the transfer configuration).

Examples:

Enter the following command to schedule a backfill for transfer configuration projects/myproject/locations/us/transferConfigs/1234a123-1234-1a23-1be9-12ab3c456de7.

bq mk --transfer_run --start_time 2017-05-25T00:00:00Z --end_time 2017-05-25T00:00:00Z projects/myproject/locations/us/transferConfigs/1234a123-1234-1a23-1be9-12ab3c456de7

API

Use the projects.locations.transferConfigs.scheduleRuns method and provide the transfer configuration resource using the parent parameter.

Updating credentials

A transfer uses the credentials of the user that created it. If you need to change the user attached to a transfer configuration, you can update the transfer's credentials. This is useful if the user who created the transfer is no longer with your organization.

Updating credentials requires bigquery.transfers.update permissions. The bigquery.admin predefined IAM role includes bigquery.transfers.update permissions.

For more information on IAM roles in BigQuery, see Access Control.

To update the credentials for a transfer:

Web UI

  1. Go to the BigQuery web UI. BigQuery web UI
  2. Click Transfers.
  3. On the Transfer page, click the appropriate transfer.
  4. Click Update Credentials.
  5. Click OK when prompted.
  6. Sign into your Google account and click Allow to give the BigQuery Transfer Service permission to view your reporting data and to access and manage the data in BigQuery. You must allow pop-ups from bigquery.cloud.google.com to view the permissions window.

Command-line

Enter the bq update command, provide the transfer configuration's resource name using the --transfer_config flag, and supply the --update_credentials flag.

bq update --update_credentials=[BOOLEAN] --transfer_config [RESOURCE_NAME]

Where:

  • --update_credentials is a boolean value indicating whether the credentials should be updated for the transfer configuration.
  • [RESOURCE_NAME] is the transfer's Resource Name (also referred to as the transfer configuration).

Examples:

Enter the following command to update the credentials for AdWords transfer projects/myproject/locations/us/transferConfigs/1234a123-1234-1a23-1be9-12ab3c456de7.

bq update --update_credentials=true --transfer_config projects/myproject/locations/us/transferConfigs/1234a123-1234-1a23-1be9-12ab3c456de7

API

Use the projects.transferConfigs.patch method and supply the authorizationCode and updateMask parameters.

Disabling a transfer

When you disable a transfer, [DISABLED] is added to the transfer name. When the transfer is disabled, no new transfer runs are scheduled, and no new backfills are allowed. Any transfer runs in progress are completed.

Disabling a transfer does not remove any data already transferred to BigQuery. Data previously transferred incurs standard BigQuery storage costs until you delete the dataset or delete the tables.

Disabling a transfer requires bigquery.transfers.update permissions. The bigquery.admin predefined IAM role includes bigquery.transfers.update permissions.

For more information on IAM roles in BigQuery, see Access Control.

To disable a transfer:

Web UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Transfers.

  3. Click the transfer you're disabling.
  4. On the transfer properties page, click Disabled.

    Disable transfer

  5. After disabling the transfer, click Save.

Command-line

Disabling a transfer is not supported by the CLI.

API

Use the projects.locations.transferConfigs.patch method and set disabled to true in the projects.locations.transferConfig resource.

Deleting a transfer

When a transfer is deleted, no new transfer runs are scheduled. Any transfer runs in progress are stopped.

Deleting a transfer does not remove any data already transferred to BigQuery. Data previously transferred incurs standard BigQuery storage costs until you delete the dataset or delete the tables.

Deleting a transfer requires bigquery.transfers.update permissions. The bigquery.admin predefined IAM role includes bigquery.transfers.update permissions.

For more information on IAM roles in BigQuery, see Access Control.

To delete a transfer:

Web UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Transfers.

  3. Click the transfer you're editing.
  4. On the transfer properties page, click Delete.
  5. When prompted, click Ok to remove the transfer.

Command-line

Enter the bq rm command and provide the transfer configuration's resource name. You can use the -f flag to delete a transfer config without confirmation.

bq rm -f --transfer_config [RESOURCE_NAME]

Where:

  • [RESOURCE_NAME] is the transfer's Resource Name (also referred to as the transfer configuration).

For example, enter the following command to delete transfer configuration projects/myproject/locations/us/transferConfigs/1234a123-1234-1a23-1be9-12ab3c456de7.

bq rm --transfer_config projects/myproject/locations/us/transferConfigs/1234a123-1234-1a23-1be9-12ab3c456de7

API

Use the projects.locations.transferConfigs.delete method and supply the resource you're deleting using the name parameter.

Send feedback about...