:Data migration from Amazon Redshift to MaxCompute

Step 1: Unload data from Redshift to S3

Amazon Redshift supports two authentication methods: IAM roles and temporary security credentials. You can use the Amazon Redshift UNLOAD command to unload data to Amazon S3 with either method. For detailed instructions, see Unloading data.

Follow these steps to migrate your data using IAM role authentication and the Parquet format.

1. Create an IAM role for Amazon Redshift.

   1. Log on to the IAM console and click Create Role.

   2. On the Create Role page, in the Select trusted entity section, choose AWS service. For Use case, choose Redshift - Customizable, and then click Next.

2. Add a permission policy for read and write access to Amazon S3. On the Create Role page, in the Attach permission policy section, enter S3, select AmazonS3FullAccess, and click Next: Tags.

3. Name the IAM role and complete its creation.

   1. Click Next: Review. On the Create Role page, in the Review section, set the Role Name and Role Description, and click Create Role to complete the IAM role creation.

      For Role name, enter redshift_s3_role. Confirm that the trusted entity is redshift.amazonaws.com and the permission policy is AmazonS3FullAccess.

   2. Return to the IAM console, enter redshift_s3_role in the search box, click the redshift_s3_role role name, and obtain and record the role ARN.

      You use the role ARN to access Amazon S3 when running the UNLOAD command. The Role ARN is displayed at the top of the role's summary page. It is in the format arn:aws:iam::<AccountID>:role/redshift_s3_role. Copy and record this value.

4. Associate the IAM role with the Amazon Redshift cluster.

   1. Log on to the Amazon Redshift console. In the upper-right corner, select the Asia Pacific (Singapore) region.

   2. In the left-side navigation pane, click Cluster. Select your Amazon Redshift cluster. From the Actions drop-down menu, select Manage IAM roles.

   3. On the Manage IAM Roles page, click the icon to the right of the search box and select redshift_s3_role. Click Add IAM Role > OK to add the redshift_s3_role with Amazon S3 access permissions to the Amazon Redshift cluster.

5. Unload data from Amazon Redshift to Amazon S3.

   1. Return to the Amazon Redshift console.

   2. In the left-side navigation pane, click Query editor v2. Run the UNLOAD command to export data from Amazon Redshift to an Amazon S3 bucket directory in Parquet format.

      UNLOAD ('SELECT * FROM customer')   
      TO 's3://bucket_name/unload_from_redshift/customer_parquet/customer_' 
      FORMAT AS PARQUET 
      IAM_ROLE 'arn:aws:iam::xxxx:role/redshift_s3_role';
      UNLOAD ('SELECT * FROM orders')   
      TO 's3://bucket_name/unload_from_redshift/orders_parquet/orders_' 
      FORMAT AS PARQUET 
      IAM_ROLE 'arn:aws:iam::xxxx:role/redshift_s3_role';
      UNLOAD ('SELECT * FROM lineitem')   
      TO 's3://bucket_name/unload_from_redshift/lineitem_parquet/lineitem_' 
      FORMAT AS PARQUET 
      IAM_ROLE 'arn:aws:iam::xxxx:role/redshift_s3_role';
      UNLOAD ('SELECT * FROM nation')   
      TO 's3://bucket_name/unload_from_redshift/nation_parquet/nation_' 
      FORMAT AS PARQUET 
      IAM_ROLE 'arn:aws:iam::xxxx:role/redshift_s3_role';
      UNLOAD ('SELECT * FROM part')   
      TO 's3://bucket_name/unload_from_redshift/part_parquet/part_' 
      FORMAT AS PARQUET 
      IAM_ROLE 'arn:aws:iam::xxxx:role/redshift_s3_role';
      UNLOAD ('SELECT * FROM partsupp')   
      TO 's3://bucket_name/unload_from_redshift/partsupp_parquet/partsupp_' 
      FORMAT AS PARQUET 
      IAM_ROLE 'arn:aws:iam::xxxx:role/redshift_s3_role';
      UNLOAD ('SELECT * FROM region')   
      TO 's3://bucket_name/unload_from_redshift/region_parquet/region_' 
      FORMAT AS PARQUET 
      IAM_ROLE 'arn:aws:iam::xxxx:role/redshift_s3_role';
      UNLOAD ('SELECT * FROM supplier')   
      TO 's3://bucket_name/unload_from_redshift/supplier_parquet/supplier_' 
      FORMAT AS PARQUET 
      IAM_ROLE 'arn:aws:iam::xxxx:role/redshift_s3_role';

      Note

      The Query editor can run multiple UNLOAD commands simultaneously.

   3. Log on to the Amazon S3 console and verify the exported data in the Amazon S3 bucket directory.

      The data is in the expected Parquet format. In the unload_from_redshift directory of the bucket, you can see the data folders for the eight exported TPC-H tables: customer_parquet, lineitem_parquet, nation_parquet, orders_parquet, part_parquet, partsupp_parquet, region_parquet, and supplier_parquet.

Step 2: Migrate data from S3 to OSS

Use the Data Online Migration service in Object Storage Service (OSS) to migrate data from Amazon S3 to OSS. For more information, see Migrate data from Amazon S3 to OSS.

1. Log on to the OSS console and create a bucket to store the migrated data. For more information, see Create buckets.

   After you create the bucket, the unload_from_redshift/ folder for the exported Redshift data is available on the Object Management page.

2. Create a RAM user and grant the required permissions.

   1. Log on to the RAM console and create a RAM user.

   2. On the Identities > Users page, find the new RAM user and click Add Permissions in the Actions column.

   3. In the Grant Permission panel, grant the new RAM user the AliyunOSSFullAccess (read and write permissions for the bucket) and AliyunMGWFullAccess (online migration permissions) policies. Then, click OK.

   4. In the left navigation pane, click Overview. On the Overview page, in the Basic Information section, click the Login URL link. Use the new RAM user to log on to the Alibaba Cloud console.

3. In AWS, create an IAM user with programmatic access to Amazon S3. For more information, see IAM users.

   1. Log on to the Amazon S3 console.

   2. To get the total size of the data and the number of objects, right-click the exported data directory and select Get total size.

      • For example, selecting the unload_from_redshift folder shows that its total size is 32.8 MB and it contains 18 objects.

   3. Log on to the IAM console and click Add User.

   4. On the Add User page, specify the Username. In the Select AWS access type section, select Programmatic access. Then, click Next: Permissions.

   5. On the Add User page, click Attach existing policies directly. In the search box, enter S3, select the AmazonS3ReadOnlyAccess policy, and then click Next: Tags.

   6. Click Next: Review > Create User to create the IAM user and record the key information.

      You will use this information when you create an online migration job. After you create the user, click Download .csv to save the Access Key ID and Access Key Secret for the Read_S3 user.

4. Log on to the Alibaba Cloud Data Migration console and create a data source and a destination address.

   1. In the left navigation pane, choose Data Online Migration > Address Management and click Create Address.

   2. On the Manage Data Address page, click Create Data Address, configure the parameters for the data source and destination address, and then click OK. For more information about the parameters, see Migration implementation.

      Note

      The Access Key ID and Access Key Secret are the access credentials for the IAM user.

5. Log on to the Alibaba Cloud Data Migration console and create an online migration job.

   1. In the left-side navigation pane, choose Data Online Migration > Migration Tasks, and then click Create Task.

   2. On the Select Address page, configure the following parameters, and then click Next.

      Parameter	Required	Description
      Name	Yes	Enter a name for the migration task. The name must meet the following requirements: The name must be 3 to 63 characters in length. The name can contain lowercase letters, digits, hyphens (-), and underscores (_). The name is case-sensitive. The name cannot start with a hyphen (-) or an underscore (_).
      Source Address	Yes	Select an existing source address.
      Destination Address	Yes	Select an existing destination address.

   3. On the Task Configurations page, configure the following parameters.

      Parameter	Required	Description
      Basic settings
      Migration Bandwidth	No	Select the migration bandwidth. Default: The maximum available bandwidth. The actual speed depends on the file size and the number of files. Specify an upper limit: Specify a maximum bandwidth limit. Important The actual migration bandwidth depends on factors such as the data source, network conditions, destination throttling, and file size, and may not reach the specified upper limit. Select a value based on your data source, destination, business requirements, and network bandwidth. Improper throttling can disrupt your business operations.
      Files Migrated Per Second	No	Select the number of files migrated per second. Default: Uses the default rate for files migrated per second. Specify an upper limit: Specify a maximum number of files migrated per second. Important The actual number of files migrated per second is affected by factors such as the data source, network conditions, destination throttling, and file size, and may not reach the specified upper limit. Select a value based on your data source, destination, business requirements, and network bandwidth. Improper throttling can disrupt your business operations.
      Overwrite Mode	Yes	Specifies how to handle files with the same name. Do not overwrite: Skips the file. Overwrite All: The source file overwrites the destination file. Overwrite based on the last modification time: If the last modified time of the source file is later than that of the destination file, the destination file is overwritten. If the last modified time of the source and destination files are the same, the destination file is overwritten if their Size or Content-Type differs. Warning With the Overwrite based on the last modification time policy, there is a risk that an older file may overwrite a newer file because it does not strictly guarantee that newer files will be preserved. If you select the Overwrite based on the last modification time policy, ensure that the source files can return metadata such as last modified time, Size, and Content-Type. Otherwise, the overwrite policy may not work as expected. When you select Do not overwrite or Overwrite if last modified time is later, the service sends one request to the source and one to the destination to retrieve metadata for the overwrite check. This incurs request fees at both the source and the destination.
      Audit settings
      Migration Report	Yes	Specifies how to push the migration report. Do not push (Default): Does not push the migration report to the destination bucket. Push: Pushes the migration report to the destination bucket. For path details, see Next steps. Important Pushing the migration report consumes storage space at the destination. Pushing the migration report may be delayed. Each task execution record has a unique ID. The migration report is pushed only once. Do not delete it unless necessary.
      Migration Logs	Yes	Specifies how to push the migration log. Do not push (Default): Does not push the migration log. Push: Pushes the migration log to Log Service (SLS). You can view the migration log in SLS. Push only file error logs.: Pushes only file error logs to Log Service (SLS). You can view these logs in SLS. When you select Push or Push only file error logs., Online Migration Service creates a project in Log Service (SLS) named aliyun-oss-import-log-<Alibaba Cloud account ID>-<current deployment region>, for example, aliyun-oss-import-log-137918634953****-cn-hangzhou. Important Before you select Push or Push only file error logs., ensure you have completed the following operations. Otherwise, the migration task may fail. You have activated Log Service (SLS). You have granted the required permissions on the Authorize page.
      Authorize	No	This option appears only when you set Migration Logs to Push or Push only file error logs.. Click Authorize to go to the Cloud Resource Access Authorization page. The system creates the AliyunOSSImportSlsAuditRole role and grants it the necessary permissions. Click Agree to Authorization to complete the authorization.
      Filters
      File Name	No	A filter based on filenames. Supports Include and Exclude rules using regular expressions based on the RE2 library (only a subset of the syntax is supported). For example: .*\.jpg$ matches all files that end with .jpg. ^file.* matches all files at the root level that start with file by default. If you set a prefix for the source address, such as data/to/oss/, you must use ^data/to/oss/file.* to match all files that start with file under that prefix. .*/picture/.* matches any subdirectory named picture. Important When you use an Include rule, all matching files are migrated. If there are multiple Include rules, any file that matches at least one rule is migrated. For example, if you have two files, picture.jpg and picture.png, and you set an Include rule for .*\.jpg$, only picture.jpg is migrated. If you also add an Include rule for .*\.png$, both files are migrated. When you use an Exclude rule, no matching files are migrated. If there are multiple Exclude rules, any file that matches at least one rule is not migrated. For example, if you have two files, picture.jpg and picture.png, and you set an Exclude rule for .*\.jpg$, only picture.png is migrated. If you also add an Exclude rule for .*\.png$, neither file is migrated. Exclude rules take precedence. If a file matches both an Exclude rule and an Include rule, the file is not migrated. For example, for a file named file.txt, if you set an Exclude rule for .*\.txt$ and an Include rule for file.*, the file file.txt is not migrated.
      File Modification Time	No	Filters files based on their last modified time. You can specify a time range to migrate only files whose last modified time falls within that range. The rules are as follows: If you specify only a start time of January 1, 2019, the task migrates only files last modified on or after that date. If you specify only an end time of January 1, 2022, the task migrates only files last modified on or before that date. If you specify a start time of January 1, 2019 and an end time of January 1, 2022, the task migrates only files last modified on or between these two dates.
      Migration configuration
      Retain last modified time	Yes	Specifies whether to retain the last modified time of the source file. Retain (Default): The destination file retains the last modified time of the source file. Do not retain: The last modified time is not set.
      Convert Appendable Object to Normal Object/Multipart Object	No	Specifies whether to convert Appendable Objects to Normal Objects or Multipart Objects. Yes: Converts source Appendable Objects to Normal Objects or Multipart Objects at the destination. No: Source Appendable Objects are migrated to the destination as Appendable Objects. Important This option is visible and configurable only if your account is on the allowlist. This option is currently supported only for OSS-to-OSS migration tasks.
      Specify destination storage class	No	Specifies whether to set the storage class for destination files. Specify: Migrated files are assigned the specified storage class at the destination. The following storage classes are available: Standard Infrequent Access Archive Cold Archive Deep Cold Archive Do not specify (Default): Migrated files at the destination use the default storage class of the destination bucket. Important This option is visible and configurable only if your account is on the allowlist. This option is currently supported only for tasks with an OSS destination.
      Task scheduling
      Execution time	No	Important If a migration task is still running at its next scheduled execution time, that execution is postponed until the next scheduled time after the current run completes. This continues until the specified number of executions is reached. Concurrency limit for migration tasks: Deployment regions in the Chinese mainland and China (Hong Kong) support a maximum of 10 concurrent tasks. Overseas regions support a maximum of 5 concurrent tasks. Exceeding this limit may cause scheduled tasks to fail. Defines when to run the migration task. Immediately: Runs the task as soon as it is created. At the Specified Time: Specifies a daily time window for the task to run. By default, the task starts at the specified start time and pauses at the specified stop time. Periodic Scheduling: Runs the task based on a specified frequency and number of executions. Execution frequency: Supports five types of frequencies: Hourly, Daily, Weekly, Specific days of the week, and Custom. For more information, see Execution frequency reference. Number of executions: Specifies how many times the task runs. If not set, the task runs once by default. For the maximum number of executions, refer to the console prompt. Important You can manually start and stop tasks at any time, regardless of the custom execution time settings.

   After you create the migration job, wait for its status to become "Finished". A "Finished" status indicates that the migration is complete. You can then log on to the OSS console, and on the bucket page, click Object Management to view the migration result.

   The unload_from_redshift/ directory contains eight migrated Parquet data directories: customer_parquet/, lineitem_parquet/, nation_parquet/, orders_parquet/, part_parquet/, partsupp_parquet/, region_parquet/, and supplier_parquet/.

Step 3: Migrate data from OSS to MaxCompute

Run the MaxCompute LOAD command to migrate data from OSS to a MaxCompute project in the same region.

The LOAD command supports two authentication methods: STS authentication and AccessKey authentication. AccessKey authentication requires you to enter your AccessKey ID and AccessKey secret in plaintext. STS authentication is more secure because it does not expose your AccessKey information. This topic uses STS authentication as an example.

1. In the DataWorks ad hoc query interface or the MaxCompute client (odpscmd), use the DDL from the Amazon Redshift cluster to create tables for the data to be migrated.

   For more information about the ad hoc query feature, see Run SQL statements in ad hoc query mode (optional). The following are sample commands.

   CREATE TABLE customer(
   C_CustKey int ,
   C_Name varchar(64) ,
   C_Address varchar(64) ,
   C_NationKey int ,
   C_Phone varchar(64) ,
   C_AcctBal decimal(13, 2) ,
   C_MktSegment varchar(64) ,
   C_Comment varchar(120) ,
   skip varchar(64)
   );
   CREATE TABLE lineitem(
   L_OrderKey int ,
   L_PartKey int ,
   L_SuppKey int ,
   L_LineNumber int ,
   L_Quantity int ,
   L_ExtendedPrice decimal(13, 2) ,
   L_Discount decimal(13, 2) ,
   L_Tax decimal(13, 2) ,
   L_ReturnFlag varchar(64) ,
   L_LineStatus varchar(64) ,
   L_ShipDate timestamp ,
   L_CommitDate timestamp ,
   L_ReceiptDate timestamp ,
   L_ShipInstruct varchar(64) ,
   L_ShipMode varchar(64) ,
   L_Comment varchar(64) ,
   skip varchar(64)
   );
   CREATE TABLE nation(
   N_NationKey int ,
   N_Name varchar(64) ,
   N_RegionKey int ,
   N_Comment varchar(160) ,
   skip varchar(64)
   );
   CREATE TABLE orders(
   O_OrderKey int ,
   O_CustKey int ,
   O_OrderStatus varchar(64) ,
   O_TotalPrice decimal(13, 2) ,
   O_OrderDate timestamp ,
   O_OrderPriority varchar(15) ,
   O_Clerk varchar(64) ,
   O_ShipPriority int ,
   O_Comment varchar(80) ,
   skip varchar(64)
   );
   CREATE TABLE part(
   P_PartKey int ,
   P_Name varchar(64) ,
   P_Mfgr varchar(64) ,
   P_Brand varchar(64) ,
   P_Type varchar(64) ,
   P_Size int ,
   P_Container varchar(64) ,
   P_RetailPrice decimal(13, 2) ,
   P_Comment varchar(64) ,
   skip varchar(64)
   );
   CREATE TABLE partsupp(
   PS_PartKey int ,
   PS_SuppKey int ,
   PS_AvailQty int ,
   PS_SupplyCost decimal(13, 2) ,
   PS_Comment varchar(200) ,
   skip varchar(64)
   );
   CREATE TABLE region(
   R_RegionKey int ,
   R_Name varchar(64) ,
   R_Comment varchar(160) ,
   skip varchar(64)
   );
   CREATE TABLE supplier(
   S_SuppKey int ,
   S_Name varchar(64) ,
   S_Address varchar(64) ,
   S_NationKey int ,
   S_Phone varchar(18) ,
   S_AcctBal decimal(13, 2) ,
   S_Comment varchar(105) ,
   skip varchar(64)
   );

   The TPC-H dataset in this topic uses MaxCompute 2.0 data types and Decimal 2.0 data types. Therefore, the destination project must use MaxCompute 2.0. If you need to enable 2.0 data types for your project, add the following statements before the CREATE TABLE commands and run them together.

   setproject odps.sql.type.system.odps2=true;
   setproject odps.sql.decimal.odps2=true;

2. Create and authorize a RAM role with permissions to access OSS. For more information, see Authorization for accessing external data sources.

3. Run the LOAD command multiple times to load all data from OSS into the MaxCompute tables. Then, run SQL commands to view and verify the imported data. For more information about the LOAD command, see LOAD.

   LOAD OVERWRITE TABLE orders 
   FROM  LOCATION 'oss://endpoint/oss_bucket_name/unload_from_redshift/orders_parquet/' --Specifies the location of the data in the OSS bucket.
   ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
   WITH SERDEPROPERTIES ('odps.properties.rolearn'='acs:ram::xxx:role/xxx_role')
   STORED AS PARQUET;

   Run the following command to view and verify the data import results.

   SELECT * FROM orders limit 100;

4. Verify that the data migrated to MaxCompute is consistent with the data in the Amazon Redshift cluster by comparing the number of tables, the number of rows, and the query results of a typical job.

   1. Log on to the Amazon Redshift console. In the upper-right corner, select the Asia Pacific (Singapore) region. In the left-side navigation pane, click EDITOR. Then, enter and run the following query:

      SELECT l_returnflag, l_linestatus, SUM(l_quantity) as sum_qty,
      SUM(l_extendedprice) AS sum_base_price, SUM(l_extendedprice*(1-l_discount)) AS sum_disc_price,
      SUM(l_extendedprice*(1-l_discount)*(1+l_tax)) AS sum_charge, AVG(l_quantity) AS avg_qty,
      AVG(l_extendedprice) AS avg_price, AVG(l_discount) AS avg_disc,  COUNT(*) AS count_order
      FROM lineitem
      GROUP BY l_returnflag, l_linestatus
      ORDER BY l_returnflag,l_linestatus;

      The query runs successfully and returns four rows representing the (A,F), (N,F), (N,O), and (R,F) combinations of l_returnflag and l_linestatus. Use these results as the Amazon Redshift baseline for comparison with the MaxCompute query results.

   2. In the DataWorks ad hoc query interface or the MaxCompute client (odpscmd), run the same command and verify that the results are consistent with those from the Amazon Redshift cluster.