Migrate schema and data from Amazon Redshift

This document describes the process of migrating data from Amazon Redshift to BigQuery using public IP addresses.

You can use the BigQuery Data Transfer Service to copy your data from an Amazon Redshift data warehouse to BigQuery. The service engages migration agents in GKE and triggers an unload operation from Amazon Redshift to a staging area in an Amazon S3 bucket. Then the BigQuery Data Transfer Service transfers your data from the Amazon S3 bucket to BigQuery.

This diagram shows the overall flow of data between an Amazon Redshift data warehouse and BigQuery during a migration.

If you'd like to transfer data from your Amazon Redshift instance through a virtual private cloud (VPC) using private IP addresses, see Migrating Amazon Redshift data with VPC .

Before you begin

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Go to project selector

Verify that billing is enabled for your Google Cloud project .

Enable the BigQuery and BigQuery Data Transfer Service APIs.

Enable the APIs

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Go to project selector

Verify that billing is enabled for your Google Cloud project .

Enable the BigQuery and BigQuery Data Transfer Service APIs.

Enable the APIs

Set required permissions

Before creating an Amazon Redshift transfer:

1. Ensure that the principal creating the transfer has the following permissions in the project containing the transfer job:

   • bigquery.transfers.update permissions to create the transfer
   • Both bigquery.datasets.get and bigquery.datasets.update permissions on the target dataset

   The roles/bigquery.admin predefined Identity and Access Management (IAM) role includes bigquery.transfers.update , bigquery.datasets.update and bigquery.datasets.get permissions. For more information on IAM roles in BigQuery Data Transfer Service, see Access control .

2. Consult the documentation for Amazon S3 to ensure you have configured any permissions necessary to enable the transfer. At a minimum, the Amazon S3 source data must have the AWS managed policy AmazonS3ReadOnlyAccess applied to it.

Create a dataset

Create a BigQuery dataset to store your data. You do not need to create any tables.

Grant access to your Amazon Redshift cluster

Follow the instructions in Configure inbound rules for SQL clients to allowlist the following IP addresses. You can allowlist the IP addresses that correspond to your dataset's location, or you can allowlist all of the IP addresses in the table below. These Google-owned IP addresses are reserved for Amazon Redshift data migrations.

Regional locations

Multi-regional locations

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

Grant access to your Amazon S3 bucket

You must have an Amazon S3 bucket to use as a staging area to transfer the Amazon Redshift data to BigQuery. For detailed instructions, see the Amazon documentation .

1. We recommended that you create a dedicated Amazon IAM user, and grant that user only Read access to Amazon Redshift and Read and Write access to Amazon S3. To achieve this step, you can apply the following policies:

   

2. Create an Amazon IAM user access key pair .

Configure workload control with a separate migration queue

Optionally, you can define an Amazon Redshift queue for migration purposes to limit and separate the resources used for migration. You can configure this migration queue with a maximum concurrency query count. You can then associate a certain migration user group with the queue and use those credentials when setting up the migration to transfer data to BigQuery. The transfer service only has access to the migration queue.

Gather transfer information

Gather the information that you need to set up the migration with the BigQuery Data Transfer Service:

• Follow these instructions to get the JDBC URL.
• Get the username and password of a user with appropriate permissions to your Amazon Redshift database.
• Follow the instructions at Grant access to your Amazon S3 bucket to get an AWS access key pair.
• Get the URI of the Amazon S3 bucket you want to use for the transfer. We recommend that you set up a Lifecycle policy for this bucket to avoid unnecessary charges. The recommended expiration time is 24 hours to allow sufficient time to transfer all data to BigQuery.

Assess your data

As part of the data transfer, BigQuery Data Transfer Service writes data from Amazon Redshift to Cloud Storage as CSV files. If these files contain the ASCII 0 character, they can't be loaded into BigQuery. We suggest you assess your data to determine if this could be an issue for you. If it is, you can work around this by exporting your data to Amazon S3 as Parquet files, and then importing those files by using BigQuery Data Transfer Service. For more information, see Overview of Amazon S3 transfers .

Set up an Amazon Redshift transfer

Select one of the following options:

bq  
mk  
 \ 
  
--transfer_config  
 \ 
  
--project_id = 
 project_id 
  
 \ 
  
--data_source = 
 data_source 
  
 \ 
  
--target_dataset = 
 dataset 
  
 \ 
  
--display_name = 
 name 
  
 \ 
  
--service_account_name = 
 service_account 
  
 \ 
  
--params = 
 ' parameters 
' 

 bq mk \
    --transfer_config \
    --project_id=myproject \
    --data_source=redshift \
    --target_dataset=mydataset \
    --display_name='My Transfer' \
    --params='{"jdbc_url":"jdbc:postgresql://test-example-instance.sample.us-west-1.redshift.amazonaws.com:5439/dbname","database_username":"my_username","database_password":"1234567890","access_key_id":"A1B2C3D4E5F6G7H8I9J0","secret_access_key":"1234567890123456789012345678901234567890","s3_bucket":"s3://bucket/prefix","redshift_schema":"public","table_name_patterns":"table_name"}' 

  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 create redshift transfer config 
 public 
  
 class 
 CreateRedshiftTransfer 
  
 { 
  
 public 
  
 static 
  
 void 
  
 main 
 ( 
 String 
 [] 
  
 args 
 ) 
  
 throws 
  
 IOException 
  
 { 
  
 // TODO(developer): Replace these variables before running the sample. 
  
 final 
  
 String 
  
 projectId 
  
 = 
  
 "MY_PROJECT_ID" 
 ; 
  
 String 
  
 datasetId 
  
 = 
  
 "MY_DATASET_ID" 
 ; 
  
 String 
  
 datasetRegion 
  
 = 
  
 "US" 
 ; 
  
 String 
  
 jdbcUrl 
  
 = 
  
 "MY_JDBC_URL_CONNECTION_REDSHIFT" 
 ; 
  
 String 
  
 dbUserName 
  
 = 
  
 "MY_USERNAME" 
 ; 
  
 String 
  
 dbPassword 
  
 = 
  
 "MY_PASSWORD" 
 ; 
  
 String 
  
 accessKeyId 
  
 = 
  
 "MY_AWS_ACCESS_KEY_ID" 
 ; 
  
 String 
  
 secretAccessId 
  
 = 
  
 "MY_AWS_SECRET_ACCESS_ID" 
 ; 
  
 String 
  
 s3Bucket 
  
 = 
  
 "MY_S3_BUCKET_URI" 
 ; 
  
 String 
  
 redShiftSchema 
  
 = 
  
 "MY_REDSHIFT_SCHEMA" 
 ; 
  
 String 
  
 tableNamePatterns 
  
 = 
  
 "*" 
 ; 
  
 String 
  
 vpcAndReserveIpRange 
  
 = 
  
 "MY_VPC_AND_IP_RANGE" 
 ; 
  
 Map<String 
 , 
  
 Value 
>  
 params 
  
 = 
  
 new 
  
 HashMap 
<> (); 
  
 params 
 . 
 put 
 ( 
 "jdbc_url" 
 , 
  
  Value 
 
 . 
 newBuilder 
 (). 
 setStringValue 
 ( 
 jdbcUrl 
 ). 
 build 
 ()); 
  
 params 
 . 
 put 
 ( 
 "database_username" 
 , 
  
  Value 
 
 . 
 newBuilder 
 (). 
 setStringValue 
 ( 
 dbUserName 
 ). 
 build 
 ()); 
  
 params 
 . 
 put 
 ( 
 "database_password" 
 , 
  
  Value 
 
 . 
 newBuilder 
 (). 
 setStringValue 
 ( 
 dbPassword 
 ). 
 build 
 ()); 
  
 params 
 . 
 put 
 ( 
 "access_key_id" 
 , 
  
  Value 
 
 . 
 newBuilder 
 (). 
 setStringValue 
 ( 
 accessKeyId 
 ). 
 build 
 ()); 
  
 params 
 . 
 put 
 ( 
 "secret_access_key" 
 , 
  
  Value 
 
 . 
 newBuilder 
 (). 
 setStringValue 
 ( 
 secretAccessId 
 ). 
 build 
 ()); 
  
 params 
 . 
 put 
 ( 
 "s3_bucket" 
 , 
  
  Value 
 
 . 
 newBuilder 
 (). 
 setStringValue 
 ( 
 s3Bucket 
 ). 
 build 
 ()); 
  
 params 
 . 
 put 
 ( 
 "redshift_schema" 
 , 
  
  Value 
 
 . 
 newBuilder 
 (). 
 setStringValue 
 ( 
 redShiftSchema 
 ). 
 build 
 ()); 
  
 params 
 . 
 put 
 ( 
 "table_name_patterns" 
 , 
  
  Value 
 
 . 
 newBuilder 
 (). 
 setStringValue 
 ( 
 tableNamePatterns 
 ). 
 build 
 ()); 
  
 params 
 . 
 put 
 ( 
  
 "migration_infra_cidr" 
 , 
  
  Value 
 
 . 
 newBuilder 
 (). 
 setStringValue 
 ( 
 vpcAndReserveIpRange 
 ). 
 build 
 ()); 
  
  TransferConfig 
 
  
 transferConfig 
  
 = 
  
  TransferConfig 
 
 . 
 newBuilder 
 () 
  
 . 
 setDestinationDatasetId 
 ( 
 datasetId 
 ) 
  
 . 
  setDatasetRegion 
 
 ( 
 datasetRegion 
 ) 
  
 . 
 setDisplayName 
 ( 
 "Your Redshift Config Name" 
 ) 
  
 . 
 setDataSourceId 
 ( 
 "redshift" 
 ) 
  
 . 
 setParams 
 ( 
  Struct 
 
 . 
 newBuilder 
 (). 
  putAllFields 
 
 ( 
 params 
 ). 
 build 
 ()) 
  
 . 
 setSchedule 
 ( 
 "every 24 hours" 
 ) 
  
 . 
 build 
 (); 
  
 createRedshiftTransfer 
 ( 
 projectId 
 , 
  
 transferConfig 
 ); 
  
 } 
  
 public 
  
 static 
  
 void 
  
 createRedshiftTransfer 
 ( 
 String 
  
 projectId 
 , 
  
  TransferConfig 
 
  
 transferConfig 
 ) 
  
 throws 
  
 IOException 
  
 { 
  
 try 
  
 ( 
  DataTransferServiceClient 
 
  
 client 
  
 = 
  
  DataTransferServiceClient 
 
 . 
 create 
 ()) 
  
 { 
  
  ProjectName 
 
  
 parent 
  
 = 
  
  ProjectName 
 
 . 
 of 
 ( 
 projectId 
 ); 
  
  CreateTransferConfigRequest 
 
  
 request 
  
 = 
  
  CreateTransferConfigRequest 
 
 . 
 newBuilder 
 () 
  
 . 
 setParent 
 ( 
 parent 
 . 
  toString 
 
 ()) 
  
 . 
 setTransferConfig 
 ( 
 transferConfig 
 ) 
  
 . 
 build 
 (); 
  
  TransferConfig 
 
  
 config 
  
 = 
  
 client 
 . 
 createTransferConfig 
 ( 
 request 
 ); 
  
 System 
 . 
 out 
 . 
 println 
 ( 
 "Cloud redshift transfer created successfully :" 
  
 + 
  
 config 
 . 
  getName 
 
 ()); 
  
 } 
  
 catch 
  
 ( 
  ApiException 
 
  
 ex 
 ) 
  
 { 
  
 System 
 . 
 out 
 . 
 print 
 ( 
 "Cloud redshift transfer was not created." 
  
 + 
  
 ex 
 . 
 toString 
 ()); 
  
 } 
  
 } 
 } 
 

Quotas and limits

BigQuery has a load quota of 15 TB for each load job for each table. Internally, Amazon Redshift compresses the table data, so the exported table size will be larger than the table size reported by Amazon Redshift. If you plan to migrate a table larger than 15 TB, please contact Cloud Customer Care first.

Costs can be incurred outside of Google by using this service. Review the Amazon Redshift and Amazon S3 pricing pages for details.

Because of Amazon S3's consistency model , it's possible that some files will not be included in the transfer to BigQuery.

What's next

• Learn about Migrating Amazon Redshift private instances with VPC .
• Learn more about the BigQuery Data Transfer Service .
• Migrate SQL code with the Batch SQL translation .