AnalyticDB:Migrate data from a Teradata database to an AnalyticDB for PostgreSQL instance

Migration requirements

The core data types of AnalyticDB for PostgreSQL and Teradata are mutually compatible with each other. Only some data types need to be modified. DDL statements for table creation are automatically converted in batches using the script-based tools in AnalyticDB for PostgreSQL. The following table lists data types in AnalyticDB for PostgreSQL and Teradata.

Table creation statements

This section uses examples to describe the differences between AnalyticDB for PostgreSQL and Teradata.

To create a table in Teradata, execute the following statements:

CREATE MULTISET TABLE test_table,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      first_column DATE FORMAT 'YYYYMMDD' TITLE 'COLUMN 1' NOT NULL,
      second_column INTEGER TITLE 'COLUMN 2' NOT NULL ,
      third_column CHAR(6) CHARACTER SET LATIN CASESPECIFIC TITLE 'COLUMN 3' NOT NULL ,
      fourth_column CHAR(20) CHARACTER SET LATIN CASESPECIFIC TITLE 'COLUMN 4' NOT NULL,
      fifth_column CHAR(1) CHARACTER SET LATIN CASESPECIFIC TITLE 'COLUMN 5' NOT NULL,
      sixth_column CHAR(24) CHARACTER SET LATIN CASESPECIFIC TITLE 'COLUMN 6' NOT NULL,
      seventh_column VARCHAR(18) CHARACTER SET LATIN CASESPECIFIC TITLE 'COLUMN 7' NOT NULL,
      eighth_column DECIMAL(18,0) TITLE 'COLUMN 8' NOT NULL ,
      nineth_column DECIMAL(18,6) TITLE 'COLUMN 9' NOT NULL )
PRIMARY INDEX ( first_column ,fourth_column )
PARTITION BY RANGE_N(first_column  BETWEEN DATE '1999-01-01' AND DATE '2050-12-31' EACH INTERVAL '1' DAY );

CREATE INDEX test_index (first_column, fourth_column) ON test_table;

To create a table in AnalyticDB for PostgreSQL, execute the following statements:

CREATE TABLE test_table
     (
      first_column DATE NOT NULL,
      second_column INTEGER NOT NULL ,
      third_column CHAR(6) NOT NULL ,
      fourth_column CHAR(20) NOT NULL,
      fifth_column CHAR(1) NOT NULL,
      sixth_column CHAR(24) NOT NULL,
      seventh_column VARCHAR(18) NOT NULL,
      eighth_column DECIMAL(18,0) NOT NULL ,
      nineth_column DECIMAL(18,6) NOT NULL )
DISTRIBUTED BY ( first_column ,fourth_column )
PARTITION BY RANGE(first_column) 
(START (DATE '1999-01-01')  INCLUSIVE
END (DATE '2050-12-31')  INCLUSIVE
EVERY (INTERVAL '1 DAY' ) );

create index test_index on test_table(first_column, fourth_column);

Based on the preceding examples, the similarities and differences between table creation statements in AnalyticDB for PostgreSQL and Teradata are as follows:

• Core data types are compatible with each other and require no modifications.
• Both AnalyticDB for PostgreSQL and Teradata support distribution columns, but the syntax is different. The PRIMARY INDEX clause is used in Teradata, while DISTRIBUTED BY is used in AnalyticDB for PostgreSQL.
• Both AnalyticDB for PostgreSQL and Teradata support the PARTITION BY clause. Such clauses in AnalyticDB for PostgreSQL and Teradata have the same semantics but different syntax.
• Both AnalyticDB for PostgreSQL and Teradata allow you to create indexes on tables, but the syntax used is different.
• AnalyticDB for PostgreSQL does not support the TITLE keyword, but allows you to add a comment to a specific column by executing the following statement: COMMENT ON COLUMN table_name.column_name IS 'XXX';.
• AnalyticDB for PostgreSQL cannot declare the encoding type when you define the CHAR or VARCHAR data type. You can use the SET client_encoding = latin1; statement to declare the encoding type.

Imported and exported data formats

SQL statements

AnalyticDB for PostgreSQL is compatible with the syntax of most SQL statements in Teradata. You only need to modify the following syntax:

• CAST

  In Teradata:

  cast(XXX as int format '999999')
  cast(XXX as date format 'YYYYMMDD')

  In AnalyticDB for PostgreSQL:

  cast(XXX as int)
  cast(XXX as date)

  AnalyticDB for PostgreSQL does not declare a format in a CAST statement.

  • For the cast(XXX as int format '999999') statement, you must write a function for replacement.
  • You do not need to modify the cast(XXX as date format 'YYYYMMDD') statement because AnalyticDB for PostgreSQL supports the 'YYYY-MM-DD' format for a date.
• QUALIFY

  The QUALIFY keyword in Teradata is used to further filter the results of the sorting function based on user-specified conditions.

  Example:

  SELECT itemid, sumprice, RANK() OVER (ORDER BY sumprice DESC)
       FROM (SELECT a1.item_id, SUM(a1.sale)
             FROM sales AS a1 
             GROUP BY a1.itemID) AS t1 (itemid, sumprice) 
       QUALIFY RANK() OVER (ORDER BY sum_price DESC) <=100;

  AnalyticDB for PostgreSQL does not support the QUALIFY keyword. You must change an SQL statement with this keyword to a nested subquery.

  SELECT itemid, sumprice, rank from 
  (SELECT itemid, sumprice, RANK() OVER (ORDER BY sumprice DESC) as rank
       FROM (SELECT a1.item_id, SUM(a1.sale)
             FROM sales AS a1 
             GROUP BY a1.itemID) AS t1 (itemid,sumprice)
  )  AS a
  where rank <=100;

• MACRO

  Teradata uses MACRO to execute a group of SQL statements. Example:

  CREATE MACRO Get_Emp_Salary(EmployeeNo INTEGER) AS ( 
     SELECT 
     EmployeeNo, 
     NetPay 
     FROM  
     Salary 
     WHERE EmployeeNo = :EmployeeNo; 
  );

  AnalyticDB for PostgreSQL does not support macro, but you can use the FUNCTION statement to implement the MACRO function of Teradata. Example:

  CREATE OR REPLACE FUNCTION Get_Emp_Salary(
          EmployeeNo INTEGER,
          OUT EmployeeNo INTEGER,
          OUT NetPay FLOAT
  ) returns setof record AS 
  $$
  
          SELECT EmployeeNo,NetPay 
          FROM Salary
          WHERE EmployeeNo = $1
  
  $$
   LANGUAGE SQL;

Function mapping