BigQuery Data Transfer Service error from 28th October

[s-furu]

When using BigQuery's DataTransferService to perform cross-region dataset copy, the following error occurs for a specific table:

Failed to start job for table {table_name} with error INVALID_ARGUMENT: Cross-region table copy for tables with column level DDL operations is not supported.

The table is a regular table and does not contain nested columns.

Until 28th October, I was able to perform the table copy without any issues. However, starting from that date, I started encountering the above error and the copy fails.

Other tables can be copied without any issues, so it is unclear why only this table cannot be copied.

Does anyone have any ideas?

Environment:
• source: asia-northeast1
• destination: asia-northeast2
• Source table information:
   ◦ Number of partitions: 107
   ◦ Total logical bytes: 43 GB
   ◦ Total physical bytes: 2 GB

Attempts:
• Tried deleting the destination table and retrying the copy.

[ms4446 · Accepted solution]

The error message "Cross-region table copy for tables with column level DDL operations is not supported" indicates that BigQuery's Data Transfer Service cannot copy tables that have had changes to the column definition after their initial creation. This includes operations such as adding or removing columns and changing the data type of a column.

Troubleshooting:

If you are unable to identify any column level DDL operations that have been performed on the table, you can try the following:

• Export the table to a Cloud Storage bucket in the source region.
• Import the table from the Cloud Storage bucket in the destination region.

If you are still unable to copy the table, consider reaching out to Google Cloud support for assistance.

Additional Troubleshooting Tips:

• Ensure you have the necessary permissions if the source and destination datasets are in different projects.
• Confirm that the source and destination datasets are in supported regions.
• Ensure that the destination table does not already exist.
• Consider creating a new transfer configuration and running the transfer again.

Request for More Information:

If the issue persists despite the above steps, please provide more details about your environment, such as:

• The name of the source and destination datasets.
• The schema of the source table.
• Any recent changes to the table schema.

[s-furu]

Hi ms4446,

Thank you for your good explanation and solution.

I finally solved this problem by following.

• Copy original table within the same dataset
• Drop original table
• Rename copied table to original name
• Rerun dataset copy job on Data Transfer Service