集成 Informatica 和 OushuDB 实现 ELT
OushuDB作为一款高性能云数仓,可以通过Informatica将分布的、异构数据源中的数据如关系型数据库中的数据、平面数据文件等抽取到OushuDB后进行清洗、转换、集成,再用OushuDB进行统一的数据加工、汇总,成为数据分析处理、机器学习的基础。
ELT过程中的主要环节就是数据抽取、加载和数据转换加工。一般我们在数据抽取和加载环节可以使用Informatica抽取并加载数据到OushuDB,然后数据转换加工部分可以使用Informatcia的调度引擎调度SQL脚本的方式在OushuDB中处理,这样可以充分利用OushuDB的高性能并行处理的能力。
Informatica连接OushuDB
从 Informatica 9.6.1 开始,支持使用 PowerExchange for Greenplum 连接到OushuDB。 可以使用 ODBC 连接到OushuDB或使用 PowerExchange for Greenplum 将数据批量加载到 OushuDB。
1、使用Informatica导入数据到OushuDB
OushuDB支持批量加载和逐行加载两种加载方式。批量加载是将读取到的数据批量写入目标表中,适合高吞吐大数据量的离线分析场景;逐行加载是将读取到的数据逐行写入目标表,适合实时分析场景。
1.1 批量加载外部数据源的数据到OushuDB
OushuDB支持通过gpfdist外部表和hdfs外部表进行高性能并行数据批量加载。gpfdist是一个HTTP服务器,用户可以在同一机器上,或者不同的机器上启动多个gpfdist实例,hdfs为Hadoop分布式文件系统。两种方式都可以充分利用多台机器,多个网卡实现大规模并行加载。
FTP文件输出
gpfdist -d /data1/load-files -p 8081 -l /data1/log1
gpfdist -d /data2/load-files -p 8082 -l /data2/log2
二、Designer中选择->目标->创建->平面文件。编辑映射并导出为ftp文件
三、SQL脚本实现:通过gpfdist外部表导入到OushuDB的内表中
脚本示例:定义gpfdist外部表:
CREATE EXTERNAL TABLE ext_expenses
( name text, date date, amount float4, category text, desc1 text )
LOCATION ('gpfdist://etlhost-1:8081/*', 'gpfdist://etlhost-1:8082/*')
FORMAT 'TEXT' (DELIMITER '|');
加载数据到OushuDB:
CREATE TABLE expenses
(name text, date date, amount float4, category text, desc1 text);
INSERT INTO expenses SELECT * FROM ext_expenses;
Hadoop文件输出
一、Designer中选择->目标->创建->平面文件。编辑映射并导出到hadoop
二、SQL脚本实现:通过hdfs外部表导入到OushuDB的内表中
脚本示例:
定义hdfs外部表:
CREATE EXTERNAL TABLE ext_expenses
( name text, date date, amount float4, category text, desc1 text )
LOCATION ('hdfs://nnhost:nnport/expense')
FORMAT 'csv' (DELIMITER '|');
加载数据:
CREATE TABLE expenses
(name text, date date, amount float4, category text, desc1 text);
INSERT INTO expenses SELECT * FROM ext_expenses;
参考文档:
PowerExchange for Hadoop用户指南:
https://docs.informatica.com/data-integration/powerexchange-adapters-for-powercenter/10-5/powerexchange-for-hadoop-user-guide-for-powercenter/preface.html
1.2 逐行加载外部数据源的数据到OushuDB
该方式目标表只适合magma表,如果没有实时性的要求,一般的场景无需使用这种方式入库。
在OushuDB上创建或导入表,表类型为magma,脚本示例如下:
CREATE TABLE rank3
(id int, rank int, year smallint,gender char(1), count int)
format 'magmaap';
二、安装配置informatica服务器上的ODBC,Postgresql数据源或 Greenplum数据源
三、Designer中选择->目标->从数据库导入->连接ODBC源,查找选择OushuDB上已经创建好的magma表
如果OushuDB的版本较低或者没带Magma存储,可以使用PWX Greenplum连接器把数据批量加载到OushuDB
一、安装PWX Greenplum连接器
二、在OushuDB上创建或导入表
三、Designer中选择->目标->从数据库导入->连接Greenplum,查找选择OushuDB上已经创建好的表
参考文档:
PowerExchange for Greenplum 用户指南:
https://docs.informatica.com/data-integration/powerexchange-adapters-for-informatica/10-5/powerexchange-for-greenplum-user-guide/preface.html
用于 Informatica 的 Greenplum 连接器:
https://greenplum-informatica.docs.pivotal.io/1-0/using-powercenter.html
2、使用Informatica从OushuDB导出数据
2.1 源为OushuDB的表
安装配置informatica服务器上的ODBC,Postgresql数据源或Greenplum数据源 Designer中选择->源->从数据库导入->连接ODBC源,查找选择OushuDB上的表
2.2 导出数据到本地文件系统
启动gpfdist服务,指向本地文件目录
gpfdist -d /data1/load-files -p 8081 -l /data1/log1
gpfdist -d /data2/load-files -p 8082 -l /data2/log2
二、SQL脚本实现:建立gpfdist可写外部表,并将OushuDB的内表导入
脚本示例:定义gpfdist可写外部表
CREATE WRITABLE EXTERNAL TABLE expense_out (LIKE myexpenses)
LOCATION ('gpfdist://etlhost-1:8081/sales1.out'
, 'gpfdist://etlhost-1:8082/sales2.out')
FORMAT 'TEXT' ( DELIMITER '|' NULL ' ')
DISTRIBUTED BY (name);
导出数据:
INSERT INTO expense_out select * from myexpenses;
2.3 导出数据到HDFS
SQL脚本实现:建立hdfs可写外部表,并将OushuDB的内表导入
脚本示例:定义hdfs可写外部
CREATE WRITABLE EXTERNAL TABLE expense_out (LIKE myexpenses)
LOCATION ('hdfs://nnhost:nnport/expense')
FORMAT 'csv' ( DELIMITER '|' NULL ' ');
导出数据:
INSERT INTO expense_out select * from myexpenses;
3、使用Informatica的调度OushuDB来做数据加工转换
3.1、OushuDB库内处理增量
批量增量处理:增量数据入库后,加载到贴源临时表中,如果贴源表在目标表中存在重复行或者更新行,需要两张表根据源表的主键关联删除后再全量导入。
OushuDB 4.5以上版本默认建的Native ORC格式的表和Magma表均支持更新和删除语句。
实时增量处理:实时性较高的贴源加载场景,需要源端支持CDC,目标表为通过ODBC连接OushuDB的Magma表
3.2、OushuDB库内运行SQL脚本
数据进行清洗、转换、集成等加工、转换过程可以写成SQL脚本,通过workflow调度执行SQL脚本任务,可以充分利用OushuDB并行处理的性能。
来自:https://mp.weixin.qq.com/s/u1vsq_R8712uhhGq-dIfgw
相关文章