AnalyticDB:Migrate Teradata applications to AnalyticDB for PostgreSQL

Migration requirements

A successful migration from Teradata to AnalyticDB for PostgreSQL is designed to meet the following goals:

• Perform a complete migration for the underlying data platform of the data warehouse.

• Perform smooth migration for deployed applications on the data warehouse system.

• Perform migration of which business users are not aware for business platforms and data.

• Ensure good performance of the data warehouse after migration.

• Set favorable migration duration and migration plans.

• Fully reuse the original system architecture, ETL process, data structure, and management tools.

Migration scope

A complete Teradata migration involves four areas:

1. Historical data: Export data as text files using the specified delimiter and character encoding, store them on Elastic Compute Service (ECS) instance local disks or in Object Storage Service (OSS) buckets on the same network as the AnalyticDB for PostgreSQL instance, and load the files through OSS external tables over the gpfdist protocol. Export DDL scripts from Teradata and batch-convert them using AnalyticDB for PostgreSQL syntax.

2. Extract-Transform-Load (ETL) processes: Convert ETL statements to AnalyticDB for PostgreSQL Data Manipulation Language (DML) syntax using the script-based conversion tools. Replace Teradata functions with their AnalyticDB for PostgreSQL equivalents based on the function mapping table in this document. Reconfigure and restart ETL jobs after historical data migration completes.

3. API connections: AnalyticDB for PostgreSQL supports JDBC and ODBC protocols, so Business Intelligence (BI) frontend tools connect the same way. Update the instance IP address to complete the API migration.

4. Management tools: Deploy backup and recovery tools for the AnalyticDB for PostgreSQL instance and schedule regular recovery drills.

Data type mapping

Most core data types map directly between Teradata and AnalyticDB for PostgreSQL. The script-based Data Definition Language (DDL) conversion tools handle batch conversion automatically.

Table creation statements

The following examples show how a Teradata DDL statement maps to AnalyticDB for PostgreSQL. Key differences are annotated in the converted statement.

Teradata:

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;

AnalyticDB for PostgreSQL:

-- MULTISET TABLE, NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL,
-- CHECKSUM, DEFAULT MERGEBLOCKRATIO: Teradata-specific clauses removed
-- FORMAT 'YYYYMMDD', TITLE, CHARACTER SET, CASESPECIFIC: column attributes removed
CREATE TABLE test_table
     (
      first_column DATE NOT NULL,           -- FORMAT 'YYYYMMDD' removed; use SET client_encoding for encoding
      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 )   -- PRIMARY INDEX -> DISTRIBUTED BY
PARTITION BY RANGE(first_column)                  -- RANGE_N syntax converted to standard RANGE
(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);  -- index syntax unchanged

Data import and export formats

Both Teradata and AnalyticDB for PostgreSQL support TXT and CSV file formats. The key difference is the separator:

• Teradata: Two-character separators

• AnalyticDB for PostgreSQL: Single-character separators

Convert separators in your data files before loading.

SQL statement differences

AnalyticDB for PostgreSQL supports most Teradata SQL syntax. The following statements require conversion.

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;

-- Wrap the window function in a subquery, then filter in the outer WHERE clause
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;

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

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

The following table maps Teradata functions to their AnalyticDB for PostgreSQL equivalents. For functions with no direct equivalent, the table provides the SQL expression to use.