1、teredata业务改写注意事项

业务改写包括以下几个方面，包括环境参数设置、兼容语法修改、性能优化和错误处理。

1.1 环境参数设置

在脚本开始位置，添加打开sql执行时间显示设置。

\timing on

\timing on可以添加到~/.psqlrc里面，启动gsql会自动执行~/.psqlrc脚本。

在脚本结尾位置添加作业主表的analyze语句。例如，

analyze edisum.e01_cust_belong_info;

1.2 兼容语法修改

td作业日志中所有的日期常量在gaussdb(dws)脚本中都要替换成见名知义的变量，以便用gsql调度作业时可以动态传入这些变量，适应按日期跑批地要求。日期变量可以用文本工具批量替换，即把出现在td日志中的同一日期常量替换成变量形式，

gsql命令长度本身没有限制，它只依赖linux系统对命令参数长度的限制。单个参数长度上限可参考/usr/include/linux/limits.h中的arg_max定义（默认131072字节），参数总长度不能超过1/4 stack size（ulimit -s），默认值2048字节。

1.3 性能优化

delete语句

对分区表的分区键做delete的语句和直接delete全表的语句需要改写成truncate，例如

delete from table1;

=>truncate table1;

delete分区表

delete from e01_cust_belong_info where work_dt=’***’; （work_dt是分区键）

=>alter table table1 truncate partition e01_cust_belong_info_p:tx_date;

临时表

td的临时表是session级的，每个作业由不同的session调度，各个session之间可以使用同名临时表。在gaussdb(dws)脚本中临时表是全局性的，原则上不能同名，以免调度或空跑时冲突。针对每个作业，临时表名都要调整为‘作业名_temp_数字’的格式，调整时注意避免混淆。

gaussdb(dws)表名长度限制为64字节。

统计信息收集

统计信息收集是获取好的执行计划的基本保证。edi系统中有以下几类表，分别列出其统计信息收集的时机：

%2. 基础表（ediods模式表）：存储存量数据，在由gds导入到数据库后做analyze；每天追批导入新的增量数据后做analyze

%2. 作业主表（edisum/edi*mart）：在相关作业的最后做analyze

%2. 临时表：在最后一次insert数据之后或使用其作为sql输入表之前做analyze

%2. 另外，由于各种原因手工重建以上表时需同时做analyze

补充：做analyze的目的是让优化器可以准确的估算代价，产生好的执行计划，因此analyze的原则是在查询该表前最后一次更新该表数据时增加analyze语句。

临时表分布列选择

对于脚本内部出现的临时表，gaussdb(dws)需要仔细选择分布列，在create unlogged table结尾显式标明分布列，例如：

create unloggedtable t1 (

col1,

col2,

col3,

…)

distribute by hash(col1);

选择的原则如下：

%2. 选择长度大的字段，一般选择长度15字节以上的字段

%2. 选择号码类的字段，比如cino，cardno等

%2. 如1)和2)仍然不能确定分布列，可以实际各列的distinct值数目来定

%2. 无需使用seq_num，实在找不出就选择一个字段

复制表

参与大型查询的小表（如参数表）可以改成复制表，以消除其对数据重分布的影响。改复制表在临时表中比较常见，比如cst_cust_stru_a作业中的cst_cust_stru_a_tmp_cust_type临时表，它只存储了32种客户类型组合，即32条记录，如果该表导致执行计划中产生多余的重分布streaming算子就可以把它改成复制表。不同数据量和并发度的系统复制表的选择标准不同，视具体场景而定。

一般同时符合以下条件的表可以做成复制表

%7. 宽度小于1024字节

%7. 记录数小于10000

not in

edi所有的表列属性都已设置为not null，因此not in可以全部修改为not exists，以选择hash-anti-join执行计划。例如

select * from t1 where t1.c1 not in (select t2.c2 from t2);

=>

select * from t1 where not exists (select * from t1,t2 where t1.c1=t2.c2);

1.4 错误处理

脚本改写完成后需要在测试环境带数运行，以排除脚本和数据之间不能匹配造成的错误。作业脚本改写完成后，需要处理以下几类错误：

• 修改insert数据超长错误。

teradata的char/varchar类型字段对于insert超长数据的默认行为是自动靠左截断，fusioninsight gaussdb(dws)如果遇到超长数据insert则会报错。这在脚本带数运行时是常见的错误，大部分情况在insert数据时按照目标字段长度用substr函数截断即可，但是不能完全排除teradata导出数据错误，需要进一步和业务人员确认。

典型的错误如下：

error: value too long for type character(num)

典型的sql如下：

insert into table1
col1,
col2,
select
c1, #修改为substr(c1,1,length)
c2,
from
t1;

修改时需要检查insert目标列（col1）的长度，在select列上面增加按此长度截断的substr函数。

• 修改日期越界错误。

在实际作业中有少量日期越界错误，原因是teradata导出的数据和作业逻辑不相符。

典型的日期错误如下：

error: date/time field value of out range: "31090000"

该错误原因是insert日期字段的输入数据是在某个字符型字段中截取的部分数据。为了让作业跑通，可以暂时删除无效数据，之后和业务相关人员确认处理措施。

• 处理非空约束。

在实际业务中，存量表字段都加上了非空约束，带数运行时如果数据产生空值就会报错，可以在insert字段时增加coalesce函数规避，空值产生的原因需要和业务相关人员确认。

典型的非空约束错误如下：

error: null value in column "prod_id" violates not-null constraint

典型的sql如下：

insert into table1
col1,
col2,
select
c1, #修改为 coalesce(c1,'')
c2,
from
t1;

修改时检查报错insert字段的属性，如果该字段属性是not null，则在该字段加上coalesce函数，在数据为null时转换成空串''。

2、 gaussdb(dws)与td语法差异（易犯错部分）

1.%2 ddl语法差异

1.%2.%3 普通表的差异

1.1.%3.%4 临时表差异

压缩：gaussdb(dws)不支持字段属性上添加compress，但凡遇到类似情况，去掉compress即可。其他未尽事项，请参照ddl差异和字段类型差异适当改造。

2.%2.%3 数据类型差异：

1.%2.%3.%4 字段类型差异

char类型：

a. format转换方法：

format案例：

1

1.1

1.2

1.3

1.3.1

2.%2.%3.%4 date类型

1. gaussdb(dws) date类型，没有format关键字，且默认日期格式为yyyy-mm-dd。td默认日期格式为8位。

2. 对于形如2016-001的日期，gaussdb(dws)认为合法，td认为不合法

综合以上两点，容易产生这类问题：

例如，获取当月1号的日期，td原始语法：

select cast( substr( trim( cast('20160501' as date format 'yyyymmdd') ) ,1,6 )||'05' as date format 'yyyymmdd');

td中得到日期型：2016-05-05

翻译gaussdb(dws)后，直接去除format 'yyyymmdd'关键字，得到：

gaussdb(dws)中得到日期型：2016-01-05

由于2016-001在gaussdb(dws)中意为：从2016年开始算的第1天。

在gaussdb(dws)中cast('20160501' as date)得到日期型2016-05-01，

trim(cast('20160501' as date)) 之后，得到2016-0（由于日期型默认是10位格式，而gaussdb(dws)无法使用日期format），

substr(trim(cast('20160501' as date)),1,6 )||'05'之后，得到2016-005，

最后cast(substr(trim(cast('20160501' as date)),1,6 )||'05' as date)，得到2016年的第5天，即日期型2016-01-05。

3.%2.%3.%4 varchar类型

对于空白字符串与空字符串的比较，gaussdb(dws)认为不相等，td认为相等。

因此如果遇到字段中有空白，翻译到gaussdb(dws)之后，要加上trim才可以做到与td保持一致。

例如：

select case when ''=' ' then 1 else 0 end;

td返回1

gaussdb(dws)返回0

对于右边带空格的varchar类型比较，td会去除空格之后再比较，gaussdb(dws)不会。

select case when '2' < '2 ' then 1 else 0 end;

td返回0

gaussdb(dws)返回1

4.%2 函数与值差异

1.3.2

4.2.%3 函数迁移对照关系

自定义函数统一建立到所属数据库的public模式中，用户可以直接访问无需模式名称。

现有td自定义函数，保持功能不变迁移到新平台，列表如下：

a. td与gaussdb无差异函数

函数列表（持续完善）

b. td支持但gaussdb不支持，且存量脚本使用的函数，保持功能不变迁移到新平台。

函数列表（持续完善）

c. td支持但gaussdb有同样函数，改造过程中通过脚本转换工具统一替换。

函数对照表（持续完善）

4.3.%3 null值

由于语法差异：null与字符串拼接，gaussdb(dws)得到字符串，td得到null

造成在脚本中使用coallesce判空时，容易造成差异，例如：

脚本中会在coallesce中使用两个值拼接，一个为null，一个为字符串coallesce(null||’abc’,’1’)，在gaussdb(dws)与td得到结果不同：

gaussdb(dws)：abc

td：1

修改方法：

coalesce('ic'||t8.comp_org_num,'')

改为

coalesce(decode(t8.comp_org_num,null,null,'ic'||t8.comp_org_num),'')

4.4.%3 coallesce变量类型

coallesce后的变量，gaussdb(dws)中需要变量类型相同，td中变量类型可以不同。

但目前版本（gauss200 olap v100r005c10 build 5562）现状为：

如果coalesce后的2个参数都为常量，第一个参数为常量，就可以做隐式转换，不报错。

mppedi=> select coalesce('1',1) from dual;

coalesce

----------

1

(1 row)

mppedi=> select coalesce('1',b) from test_v;

coalesce

----------

1

1

1

(3 rows)

如果coalesce后的2个参数，如果第一个参数为变量，则后面的参数类型必须与第一个一致，否则报错。

mppedi=> \d test_v

table "public.test_v"

column | type | modifiers

-------- ---------------------- -----------

a | character varying(1) |

b | numeric(1,0) |

mppedi=> select coalesce(a,1) from test_v;

error: coalesce types character varying and integer cannot be matched

mppedi=> select coalesce(a,b) from test_v;

error: coalesce types character varying and numeric cannot be matched

line 1: select coalesce(a,b) from test_v;

^

context: referenced column: coalesce

4.5.%3 窗口函数差异

rank 在gaussdb(dws)里默认升序，在td默认降序。

4.6.%3 数值计算精度差异

select

case when ( cast('2100001' as decimal(17,0)) /100.00 - cast('2000000' as decimal(17,0)) /100.00) / (cast('2000000' as decimal(17,0)) /100.00)<=0.05

then 'a'

else 'b'

end;

gaussdb(dws) 为 b，td为 a

4.7.%3 like any差异

gaussdb(dws)没有like any语法，直接使用td语法会报中断，目前翻译工具无法翻译改语法，需手工修改。

4.8.%3 like any常量值的修改方法：

td原文：where a.memo like any (‘%水费%’,’%电费%’,’%煤气费%’)

gaussdb(dws)需修改为：

where a.memo like ‘%水费%’

or a.memo like ’%电费%’

or a.memo like ’%煤气费%’

4.9.%3 like any集合的写法：

td原文：

(case when t31.chrem_prod_cd is not null then substr(prod1,2) else prod1 end) like any (select distinct t1.chrem_fund_id||'%' --理财：取前n位判断

from ${ediodsviewdb}.ed3_das_pbk_cash_mgmt_1 t1

where t1.prod_sort = '1' --现金管理类产品

and t1.in_ind = '1' --in判断

)

改写方法：

select * from jobs e join jobs j on e.job_title similar to j.job_title||'%';

由于该写法不能下推，因此只能适用于数据量不大的表

5.%2 分区差异

gaussdb(dws)与td在分区定义及使用上差异较大，td可以定义区间，灵活指定分区间隔，而gaussdb(dws)必须显示指定每一个分区的区间。

定义：

td：

create multiset table……

primary index ( agt_num )

partition by ( range_n(data_dt between date '2007-01-01' and '2014-12-31' each interval '1' year , date '2015-01-01' and date '2016-12-31' each interval '1' day , no range or unknown));

gaussdb(dws)：

5.2.%3 建表分区定义

create table if not exists ediods.ifs_othcpdtl

……

partition by range (pt_dt)

(

partition ediods_ifs_othcpdtl_p20170101 values less than ('2017-01-02'),

partition ediods_ifs_othcpdtl_p20170102 values less than ('2017-01-03'),

……

partition ediods_ifs_othcpdtl_p20171231 values less than ('2018-01-01'),

partition ediods_ifs_othcpdtl_p99991231 values less than (maxvalue)

)

5.3.%3 修改增加分区

行存表

alter table ediods.ifs_othcpdtl split partition ediods_ifs_othcpdtl_p99991231 into (

partition ediods_ifs_othcpdtl_p20180101 values less than ('2018-01-02'),

partition ediods_ifs_othcpdtl_p20180102 values less than ('2018-01-03'),

……

partition ediods_ifs_othcpdtl_p20191229 values less than ('2019-12-30'),

partition ediods_ifs_othcpdtl_p20191230 values less than ('2019-12-31'),

partition ediods_ifs_othcpdtl_p99991231_new values less than (maxvalue) );

alter table ediods.ifs_othcpdtl rename partition ediods_ifs_othcpdtl_p99991231_new to ediods_ifs_othcpdtl_p99991231;

列存表（高压缩比）

alter table ediods.nae_nthwjnl1 drop partition ediods_nae_nthwjnl1_p20171231;

alter table ediods.nae_nthwjnl1 split partition ediods_nae_nthwjnl1_p99991231 into (

partition ediods_nae_nthwjnl1_p20171231 values less than ('2018-01-01'),

partition ediods_nae_nthwjnl1_p20180101 values less than ('2018-01-02'),

……

partition ediods_nae_nthwjnl1_p20201229 values less than ('2020-12-30'),

partition ediods_nae_nthwjnl1_p20201230 values less than ('2020-12-31'),

partition ediods_nae_nthwjnl1_p99991231_new values less than (maxvalue) );

alter table ediods.nae_nthwjnl1 rename partition ediods_nae_nthwjnl1_p99991231_new to ediods_nae_nthwjnl1_p99991231;

5.4.%3 删除分区（清理数据使用）

alter table edisum:suffix.e03_indv_time_dpsit_acct truncate partition edisum_e03_indv_time_dpsit_acct_p:tx_date;

语法是可以使用直接alter table具体值的方法以删除分区的。但因为出过较多生产问题，因此请开发人员勿使用直接删除分区日期的写法，而应采用上述蓝色字体写法来删除分区。

5.5.%3 隐式转换运算差异

select 字符*数字

这个是隐式转换了，字符默认转为数字。

如果字符不是数字，gaussdb(dws)会报错，td会转为0继续做运算。

例：

select '-'*7

td:0

gaussdb(dws): 报错

select trim('-')*7

td:0

gaussdb(dws): 报错

select '.'*7

td: 报错

gaussdb(dws): 报错

select trim('.')*7

td: 0

gaussdb(dws): 报错

select 'h'*7

td: 报错

gaussdb(dws): 报错

select trim('h')*7

td: 报错

gaussdb(dws): 报错