如何将Teradata应用迁移至AnalyticDB for PostgreSQL

2021-07-21 06:55:17

AnalyticDB for PostgreSQL（简称：ADB for PG）对Teradata语法有着很好的兼容，将Teradata应用迁移到ADB for PG，只需进行有限的修改。本指南在将TD数仓应用迁移至ADB PG云化数仓过程中，秉承充分复用旧系统架构、ETL算法、数据结构和工具的原则，需对原加工脚本进行转换，另外，需对历史数据进行迁移，并保证数据的准确性，完整性。

对数据仓库基础数据平台的完整迁移；
对数据仓库系统上已部署应用的平滑迁移；
业务外观透明迁移，保持新旧系统业务操作一致性；
充分保证数据仓库迁移后的性能；
可接受的系统迁移周期及良好的迁移可操作性；
充分复用旧系统架构、ETL算法、数据结构和工具。

历史数据迁移，首先从TD数据库按规定分隔符及字符编码将历史数据导成文本文件，存放于ADB PG数据库网络相通的ECS服务器本地磁盘或云存储OSS上，确保ADB PG数据库通过gpfidst协议的外部表后ADBPG的OSS外部表能读取数据文件。之后从TD导出DDL脚本，按ADB PG语法批量修改脚本，确保在ADB PG能成功创建所有用户表。
日常加工流程迁移：对ETL查询加工语句按ADB PG的DML语法进行转换（ADBPG构建了相关基于脚本的自动化转化工具，可以对语法进行自动mapping转换），并根据TD与ADB PG函数对照表替换相关函数，转换ETL连接数据库方式。重新配置加工作业，历史数据迁移成功后，启动日常ETL作业。
应用接口迁移：ADB PG数据库支持ODBC/JDBC，BI前端展现等工具可通过ODBC或JDBC标准访问DW，改动网络连接IP等即可。
管理工具迁移：部署ADB PG备份及恢复工具，定期备份数据及定期进行恢复演练。

1 数据类型

分析型数据库PostgreSQL版和Teradata的核心数据类型是互相兼容的，仅部分数据类型需要进行修改，通过ADBPG 的自动化转化工具，可以批量进行TD建表DDL语句的转换。详情请参见下表：

Teradata	ADB for PG
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

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函数转换对照表

Teradata	ADB for PG	说明
Zeroifnull	Coalesce	对数据作累计处理时，将空值作零处理
NULLIFZERO	Coalesce	对数据作累计处理时，忽略零值
Index	Position	字符串定位函数
Add_months	To_date	从某日期增加或减少指定月份的日期
format	To_char/to_date	函数定义数据格式
csum	可通过子查询方式实现	计算一列的连续的累计的值
MAVG	可通过子查询方式实现	基于预定的行数(查询宽度)计算一列的移动平均值
MSUM	可通过子查询方式实现	基于预定的查询宽度计算一列的移动汇总值
MDIFF	可通过子查询方式实现	基于预定的查询宽度计算一列的移动差分值
qualify	可通过子查询方式实现	QUALIFY子句限制排队输出的最终结果
Char/characters	length	字符个数

码农公寓