回复
如何从Teradata迁移到Greenplum（下篇）
delphi6fans
发布于 2022-5-19 17:25
浏览
0收藏
在如何从Teradata迁移到Greenplum（上篇）中，向大家介绍了Teradata产品和用户面临的问题、从Teradata迁移到Greenplum的可行性以及如何从Teradata迁移到Greenplum的部分内容，今天将接着和大家分享如何从Teradata迁移到Greenplum的具体内容。
03数据操作语句(DML)转换
1. 关键字转换
在Teradata中，SELECT关键字可以简写为SEL，而在Greenplum中不支持这种简写。转换规则如下：把SEL替换为标准关键字写法SELECT。
注意：该转换不建议全局替换，因为有可能字段名为SEL，因此需要手动搜索脚本进行转换。
2. 别名关键字转换
在Teradata中，字段的别名除了可以通过AS子句指定外，还可以通过NAMED子句指定；而在Greenplum中，不支持NAMED子句，因此，需要进行转换。
注意：该转换不建议全局替换，因为有可能字段名为NAMED，因此需要手动搜索脚本进行转换。
3. 子查询别名转换
在Teradata中子查询不需要指定别名，在Greenplum中子查询需要给予别名。转换规则如下：
SELECT column1
FROM table1
WHERE column2 IN
(SELECT column1
FROM table2 );
转换为：
SELECT column1
FROM table1
WHERE column2 IN
(SELECT column1
FROM table2 ) as alias_name;
注意：该转换需要手动搜索脚本进行转换。
4. 字段别名转换
在Teradata中，字段引用别名后，直接可以引用别名进行其他操作。但是，Greenplum不支持在定义字段别名后，直接通过别名对字段进行其他操作。只能通过嵌套子查询的方式进行转换。
SELECT
sum(column1) as alias1,
alias1/12 as alias2
FROM table1;
转换为：
SELECT
Sum(column1) as alias1,
Sum(column1)/12 as alias2
FROM table1
WHERE column2 IN
(SELECT column1
FROM table2 ) as alias_name;
举例：
注意：该转换需要手动搜索脚本进行转换。
5. 调用函数区别
在查询中调用函数，绝大多数的Teradata支持的标准化函数Greenplum都支持。对于部分Teradata特有的函数，Greenplum虽然没有相同的函数名，但是有相应的函数或者解决方式，请参考后面04 函数转换的内容进行相应转换。
注意：该转换需要手动搜索脚本进行转换。
6. Delete
（1）在不关联其他表，本地根据条件删除记录时，Teradata与Greenplum的语法相同，无须转换；需要关联其他表删除符合条件的记录时，Teradata与Greenplum如果使用子查询的方式，则语法相同；如果通过直接连接的方式，则语法不同，需要进行相应的转换。Greenplum从目标表删除符合条件记录语句的语法如下：
DELETE FROM [ONLY] table [[AS] alias]
[USING usinglist]
[WHERE condition]
其中：
- table：需要删除记录的目标表名。
- alias：需要删除记录的目标表名的别名。
- usinglist：关联其他表筛选出所要删除记录的子集。
- condition：筛选出所要删除记录的条件。
转换规则为：子查询的关联方式无须转换，通过表连接的方式，Greenplum需要通过USING关键字来申明连接表表名。转换示例如下：
（2）在Teradata中，删除所有数据可使用DELETE FORM TABLE，该方式在Greenplum中是不推荐。Greenplum采用TRUNCATE的方式来完成对整表的删除，语法如下：
TRUNCATE TABLE schemaname.tablename;
注意：该转换需要手动搜索脚本进行手工转换。
7. Update
Teradata数据库的UPDATE操作与Greenplum基本相同，但是关联表进行更新时，Teradata的FROM子句在前，而SET子句在后；而Greenplum刚好相反，且Teradata需要在FROM子句声明更新表及被关联表，Greenplum只需要声明被关联表，更新表则在UPDATE关键字后面直接申明，如下图所示：
注意：该转换需要手动搜索脚本进行转换。
8. Insert
Teradata与Greenplum在INSERT操作语法上没有任何区别，其中Teradata的INSERT-SELECT方式在Greenplum中也支持，无须进行转换。
04函数转换
1.Teradata与Greenplum函数转换规则
Teradata与Greenplum的函数转换规则如下表所示。
2.函数转换示例
（1）QUALIFY 函数
将QUALIFY ROW_NUMBER() OVER () =1 语法修改为下图所示的形式。
（2）CHAR函数
在Greenplum数据库部署CHAR函数，函数定义如下：
CREATE OR REPLACE FUNCTION "character"(text)
RETURNS integer AS
$BODY$select length($1)$BODY$
LANGUAGE 'sql' IMMUTABLE STRICT;CREATE OR REPLACE FUNCTION "char"(text)
RETURNS integer AS
$BODY$select length($1)$BODY$
LANGUAGE 'sql' IMMUTABLE STRICT;
（3）CHAR_CHN函数
在Greenplum数据库部署CHAR_CHN函数，函数定义如下：
CREATE OR REPLACE FUNCTION "char_chn"(text)
RETURNS integer AS
$BODY$select octet_length(convert($1,'UNICODE','GBK'))$BODY$
LANGUAGE 'sql' IMMUTABLE STRICT;
（4）ZEROIFNULL函数
在Greenplum数据库部署ZEROIFNULL函数，函数定义如下：
CREATE OR REPLACE FUNCTION zeroifnull(anyelement)
RETURNS integer AS
$BODY$select coalesce($1,0)::integer $BODY$
LANGUAGE 'sql' IMMUTABLE ;
（5）其他转换
Teradata和Greenplum的其他转换如下表所示。
（6）数据加载转换
Teradata和Greenplum数据加载比较如下表所示。
Greenplum有两种加载数据工具，包括外部表或者COPY工具。一般来说，如果加载数据量较小时，Greenplum推荐使用COPY工具；但是如果批量数据或者数据量较大时，推荐使用外部表的模式，因为外部表的模式为通过节点并行加载，而COPY则是通过Master主机非并行加载，外部表的加载速度比COPY快N倍。
05ETL应用工具连接转换
1. 在Perl中通过PSQL命令连接数据库
在Perl中通过PSQL命令连接数据库的方法如：
open(PSQL, "|psql -a -v ON_ERROR_STOP=1");
print PSQL <<ENDOFINPUT;
......（SQL ）
ENDOFINPUT
close(PSQL);
my $RET_CODE = $? >> 8;（获取返回值）
2. 在Perl中通过DBI连接数据库
在Perl中通过DBI连接数据库的方法如下：
use DBI;
use DBD::Pg;
$dbh = DBI->connect("dbi:Pg:dbname=${ETL::ETL_DSN};host=$hostname;port=$port;", $username, $decodepass) ;
my $sqlText = "UPDATE ${ETL::ETLDB}ETL_Job SET JobSessionID = JobSessionID + 1" .
" WHERE ETL_System = '$sys' AND ETL_Job = '$job'";
my $sth = $dbh->prepare($sqlText) or return $ETL::FALSE;
my $ret = $sth->execute();
$sth->finish();
$dbh->disconnect()
3. 基于Perl PSQL连接数据库函数
基于Perl PSQL连接数据库函数的方法如下：
##函数名：run_psql_command
##参 数：$gp_database 数据库名
## $gp_db_ip Master服务器IP
## $db_port 数据库端口号
## $db_usr 数据库用户名
## $ext_sql SQL语句
##功 能：通过DBI执行SQL语句
##返回值：0 执行成功
## 1 执行失败
######################################################################
# PSQL function
sub run_psql_command
{
my $rc = open(PSQL, "|psql -a -v ON_ERROR_STOP=1 -d $gp_database -p $db_port –U $db_usr ");
# To see if PSQL command invoke ok?
unless ($rc) {
print "Could not invoke PSQL command\n";
return -1;
}
### Below are PSQL scripts ###
print PSQL <<ENDOFINPUT;
$ext_sql
ENDOFINPUT
### End of PSQL scripts ###
close(PSQL);
my $RET_CODE = $? >> 8;
# if the return code is 12, that means something error happen
# so we return 1, otherwise, we return 0 means ok
if ( $RET_CODE !=0 ) {
return 1;
}
else {
return 0;
}
}
4. 基于Perl DBI连接数据库函数
基于Perl DBI连接数据库函数的方法如下：
##函数名：execute_sql_dbi
##参 数：$gp_database 数据库名
## $gp_db_ip Master服务器IP
## $db_port 数据库端口号
## $db_usr 数据库SCHEMA
## $db_pwd 源系统编码
## $ext_sql SQL语句
##功 能：通过DBI执行SQL语句
##返回值：0 执行成功
## 1 执行失败
######################################################################
sub execute_sql_dbi
{
my (“DBI:Pg:dbname=$gp_database;host=$gp_db_ip;port=$db_port”, $db_usr, $db_pwd, $ext_sql) = @_;
print_log("[Info][ETL_BASE-execute_sql_dbi] EXECUTE SQL USE DBI");
print "[Info][ETL_BASE-execute_sql_dbi] ext_sql:\n$ext_sql\n";
my $dbh;
my $sth;
eval{
$dbh = DBI->connect("DBI:Pg:dbname=$gp_database;host=$gp_db_ip;port=$db_port", $db_usr, $db_pwd,{ AutoCommit => 1, PrintError => 1, RaiseError => 1 });
if (not defined($dbh)){
die ("[Error][ETL_BASE-execute_sql_dbi] Can not Connect To DB !\n");
}
$sth = $dbh->prepare($ext_sql);
unless($sth){
die ("[Error][ETL_BASE-execute_sql_dbi] Unable to prepare statement for $ext_sql\n");
}
$sth->execute() or die ("[Error][ETL_BASE-execute_sql_dbi] Error when execute SQL statement!\n");
$sth->finish();
$dbh->disconnect();
};
if ($@) {
warn "[Error][ETL_BASE-execute_sql_dbi] Transaction aborted because\n $@";
if(defined($sth)){
$sth->finish();
}
if (defined($dbh)){
$dbh->disconnect();
}
return -1;
};
return 0;
}
06其他应用接口迁移
Greenplum支持使用标准数据库应用接口。例如 ODBC、JDBC 的客户端程序，Perl DBI及Python DBI可以通过配置的方式连接到 Greenplum。常用应用接口如下图所示：
如果是ODBC、JDBC 的客户端程序，那么可通过常规配置连接Greenplum数据库。
通过JDBC连接Greenplum的示例代码如下，可以直接使用postgresql最新版本的jdbc驱动。由于Greenplum中执行的大多是较为复杂的SQL，SQL解析的时间基本上可以忽略，建议在配置JDBC连接时将preferQueryMode设置为simple，强制每次查询进行解析和重新生成执行计划，避免执行计划出错。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.util.Properties;
public class HelloWorld {
public static void main(String[] args) {
String url = "jdbc:postgresql://192.168.254.136:5432/pivotal";
Properties props = new Properties();
props.setProperty("user", "gpadmin");
props.setProperty("password", "gpadmin");
props.setProperty("preferQueryMode", "simple");
try {
Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
Connection conn = DriverManager.getConnection(url, props);
PreparedStatement ps = conn.prepareStatement("select count(*) as cnt from hello where id=?");
ps.setInt(1, 100);
ResultSet resultSet = ps.executeQuery();
while (resultSet.next()) {
System.out.printf("%-30.30s", resultSet.getString("cnt"));
}
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
04特殊场景
在Teradata中，可以通过声明表的类型为set，在数据加载过程中自动去掉重复记录，在Greenplum中也可以实现同样的功能。下面介绍两种方案。
01事前微批去重
通过在heap表上创建主键索引，可以在数据加载过程中，利用主键进行去重。目前，Greenplum还不支持INSERT ON CONFLICT 语法，但可以通过函数实现同样的功能。在数据通过外部表往目标表导入，当违反唯一性约束时，通过join的方式找出非重复记录插入目标表。具体函数代码如下：
CREATE OR REPLACE FUNCTION merge_table(targettable character varying, srctable character varying) RETURNS bigint AS
$BOY$
DECLARE
v_targettable varchar := $1;
v_srctable varchar :=$2;
v_left_join_sql varchar;
v_left_join_condition varchar;
v_insert_sql varchar;
v_merge_sql varchar;
v_insert_cnt bigint;
BEGIN
set enable_hashjoin=off;
set enable_nestloop=on;
v_insert_sql :='insert into '||v_targettable||' select * from '||v_srctable;
EXECUTE v_insert_sql;
GET DIAGNOSTICS v_insert_cnt = ROW_COUNT;
RETURN v_insert_cnt;
EXCEPTION WHEN unique_violation THEN
v_left_join_sql :=$$SELECT 'on (src.'||string_agg(a.attname::text,',src.')||')'||'='||'(target.'||string_agg(a.attname::text,',target.')||') where '||'(target.'||string_agg(a.attname::text,',target.')||') is null ' FROM pg_index i JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY( i.indkey) WHERE i.indrelid = '$$||v_targettable||$$'::regclass AND i.indisprimary;$$;
EXECUTE v_left_join_sql into v_left_join_condition;
v_merge_sql :='insert into '||v_targettable||' select distinct src.* from '||v_srctable||' as src left join '||v_targettable||' as target '||v_left_join_condition;
EXECUTE v_merge_sql;
GET DIAGNOSTICS v_insert_cnt = ROW_COUNT;
RETURN v_insert_cnt;
END;
$BOY$
LANGUAGE plpgsql volatile;
注意：这种方式适用于对数据质量要求比较高，要求目标表在任何时刻都不能有重复记录的情况。比如，某金融客户用Greenplum做实时监查，交易数据通过kafka做流转，源端有可能发送重复的记录，必须借助下游数据库实现数据的去重。另外，由于目前AO表不支持主键索引，无法通过上述函数实现，可以通过将其改写成delete+insert的方式实现。
02事后批量去重
当数据全部入库后，可通过对数据分组找出重复记录，然后删除原有表的重复记录。
生成测试数据：
create table hello(id int,name text) with(appendonly=true,compresstype=zlib,compresslevel=5,orientation=column) distributed randomly;
insert into hello select generate_series(1,10000000),'good';
insert into hello select generate_series(1,10000000,100),'good';
insert into hello select generate_series(1,10000000,200),'good';
insert into hello select generate_series(1,10000000,300),'good';
去重操作：
delete from hello using
(select min(gp_segment_id||ctid::text) as gctid,id,name from hello group by id,name having count(*)>1) foo
where gp_segment_id||ctid::text<>gctid and hello.id=foo.id and hello.name=foo.name;
注意：这种方式对于heap表和AO表同样有效，通过gp_segment_id和ctid字段的组合可以实现分布式环境下数据的精确定位，最后删除多余的记录。
文章转自公众号：Greenplum中文社区
分类
标签
已于2022-5-19 17:25:39修改
赞
收藏
回复
相关推荐
