The combination of the BigQuery Data Transfer Service and a special migration agent allows you to copy data from an on-premises data warehouse system to BigQuery. This document provides an overview of the migration process.
To use the BigQuery Data Transfer Service for data warehouse migrations, first you download an on-premises agent that connects to your local data warehouse. Then you configure a transfer in the BigQuery Data Transfer Service. The on-premises agent communicates with the BigQuery Data Transfer Service to copy tables from your data warehouse to BigQuery. You can monitor recurring data loads from the data warehouse to BigQuery via the BigQuery Data Transfer Service's web UI.
This diagram shows the overall flow of data between a data warehouse and BigQuery.
- If your BigQuery dataset is in a multi-regional location, the Cloud Storage bucket containing the data you're loading must be in a regional or multi-regional bucket in the same location. For example, if your BigQuery dataset is in the EU, the Cloud Storage bucket must be in a regional or multi-regional bucket in the EU.
- If your dataset is in a regional location, your Cloud Storage bucket must be a regional bucket in the same location. For example, if your dataset is in the Tokyo region, your Cloud Storage bucket must be a regional bucket in Tokyo.
- Exception: If your dataset is in the US multi-regional location, you can load data from a Cloud Storage bucket in any regional or multi-regional location.
Restrictions and limitations
- Only tables can be migrated. Other database resources, such as saved queries, views, user-defined functions, and stored procedures are not supported.
- Data is extracted to a folder on the local filesystem (on-premises). You need to ensure there is enough free space for the data.
- The extracted data from the data warehouse is not encrypted. Take appropriate steps to restrict access to the extracted files in the local folder, and ensure the Cloud Storage bucket is properly secured.
- The speed of the extraction is bounded by your JDBC connection.
Quotas and limits
The BigQuery Data Transfer Service for data warehouse migrations uses load jobs to load data warehouse data into BigQuery. Each table extracted from your data warehouse is loaded into BigQuery independently via a load job. All BigQuery Quotas & limits on load jobs apply.