DocsICP FilingConsole
官方文档
首页

Manually migrate data from Amazon Redshift to AnalyticDB for PostgreSQL

更新时间：
复制 MD 格式
我的收藏

Migrate data from Amazon Redshift to AnalyticDB for PostgreSQL by using Alibaba Cloud Data Online Migration or the ossimport tool.

Prerequisites

  • The source Amazon Redshift instance.

  • An Amazon S3 bucket for exporting data from the Amazon Redshift instance.

  • You have activated Alibaba Cloud Object Storage Service (OSS). For more information about OSS, see What is Object Storage OSS.

  • An OSS bucket has been created. For more information, see Create a bucket.

    Note

    We recommend that you create the OSS bucket in the same region as your AnalyticDB for PostgreSQL instance to simplify importing data into the database.

  • Create an AnalyticDB for PostgreSQL instance. For information about how to select instance specifications, see Select instance specifications.

Specification selection

Select the specifications for the destination AnalyticDB for PostgreSQL instance based on the specifications of the source Amazon Redshift instance.

An Amazon Redshift instance consists of a leader node and multiple compute nodes.

  • The leader node is equivalent to the AnalyticDB for PostgreSQL coordinator node. It communicates with clients, parses queries, develops execution plans, and dispatches tasks to compute nodes.

  • Compute Node: A compute node is equivalent to a Segment node in AnalyticDB for PostgreSQL and consists of multiple node slices. Each node slice is equivalent to a single Segment node in AnalyticDB for PostgreSQL and is responsible for data storage and query computation.

To determine the appropriate AnalyticDB for PostgreSQL node specifications, match them to the specifications of each node slice in the source Amazon Redshift instance.

Example

The source Amazon Redshift instance contains four compute nodes, and each compute node has two node slices. Each node slice has 2 cores, 16 GB of memory, and 1 TB of storage.

Based on the specifications of the source Amazon Redshift instance, you can select eight segment nodes when you create the AnalyticDB for PostgreSQL instance. For each segment node, you can select the 2-core 16 GB specification and set the storage capacity to 1,000 GB.

Note

  • For more information about how to create an AnalyticDB for PostgreSQL instance, see Create an instance.

  • We recommend that you select ESSD for the storage disk type. An ESSD delivers better I/O performance than an Ultra Disk.

Procedure

Step 1: Export data from Amazon Redshift

You can use the UNLOAD command to export data. For more information, see the UNLOAD documentation.

The syntax of the UNLOAD command is as follows.

UNLOAD ('select-statement')
TO 's3://object-path/name-prefix'
AUTHORIZATION
[ option [ ... ] ]

WHERE option IS
{ [ FORMAT [ AS ] ] CSV | PARQUET
| PARTITION BY ( column_name [, ... ] ) [ INCLUDE ]
| MANIFEST [ VERBOSE ] 
| HEADER           
| DELIMITER [ AS ] 'delimiter-char' 
| FIXEDWIDTH [ AS ] 'fixedwidth-spec'   
| ENCRYPTED [ AUTO ]
| BZIP2  
| GZIP 
| ZSTD
| ADDQUOTES 
| NULL [ AS ] 'null-string'
| ESCAPE
| ALLOWOVERWRITE
| CLEANPATH
| PARALLEL [ { ON | TRUE } | { OFF | FALSE } ]
| MAXFILESIZE [AS] max-size [ MB | GB ] 
| REGION [AS] 'Amazon-region' }
Note

  • When you export data, we recommend that you use the FORMAT AS PARQUET or CSV format.

  • To improve export efficiency and generate more file shards, we recommend that you enable parallel export by using PARALLEL ON.

  • Use the MAXFILESIZE parameter to generate multiple file segments. Set the number of segments to an integer multiple of the number of nodes in your AnalyticDB for PostgreSQL instance to improve parallel import efficiency through an OSS foreign table.

Step 2: Synchronize data to Alibaba Cloud OSS

You can synchronize data by using one of two methods: Data Online Migration or the ossimport tool.

Data Online Migration

  1. Log on to the Alibaba Cloud Data Transport console.

  2. Create a source data address and a destination data address for data synchronization.

    1. In the left-side navigation pane, choose Data Online Migration > Data Addresses.

    2. Click Create Data Address to create a source address.

    3. In the Create Data Address panel, configure the following parameters and click OK.

      Parameter

      Required

      Description

      Name

      Yes

      • 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 (_).

      Type

      Yes

      Select AWS S3.

      Domain name

      Yes

      Enter the AWS S3 service endpoint. Endpoints are listed in Amazon S3 endpoints.

      AccessKeyId

      Yes

      Enter the IAM user access key. This key authenticates read access to the source data.

      SecretAccessKey

      Yes

      Bucket

      Yes

      Enter the name of the source AWS S3 bucket.

      Note

      The bucket name cannot start or end with spaces, line breaks, or tabs.

      Prefix

      No

      Specify a prefix to migrate a subset of the data.

      • If specified, only data under that prefix is migrated. The prefix must not start with a forward slash (/) but must end with one.

      • If you omit this parameter, all data in the bucket is migrated.

      Tunnel

      No

      Select the name of the channel to use.

      Important

      • This parameter is required only when migrating data over an Express Connect or VPN connection, or when migrating data from a self-managed storage system.

      • You must associate an agent if the destination is a local file system or if the migration uses a dedicated connection, for example, to a financial cloud or Apsara Stack environment.

      Agent

      No

      Select the name of the agent to use.

      Important

      • This parameter is required only when migrating data over an Express Connect or VPN connection, or when migrating data from a self-managed storage system.

      • You can select a maximum of 200 agents for a specified channel.

    4. Click Create Data Address again to create a destination address.

    5. In the Create Data Address panel, configure the following parameters and click OK.

      Parameter

      Required

      Description

      Data Type

      Yes

      The type of the destination data address. Select OSS.

      Data Name

      Yes

      The name of the source data address. The name must be 3 to 63 characters in length. The name cannot contain special characters except for hyphens (-) and underscores (_).

      Data Region

      Yes

      The region in which the destination data address resides.

      Enable Transfer Acceleration

      No

      If you want to use Data Online Migration together with the transfer acceleration feature of Object Storage Service (OSS), enable the transfer acceleration feature for buckets. The transfer acceleration feature takes effect within 30 minutes after it is enabled. After you enable the feature, wait for 30 minutes before you create a migration job.

      Important

      If you use the transfer acceleration feature to access your OSS buckets, you are charged additional transfer acceleration fees. For more information, see Access OSS using transfer acceleration.

      OSS Endpoint

      Yes

      The endpoint of the destination data address. Select an endpoint based on the region in which the destination data address resides. For more information, see Regions and endpoints.

      Note

      When you migrate data from third-party cloud services to OSS, you must access OSS from a public endpoint over the Internet.

      Access Key Id and Access Key Secret

      Yes

      The AccessKey pair of the RAM user that is used to write data to the destination data address. The AccessKey pair is used by OSS to check whether the RAM user has the permissions to write data to the destination data address.

      OSS Bucket

      Yes

      The OSS bucket to which the data is migrated.

      OSS Prefix

      No

      The prefix of the destination data address.

      • If you specify a prefix, the source data is migrated to the specified directory. The value cannot start with a forward slash (/) and must end with a forward slash (/). Example: data/to/oss/.

      • If you do not specify a prefix, the source data is migrated to the root directory of the destination bucket.

      Important

      If the name of a source file starts with a forward slash (/), you must specify an OSS prefix when you specify the destination data address. If no OSS prefix is specified, the migration job fails. For example, if the name of a file to be migrated is /test/test.png, you must specify an OSS prefix, such as oss/. After the migration is complete, the name of the file changes from /test/test.png to oss//test/test.png.

  3. Create an online migration job.

    1. In the left-side navigation pane, choose Data Online Migration > Data Migration Tasks.

    2. Click Create Data Migration Task.

    3. In the Create Data Migration Task panel, read the terms of service, select I understand the preceding terms and want to activate Data Transport, and then click Next.

    4. In the Charges dialog box, click Create.

    5. On the Configure Task tab, configure the following parameters and click Next.

      Parameter

      Required

      Description

      Job Name

      Yes

      The name of the source data address. The name must be 3 to 63 characters in length. The name cannot contain special characters except for hyphens (-) and underscores (_).

      Source Data Address

      Yes

      The source data address that you created.

      Destination Data Address

      Yes

      The destination data address that you created.

      Specified Directory

      No

      The directories in which the files and subdirectories are migrated or not migrated. Valid values:

      • Do not filter: migrates all data from the source data address.

      • Exclude: does not migrate the files or subdirectories in the specified directories.

        For example, you want to migrate all the directories in the root_dir/ directory except root_dir/bad_sub1/ and root_dir/bad_sub2/. In this case, you can select this method and specify bad_sub1/ and bad_sub2/.

      • Contain: migrates only the files and subdirectories in the specified directories.

        For example, you want to migrate only root_dir/good_sub1/ and root_dir/good_sub2/ in the root_dir/ directory. In this case, you can select this method and specify good_sub1/ and good_sub2/.

      Note

      • A directory name can contain only digits and letters. If you use special characters, the migration job may fail.

      • A directory name cannot start with a forward slash (/) or a backslash (\), and cannot contain double forward slashes (//), double periods (..), or double quotation marks ("). The character string that consists of all the specified directory names cannot exceed 10 KB in length.

      • A directory name must end with a forward slash (/). For example, you can specify docs/ as the directory name.

      • You can specify up to 20 directories of the Exclude or Contain type.

      Migration Type

      Yes

      The data migration mode. Valid values:

      • Full: The system migrates all data from the source data address to the destination data address. You must configure the Start Time Point of File parameter. The system migrates all data whose last modification time is later than the specified time. After all data is migrated, the migration job ends.

        If you change the data at the source data address after the migration job is complete, you can submit another job to migrate all data. In this case, the system migrates only the changed data.

      • Incremental: The system runs a migration job based on the values that you specified for the Migration Interval and Migration Times parameters.

        • You must configure the Start Time Point of File parameter. During the first migration, the system migrates all data whose last modification time is later than the specified time. After the first migration is complete, incremental migration is performed based on the value that you specified for the Migration Interval parameter. For each incremental migration, the system migrates only the data that is created or modified after the previous migration starts and before the current migration starts.

        • If you set the Migration Times parameter to N, full migration is performed once and then incremental migration is performed (N - 1) times.

          For example, the current time is 08:00, March 10, 2019 and you set the Migration Interval parameter to 1, the Migration Times parameter to 5, and the Start Time Point of File parameter to 08:00, March 5, 2019. When the first migration starts, the system migrates all files whose last modification time is between 08:00, March 5, 2019 and 08:00, March 10, 2019. In this example, the first migration requires one hour to complete and the second migration starts at 10:00, March 10, 2019. The duration from 08:00 to 10:00 consists of one hour for the first migration process and one hour for the migration interval. During the second migration, the system migrates the files whose last modification time is between 08:00, March 10, 2019 and 10:00, March 10, 2019. The migration job consists of one full migration and four incremental migrations.

      Important

      Before full or incremental migration starts, the system compares the files at the source data address with the files at the destination data address. If a source file has the same name as a destination file, the destination file is overwritten when one of the following conditions is met:

      • The Content-Type values of the files at the source data address and the destination data address are different.

      • The last modification time of the file at the source data address is later than that of the file at the destination data address.

      • The size of the file at the source data address is different from that of the file at the destination data address.

      Start Time Point of File

      Yes

      The time to filter data to be migrated. The system migrates the data that is created or modified after the specified time. Valid values:

      • All: migrates all files.

      • Assign: migrates only the files that are created or modified after the specified time.

        For example, if you set the time to 08:00:00, November 1, 2018, only the files that are created or modified after 08:00:00, November 1, 2018 are migrated.

      Migration Interval

      This parameter is required if you set the Migration Type parameter to Incremental.

      The default value is 1, and the maximum value is 24. Unit: hours.

      Migration Times

      This parameter is required if you set the Migration Type parameter to Incremental.

      The default value is 1, and the maximum value is 30.

      File Overwrite Method

      Yes

      The method that is used to process the file at the source data address whose name is the same as that of a file at the destination data address. Valid values:

      • LastModified: If a source file has the same name as a destination file, the system compares the last modified time indicated by the LastModified property of the files.

        • If the last modification time of the file at the source data address is later than that of the file at the destination data address, the file at the source data address is migrated and the file at the destination data address is overwritten.

        • If the last modification time of the file at the source data address is earlier than that of the file at the destination data address, the file at the source data address is not migrated and the file at the destination data address is retained.

        • If the two files have the same last modification time, the system checks the values of the Size and Content-Type properties of the files.

          If the two files have the same Size and Content-Type values, the file at the source data address is not migrated. If the two files have different values of at least one property, the file at the source data address is migrated and the file at the destination data address is overwritten.

      • Condition: If a source file has the same name as a destination file, the system compares the values of the LastModified, Size, and Content-Type properties of the files.

        • If all property values of the two files are the same, the file at the source data address is not migrated.

        • If the two files have different values of at least one property, the file at the source data address is migrated and the file at the destination data address is overwritten.

      • All: If a source file has the same name as a destination file, the system performs no comparison and directly overwrites the destination file with the source file.

      • No: If a source file has the same name as a destination file, the system performs no comparison and directly skips the source file in the migration.

        Warning

        • If you select Condition or LastModified, earlier files may overwrite later files.

        • If you select Condition or LastModified, make sure that the source files have LastModified, Size, and Content-Type values. Otherwise, the overwrite policy may become ineffective and data is not migrated as expected.

    6. On the Performance Tuning tab, in the Data Estimation section, enter the Size of Data to Be Migrated and Number of Files to Be Migrated.

      Note

      To ensure a smooth migration, please estimate the data as accurately as possible. For more information, see Estimate migration data.

    7. Optional: On the Performance Tuning tab, in the Bandwidth Throttling section, set Time Range (Hour) and Max Bandwidth, and then click Add.

      Note

      To avoid affecting your online business, we recommend that you set the Time Range (Hour) and Max Bandwidth for migration based on the peak and off-peak hours of your business.

    8. Click Create and wait for the migration job to complete.

ossimport

  1. Download and install ossimport in standalone mode. For more information, see ossimport overview.

    The file structure of ossimport in standalone mode is as follows:

    ossimport
├── bin
│   └── ossimport2.jar  # A full JAR that includes Master, Worker, TaskTracker, and Console modules
├── conf
│   ├── local_job.cfg   # Job configuration file
│   └── sys.properties  # System runtime parameter configuration file
├── console.bat         # Windows command-line interface (CLI) for distributed task submission
├── console.sh          # Linux CLI for distributed task submission
├── import.bat          # One-click import on Windows. Runs the migration task configured in conf/local_job.cfg, including startup, migration, validation, and retries.
├── import.sh           # One-click import on Linux. Runs the migration task configured in conf/local_job.cfg, including startup, migration, validation, and retries.
├── logs                # Log directory
└── README.md           # Documentation. We strongly recommend that you read this file *before use*.

  2. Configure ossimport in standalone mode.

    Modify the following parameters in the conf/local_job.cfg configuration file.

    srcType=s3
srcAccessKey=<Amazon AccessKey ID>
srcSecretKey=<Amazon AccessKey Secret>
srcDomain=<Domain name of the Amazon S3 region>
srcBucket=<Name of the Amazon S3 bucket>
destAccessKey=<Alibaba Cloud AccessKey ID>
destSecretKey=<Alibaba Cloud AccessKey Secret>
destDomain=<Endpoint of the OSS region>
destBucket=<Name of the OSS bucket>
destPrefix=
isSkipExistFile=true

    For more information about ossimport configuration, see ossimport overview.

  3. Run ossimport to synchronize data to OSS. For more information about ossimport in standalone mode, see Standalone deployment.

Step 3: Create destination tables

Create destination tables in the AnalyticDB for PostgreSQL instance with the same structure as the source Amazon Redshift tables. For the table creation syntax, see CREATE TABLE.

If you need to modify DDL definitions for objects such as schemas, tables, functions, and views, see DDL syntax conversion.

Step 4: Import data from OSS

Import data into AnalyticDB for PostgreSQL by using the COPY command or an OSS foreign table:

DDL syntax conversion

Amazon Redshift DDL syntax differs from AnalyticDB for PostgreSQL syntax. Convert the following DDL statements before migration.

Create schema

Create a schema based on the syntax of AnalyticDB for PostgreSQL. The following is an example:

CREATE SCHEMA schema1 AUTHORIZATION xxxpoc;
GRANT ALL ON SCHEMA schema1 TO xxxpoc;
GRANT ALL ON SCHEMA schema1 TO public;
COMMENT ON SCHEMA model IS 'for xxx migration  poc test';

CREATE SCHEMA oss_external_table AUTHORIZATION xxxpoc;

Create function

AnalyticDB for PostgreSQL is not compatible with some Amazon Redshift SQL functions. You must customize or rewrite these functions. For example:

  • Replace CONVERT_TIMEZONE(a,b,c) with the following statement:

    timezone(b, timezone(a,c))

  • Replace GETDATE() with the following statement:

    current_timestamp(0)::timestamp

  • Replace or optimize a user-defined function (UDF). The following is a sample function in Redshift:

    CREATE OR REPLACE FUNCTION public.f_jdate(dt timestamp WITHOUT time zone)
RETURNS character varying AS
'      from datetime import timedelta, datetime
       if dt.hour < 4:
              d = timedelta(days=-1)
              dt = dt + d
       return str(dt.date())'
LANGUAGE plpythonu IMMUTABLE;
COMMIT;

    Use the following statement to replace the preceding function:

    to_char(a - interval '4 hour', 'yyyy-mm-dd')

  • Other standard functions of Amazon Redshift.

    Refer to the PostgreSQL documentation on Functions and Operators to help you modify or implement functions that are incompatible between Amazon Redshift and AnalyticDB for PostgreSQL. Examples of incompatible functions include:

Create table

  • Modify the table name

    The maximum length of a table name in Amazon Redshift is 127 bytes, whereas the default maximum length of a table name in AnalyticDB for PostgreSQL is 63 bytes. You must shorten any object names (such as tables, functions, or views) that exceed this limit.

  • Modify the compression algorithm

    Delete ENCODE XXX from the Amazon Redshift CREATE TABLE statement and replace it with the following clause:

    WITH (COMPRESSTYPE={ZLIB|ZSTD|RLE_TYPE|NONE})

    For information about the compression algorithms that AnalyticDB for PostgreSQL supports, see Compression.

  • Modify the distribution key

    Amazon Redshift supports three distribution styles. Modify the distribution key according to the following rules:

    • EVEN: replace with DISTRIBUTED RANDOMLY.

    • KEY: replace with DISTRIBUTED BY (column, [ ... ] ).

    • ALL: replace with DISTRIBUTED REPLICATED.

  • Modify the sort key

    Delete the COMPOUND or INTERLEAVED option from the Amazon Redshift sort key clause [ COMPOUND | INTERLEAVED ] SORTKEY (column_name [, ...] ) ] and replace it with the following clause:

    ORDER BY (column, [ ... ])

The following are examples:

  • Example 1

    The CREATE TABLE statement in Amazon Redshift:

    CREATE TABLE schema1.table1
(
    filed1 VARCHAR(100) ENCODE lzo,
    filed2 INTEGER DISTKEY,
    filed3 INTEGER,
    filed4 BIGINT ENCODE lzo,
    filed5 INTEGER
)
INTERLEAVED SORTKEY
(
    filed1,
    filed2
);

    The converted CREATE TABLE statement in AnalyticDB for PostgreSQL:

    CREATE TABLE schema1.table1
(
    filed1 VARCHAR(100) ,
    filed2 INTEGER,
    filed3 INTEGER,
    filed4 BIGINT,
    filed5 INTEGER
)
WITH(APPENDONLY=true,ORIENTATION=column,COMPRESSTYPE=zstd)
DISTRIBUTED BY (filed2)
ORDER BY (filed1, filed2);

-- Sort
SORT schema1.table1;
MULTISORT schema1.table1;

  • Example 2

    The CREATE TABLE statement in Amazon Redshift, which includes the ENCODE and SORTKEY options:

    CREATE TABLE schema2.table2
(
    filed1 VARCHAR(50) ENCODE lzo,
    filed2 VARCHAR(50) ENCODE lzo,
    filed3 VARCHAR(20) ENCODE lzo,
)
DISTSTYLE EVEN
INTERLEAVED SORTKEY
(
    filed1
);

    The converted CREATE TABLE statement in AnalyticDB for PostgreSQL:

    CREATE TABLE schema2.table2
(
    filed1 VARCHAR(50),
    filed2 VARCHAR(50),
    filed3 VARCHAR(20)
)
WITH(APPENDONLY=true, ORIENTATION=column, COMPRESSTYPE=zstd)
DISTRIBUTED randomly
ORDER BY (filed1);

-- Sort
SORT schema2.table2;
MULTISORT schema2.table2;

Create view

You must convert the Amazon Redshift CREATE VIEW statement into an SQL statement that complies with the syntax of AnalyticDB for PostgreSQL. The conversion rules are similar to those for CREATE TABLE.

Note

The WITH NO SCHEMA BINDING clause is not supported.

Related documents

For information about automatic migration, see Automatically migrate data from Amazon Redshift to AnalyticDB for PostgreSQL.

Previous：Migrate or synchronize data from a third-party cloud databaseNext：Configure automatic data migration from an Amazon Redshift cluster to an AnalyticDB for PostgreSQL