全部產品
Search
文件中心

AnalyticDB:Teradata應用遷移至AnalyticDB PostgreSQL

更新時間:Feb 05, 2024

本文介紹如何將Teradata資料和應用遷移到雲原生資料倉儲AnalyticDB PostgreSQL版

遷移原則

雲原生資料倉儲AnalyticDB PostgreSQL版對Teradata文法有著很好的相容。本指南在將TD數倉應用遷移至AnalyticDB PostgreSQL雲化數倉過程中,秉承充分複用舊系統架構、ETL演算法、資料結構和工具的原則,需對原加工指令碼進行轉換,另外,需對歷史資料進行遷移,並保證資料的準確性,完整性。
  • 對資料倉儲基礎資料平台的完整遷移。
  • 對資料倉儲系統上已部署應用的平滑遷移。
  • 業務外觀透明遷移,保持新舊系統業務操作一致性。
  • 充分保證資料倉儲遷移後的效能。
  • 可接受的系統遷移周期及良好的遷移可操作性。
  • 充分複用舊系統架構、ETL演算法、資料結構和工具。
TD2ADBPG
  1. 歷史資料移轉,首先從TD資料庫按規定分隔字元及字元編碼將歷史資料導成文字檔,存放於AnalyticDB PostgreSQL資料庫網路相通的ECS伺服器本地磁碟或雲端儲存OSS上,確保AnalyticDB PostgreSQL資料庫通過gpfdist協議的外部表格後AnalyticDB PostgreSQL的OSS外部表格能讀取資料檔案。之後從TD匯出DDL指令碼,按AnalyticDB PostgreSQL文法批量修改指令碼,確保在AnalyticDB PostgreSQL能成功建立所有使用者表。
  2. 日常加工流程遷移:對ETL查詢加工語句按AnalyticDB PostgreSQL的DML文法進行轉換(AnalyticDB PostgreSQL構建了相關基於指令碼的自動化轉化工具,可以對文法進行自動mapping轉換),並根據TD與AnalyticDB PostgreSQL函數對照表替換相關函數,轉換ETL串連資料庫方式。重新設定加工作業,歷史資料移轉成功後,啟動日常ETL作業。
  3. 應用介面遷移:AnalyticDB PostgreSQL資料庫支援ODBC/JDBC,BI前端展現等工具可通過ODBC或JDBC標準訪問DW,改動網路連接IP等即可。
  4. 管理工具遷移:部署AnalyticDB PostgreSQL備份及恢複工具,定期備份資料及定期進行恢複演練。

雲原生資料倉儲PostgreSQL版和Teradata的核心資料類型是互相相容的,僅部分資料類型需要進行修改,通過AnalyticDB PostgreSQL的自動化轉化工具,可以批量進行TD建表DDL語句的轉換。詳情請參見下表:

TeradataAnalyticDB PostgreSQL
charchar
varcharvarchar
long varcharvarchar(64000)
varbyte(size)bytea
byteint無,可用bytea替代
smallintsmallint
integerinteger
decimal(size,dec)decimal(size,dec)
numeric(precision,dec)numeric(precision,dec)
floatfloat
realreal
double precisiondouble precision
datedate
timetime
timestamptimestamp

建表語句

我們通過一個建表語句的例子來比較雲原生資料倉儲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函數函數 說明
ZeroifnullCoalesce對資料作累計處理時,將空值作零處理
NULLIFZEROCoalesce對資料作累計處理時,忽略零值
IndexPosition字串定位函數
Add_monthsTo_date從某日期增加或減少指定月份的日期
formatTo_char/to_date函數定義資料格式
csum可通過子查詢方式實現計算一列的連續的累計的值
MAVG可通過子查詢方式實現基於預定的行數(查詢寬度)計算一列的移動平均值
MSUM可通過子查詢方式實現基於預定的查詢寬度計算一列的移動匯總值
MDIFF可通過子查詢方式實現基於預定的查詢寬度計算一列的移動差分值
qualify可通過子查詢方式實現QUALIFY子句限制排隊輸出的最終結果
Char/characterslength字元個數