使用sqluldr2实现将Oracle数据无落地快速导入Greenplum数据库

环境信息：

Greenplum：Greenplum Version: 'postgres (Greenplum Database) 4.3.8.2 build 1'

Oracle：Release 11.2.0.1.0 Production

1. Oracle客户端部署

在使用sqluldr2工具之前，首先需要在Greenplum(以下都称为GP)的Master节点部署Oracle客户端，因为sqluldr2需要使用libclntsh.so等共享库。

假如你想省事的话，也直接从Oracle服务器将安装目录拷贝过来，然后配置以下环境变量就可以了。

为了防止配置Oracle环境变量会影响GP数据库客户端工具的使用，我们单独给Oracle客户端创建一个用户，然后来单独配置Oracle环境变量，就不和GP参合到一起了。

首先创建oracle用户组和用户：

groupadd -g 1000 dba

groupadd -g 10001 oinstall

useradd -u 1002 -d /var/lib/oracle -s /bin/bash -g oinstall -G dba -m oracle

passwd oracle

比如这里，我们就直接将Oracle服务器的软件安装目录product拷贝过来，并放到/var/lib/oracle下面：

[root@CDHA oracle]# pwd

/var/lib/oracle

[root@CDHA oracle]# chown -R oracle:oinstall /var/lib/oracle

[root@CDHA oracle]# ll

drwxrwxrwx 3 oracle oinstall 4096 Jul 11 22:14 product

-rwxrwxrwx 2 oracle oinstall 185766 Mar 13 2013 sqluldr2_linux64_10204.bin

最后我们配置Oracle的相关环境变量，添加到Oracle的家目录的.bashrc文件中：

export ORACLE_BASE=/var/lib/oracle

export ORACLE_HOME=/var/lib/oracle/product/11.2.0/dbhome_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib

export PATH=$ORACLE_HOME/bin:/sbin:/usr/sbin:/usr/ccs/bin:/usr/bin:/sbin:$PATH:/bin

export TNS_NAMES=$ORACLE_HOME/network/admin

export TNS_ADMIN=$ORACLE_HOME/network/admin

# CLASSPATH must include the following JRE locations:

CLASSPATH=${ORACLE_HOME}/rdbms/jlib:${CLASSPATH}

export CLASSPATH=$CLASSPATH:/opt/oracle/product/11/network/jlib

#将GP的环境信息也配置进来，后面需要使用gpload访问GP数据库

source /usr/local/greenplum-db/greenplum_path.sh

export MASTER_DATA_DIRECTORY=/hadoopdata1/master/gpseg-1

export PGPORT=5432

export PGUSER=gpadmin

export PGDATABASE=template1

2. sqluldr2安装部署

下载sqluldr2_linux64_10204.bin文件，如果你的环境是windows或者linux32位的，请下载对应的文件。

我们可以将这个文件重新命名并放到下面的路径中或者建立一个软链接(这个路径我们已经配置到PATH中)：

[oracle@CDHA ~]$ pwd

/var/lib/oracle

[oracle@CDHA ~]$ ll product/11.2.0/dbhome_1/bin/sqluldr2

-rwxrwxrwx 2 oracle oinstall 185766 Mar 13 2013 product/11.2.0/dbhome_1/bin/sqluldr2

我们来验证一下：

[oracle@CDHA ~]$ sqluldr2

SQL*UnLoader: Fast Oracle Text Unloader (GZIP, Parallel), Release 4.0.1

(@) Copyright Lou Fangxin (AnySQL.net) 2004 - 2010, all rights reserved.

License: Free for non-commercial useage, else 100 USD per server.

Usage: SQLULDR2 keyword=value [,keyword=value,...]

Valid Keywords:

user = username/password@tnsname

sql = SQL file name

query = select statement

field = separator string between fields

record = separator string between records

rows = print progress for every given rows (default, 1000000)

file = output file name(default: uldrdata.txt)

log = log file name, prefix with + to append mode

fast = auto tuning the session level parameters(YES)

text = output type (MYSQL, CSV, MYSQLINS, ORACLEINS, FORM, SEARCH).

charset = character set name of the target database.

ncharset= national character set name of the target database.

parfile = read command option from parameter file

for field and record, you can use '0x' to specify hex character code,

\r=0x0d \n=0x0a |=0x7c ,=0x2c, \t=0x09, :=0x3a, #=0x23, "=0x22 '=0x27

可以看到非常详细的帮助说明。

3. gpload简单介绍

gpload是GP中提供的用来进行数据装载的工具，其可以看做是对gpfdist的封装，所以依赖gpfdist组件。

gpload通过它的控制文件指定装载的细节信息。所以编写控制文件是用好gpload的关键所在。gpload的控制文件采用YAML1.1文档格式编写，因此它必须得是有效的YAML格式。

这一块的内容我在博客http://blog.csdn.net/jiangshouzhuang/article/details/51817520中已经介绍过了，大家可以看一下。本章主要介绍将Oracle数据直接迁移到GP数据库中。

4. 实现Oracle数据直接并行加载到GP的原理

使用gpload，一方面我们可以实现GP中不能直接实现的merge操作，另外通过结合命名管道，我们可以实现无落地文件的并行快速加载。从而帮助我们提高海量数据加载效率，也避免了使用传统落地文件方式加载的过大存储开销，以及超大文件落地过程导致的加载性能瓶颈。

本文使用的方法是通过Linux的命名管道，gpload，以及sqluldr2程序(用于oracle数据的快速卸载)实现从Oracle到GP无落地文件快速加载。

5. 示例

场景：将Oracle的iphone_user_detail表加载到GP数据库的iphone_user_detail表中。

首先，我们创建gpload的控制文件，gpload.ctl内容如下：

---

VERSION: 1.0.0.1

DATABASE: zhangyun_db

USER: zhangyun

HOST: CDHA

PORT: 5432

GPLOAD:

INPUT:

- SOURCE:

LOCAL_HOSTNAME:

- CDHA

PORT: 46666

FILE:

- /var/lib/oracle/iphone_user_detail/iphone_user_detail_file

- FORMAT: text

- DELIMITER: E'\001'

- NULL_AS: ''

- ENCODING: utf8

- ERROR_LIMIT: 25

- ERROR_TABLE: err_iphone_user_detail

OUTPUT:

- TABLE: ann_gbs_safe.iphone_user_detail

- MODE: INSERT

然后编写脚本export_iphone_user_detail.sh，用于执行数据迁移工作：

#!/bin/bash

mknod /var/lib/oracle/iphone_user_detail/iphone_user_detail_file p

sqluldr2 user=zy/xxxxxx@192.168.1.122:1521/salesdb query="select /*+ parallel(8)*/ deptno,fmi,location,iphone_number from pps.iphone_user_detail" field=0x01 charset=utf8 safe=yes FILE=/var/lib/oracle/iphone_user_detail/iphone_user_detail_file &

gpload -f gpload.ctl -V -l gpload.log

rm -rf /var/lib/oracle/iphone_user_detail/iphone_user_detail_file

6. 执行export_iphone_user_detail.sh脚本，并查看日志

在执行脚本之前，确保GP中已经创建好了iphone_user_detail表。

[oracle@CDHA ~]$ bash export_iphone_user_detail.sh

2016-07-13 14:47:00|INFO|gpload session started 2016-07-13 14:47:00

2016-07-13 14:47:00|INFO|started gpfdist -p 21211 -P 21212 -f "/var/lib/oracle/iphone_user_detail/iphone_user_detail_file" -t 30

0 rows exported at 2016-07-13 14:47:03, size 0 MB.

1000000 rows exported at 2016-07-13 14:47:10, size 719 MB.

2000000 rows exported at 2016-07-13 14:47:18, size 1451 MB.

3000000 rows exported at 2016-07-13 14:47:25, size 2154 MB

.……………………….

等执行完成后，就可以进入GP数据库中查看。

zhangyun_db=# select count(*) from iphone_user_detail;

count

--------

194000000

(1 row)