如何从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 );1.
2.
3.
4.
5.

转换为：

SELECT column1
FROM table1
WHERE column2 IN
(SELECT column1
FROM table2 ) as alias_name;1.
2.
3.
4.
5.

注意：该转换需要手动搜索脚本进行转换。

4. 字段别名转换

在Teradata中，字段引用别名后，直接可以引用别名进行其他操作。但是，Greenplum不支持在定义字段别名后，直接通过别名对字段进行其他操作。只能通过嵌套子查询的方式进行转换。

SELECT
sum(column1) as alias1,
alias1/12 as alias2
FROM table1;1.
2.
3.
4.

转换为：

SELECT
Sum(column1) as alias1,
Sum(column1)/12 as alias2
FROM table1
WHERE column2 IN
(SELECT column1
FROM table2 ) as alias_name;1.
2.
3.
4.
5.
6.
7.

举例：

如何从Teradata迁移到Greenplum（下篇）-鸿蒙开发者社区

注意：该转换需要手动搜索脚本进行转换。

5. 调用函数区别

在查询中调用函数，绝大多数的Teradata支持的标准化函数Greenplum都支持。对于部分Teradata特有的函数，Greenplum虽然没有相同的函数名，但是有相应的函数或者解决方式，请参考后面04 函数转换的内容进行相应转换。

注意：该转换需要手动搜索脚本进行转换。

6. Delete

（1）在不关联其他表，本地根据条件删除记录时，Teradata与Greenplum的语法相同，无须转换；需要关联其他表删除符合条件的记录时，Teradata与Greenplum如果使用子查询的方式，则语法相同；如果通过直接连接的方式，则语法不同，需要进行相应的转换。Greenplum从目标表删除符合条件记录语句的语法如下：

DELETE FROM [ONLY] table [[AS] alias] 
[USING usinglist] 
[WHERE condition]1.
2.
3.

其中：

table：需要删除记录的目标表名。
alias：需要删除记录的目标表名的别名。
usinglist：关联其他表筛选出所要删除记录的子集。
condition：筛选出所要删除记录的条件。

转换规则为：子查询的关联方式无须转换，通过表连接的方式，Greenplum需要通过USING关键字来申明连接表表名。转换示例如下：

如何从Teradata迁移到Greenplum（下篇）-鸿蒙开发者社区

（2）在Teradata中，删除所有数据可使用DELETE FORM TABLE，该方式在Greenplum中是不推荐。Greenplum采用TRUNCATE的方式来完成对整表的删除，语法如下：

TRUNCATE TABLE schemaname.tablename;1.

注意：该转换需要手动搜索脚本进行手工转换。

7. Update

Teradata数据库的UPDATE操作与Greenplum基本相同，但是关联表进行更新时，Teradata的FROM子句在前，而SET子句在后；而Greenplum刚好相反，且Teradata需要在FROM子句声明更新表及被关联表，Greenplum只需要声明被关联表，更新表则在UPDATE关键字后面直接申明，如下图所示：

如何从Teradata迁移到Greenplum（下篇）-鸿蒙开发者社区

注意：该转换需要手动搜索脚本进行转换。

8. Insert

Teradata与Greenplum在INSERT操作语法上没有任何区别，其中Teradata的INSERT-SELECT方式在Greenplum中也支持，无须进行转换。

04函数转换

1.Teradata与Greenplum函数转换规则

Teradata与Greenplum的函数转换规则如下表所示。

如何从Teradata迁移到Greenplum（下篇）-鸿蒙开发者社区

2.函数转换示例

（1）QUALIFY 函数
将QUALIFY ROW_NUMBER() OVER () =1 语法修改为下图所示的形式。

如何从Teradata迁移到Greenplum（下篇）-鸿蒙开发者社区

（2）CHAR函数

在Greenplum数据库部署CHAR函数，函数定义如下：

CREATE OR REPLACE FUNCTION "character"(text)
    RETURNS integer AS
  $BODY$select  length($1)$BODY$
    LANGUAGE 'sql' IMMUTABLE STRICT;1.
2.
3.
4.

CREATE OR REPLACE FUNCTION "char"(text)
    RETURNS integer AS
  $BODY$select  length($1)$BODY$
    LANGUAGE 'sql' IMMUTABLE STRICT;1.
2.
3.
4.

（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;1.
2.
3.
4.

（4）ZEROIFNULL函数
在Greenplum数据库部署ZEROIFNULL函数，函数定义如下：

CREATE OR REPLACE FUNCTION zeroifnull(anyelement)
RETURNS integer AS
$BODY$select  coalesce($1,0)::integer $BODY$
LANGUAGE 'sql' IMMUTABLE ;1.
2.
3.
4.

（5）其他转换

Teradata和Greenplum的其他转换如下表所示。

如何从Teradata迁移到Greenplum（下篇）-鸿蒙开发者社区

（6）数据加载转换

Teradata和Greenplum数据加载比较如下表所示。

如何从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;（获取返回值）1.
2.
3.
4.
5.
6.

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()1.
2.
3.
4.
5.
6.
7.
8.
9.

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;
   }
}1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.

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;
}1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.

06其他应用接口迁移

Greenplum支持使用标准数据库应用接口。例如 ODBC、JDBC 的客户端程序，Perl DBI及Python DBI可以通过配置的方式连接到 Greenplum。常用应用接口如下图所示：

如何从Teradata迁移到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();
        }
    }
}1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.

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;1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.

注意：这种方式适用于对数据质量要求比较高，要求目标表在任何时刻都不能有重复记录的情况。比如，某金融客户用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;1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

注意：这种方式对于heap表和AO表同样有效，通过gp_segment_id和ctid字段的组合可以实现分布式环境下数据的精确定位，最后删除多余的记录。

文章转自公众号：Greenplum中文社区

分类

其他

标签

Greenplum

已于2022-5-19 17:25:39修改

51CTO

51CTO博客

51CTO学堂

如何从Teradata迁移到Greenplum（下篇）

订阅鸿蒙技术特刊，精选内容抢先看