如何从Teradata迁移到Greenplum(下篇)

发布于 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;

 

举例:

如何从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]

 

其中:

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

 

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

如何从Teradata迁移到Greenplum(下篇)-开源基础软件社区

(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关键字后面直接申明,如下图所示:

如何从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;
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的其他转换如下表所示。

如何从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;(获取返回值)

 

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。常用应用接口如下图所示:

如何从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();
        }
    }
}

 

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修改
收藏
回复
举报
回复
添加资源
添加资源将有机会获得更多曝光,你也可以直接关联已上传资源 去关联
    相关推荐