Migrate schema and data from Amazon Redshift

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

You can use the BigQuery Data Transfer Service to copy your data from an Amazon Redshift datawarehouse to BigQuery. The service engages migration agentsin GKE and triggers an unload operation from Amazon Redshift to astaging area in an Amazon S3 bucket. Then the BigQuery Data Transfer Service transfersyour data from the Amazon S3 bucket to BigQuery.

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

If you'd like to transfer data from your Amazon Redshift instance through a virtualprivate cloud (VPC) using private IP addresses, seeMigrating 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.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

Enable the BigQuery and BigQuery Data Transfer Service APIs.

Roles required to enable APIs

To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains theserviceusage.services.enable permission.Learn how to grant roles.

Enable the APIs

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

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

Enable the BigQuery and BigQuery Data Transfer Service APIs.

Roles required to enable APIs

To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains theserviceusage.services.enable permission.Learn how to grant roles.

Enable the APIs

Set required permissions

Before creating an Amazon Redshift transfer:

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

   • bigquery.transfers.update permissions to create the transfer
   • Bothbigquery.datasets.get andbigquery.datasets.update permissions onthe target dataset

   Theroles/bigquery.admin predefined Identity and Access Management (IAM) roleincludesbigquery.transfers.update,bigquery.datasets.update andbigquery.datasets.get permissions. For more information onIAM roles in BigQuery Data Transfer Service, seeAccess control.

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

Create a dataset

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

Grant access to your Amazon Redshift cluster

Add the following IP ranges of your private Amazon Redshift cluster to an allowlistbyconfiguring the security group rules.You can allowlist the IPaddresses that correspond to your dataset's location, or you can allowlist allof the IP addresses in the table below. These Google-owned IP addresses arereserved for Amazon Redshift data migrations.

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

1. We recommended that you create a dedicated Amazon IAM user, and grant thatuser 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 AmazonIAM user access key pair.

Configure workload control with a separate migration queue

Optionally, you candefine an Amazon Redshift queue for migration purposesto limit and separate the resources used for migration. You can configure thismigration queue with a maximum concurrency query count. You can then associate acertainmigration user groupwith the queue and use those credentials when setting up the migration totransfer data to BigQuery. The transfer service only hasaccess to the migration queue.

Gather transfer information

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

• Followthese instructions to get the JDBC URL.
• Get the username and password of a user with appropriate permissions toyour Amazon Redshift database.
• Follow the instructions atGrant access to your Amazon S3 bucket toget 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 aLifecycle policy for this bucket to avoid unnecessary charges. The recommendedexpiration time is 24 hours to allow sufficient time to transfer all datato BigQuery.

Assess your data

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

Set up an Amazon Redshift transfer

Select one of the following options:

bqmk\--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"}'

importcom.google.api.gax.rpc.ApiException;importcom.google.cloud.bigquery.datatransfer.v1.CreateTransferConfigRequest;importcom.google.cloud.bigquery.datatransfer.v1.DataTransferServiceClient;importcom.google.cloud.bigquery.datatransfer.v1.ProjectName;importcom.google.cloud.bigquery.datatransfer.v1.TransferConfig;importcom.google.protobuf.Struct;importcom.google.protobuf.Value;importjava.io.IOException;importjava.util.HashMap;importjava.util.Map;// Sample to create redshift transfer configpublicclassCreateRedshiftTransfer{publicstaticvoidmain(String[]args)throwsIOException{// TODO(developer): Replace these variables before running the sample.finalStringprojectId="MY_PROJECT_ID";StringdatasetId="MY_DATASET_ID";StringdatasetRegion="US";StringjdbcUrl="MY_JDBC_URL_CONNECTION_REDSHIFT";StringdbUserName="MY_USERNAME";StringdbPassword="MY_PASSWORD";StringaccessKeyId="MY_AWS_ACCESS_KEY_ID";StringsecretAccessId="MY_AWS_SECRET_ACCESS_ID";Strings3Bucket="MY_S3_BUCKET_URI";StringredShiftSchema="MY_REDSHIFT_SCHEMA";StringtableNamePatterns="*";StringvpcAndReserveIpRange="MY_VPC_AND_IP_RANGE";Map<String,Value>params=newHashMap<>();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());TransferConfigtransferConfig=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);}publicstaticvoidcreateRedshiftTransfer(StringprojectId,TransferConfigtransferConfig)throwsIOException{try(DataTransferServiceClientclient=DataTransferServiceClient.create()){ProjectNameparent=ProjectName.of(projectId);CreateTransferConfigRequestrequest=CreateTransferConfigRequest.newBuilder().setParent(parent.toString()).setTransferConfig(transferConfig).build();TransferConfigconfig=client.createTransferConfig(request);System.out.println("Cloud redshift transfer created successfully :"+config.getName());}catch(ApiExceptionex){System.out.print("Cloud redshift transfer was not created."+ex.toString());}}}

BigQuery has a load quota of 15 TB for each load job for eachtable.Internally, Amazon Redshift compresses the table data, so the exported table sizewill be larger than the table size reported by Amazon Redshift. If you planto migrate a table larger than 15 TB, please contactCloud Customer Care first.

Costs can be incurred outside of Google by using this service. Review theAmazon Redshift andAmazon S3 pricing pages for details.

Because ofAmazon S3's consistency model,it's possible that some files will not be included in the transfer toBigQuery.

What's next

• Learn aboutMigrating Amazon Redshift private instances with VPC.
• Learn more about theBigQuery Data Transfer Service.
• Migrate SQL code with theBatch SQL translation.