Greenplum 5数据加载最佳实践之Kettle

Greenplum 作为分布式大数据计算平台,除了可以高速并行执行分析查询,还以高速的数据加载著称。Greenplum 用户麦煜遥将在本文详细介绍如何使用开源的ETL工具kettle和gpload实现向Greenplum 高速加载数据。

近期,因工作需要,需要通过kettle往Greenplum数据库导入数据,但发现”表输出”的组件非常慢,500条/秒。后来,研究Greenplum批量加载功能,最后发现能达12000条/秒。

原理

开始,我以为要在Greenplum 数据库server开 gpfdist服务,然后在服务器端建外部表。

后来,发现是在kettle本机装Greenplum loads的服务端,在本机开 gpfdist服务,在本机运行Greenplum外部表,数据保存在data file中,外部表的定义在control file中。data file和control file可以设定在运行程序后,保留或自动删除。

以下是集群的情况: 

运行Greenplum loads,依赖很多软件(这就是开源,一些人开发出开源工具,其他人借助这些工具,又开发出很酷的功能,只是当我们使用很酷的功能时,要到各处下载依赖工具)。

实现过程

一、Windows下kettle使用gpload加载数据到Greenplum

1.下载软件

(1)下载Greenplum-loaders-5.0.0-WinXP-x86_32.msi

先查查Greenplum的gpload是什么版本,以免兼容问题,我用的是5.0.0版本。

  • 要下载,先注册。填用户名、邮箱,然后用邮箱激活。
https://network.pivotal.io/products/pivotal-gpdb>/releases/6929/file_groups/691

官方网站提示:Greenplum loader要求安装 Python 2.5.4 (32-bit version),因为里面的加载程序  (gpload.py) 是用python写的,版本是2.5。

(2)下载python-2.5.4.msi

https://www.python.org/downloads/windows/

我的电脑是win7 64位,但是装这个32位的python也可以运行,既然可以运行就不考究了。

(3)下载PyYAML-3.10.win32-py2.5.exe

https://pypi.org/project/PyYAML/3.10/>files

YAML 是专门用来写配置文件的语言,非常简洁和强大,远比 JSON 格式方便。我们要下载python语言中的PyYAML安装包。

(4)下载PyGreSQL-4.1.1.win-amd64-py2.5.msi

(5)下载PyGreSQL-4.1.1.win-amd64-py2.5.exe

http://pygresql.org/files/

Python使用PyGreSQL操作PostgreSQL数据库教程,Greenplum的内核PostgreSQL。两个文件一起下载,先装.msi文件,再装.exe文件。

2.安装软件

(1)按图中顺序安装

有一点切记:安装python和PyGreSQL…msi时,选择使用用户,要选”this user only”,否则安装PyGreSQL…exe将出现注册表中没有python。

(2)配置环境变量

PYTHON_HOME是我自己配的,好像没用。但安装后 GPHOME_LOADERS 和 PYTHONPATH 会自动生成,它是用Greenplum里面自带的python运行。

(3)查看是否安装成功

在电脑输入cmd,呼叫终端,输入python并能在python环境运行import两个组件即可。

4.配置kettle -greenplum Load

(1)Fields

A1:数据库及表连接

A2:自动加载/只生成data(当然是直接加载)

A3:配置文档和数据文档是否运行完后删除(删除,留着没用)

A4:插入/更新/有则更新,无则插入

A5:字段映射

(2)Local Host Names

Hostname:kettle服务器IP, 不要填”127.0.0.1”,供Greenplum服务器访问。Port: 不填,等系统自动分配,避免写死后,与其他端口冲突。

(3)GP configuration

除了error table和encoding,都要求填路径+文件名,如果不填路径默认KETTLE_HOME。

path to the gpload :放置python.py文件,也就是整个load的处理核心。
control file :保存greenplum数据库的连接、表定义等,运行完可以删除。
error table:表名,不要填。加载出现错误的数据会自动在greenplum数据库创建这个表,但我测试时,说数据库不支持,留空,只能用log file来记录错误信息。
log file :gpload运行日志,可以随意指定地方。
data file :程序提取数据后,暂存的数据文件,可以设定程序运行完后删除。
encoding:编码,选UTF-8
max errors:最大允许错误数量,也就是插入或更新错误的记录超过该值,程序终止。
delimiter:数据字段列分隔符,只能是单个ASCII码。(csv常见的转义字符问题,greenplum的外部表一般用(,)逗号分割,即字段中出现和分割符一样的字符,就报错。)

5.运行

(1)在windows打开gpfdist服务

(2)在windows运行kettle程序

观察控制台输出,正常运行信息量较少,程序报错就可以发现很多细节:

(i)程序会随机生成一个8000~9000的端口

started gpfdist -p 8000 -P 9000 -f "load02.dat" -t 30

(ii)Greenplum load应该在kettle服务器的内存中建立Greenplum的外部表,因为每次跑table的名称都不一样,随机生成的一个表(内存中读写数据很快)。 

(iii)如上图,采用” insert into SELECT “ 的批量方式,把kettle服务器内存的数据,当成Greenplum数据库的外部表,实现同一台机器的语法。

(iv)kettle输入速度为1.6w/秒,此时Greenplum数据库尚未看到数据;最后一次性commit。所以看到,Greenplum load的完成时间比提取慢5分钟。

二、Linux下kettle使用gpload加载数据到Greenplum

1.安装软件

(1)安装python* 

用putty登录kettle的服务器,用命令查看是否安装python:

> python

发现已安装pyth2.6,Greenplum用的是2.5版,暂不安装2.5,后续测是否兼容。用函数exit()或键盘ctrl+D退出python。

(2)安装PyYAML

检查是否已经安装PyYAML。

> python
>>> import yaml

发现没有安装PyYAML,去官网找。发现一个命令,和Windows的安装包,没有Linux的安装包,最后一个文件是源码。

https://pypi.org/project/PyYAML/>files

用命令安装,但Linux没有安装pip命令。

> pip install PyYAML

一直以来,我的认知是服务器不能连外网,以确保安全。所以,暂时的解决思路就不去连网安装pip命令,而是Windows下载源码传到Linux,在本地进行安装。

下载源码,用工具WinSCP传到Kettle的 Linux服务器的/home目录

解压,安装

> cd /home
> tar -zxvf PyYAML-3.13.tar.gz
> cd /home/PyYAML-3.13
> python setup.py install

发现一个错误,找不到libyaml,并强制进行安装成功,测试已经安装了PyYAML。

虽然安装成功,但还是修复该问题

> yum -y install libyaml

(3)安装PyGreSQL

没有找到Linux的安装包,只能去官网试着找源码编译。

下载到Windows,拷到kettle的Linux服务器

解压、编译安装(压缩包不是gzip压缩格式,所以不用-z,-z表示压缩过的)

> cd /home
> tar -xvf PyGreSQL.tar.gz
> cd /home/PyGreSQL-5.0.6
> python setup.py install

搞了一大轮发现是版本不对,官方提供的是给python2.7以上版本的源码,我们服务器的python是2.6的。我终于放弃了原则,换成用最快的方式,Linux连外网, 命令安装。

  1. 命令下载pip安装包(pip是类型Linux的yum工具,提供命令安装python组件)
> wget https://bootstrap.pypa.io/2.6/get-pip.py

2. 安装python pip工具

3. 将要安装一堆依赖包,才能装PyGreSQL

> cd /usr/local
> yum install postgresql-devel
> pip install setuptools –upgrade
> yum install python-devel
> yum -y install gcc
> pip install PyGreSQL

提示成功:Successfully installed PyGreSQL-5.0.6

(4)安装Greenplum loaders

下载Linux版的Greenplum loaders,并用winSCP传至Kettle服务器

/home 用户主目录的基点,测试的时候没有考虑好,应该要放到/usr/local下。
usr的并不是user的意思,而是unix system resrouces。直接通过apt-get安装的软件一般会在/usr下面,自己编译安装的软件(或者其他的非官方途径)会在/usr/local下面
/usr/bin:几乎所有的系统可执行文件都会安装在这里
/usr/local/bin:则是可以存放一些系统用户自己特定的可执行文件,不用担心会被系统升级之类的行为覆盖、破坏,这个目录不是必须的。

解压、安装

> cd /home
> unzip /home/greenplum-loaders-5.0.0-rhel6-x86_64.zip
> ./greenplum-loaders-5.0.0-rhel6-x86_64.bin

i 读完安装须知(共13章,按Q退出,接下来是许可)。

ii 同意许可,输入”yes”。

iii 用默认路径按”回车”, 但默认路径太长,键盘输入其他路径,按”回车”。

iv 后续输入”yes”即可。

2.配置环境变量

(1)修改kettle服务器的/etc/profile

参考Windows的环境变量,添加Greenplum的部分,不要影响之前java的环境变量。

export GPHOME_LOADERS=/usr/local/greenplum-loaders-5.0.0
export PATH=$PATH:$JAVA_HOME/bin:$GPHOME_LOADERS/bin:$GPHOME_LOADERS/lib

(2)使/etc/profile生效

> source /etc/profile
> echo $GPHOME_LOADERS

(但后续不知为何还是找不到/usr/local/greenplum-loaders-5.0.0/lib下面的文件)

3.运行

我们通过putty等工具去连接Linux服务器是没有图形界面的,但为了调试方便,通过Xming+putty 调出Linux下面的kettle开发界面来调试。

Linux/Unix的X Window具有网络透明性。X Window系统里有一个统一的Server来负责各个程序与显示器、键盘和鼠标等输入输出设备的交互,每个有GUI的应用程序都通过网络协议与 Server进行交互。

所以对于任何一个应用程序,本地运行和远程运行的差别仅仅是X Server的地址不同,别的没有差别。所以在Windows运行一个X Server,就可以很方便的远程运行有GUI的Linux应用了。同时,OpenSSH具有X转发功能,可以将Linux主机的X程序通过SSH的管道转发给客户端。

于是,通过PuTTY到主机,再将接收到的由主机转发来的X程序的交互交给在本地Windows系统下运行的X Server来管理和显示,这就可以实现远程使用Linux的GUI程序。

(1)下载、安装、于运行putty前运行Xming

(2)运行putty,注意要enable X11 forwarding(127.0.0.1:0)

(3)开启gpfdist服务* 

先以客户端输出log方式开启,错误信息在客户端输出

> cd /home/gpadmin
> /usr/local/greenplum-loaders-5.0.0/bin/gpfdist -d /home/gpadmin/ -p 8000 -P 9000 > /home/gpadmin/gpfdist.log
error while loading shared libraries: libssl.so.1.0.0: cannot open shared object file: No such file or directory

开启后,发现少了几个ssl相关的文件,而其实这些文件在Greenplum的bin目录,而且bin目录也加入环境变量中,刷新无果,只能建立软连接:

ln -s /usr/local/greenplum-loaders-5.0.0/lib/libssl.so.1.0.0 /usr/lib64/libssl.so.1.0.0
ln -s /usr/local/greenplum-loaders-5.0.0/lib/libcrypto.so.1.0.0 /usr/lib64/libcrypto.so.1.0.0
ln -s /usr/local/greenplum-loaders-5.0.0/lib/libyaml-0.so.1 /usr/lib64/libyaml-0.so.1

调试成功,以后台方式开启,错误信息在log文件输出

> nohup /usr/local/greenplum-loaders-5.0.0/bin/gpfdist -d /home/gpadmin/ -p 8000 -P 9000 > /home/gpadmin/gpfdist.log 2>&1 &

nohup表示: 不挂断地运行。关闭标准输入,终端不再能够接收任何输入(也就是你打开的终端不能用了),即使关闭xshell退出当前session依然继续运行,再重定向标准输出和标准错误到当前目录下的nohup.out文件(所以你要先cd到合适的目录)。

结尾的&表示: 任务放到后台 。关闭xshell,对应的任务也跟着停止。nohup 命令 & 这样就能使命令永久在后台执行,而你当前的客户端又能用。

2>&1表示:将标准错误(2)重定向到标准输出(&1),标准输出(1)再被重定向输入到nohup.out文件中

/home/gpadmin/表示: 数据文件所在的目录

-p 8000 -P 9000表示: 随机开一个8000~9000的端口

/home/gpadmin/gpfdist.log表示:gpfdist的log文件路径

查看后台运行

> ps -ef | grep gpfdist

查看log

(4)在Windows操作Linux上的Kettle

在Linux用命令行打开kettle

> cd /root/kettle
> sh spoon.sh

查看kettle已经在Xming上有输出

程序迁移(Windows->Linux)

我把之前在Windows写好的转换(. Ktr)文件,通过winSCP拷到Linux上。

为什么不用数据库资源库?这是为了测试用文件资源库保存程序。如果是集群,程序存成文件来搬移更加方便,而且可以进行版本控制。

在Windows上的Xming操作Linux上的Kettle

Host改成kettle服务器的IP:192.168.xx.xx,端口不写。

GP configuration配置Kettle服务器上的路径和文件,参数见windows配置的部分。

如:选择核心程序gpload.py。

错误:PyGreSQL组件找不到,但已经安装了,这是python操作Greenplum数据库的工具。

修改/etc/profile,PYTHONPATH配成gpload 插件 pygresql所在的目录。

错误:libpython2.7.so.1.0库找不到,

这是python插件用到的库,已经安装,但是添加路径为环境变量无效。把它做个软连接放到系统库/usr/lib64里面就可以了。

ln -s /usr/local/greenplum-loaders-5.0.0/ext/python/lib/libpython2.7.so.1.0 /usr/lib64/libpython2.7.so.1.0

至此,Linux下的批量加载运行成功。

三、设置kettle全局变量,方便程序迁移

(1)在Windows下的设定

配置kettle全局变量D:\IDE\data-integration\.kettle\kettle.properties,这是为了迁移程序不用改配置。

根据实际情况创建好(log目录按照自己的习惯建好),保存并重启kettle生效。注意java里面的斜杠要转义。

 (2)在Linux下的设定

修改/root/kettle/.kettle/kettle.properties,增加变量

新建目录和log文件

(3)Windows开发程序

path to the gpload :${PATH_GPLOAD}
control file :${PATH_GPLOAD_LOG}${file.separator}control-TEST01-${Internal.Step.CopyNr}.cfg
error table:不要填。
log file :${PATH_GPLOAD_LOG}${file.separator}gpload.log
data file :${PATH_GPLOAD_LOG}${file.separator}load-TEST01-${Internal.Step.CopyNr}.dat
encoding:UTF-8
max errors:500,测试用,实际应该填0
delimiter:#

TEST01是表名

{file.separator}此变量是因为Windows和linux的文件分隔符不同

${Internal.Step.CopyNr}此变量是为了区分集群的不同文件。

(4)不用文件保存程序,用DB资源库

新建job: DEMO_GPLOAD,并保存到ORACLE资源库。

(5)用kettle-manager直接调度

 运行成功!!!

关于作者

麦煜遥 (公众号:Entelodon):毕业于电子科技大学(成都) ,热衷IT技术,擅长数据分析。曾从事社保数据分析处理,IBM/AS400 DB2开发、数据分析,参保人员数据量月增千万级;曾于电脑OEM厂商,负责EDI(电子数据交换),电子订单秒级并发量万级。现于供职于风电行业,从事大数据分析。



发表评论

电子邮件地址不会被公开。 必填项已用*标注