Configure a Redshift data source

Step 1: Prepare the external data source

To read data from Redshift, complete the preparations in the following table. See the steps below for detailed instructions.

1. Ensure that the Redshift cluster or serverless workgroup is created and available.

2. Get the JDBC connection URL

   Get the JDBC connection URL for Redshift. You will need this URL when adding the data source in MMS.

   • Cluster mode format: jdbc:redshift://cluster-name.xxx.redshift.amazonaws.com:5439/

   • Serverless mode format: jdbc:redshift://xxx.redshift-serverless.amazonaws.com:5439/

3. Configure network and security groups

   MMS is an Alibaba Cloud service. To access AWS Redshift across clouds, you must ensure network connectivity.

   The simplest method is to enable public access from MMS to AWS Redshift, as shown in the following example:

   1. Ensure that your Redshift instance is configured to be publicly accessible.

   2. Add the egress IP address CIDR blocks of MMS to the security group inbound rule associated with your Redshift instance.

   The following is an example of a security group inbound rule for public access:

   Type	Port	Source	Description
   Redshift	5439	MMS egress IP addresses	Allows MMS to access Redshift.

   Warning

   As a security best practice, open only the necessary IP address ranges. Avoid using 0.0.0.0/0.

4. Prepare an AWS Access Key

   Prepare an AWS Access Key ID and a Secret Access Key with read permissions for S3. MaxCompute Spark uses these credentials to read data from the temporary S3 directory.

   The following table lists the minimum required permissions.

   Permission	Description	Required
   s3:GetObject	Allows reading data files from the temporary S3 directory.	Yes
   s3:ListBucket	Allows listing objects in the S3 bucket.	Yes

   The following is an example of a policy with the minimum required permissions:

   {
     "Version": "2012-10-17",
     "Statement": [
       {
         "Effect": "Allow",
         "Action": [
           "s3:GetObject",
           "s3:ListBucket"
         ],
         "Resource": [
           "arn:aws:s3:::my-bucket",
           "arn:aws:s3:::my-bucket/*"
         ]
       }
     ]
   }

5. Create an S3 bucket (temporary directory)

   • Create an S3 bucket to store temporary data during migration.

   • We recommend that you configure a lifecycle policy to automatically clean up temporary data and reduce storage costs.

6. Create and associate an IAM role

   Create an IAM role that allows Redshift to write to S3, and associate it with your Redshift cluster or serverless configuration.

   Important

   The IAM role must be associated with the Redshift cluster or serverless configuration. Otherwise, the UNLOAD operation fails with the error message "The requested role is not associated to cluster".

   1. Required S3 permissions

      Permission	Description	Required
      s3:PutObject	Allows Redshift to export data to S3 by using the UNLOAD command.	Yes
      s3:GetObject	Allows Redshift to read data from S3.	Yes
      s3:ListBucket	Allows MaxCompute Spark to list objects in the S3 bucket.	Yes

   2. Associate the IAM role with Redshift

      The association method varies between cluster mode and serverless mode.

      Cluster mode

      1. Log on to the AWS Redshift console.

      2. Select the target cluster and click Actions.

      3. Click Manage IAM roles.

      4. Select the IAM role you created, click Associate IAM role, and save the changes.

      Serverless mode

      1. Log on to the AWS Redshift console.

      2. In the left-side navigation pane, select Redshift Serverless.

      3. Select Namespaces and click the target namespace.

      4. On the Security and encryption tab, find Permissions.

      5. Click Manage IAM roles, add the IAM role you created, and save the changes.

   3. IAM role configuration examples

      • Trust policy (allows the Redshift service to assume the role):

        {
          "Version": "2012-10-17",
          "Statement": [
            {
              "Effect": "Allow",
              "Principal": {
                "Service": [
                  "redshift.amazonaws.com",
                  "redshift-serverless.amazonaws.com"
                ]
              },
              "Action": "sts:AssumeRole"
            }
          ]
        }

      • Minimum permissions policy (includes only required permissions):

        {
          "Version": "2012-10-17",
          "Statement": [
            {
              "Effect": "Allow",
              "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:ListBucket"
              ],
              "Resource": [
                "arn:aws:s3:::my-bucket",
                "arn:aws:s3:::my-bucket/*"
              ]
            }
          ]
        }

7. Database user permissions

   The Redshift database user needs the following permissions:

   Permission type	Description
   Permissions to read system tables	SELECT permission on system tables such as pg_class, pg_namespace, pg_attribute, pg_type, pg_constraint, and svv_redshift_schemas. Regular users have this permission by default.
   Permissions to read tables	SELECT permission on the tables to be migrated.
   Permissions to write to S3	The IAM role authorizes Redshift to write to the temporary S3 directory.

Step 2: Add a data source

1. Log in to the MaxCompute console and select a region in the upper-left corner.

2. In the left-side navigation pane, choose Data Transfer > Migration Service.

3. On the Data Source tab, click Add Data Source.

   To create the service-linked role, click OK in the MaxCompute Service-linked Role dialog box. If this dialog box does not appear, the role already exists.

   On the Add Data Source page, configure the data source and click Add.

   • Data Source Basic Information

     Parameter	Required	Description
     Data Source Name	Yes	The custom name for the data source. The name can only contain letters and digits.
     Data Source Type	Yes	Select Redshift.
     Network Connection	Yes	Select the network connection to use. Network connections are created in the MaxCompute Manage Configurations > Network Connection module and allow MMS to communicate with the VPC and connect to the data source.
     AWS Access Key ID	Yes	The ID of your AWS access key. MaxCompute Spark uses this key to read migration data from the temporary S3 directory. For information about how to obtain an access key, see Creating access keys for an IAM user.
     AWS Secret Access Key	Yes	The secret for your AWS access key. Used with the AWS Access Key ID, this secret allows MaxCompute Spark to access the temporary S3 directory.
     Redshift JDBC URL	Yes	The JDBC connection URL for Redshift, without the database name, username, or password. Format: jdbc:redshift://endpoint:5439/. Cluster mode example: jdbc:redshift://cluster-name.xxx.redshift.amazonaws.com:5439/. Serverless mode example: jdbc:redshift://xxx.redshift-serverless.amazonaws.com:5439/.
     Redshift Database Name	Yes	The name of the Redshift database.
     Redshift Username	Yes	The username for the Redshift database.
     Redshift Password	Yes	The password for the Redshift database. MMS uses the password to connect to Redshift, get metadata (such as table schemas, column information, and constraints), and run UNLOAD operations to export data to S3.
     S3 Temporary Directory Region	No	The AWS region where the temporary S3 directory is located. Specifying the region can prevent cross-region issues and auto-discovery delays. Examples: us-east-1, us-west-2.
     S3 Temporary Directory	Yes	The temporary S3 directory used to store data from UNLOAD operations. Format: s3a://bucket/path/. After migration, you can configure an S3 lifecycle policy to automatically clean up the temporary data.
     AWS IAM Role	Yes	The IAM role that Redshift uses to write to S3. Redshift assumes this role to UNLOAD data into the temporary S3 directory. Format: arn:aws:iam::account-id:role/role-name. For more information, see Creating IAM roles and the UNLOAD documentation in the AWS documentation.
     Default Destination MaxCompute Project	Yes	The target project for data migration. This setting cannot be modified after the data source is created.
     Destination MaxCompute Project List	No	If data from a single data source needs to be migrated to multiple destination projects, specify the list of target MaxCompute projects.
     Project for running migration jobs	Yes	The project where MaxCompute runs migration jobs, such as Spark and SQL jobs. This project's default computing quota is used.

   • Other information

     The following parameters are optional. Configure them as needed.

     Parameters

     Parameter	Description
     Scheduled metastore update	Specifies whether to periodically pull metadata from the data source. Enable: Periodically pulls metadata. You can set the update frequency to daily or hourly and configure the start time. Daily update: The scheduler runs once a day at a specific time (accurate to the minute). Hourly update: The scheduler runs once an hour at a specific minute within the hour. Disable: Does not pull metadata on a schedule.
     Metastore Access Concurrency	The number of concurrent requests to the metastore. Default value: 3.
     Schema allowlist	The schemas to migrate. Separate multiple values with commas (,).
     Schema blocklist	The schemas not to migrate. Separate multiple values with commas (,).
     Table blocklist	The Redshift data tables not to migrate. The format for a single table is schema.table or table. Separate multiple tables with commas (,).
     Table allowlist	The Redshift data tables to migrate. The format for a single table is schema.table or table. Separate multiple tables with commas (,).
     Max Concurrency for Data Migration Tasks	The maximum number of concurrent migration jobs in MMS. A high concurrency may overload the source data source. Default value: 20.
     MaxCompute Migration Task SQL Parameters	SQL parameters. For more information, see Flag parameter list.
     Table Name Character Conversion Configuration	In JSON format, such as {"%": "_"}. Maps special characters in source table names to characters supported by MaxCompute.
     Column Name Character Conversion Configuration
     Max Partitions per Task	The number of partitions migrated in a single MMS task. Migrating partitions in batches reduces the number of SQL submissions and saves time. You do not typically need to change this setting. Default value: 50.
     Max Data Size per Task (GB)	You do not typically need to change this setting. Default value: 5 GB.
     Partition Special Character Mapping	In JSON format, such as {"/": "_"}. Maps special characters in source partition values to characters supported by MaxCompute.

Step 3: Sync metadata

After you start the data source, MMS creates a job instance. This instance connects the data source to MMS and synchronizes source metadata to MMS. You need this metadata to configure migration jobs.

1. On the Data Source tab, for the target data source, click Update metadata in the Actions column.

2. The Data Source tab shows the Status of the target data source.

3. If metadata changes occur and you enabled metastore scheduled update, MMS updates metadata automatically on schedule. No manual sync is needed.

Troubleshooting connection issues

1. JDBC connection failures

   • Ensure the network connection is configured correctly.

   • Ensure the JDBC URL format is correct.

   • Ensure the username and password are correct.

2. S3 permission issues

   • Ensure the AWS Access Key has S3 read permissions.

   • Ensure the IAM Role is correctly associated with the Redshift cluster or serverless namespace.

   • Ensure the S3 bucket policy allows access.

Performance optimization

1. S3 temporary directory region

   • Create the S3 temporary directory in the same region as Redshift to avoid cross-region data transfer.

   • Explicitly configure the redshift.tempdir.region parameter to prevent auto-discovery delays.

2. S3 lifecycle policy

   • Configure a lifecycle policy for the S3 temporary directory to automatically delete expired data.

   • Set temporary files to be automatically deleted after 7 days.