如何将Teradata應用遷移至AnalyticDB for PostgreSQL

AnalyticDB for PostgreSQL（簡稱：ADB for PG）對Teradata文法有着很好的相容，将Teradata應用遷移到ADB for PG，隻需進行有限的修改。本指南在将TD數倉應用遷移至ADB PG雲化數倉過程中，秉承充分複用舊系統架構、ETL算法、資料結構和工具的原則，需對原加工腳本進行轉換，另外，需對曆史資料進行遷移，并保證資料的準确性，完整性。

• 對資料倉庫基礎資料平台的完整遷移；
• 對資料倉庫系統上已部署應用的平滑遷移；
• 業務外觀透明遷移，保持新舊系統業務操作一緻性；
• 充分保證資料倉庫遷移後的性能；
• 可接受的系統遷移周期及良好的遷移可操作性；
• 充分複用舊系統架構、ETL算法、資料結構和工具。

1. 曆史資料遷移，首先從TD資料庫按規定分隔符及字元編碼将曆史資料導成文本檔案，存放于ADB PG資料庫網絡相通的ECS伺服器本地磁盤或雲存儲OSS上，確定ADB PG資料庫通過gpfidst協定的外部表後ADBPG的OSS外部表能讀取資料檔案。之後從TD導出DDL腳本，按ADB PG文法批量修改腳本，確定在ADB PG能成功建立所有使用者表。
2. 日常加工流程遷移：對ETL查詢加工語句按ADB PG的DML文法進行轉換（ADBPG建構了相關基于腳本的自動化轉化工具，可以對文法進行自動mapping轉換），并根據TD與ADB PG函數對照表替換相關函數，轉換ETL連接配接資料庫方式。重新配置加工作業，曆史資料遷移成功後，啟動日常ETL作業。
3. 應用接口遷移：ADB PG資料庫支援ODBC/JDBC，BI前端展現等工具可通過ODBC或JDBC标準通路DW，改動網絡連接配接IP等即可。
4. 管理工具遷移：部署ADB PG備份及恢複工具，定期備份資料及定期進行恢複演練。

1 資料類型

分析型資料庫PostgreSQL版和Teradata的核心資料類型是互相相容的，僅部分資料類型需要進行修改，通過ADBPG 的自動化轉化工具，可以批量進行TD建表DDL語句的轉換。詳情請參見下表：

Teradata	ADB for PG
char
varchar
long varchar	varchar(64000)
varbyte(size)	bytea
byteint	無，可用bytea替代
smallint
integer
decimal(size,dec)
numeric(precision,dec)
float
real
double precision
date
time
timestamp

2 建表語句

我們通過一個例子比較ADB for PG和Teradata的建表語句。對于如下的Teradata建表SQL語句，

CREATE MULTISET TABLE test_table,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      first_column DATE FORMAT 'YYYYMMDD' TITLE '第一列' NOT NULL,
      second_column INTEGER TITLE '第二列' NOT NULL ,
      third_column CHAR(6) CHARACTER SET LATIN CASESPECIFIC TITLE '第三列' NOT NULL ,
      fourth_column CHAR(20) CHARACTER SET LATIN CASESPECIFIC TITLE '第四列' NOT NULL,
      fifth_column CHAR(1) CHARACTER SET LATIN CASESPECIFIC TITLE '第五列' NOT NULL,
      sixth_column CHAR(24) CHARACTER SET LATIN CASESPECIFIC TITLE '第六列' NOT NULL,
      seventh_column VARCHAR(18) CHARACTER SET LATIN CASESPECIFIC TITLE '第七列' NOT NULL,
      eighth_column DECIMAL(18,0) TITLE '第八列' NOT NULL ,
      nineth_column DECIMAL(18,6) TITLE '第九列' 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;           

可以修改成ADB for PG的建表語句：

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);           

通過以上例子，我們可以很清晰地分析ADB for PG和Teradata建表語句的異同：

1、ADB for PG和Teradata的核心資料類型是互相相容的，資料類型不需要做修改；

2、ADB for PG和Teradata均支援分布列，但文法不同，Teradata是primary index，ADB for PG是distributed by；

3、ADB for PG和Teradata均支援PARTITION BY二級分區，語義相同但文法不同；

4、ADB for PG和Teradata均支援對表建立索引，但文法不同；

5、ADB for PG不支援TITLE關鍵字，但是支援單獨對列添加注釋COMMENT，文法為COMMENT ON COLUMN table_name.column_name IS 'XXX';

6、ADB for PG不能在定義char或者varchar時聲明編碼類型，而是在連接配接上資料庫時，通過執行“SET client_encoding = latin1;”來申明編碼類型。

3 導入導出資料格式

ADB for PG支援txt、csv格式的資料導入導出，和Teradata的差別就在于資料檔案的分隔符：Teradata支援雙分隔符，而ADB for PG隻支援單分隔符。

4 SQL語句

ADB for PG和Teradata的SQL文法大部分都是相容的，除了特定的Teradata文法是需要進行修改的。

4.1 特定文法

4.1.1 cast

Teradata支援類似如下的cast文法：

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

而ADB for PG支援類似cast(XXX as int)、cast(XXX as date)，不支援在cast中聲明format。是以，對于cast(XXX as int format '999999')，需要編寫函數來實作相同功能；而對于cast(XXX as date format 'YYYYMMDD')，ADB for PG支援date的顯示格式為'YYYY-MM-DD'，這個是不影響正常使用的。

4.1.2 qualify

Teradata的qualify關鍵字，用來根據使用者的條件，進一步過濾前序排序計算函數得到的結果。如下是一個Teradata的qualify關鍵字使用例子：

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;           

而ADB for PG是不支援qualify關鍵字的，是以需要将帶qualify的sql語句，修改為子查詢嵌套：

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;           

4.2 macro

Teradata通過macro來執行一組SQL語句，一個典型的macro例子為：

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

ADB for PG不支援macro，但是可以輕易地用ADB for PG的function來完成Teradata的macro功能：

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;           

5 函數轉化

TD與ADB PG函數轉換對照表

		說明
Zeroifnull	Coalesce	對資料作累計處理時，将空值作零處理
NULLIFZERO		對資料作累計處理時，忽略零值
Index	Position	字元串定位函數
Add_months	To_date	從某日期增加或減少指定月份的日期
format	To_char/to_date	函數定義資料格式
csum	可通過子查詢方式實作	計算一列的連續的累計的值
MAVG		基于預定的行數(查詢寬度)計算一列的移動平均值
MSUM		基于預定的查詢寬度計算一列的移動彙總值
MDIFF		基于預定的查詢寬度計算一列的移動差分值
qualify		QUALIFY子句限制排隊輸出的最終結果
Char/characters	length	字元個數