本文介紹如何將Teradata資料和應用遷移到雲原生資料倉儲AnalyticDB PostgreSQL版。
遷移原則
雲原生資料倉儲AnalyticDB PostgreSQL版對Teradata文法有著很好的相容。本指南在將TD數倉應用遷移至AnalyticDB PostgreSQL雲化數倉過程中,秉承充分複用舊系統架構、ETL演算法、資料結構和工具的原則,需對原加工指令碼進行轉換,另外,需對歷史資料進行遷移,並保證資料的準確性,完整性。
- 對資料倉儲基礎資料平台的完整遷移。
- 對資料倉儲系統上已部署應用的平滑遷移。
- 業務外觀透明遷移,保持新舊系統業務操作一致性。
- 充分保證資料倉儲遷移後的效能。
- 可接受的系統遷移周期及良好的遷移可操作性。
- 充分複用舊系統架構、ETL演算法、資料結構和工具。
- 歷史資料移轉,首先從TD資料庫按規定分隔字元及字元編碼將歷史資料導成文字檔,存放於AnalyticDB PostgreSQL資料庫網路相通的ECS伺服器本地磁碟或雲端儲存OSS上,確保AnalyticDB PostgreSQL資料庫通過gpfdist協議的外部表格後AnalyticDB PostgreSQL的OSS外部表格能讀取資料檔案。之後從TD匯出DDL指令碼,按AnalyticDB PostgreSQL文法批量修改指令碼,確保在AnalyticDB PostgreSQL能成功建立所有使用者表。
- 日常加工流程遷移:對ETL查詢加工語句按AnalyticDB PostgreSQL的DML文法進行轉換(AnalyticDB PostgreSQL構建了相關基於指令碼的自動化轉化工具,可以對文法進行自動mapping轉換),並根據TD與AnalyticDB PostgreSQL函數對照表替換相關函數,轉換ETL串連資料庫方式。重新設定加工作業,歷史資料移轉成功後,啟動日常ETL作業。
- 應用介面遷移:AnalyticDB PostgreSQL資料庫支援ODBC/JDBC,BI前端展現等工具可通過ODBC或JDBC標準訪問DW,改動網路連接IP等即可。
- 管理工具遷移:部署AnalyticDB PostgreSQL備份及恢複工具,定期備份資料及定期進行恢複演練。
雲原生資料倉儲PostgreSQL版和Teradata的核心資料類型是互相相容的,僅部分資料類型需要進行修改,通過AnalyticDB PostgreSQL的自動化轉化工具,可以批量進行TD建表DDL語句的轉換。詳情請參見下表:
Teradata | AnalyticDB PostgreSQL |
char | char |
varchar | varchar |
long varchar | varchar(64000) |
varbyte(size) | bytea |
byteint | 無,可用bytea替代 |
smallint | smallint |
integer | integer |
decimal(size,dec) | decimal(size,dec) |
numeric(precision,dec) | numeric(precision,dec) |
float | float |
real | real |
double precision | double precision |
date | date |
time | time |
timestamp | timestamp |
建表語句
我們通過一個建表語句的例子來比較雲原生資料倉儲PostgreSQL版和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;
雲原生資料倉儲PostgreSQL版的建表語句如下:
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);
通過以上例子,我們可以清晰地發現雲原生資料倉儲PostgreSQL版和Teradata建表語句的異同:
- 核心資料類型互相相容,資料類型無需修改。
- 均支援分布列,但文法不同,Teradata使用的是primary index,雲原生資料倉儲PostgreSQL版使用的是distributed by。
- 均支援PARTITION BY二級分區,語義相同但文法不同。
- 均支援對錶建立索引,但文法不同。
- 雲原生資料倉儲PostgreSQL版不支援TITLE關鍵字,但是支援單獨對列添加註釋COMMENT,文法為
COMMENT ON COLUMN table_name.column_name IS 'XXX';
- 雲原生資料倉儲PostgreSQL版不支援在定義char/varchar時聲明編碼類別型,可以在串連資料庫時,通過執行
SET client_encoding = latin1;
來聲明編碼類別型。
匯入匯出資料格式
雲原生資料倉儲PostgreSQL版和Teradata均支援txt、csv格式的資料匯入匯出,與Teradata的區別在於資料檔案的分隔字元。
- Teradata支援雙分隔字元。
- 雲原生資料倉儲PostgreSQL版支援單分隔字元。
SQL語句
雲原生資料倉儲PostgreSQL版和Teradata的大部分SQL文法都是相容的,僅有部分Teradata文法需要進行修改。需要修改的文法如下所示:
- cast
Teradata支援如下的cast文法:
cast(XXX as int format '999999') cast(XXX as date format 'YYYYMMDD')
而雲原生資料倉儲PostgreSQL版支援如下cast文法:
cast(XXX as int) cast(XXX as date)
雲原生資料倉儲PostgreSQL版不支援在cast中聲明format。
- 對於
cast(XXX as int format '999999')
,需要編寫函數來實現相同功能。 - 對於
cast(XXX as date format 'YYYYMMDD')
,雲原生資料倉儲PostgreSQL版支援date的顯示格式為'YYYY-MM-DD'
,不影響正常使用。
- 對於
- 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;
而雲原生資料倉儲PostgreSQL版不支援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;
- macro
Teradata通過macro來執行一組SQL語句,如下所示:
CREATE MACRO Get_Emp_Salary(EmployeeNo INTEGER) AS ( SELECT EmployeeNo, NetPay FROM Salary WHERE EmployeeNo = :EmployeeNo; );
雲原生資料倉儲PostgreSQL版不支援macro,但是可以使用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;
函數轉化
TD與AnalyticDB PostgreSQL函數轉換對照表
TD函數 | 函數 | 說明 |
Zeroifnull | Coalesce | 對資料作累計處理時,將空值作零處理 |
NULLIFZERO | Coalesce | 對資料作累計處理時,忽略零值 |
Index | Position | 字串定位函數 |
Add_months | To_date | 從某日期增加或減少指定月份的日期 |
format | To_char/to_date | 函數定義資料格式 |
csum | 可通過子查詢方式實現 | 計算一列的連續的累計的值 |
MAVG | 可通過子查詢方式實現 | 基於預定的行數(查詢寬度)計算一列的移動平均值 |
MSUM | 可通過子查詢方式實現 | 基於預定的查詢寬度計算一列的移動匯總值 |
MDIFF | 可通過子查詢方式實現 | 基於預定的查詢寬度計算一列的移動差分值 |
qualify | 可通過子查詢方式實現 | QUALIFY子句限制排隊輸出的最終結果 |
Char/characters | length | 字元個數 |