Greenplum:社区版代码已合并至8.3率先支持return query

真是天大的好消息,前两天微信群姚总说gpdb社区版代码已合并至8.3,于是昨晚上下班的时候,就拿下来编译了下,今早过来测试了下,这次真的支持return query了!

从此,gp的用户再也不用将计算结果落地了,再也不用循环打印输出了,SQL代码移植的任务瞬间减轻了很多,不废话来看demo演示:

1、环境

2、建一张demo表,弄点数据:

3、写一段测试代码:

注:没升级的版本这段代码无法编译,会报[Err] ERROR:  RETURN cannot have a parameter in function returning set; use RETURN NEXT at or near “query”

4、走一个查询测试一下:

5、断续来段代码测试一下是否支持recursive层级查询(好期待)?

很遗憾,仍然不支持,好在问题不大,请参考笔者之前一篇博客:《四行代码让Greenplum支持recursive层级查询

PostgreSQL:临时表不好用?如何发挥数组性能

前言:SQL编程都经常会有这样一个需求:把一部分数据临时放在某个地方,经过某些业务处理后再拿回来,在postgresql中临时表可以很方便的满足这个需求,postgresql的临时表是“real temporary table”,即数据连同表结构都是随建随用的。所以如果长期大量使用临时表会带来数据库系统元数据膨胀的问题,德哥最近一篇博客提到这个问题,并给出了很好的数组替代方案:《大量使用临时表带来的问题,学会如何擦屁股》

笔者的经验,这个方案可以再引申一下:数组本身也是有性能问题的,尤其是宽数组(数组中每个元素内部内容都比较肥胖,如Type Array/Table Array),如何规避数组性能问题?

先说答案是:数组中只需保留一个ID号,然后再拿这个数组和源表join得到表中其它信息,口说无凭,下面用案例演示一下两种方法性能比较:

1、环境,一台云主机2C4G,数据库版本:

2、建一个demo表,折中一下搞30个字段,并建一个常规索引:

3、搞100万测试数据:

注:这个工具函数会将每个varchar字段都填满:

t

pgetl_tool_padtab的源代码见https://github.com/wurenny/pgetl/blob/master/sqlsource/istlplus.sql第101行

4、写两个测试程序,分别测试“宽数组”和“简单数组”两种情况:

5、测试性能:

10000行起测:

性能相差近2倍,同时对比一下count性能:

count性能相差7倍+


来10W行:

性能相差10倍以上,同时对比一下count性能:

count性能差距缩小为2倍


再加一个数量级,来100W行:

可以看到由于宽数组消耗大量内存,触发了OOM,如果是生产系统这是比较危险的(我测试的这台机器硬件不怎么样,不过100W数据对于生产系统也很小),同时对比一下count性能:

结论:使用宽数组当表用的时候,在处理较大数据量存在很严重的性能问题,编程的时候尽量使用简单数组再join回来可以有效规避这个问题。

解释一下为什么count性能随着数据量上升,差距反而会变小,笔者猜测是由于unnest这个函数在处理宽数组时引发了性能问题,而count时只需要知道数组长度,不需要解开其内容,大部分计算量转移到了count本身,所以会出现这种情况,有兴趣的读者可以结合源代码做一下unnest函数性能评测

引申一下:Greenplum目前是不支持宽数组或Type Array/Table Array的,而且也不支持层级递归查询,使用简单数组也可以一并这两个问题,见我之前写一篇博客:《四行代码让Greenplum支持recursive层级查询》

 

PGETL数据结构设计

  • 批次定义:PRM_SCH_BATCH
列名称 数据类型 约束 描述 备注
bchno integer pk 批次号
starttime varchar(15) 批次开始时间 如:20:30:00.000000
endtime varchar(15) 批次结束时间 格式同starttime
apart interval 时间间隔 如:-1 days
remarks varchar(255) 备注

 

  • 字典注释:PRM_SCH_DICT
列名称 数据类型 约束 描述 备注
key_word varchar(128) 关键字
trans_value varchar(255) 转换后的值
remarks varchar(500) 备注

目前支持7个关键字,它们可出现在配置SQL语句中:

关键字 翻译值 说明
##batch_no## 批次号:翻译为启动pgetl时的批次号 批次号在prm_sch_batch中定义
##sch_date## 调度日期:翻译为启动pgetl时的日期值 转换后的格式为:yyyy-mm-dd
##start_time## 开始时间:调度日期+批次开始时间 开始时间定义在: prm_sch_batch中
##end_time## 结束时间:调度日期+批次结束时间 结束时间定义在: prm_sch_batch中
##src_tabname## 源表名:翻译为源模式名.源表名 在全模式中可以用到
##des_tabname## 目标表名:翻译为目标模式名.目标表名 在全模式中可以用到
##schema## 模式名:翻译为目标数据库模式名 在全模式中可以用到,仅用于prm_sch_cmd执行sql命令

 

  • 数据库模式字典:PRM_SCH_SCHEMA
列名称 数据类型 约束 描述 备注
db_name varchar(30) pk 数据库名称
schema_name varchar(30) pk 模式名称
ip varchar(15) 数据库IP地址
port integer 数据库端口号
username varchar(30) 数据库用户名 必须对当前模式有访问权限
password varchar(128) 用户密码
remarks varchar(255) 备注

 

  • ETL策略配置:PRM_SCH_ETL
列名称 数据类型 约束 描述 备注
sno integer pk 序列号 prm_sch_etl和prm_sch_cmd联合唯一
psno integer[] 前级依赖ID 带死锁检测
enable bool 是否生效 被依赖时无法设置为Flase
src_dbname varchar(128) uk 源库名称
src_tabname varchar(128) uk 源表名称
des_dbname varchar(128) uk 目标库名称
des_tabname varchar(128) uk 目标表名称
src_qry_sql varchar(4000) 查询语句(支持复杂查询或汇总) 支持换行、特殊关键字,详见字典表prm_sch_dict
des_clr_sql varchar(4000) 可为空(不清洗) 清洗语句(支持截断) 1、支持换行、特殊关键字,详见字典表prm_sch_dict

2、*号为删除所有数据,即截断表

src_schema varchar(128) 自定义模式名(支持全模式) *号为源数据库下所有模式
des_schema varchar(128) 目标库模式名

 

  • 调用sql命令配置:PRM_SCH_CMD
列名称 数据类型 约束 描述 备注
sno integer pk 序列号 prm_sch_etl和prm_sch_cmd联合唯一
psno integer[] 前级依赖ID 带死锁检测
db_name varchar(128) 数据库名称
schema_name varchar(128) 模式名称 *号为全模式
sql_text varchar(4000) 任意SQL语句 支持换行、特殊关键字,详见字典表prm_sch_dict

 

  • 日志记录:PRM_SCH_LOG
列名称 数据类型 约束 描述 备注
sch_date varchar(8) 索引 调度日期
run_time varchar(32) 执行行时间
src_db varchar(128) 源数据库名称 清洗或执行sql命令时为“-”
src_tab varchar(128) 数据传输源表 含模式名,清洗或执行sql命令时为“-”
des_db varchar(128) 目标数据库名称
des_tab varchar(128) 数据传输目标表 含模式名
shell_info varchar(255) 脚本信息 调用shell脚本名称
log_info varchar(2000) 日志内容
sql_text varchar(4000) 被调用SQL语句

 

PGETL设计原理

整个PGETL采用bash shell编程,shell脚本语言本身具有系统调用方便、性能高效的特点,启动pgetl时,首先pgetl会从元数据数据库(安装时指定)获取所有ETL配置信息,生成调度脚本,根据调度脚本循环运行作业,直到所有作业完成或出错,最后给出当次调度结果,结束运行

下面是设计ETL时通常要考虑的几个问题:

  • 如何控制并发?
    PGETL利用linux系统jobs控制并发,超出并发数会启动短暂休眠,直到满足Jobs<最大并发数,断续开始新的作业
  • 如何控制作业依赖?
    每当一个作业开始,会在调度脚本中标记当前作业为正在运行中;当作业运行结束,无论是否成功,都会回写这个标记为成功或错误,以通知其它对其具有依赖的作业
  • 如何进行死锁检测?
    PGETL配置数据保存在数据库表中,当有新的作业配置或作业变更时,利用数据库触发器进行相关的有效性判断。检测死锁利用了PostgreSQL的递归查询特性,实际上是检测了最大递归层数,(安装时限制在最大32层,用户也可自行修改这个触发器,改变死锁检测的层数,或进行一些扩展),当有两个作业有循环依赖时,会产生无限递归,递归层数也会立即膨胀,此时会给用户提示检测到死锁发生,并阻止作业插入或变更
  • 如何全内存计算?
    PGETL结合了PostgreSQL自身COPY特性及Linux系统管道的优点,省去了中间文件落地,同时也省去了buffer管理等诸多问题,这使得PGETL本身不再有IO瓶颈,将目标集中在数据传输上,也极大地节省了磁盘空间
  • 全模式支持?
    全模式支持是指模式通配,用“*”号代替某个数据库下所有schema,这样做很大程度上可以简化用户作业配置,PGETL利用了一个稍微复杂的视图来支持此特性,可以在元数据库查看相关视图v_sch_etl的源代码

 

PGETL项目介绍

PGETL,即PostgreSQL ETL,目标在于解决跨库数据传输及同步问题,支持所有兼容PSQL的数据库,PGETL主要有如下特点:

  • 全内存计算,自身无IO瓶颈
  • 支持并发调度,并发数可自定义
  • 支持多级依赖作业
  • 自动死锁检测
  • 支持多批次作业
  • 调度状态实时显示
  • 双日志引擎,后台运行时有据可查
  • 配置简单、灵活,使用标准SQL
  • 支持模式通配,方便开发
  • 支持多对一传输合并,一对多复制分发
  • 一键安装,一键卸载
  • 自带完整演示案例

安装:

  1. 准备一个PostgreSQL数据库/账号用于存储PGETL元数据,并安装好PSQL客户端
  2. 从Github下载PGETL项目:https://github.com/wurenny/pgetl
  3. 解压赋权:chmod 700 pgetl/pgetl-*
  4. 运行pgetl/pgetl-install,按提示输入存储元数据的数据库参数即可(默认会安装一个demo,如果只安装软件运行pgetl/pgetl-install nodemo)
  5. 安装完成后已配置好PGETL相关环境变量,退出重新登录即可运行pgetl命令查看帮助
  6. 要运行demo案例,执行pgetl batch=1 date=sysdate会运行一个简单的ETL场景
  7. 运行时可以运行pgetlstat -f实时查看调度状态

Demo案例介绍:

  1. 总共有5张表,其中:pgetl_oltp_prod1、pgetl_oltp_prod2模拟两个在线交易库,安装demo时会自动生成一些测试数据;pgetl_olap_ods模拟BI操作数据存储;pgetl_olap_dw模拟存量仓库;pgetl_olap_dm模拟业务模型结果
  2. 处理流程:
    • 从pgetl_oltp_prod1开始发起一次清洗,抽取数据,结束后从pgetl_oltp_prod2……prodn并发抽取
    • 若上一步没有错误,全部导入仓库存量;若任何一次抽取有错误,终止后续操作
    • 若上一步没有错误,经过pgetl_olap_dmproc数据分析处理,生成pgetl_olap_dm业务模型结果
  3. 流程图示:
  4. pgetl-demo
  5. 运行demo案例完成后,可以从pgetl_olap_dm中查看模型是否正确生成