ETL开发—基于多平台数据库

修订页

序号	修订内容	修订日期	修订人	版本号
1	创建全文	2023/03/12	高正华	v1.0.0
2	内容调整	2023/03/20	高正华	v1.1.0

/===================================================================================================/

文档定位：
1）文档内容不局限于某一特定的数据库，旨在通过总结和记录不同类型数据库使用过程中遇到的问题，为大数据开发人员快速熟悉和使用各类数据库平台提供一些关键性的参考；
2）希望该文档在ETL脚本多平台适配过程中提供一些技术细节实现的参考，尽量减少版本维护的成本；

---

关于数仓建设以及项目落地在数据层的技术选型和资源评估需要考虑的因素：
1）资源评估
资源评估包括存储资源和算力资源，对于不同的数据库平台，资源评估结果也不一致；
如Oracle数据库，不支持数据列存，同时存储和算力节点分离；
对于GaussDB集群或者Vertica集群，每个节点资源互相隔离，算力和存储共同组成一个资源单位；
同样，在资源评估中需要考虑其他功能需要的资源，如工具服务器；数据库是否支持数据压缩，数据的存储增长速率；

2）架构设计
数据链路设计，尽量按照一一映射或多对一的方式设计；
数据抽取方式，纯JDBC？外表导入？Tunnel方式？

3）细节实现
表空间
用户权限
日志文件
表行列存、分布方式
表索引设计、分区设计、是否写日志
UDF函数是否下推

4）数据时效性
ogg、kafka、flink 等中间件的使用

5）如何多租户
表行级限制？
视图实现？

1、平台类型

目前项目中涉及到的数据库如下：

类型	架构	说明
Oracle		甲骨文
MySql
Odps		阿里系
PostgreSql
Greenplum	M/S
GaussDB200	M/S	华为系
AdbForPg	M/S	阿里系
Vertica
TdPg-v3	M/S	腾讯系

概念	解释
节点
集群
分布式
库
实例
用户
模式/方案/项目空间

• 集群：指多个节点组成的物理形态的逻辑描述；
• 节点：一台物理服务器或具备独立通信地址的设备；
• 分布式：是按照组件服务形式来定义的一种描述，指通过网络连接的多个组件，通过交换信息协作而形成的系统；

Odps的集群下没有库的物理/逻辑概念，集群下直接是项目空间，等同于MySQL的模式；
Odps不是一个数据库，不具备数据库的特性，如事务、主键约束、索引；

2、核心架构

2.1、逻辑架构

Oracle：Rac集群规模为2个节点；每个节点下部署一个库，每个库下可以创建多个实例；
vertica：去中心化，每个节点都可以作为登录节点；
M/S架构：

2.1、存储形式

TableSpace —>Segment —> extend —> Block/Page —> Row

Oracle、MySql类似，表空间为文件，内部划分逻辑结构；
PostgreSql、GreenPlum、GaussDB类似

3、使用汇总

3.1、权限控制

alter default privileges in schema ${schemaname} grant select on tables to ${username};

说明：该语句对于mpp架构的数据库，可以实现特殊授权；即自动会将 ${schemaname} 下新增的对象查询权限给到 ${username}；

3.2、常规操作

3.2.1、数据类型

1）日期时间

在不同的数据库平台中，对于日期时间提供了几种对应的数据类型，分别是 date、time、timestamp；
其中 date 类型在Oracle中是包含 年月日+时分秒，在其他大部分数据库中是只到 年月日，所以为了防止日期精度丢失，
推荐：日期类型全部使用 timestamp 类型代替，在多平台数据库都通用；
timestamp数据类型按照是否包含毫秒位，又分为 timestamp(0) 和 timestamp(6)，其中 timestamp 等同于 timestamp(6)；

2）null&''区别

select case
         when '' is null then
          'Y'
         else
          'N'
       end is_null,
       'Hello World!' || null as str_1,
       'Hello World!' || '' as str_2,
       'Hello World!' || coalesce(null, '') as str_3
  from dual

在不同的数据库平台表现：

Db_Type	is_null	str_1	str_2	str_3
Oracle	Y	Hello World!	Hello World!	Hello World!
GaussDB	Y	Hello World!	Hello World!	Hello World!
Vertica	N		Hello World!	Hello World!
TdPg	N		Hello World!	Hello World!
Greenplum	N		Hello World!	Hello World!

说明：即空字符串 ‘’ 在Oracle 和GaussDB里是判断为 null的，但是在其他平台是非 null值；所以在拼接时表现同样不同；

3.2.2、函数使用

1）日期转换

1）在数据库中，经常存在将 字符串转换为日期的操作，常用的函数有 to_date(date_str, formate_str)，但是同样的受限于 date 类型在不同的数据库类型中精度表现不同，在 vertica、tdpg-v3等数据库中 使用 to_date 函数进行转换的日期类型不包含 时分秒，导致某些场景下结果异常；
推荐：使用 to_timestamp(date_str, formate_str)函数实现日期类型转换；

场景：在hive中，select date_format('2023-08-31 15:23:47','yyyy-MM-dd HH:mm:ss') ;此处日期格式字符串里的月和分有大小写区别；

2）当前时间

在数据库操作中，经常需要获取系统当前时间；不同的数据库平台对系统时间的兼容情况也不同，主要的有 sysdate、now()、current_timestamp(0) 等；
推荐：数据库系统时间的获取函数统一使用 current_timestamp(0)，在大部分的数据库中支持；

3）日期加减

日期加减在数据库中属于高频操作，按照粒度不同分为年、月、周、日、时、分、秒；
主要语法有：
A）：$date_col/timestamp_col +/- 3
B）：add_months($date_col/timestamp_col , 3)
C）：$date_col/timestamp_col +/- interval '3' day

说明：在部分数据库中不支持 A）或B）类型的语法；
推荐：所有对日期或时间戳进行加减操作，都使用 $date_col/timestamp_col +/- interval '3' day，在大部分的数据库都支持；

• 日期建个
  进行数据加减后转换为具体的固定单位的差值：如建个多少天、小时、分钟、秒；
  round((extract(epoch from current_timestamp(0))-extract(epoch from t.query_start))/60/60,2)

4）length & lengthb

关于 length 和 lengthb 的区别：
1）length是统计字符数，lengthb 是统计字节数；数据库里的长度是指字节数；
2）对于 varchar2(4000)，按照 utf-8编码格式，最大可以存 4000个单字节字符，但是汉字属于多字节字符，一个汉字占3个字节，所以最多可以存 1333个中文汉字；

4）类型转换

在Oracle数据库和GaussDB数据库中，会自动做很多数据类型的隐式转换，主要涉及如下2类场景：
A：字段赋值转换；
B：关联类型转换；
在vertica、Tdpg-v3等平台中，隐式转换的适配程度和Oracle以及GaussDB有比较大的区别，会导致某些场景下的数据赋值和数据关联的结果异常；

如下面的例子：

expression	Oracle	GaussDB	Vertica	TdPg-v3
to_char(202106.00)	202106	202106.00	202106.00	202106.00
to_char(202106.00, ‘fm999999’)	202106	202106	202106	202106
cast(202106.00 as varchar(6))	202106	202106	202106	202106
cast(202106.00 as varchar(9))	202106	202106.00	202106.00	202106.00

说明：
A：此处需要关注下 to_char 里结果 fm格式的用法；另外对于Oracle数据库在对数值进行to_char的时候会自动截取整数部分；
B：在ETL开发中对于 djxh 列的字符转换，使用 cast(djxh varchar(20)) 的结果和 cast(djxh varchar(30)) 的结果当DJXH的数据类型定义不同时结果也会不同，需要注意；

• 多行字符拼接
  string_arr(per_line,',')

3.2.3、ddl操作

3.2.4、dml操作

1）update操作

对于很多mpp类型数据库，常规的Oracle语法性能表现不加，此时可以考虑将子查询的方式调整为表关联的方式进行数据更新；

适用的平台：GaussDB、TdPg-v3；
优化前：

update $target_tab t
   set t.col_c =
       (select l.col_a from $source_tab l
         where 1 = 1
           and t.pk_col = l.pk_col)
 where exists (select 1
          from $source_tab l
         where 1 = 1
           and t.pk_col = l.pk_col);

优化后：

update $target_tab t
   set t.col_c = l.col_a 
   from $source_tab l
 where 1 = 1
  and t.pk_col = l.pk_col;

2）merge into操作

适用场景：如使用 ls_tab_a 去更新 tar_tab_b表，理想的执行计划为 ls_tab_a 走全表扫描，目标表走主键索引；

限制：大部分的关系型数据库支持merge into语法，但是对于部分mpp数据库，只有行存表支持该操作；

思考：在性能优化中，一般按照目标表被更新的数据量占比为 3%左右来衡量是否适合走索引；在merge into语法中如果目标表 tar_tab_b 数据量为 100亿，ls_tab_a 数据量为 1亿，按照数据量占比理论上可以走索引；但是实际如果按照走索引的执行计划来设计，很多时候sql无法按照预期快速完成；

建议：当临时表 ls_tab_a数据量超过 100万时，需要综合考虑使用 merge into 效率高还是使用 delete + insert 效率高；

4、运维监控

• 对象创建时间

在Oracle里可以查询到一个对象的具体创建时间，但是在分布式的数据库是没有的，可能的原因是在各个DN节点上数据文件生成的时间是不一致的；