集成 Informatica 和 OushuDB 实现 ELT

2022-04-06 00:00:00 数据 导入 数据源 脚本 加载

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服务,指向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 textdate date, amount float4, category text, desc1 text )
LOCATION ('gpfdist://etlhost-1:8081/*''gpfdist://etlhost-1:8082/*')
FORMAT 'TEXT' (DELIMITER '|');


加载数据到OushuDB:

CREATE TABLE expenses
(name textdate 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 textdate date, amount float4, category text, desc1 text )
LOCATION ('hdfs://nnhost:nnport/expense')
FORMAT 'csv' (DELIMITER '|');


加载数据:

CREATE TABLE expenses
(name textdate 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表,如果没有实时性的要求,一般的场景无需使用这种方式入库。


  1. 在OushuDB上创建或导入表,表类型为magma,脚本示例如下:
CREATE TABLE rank3
(id intrank intyear 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的表


  1. 安装配置informatica服务器上的ODBC,Postgresql数据源或Greenplum数据源
  2. Designer中选择->源->从数据库导入->连接ODBC源,查找选择OushuDB上的表


2.2 导出数据到本地文件系统


  1. 启动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


  1. 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

相关文章