Oracle连接hadoop(转)

 

原文地址：http://blog.itpub.net/7607759/viewspace-761362/

 

一、ORACLE连接HADOOP(1) - 做些准备  

二、ORACLE连接HADOOP(2) - HDFS专用SQL连接器ODCH 

三、ORACLE连接HADOOP(3) - OLH加载HDFS数据 

 

一、ORACLE连接HADOOP(1) - 做些准备  

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

 

大数据这股旋风越刮越猛烈，HADOOP家族有这股东风做助力更是如虎添翼，传统的软件厂商，比如像ORACLE这类本身就标榜着数据处理专业户的也不甘落后，提供了专门的连接器，用于处理大数据。不仅仅是努力提高自身能力，而且还可以通过配置，读取HADOOP中HDFS保存的数据。今天，咱们尝试两种读取HADOOP中数据的方案。

1、准备工作

要读取HADOOP中的数据，就需要节点能够连接HADOOP。HADOOP环境中的namenode和datanode天生就拥有这个能力，因此网上有不少文章，在配置ORACLE读取HADOOP数据时，都是选择在HADOOP节点中安装ORACLE数据库。我个人觉着这样有些不妥，因为数据节点即承担计算和存储，又提供ORACLE服务，相互之间肯定还是会有影响。最好还是ORACLE服务器提供ORACLE服务，HADOOP集群提供HDFS/M-R能力。

HADOOP家族目前已经发展到很庞大，但我觉着HADOOP的体系还是挺简单，若想让一个节点拥有访问HADOOP的能力其实很简单。安装好JAVA JDK，解压缩hadoop，配置好用户，它就能向hdfs中读写数据，或者提交M-R任务。

因此，我们只需要在某个安装了ORACLE的数据库服务器上，配置hadoop软件就行了。

设定环境如下：

 
• Hadoop集群：    
       
  • 节点：192.168.30.203/204/205/206/207
       
  • 版本：0.20.2
     
   
 
• Oracle数据库：    
       
  • IP：192.168.30.244
       
  • 版本：11gR2
       
  • SID：jssdb
     
   

在本例中，我们使用Oracle11gR2版本数据库，安装步骤略，如有不明白的可以参考：三思笔记RHEL5下安装Oracle11gR2(http://www.5ienet.com/note/html/st11g/index.shtml)。假设目前ORACLE数据库软件已经安装好，数据库服务已经启动。如何使其能够连接HADOOP呢，往下看。

创建用户，用户名与HADOOP节点中的用户名相同，这里为grid：

[root@ora11g ~]# useradd grid

[root@ora11g ~]# passwd grid

从当前任意hadoop节点复制hadoop目录到oracle服务器(jdk可以同样方式处理)：

[root@hdnode3 ~]# tar cvfz /tmp/hadoop-0.20.2.tar.gz hadoop-0.20.2

[root@hdnode3 ~]# scp /tmp/hadoop-0.20.2.tar.gz grid@192.168.30.244:/data/software/

以下操作则是在ORACLE节点中执行：

[root@ora11g ~]# tar xvfz hadoop-0.20.2.tar.gz -C /usr/local/

[root@ora11g ~]# chown -R grid:grid /usr/local/hadoop-0.20.2

[root@ora11g ~]# tar xvfz jdk1.7.tar.gz -C /usr/local/

[root@ora11g ~]# chown -R grid:grid /usr/local/jdk1.7.0_09

复制jdbc链接库文件到hadoop lib目录：

[root@ora11g ~]# cp /data/ora11g/product/11.2.0/db_1/jdbc/lib/ojdbc6.jar /usr/local/hadoop-0.20.2/lib/

编辑grid用户环境变量，加入与HADOOP相关的环境变量：

[grid@ora11g ~]$ vi /home/grid/.bash_profile

增加下列内容：

export JAVA_HOME=/usr/local/jdk1.7.0_09

export HADOOP_HOME=/usr/local/hadoop-0.20.2

export HADOOP_CONF_DIR=${HADOOP_HOME}/conf

配置完成之后，可以执行hadoop命令，看看是否能够连接HADOOP集群，读取HDFS中的文件：

[grid@localhost ~]$ hadoop dfs -ls

Found 9 items

drwxrwxrwx   - grid supergroup          0 2013-05-16 13:11 /user/grid/.Trash

drwxrwxrwx   - grid supergroup          0 2013-04-22 11:43 /user/grid/jss

drwxr-xr-x   - grid supergroup          0 2013-02-17 20:08 /user/grid/jsscount

drwxrwxrwx   - grid supergroup          0 2013-03-05 15:39 /user/grid/jssout

drwxrwxrwx   - grid supergroup          0 2013-05-15 11:59 /user/grid/ld_sql

drwxr-xr-x   - grid supergroup          0 2013-03-14 17:18 /user/grid/smssend

drwxr-xr-x   - grid supergroup          0 2013-03-15 10:23 /user/grid/web

drwxrwxrwx   - grid supergroup          0 2013-03-15 10:36 /user/grid/webout-m

drwxrwxrwx   - grid supergroup          0 2013-03-15 10:36 /user/grid/webout-s

这样新节点连接HADOOP配置好了，接下来的重心，就是如何通过ORACLE读取HADOOP中的数据了。

 

 

二、ORACLE连接HADOOP(2) - HDFS专用SQL连接器ODCH

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

 

2、HDFS专用SQL连接器

使用ORACLE连接HDFS有个专项的解决方案[O]racle [D]irect [C]onnector for [H]adoop Distributed Files System，简称ODCH。

该软件包可以到Oracle官网下载：http://www.oracle.com/technetwork/bdc/big-data-connectors/downloads/index.html

选择第一项：Oracle SQL Connector for Hadoop Distributed File System Release 2.1.0

我们这里使用2.1版本，下载到安装包后，解压即可：

[root@ora11g ~]# unzip oraosch-2.1.0.zip

变态呀，压缩包里还有压缩包：

[root@ora11g ~]# unzip orahdfs-2.1.0.zip -d /usr/local/

[root@ora11g ~]# chown -R oracle:oinstall /usr/local/orahdfs-2.1.0

在解压缩文件的bin中，hdfs_stream有两个环境变量需要手动指定，编辑该文件：

[root@ora11g ~]# vi /usr/local/orahdfs-2.1.0/bin/hdfs_stream

增加两行环境变量：

OSCH_HOME=/usr/local/orahdfs-2.1.0

HADOOP_HOME=/usr/local/hadoop-0.20.2

再次编辑grid用户环境变量：

[root@ora11g ~]$ vi /home/grid/.bash_profile

增加下列内容：

export DIRECTHDFS_HOME=/usr/local/orahdfs-2.1.0

export OSCH_HOME=${DIRECTHDFS_HOME}

export ORAHDFS_JAR=${DIRECTHDFS_HOME}/jlib/orahdfs.jar

export HDFS_BIN_PATH=${DIRECTHDFS_HOME}/bin

以oracle用户身份登录，创建两个目录，分别用于保存ODCH的操作日志和位置文件(location files)：

[oracle@ora11g ~]$ mkdir /data/ora11g/ODCH/{logs,extdir} -p

[oracle@ora11g ~]$ chmod -R 777 /data/ora11g/ODCH/

而后以sysdba身份登录到ORACLE，创建3个directory对象：

SQL> create or replace directory ODCH_LOG_DIR as '/data/ora11g/ODCH/logs';

grant read, write on directory ODCH_LOG_DIR to SCOTT;

create or replace directory ODCH_DATA_DIR as '/data/ora11g/ODCH/extdir';

grant read, write on directory ODCH_DATA_DIR to SCOTT;

create or replace directory HDFS_BIN_PATH as '/usr/local/orahdfs-2.1.0/bin';

grant read,write,execute on directory HDFS_BIN_PATH to SCOTT;

Directory created.

 

SQL> 

Grant succeeded.

 

SQL> 

Directory created.

 

SQL> 

Grant succeeded.

 

SQL> 

Directory created.

 

SQL> 

 

Grant succeeded.

 
• HDFS_BIN_PATH: hdfs_stream脚本所在目录.
 
• XTAB_DATA_DIR：用来存放“位置文件”(location files)的目录。“位置文件”(location files) 是一个配置文件，里面包含HDFS的文件路径/文件名以及文件编码格式。 
 
• ODCH_LOG_DIR, Oracle用来存放外部表的log/bad等文件的目录.

创建外部表，注意location目前是随便写的，随后还要再修改：

SQL> conn scott/tiger

Connected.

SQL> CREATE TABLE odch_ext_table

  2  (  ID NUMBER

  3    ,OWNER VARCHAR2(128)

  4    ,NAME VARCHAR2(128)

  5    ,MODIFIED DATE

  6    ,Val NUMBER

  7  ) ORGANIZATION EXTERNAL

  8  (TYPE oracle_loader

  9   DEFAULT DIRECTORY "ODCH_DATA_DIR"

 10   ACCESS PARAMETERS

 11   (

 12       records delimited by newline

 13       preprocessor HDFS_BIN_PATH:hdfs_stream

 14       badfile ODCH_LOG_DIR:'odch_ext_table%a_%p.bad'

 15       logfile ODCH_LOG_DIR:'odch_ext_table%a_%p.log'

 16       fields terminated by ',' OPTIONALLY ENCLOSED BY '"'

 17       missing field values are null

 18       (

 19         ID DECIMAL EXTERNAL,

 20         OWNER CHAR(200),

 21         NAME CHAR(200),

 22         MODIFIED CHAR DATE_FORMAT DATE MASK "YYYY-MM-DD HH24:MI:SS",

 23         Val DECIMAL EXTERNAL

 24       )

 25    )

 26   LOCATION ('odch/tmpdata.csv')

 27  ) PARALLEL REJECT LIMIT UNLIMITED;

 

Table created..

切换至grid用户，创建hdfs中的目录并上传文件到hdfs：

[grid@ora11g ~]$ hadoop dfs -mkdir odch

[grid@ora11g ~]$ hadoop dfs -put tmpdata.csv odch/

Tmpdata.csv文件是我们通过all_objects生成的，SQL脚本为：select rownum,owner,object_name,created,data_object_id from all_objects

然后，通过ODCH的jar包，生成位置文件：

[grid@ora11g ~]$ hadoop jar \

>    ${ORAHDFS_JAR} oracle.hadoop.hdfs.exttab.ExternalTable  \

>    -D oracle.hadoop.hdfs.exttab.tableName=odch_ext_table \

>    -D oracle.hadoop.hdfs.exttab.datasetPaths=odch \

>    -D oracle.hadoop.hdfs.exttab.datasetRegex=tmpdata.csv \

>    -D oracle.hadoop.hdfs.exttab.connection.url="jdbc:oracle:thin:@//192.168.30.244:1521/jssdb" \

>    -D oracle.hadoop.hdfs.exttab.connection.user=SCOTT \

>    -publish

DEPRECATED: The class oracle.hadoop.hdfs.exttab.ExternalTable is deprecated.

It is replaced by oracle.hadoop.exttab.ExternalTable.

 

Oracle SQL Connector for HDFS Release 2.1.0 - Production

 

Copyright (c) 2011, 2013, Oracle and/or its affiliates. All rights reserved.

 

[Enter Database Password:]

The publish command succeeded.

 

ALTER TABLE "SCOTT"."ODCH_EXT_TABLE" 

LOCATION

(

  'osch-20130516031513-6000-1'

);

 

The following location files were created.

 

osch-20130516031513-6000-1 contains 1 URI, 4685141 bytes

 

     4685141 hdfs://hdnode1:9000/user/grid/odch/tmpdata.csv

 

The following location files were deleted.

 

odch/tmpdata.csv not deleted. It was not created by OSCH.

其中，-D:指定相关参数

 
• tableName:外部表名字
 
• datasetPaths:源数据存放路径（HDFS）
 
• datasetRegex:数据文件名
 
• connection.url:oracle数据库连接串
 
• connection.user:数据库用户名scott

这个生成的osch-20130516031513-6000-1就是所谓的位置文件，真正指明我们的目标数据文件，在HDFS保存的位置，查看下文件内容就明白了：

[root@ora11g ~]# more /data/ora11g/ODCH/extdir/osch-20130516031513-6000-1 

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<locationFile>

    <header>

        <version>1.0</version>

        <fileName>osch-20130516031513-6000-1</fileName>

        <createDate>2013-05-16T13:54:02</createDate>

        <publishDate>2013-05-16T03:15:13</publishDate>

        <productName>Oracle SQL Connector for HDFS Release 2.1.0 - Production</productName>

        <productVersion>2.1.0</productVersion>

    </header>

    <uri_list>

        <uri_list_item size="4685141" compressionCodec="">hdfs://hdnode1:9000/user/grid/odch/tmpdata.csv</uri_list_item>

    </uri_list>

</locationFile>

根据提示修改odch_ext_table外部表读取的文件：

SQL> ALTER TABLE "SCOTT"."ODCH_EXT_TABLE" 

  2     LOCATION

  3     (

  4       'osch-20130516031513-6000-1'

  5  );

 

Table altered.

 

SQL> set line 150 pages 1000;

SQL> col owner for a10

SQL> col name for a20

SQL> select * from odch_ext_table where rownum<10;

 

        ID OWNER      NAME                 MODIFIED            VAL

---------- ---------- -------------------- ------------ ----------

         1 SYS        ICOL$                15-MAY-13             2

         2 SYS        I_USER1              15-MAY-13            46

         3 SYS        CON$                 15-MAY-13            28

         4 SYS        UNDO$                15-MAY-13            15

         5 SYS        C_COBJ#              15-MAY-13            29

         6 SYS        I_OBJ#               15-MAY-13             3

         7 SYS        PROXY_ROLE_DATA$     15-MAY-13            25

         8 SYS        I_IND1               15-MAY-13            41

         9 SYS        I_CDEF2              15-MAY-13            54

 

9 rows selected.

数据被读取出来了，竣工。

 

 

三、ORACLE连接HADOOP(3) - OLH加载HDFS数据 

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

3、加载Hadoop数据到ORACLE

 

Oracle提供了专用的数据加载器，可以直接从HADOOP中加载数据到指定对象，该软件包下载地址：Oracle Loader for Hadoop Release 2.1.0

 

直接解压缩：

 

[root@ora11g ~]# unzip oraloader-2.1.0.zip

 

这个压缩包也是包中还有包的老路数，注意解压缩后会有两个压缩包，分别对应不同的版本，具体情况可以参考README中的说明，这里我们使用第一个压缩包，继续解压：

 

 

[root@ora11g ~]# unzip oraloader-2.1.0-1.x86_64.zip -d /usr/local/

 

[root@ora11g ~]# chown -R oracle:oinstall /usr/local/oraloader-2.1.0-1

 

[root@ora11g ~]# chmod -R 777 /usr/local/oraloader-2.1.0-1

  [root@ora11g ~]$ cp /usr/local/oraloader-2.1.0-1/jlib/*.jar /usr/local/hadoop-0.20.2/lib/

 

编辑grid用户的环境变量：

 

[root@ora11g ~]$ vi /home/grid/.bash_profile

 

增加下列两项：

 

 

export OLH_HOME=/usr/local/oraloader-2.1.0-1

  export OLH_JAR=${OLH_HOME}/jlib/oraloader.jar

 

提示，大家要理解，HADOOP相关的那三个环境变量也是要有的，我这里因为延续前面的环境，因此就没必要写了。

 

切换到sqlplus命令行，创建一个表对象：

 

 

SQL> conn scott/tiger

 

Connected.

 

SQL> create table t1(RN NUMBER,OBJECT_NAME VARCHAR2(20));

 

 

  Table created.

 

创建一个数据文件：

 

 

[grid@ora11g ~]$ more t1.dat

 

1,a

 

2,b

 

3,c

  4,d

 

上传至hdfs：

   

[grid@ora11g ~]$ hadoop dfs -put t1.dat olh/

 

创建两个配置文件：

 

 

[grid@localhost ~]$ more OLH/MyConf.xml

 

<?xml version="1.0" encoding="UTF-8" ?>

 

<configuration>

 

<!--                          Input settings                             -->

 

<property>

 

   <name>mapreduce.inputformat.class</name>

 

   <value>oracle.hadoop.loader.lib.input.DelimitedTextInputFormat</value>

 

</property>

 

<property>

 

   <name>mapred.input.dir</name>

 

   <value>olh</value>

 

</property>

 

<!--                          Output settings                             -->

 

<property>

 

    <name>mapreduce.outputformat.class</name>

 

    <value>oracle.hadoop.loader.lib.output.JDBCOutputFormat</value>

 

</property>

 

<property>

 

    <name>mapred.output.dir</name>

 

    <value>olh_out</value>

 

</property>   

 

<!--                          Table information                           -->

 

<property>

 

   <name>oracle.hadoop.loader.loaderMapFile</name>

 

   <value>file:///home/grid/OLH/olh_t1.xml</value>

 

</property>   

 

<!--                          Connection information                      -->

 

<property>

 

    <name>oracle.hadoop.loader.connection.url</name>

 

    <value>jdbc:oracle:thin:@//192.168.30.244:1521/jssdb</value>

 

</property>

 

<property>

 

    <name>oracle.hadoop.loader.connection.user</name>

 

    <value>scott</value>

 

    <description>AvroSchemaAwareDBInputFormat wants this case sensitive!

 

             (cause mapred.jdbc.username points here)</description>

 

</property>

 

<property>

 

    <name>oracle.hadoop.loader.connection.password</name>

 

    <value>tiger</value>       

 

</property>

  </configuration>

 

MyConf.xml的主要参数

                                                                                                                         

mapreduce.inputformat.class	指定输入文件的格式。除了文本文件，还支持hive格式文件。也可以是自定义的文件格式。
mapred.input.dir	Hadoop里输入的数据文件（含路径）
mapreduce.outputformat.class	指定装载的输出方式           在线装载:         OCIOutputFormat(*)，JDBCOutputFormat         离线装载:         DataPumptOutputFormat , DelimitedTextOutputFormat
mapred.output.dir	输出目录（同时也是LOG所在目录）
oracle.hadoop.loader.loaderMapFile	文件与表的对应关系，包括表名，栏位对应等
oracle.hadoop.loader.connection.url/user/pass	目标数据库的连接信息，包括url，用户名，密码

 

HDFS中的文件与表之间的映射关系：

 

 

[grid@localhost ~]$ more OLH/olh_t1.xml 

 

<?xml version="1.0" encoding="UTF-8" ?>

 

<LOADER_MAP>

 

<SCHEMA>SCOTT</SCHEMA>

 

<TABLE>T1</TABLE>

 

<COLUMN field="F0">RN</COLUMN>

 

<COLUMN field="F1">OBJECT_NAME</COLUMN>

  </LOADER_MAP>

 

配置好之后，执行hadoop命令如下：

 

 

[grid@localhost ~]$ hadoop jar ${OLH_JAR} oracle.hadoop.loader.OraLoader -conf OLH/MyConf.xml 

 

Oracle Loader for Hadoop Release 2.1.0 - Production

 

 

 

Copyright (c) 2011, 2013, Oracle and/or its affiliates. All rights reserved.

 

 

 

13/05/16 16:19:49 INFO loader.OraLoader: Oracle Loader for Hadoop Release 2.1.0 - Production

 

 

 

Copyright (c) 2011, 2013, Oracle and/or its affiliates. All rights reserved.

 

 

 

 

 

13/05/16 16:19:49 INFO loader.OraLoader: Built-Against: hadoop-1.1.1 hive-0.10.0 avro-1.6.3 jackson-1.8.8

 

13/05/16 16:19:51 INFO loader.OraLoader: oracle.hadoop.loader.loadByPartition is disabled because table: J1 is not partitioned

 

13/05/16 16:19:51 INFO loader.OraLoader: oracle.hadoop.loader.enableSorting disabled, no sorting key provided

 

13/05/16 16:19:51 INFO output.DBOutputFormat: Setting reduce tasks speculative execution to false for : oracle.hadoop.loader.lib.output.JDBCOutputFormat

 

13/05/16 16:19:51 WARN loader.OraLoader: Sampler error: the number of reduce tasks must be greater than one; the configured value is 1 . Job will continue without sampled information.

 

13/05/16 16:19:51 INFO loader.OraLoader: Sampling time=0D:0h:0m:0s:20ms (20 ms)

 

13/05/16 16:19:51 INFO loader.OraLoader: Submitting OraLoader job OraLoader

 

13/05/16 16:19:53 INFO input.FileInputFormat: Total input paths to process : 1

 

13/05/16 16:19:56 INFO loader.OraLoader: map 0% reduce 0%

 

13/05/16 16:20:31 INFO loader.OraLoader: map 100% reduce 0%

 

13/05/16 16:20:40 INFO loader.OraLoader: map 100% reduce 33%

 

13/05/16 16:20:46 INFO loader.OraLoader: map 100% reduce 100%

 

13/05/16 16:20:48 INFO loader.OraLoader: Job complete: OraLoader (null)

 

13/05/16 16:20:48 INFO loader.OraLoader: Counters: 17

 

        FileSystemCounters

 

                FILE_BYTES_READ=98

 

                FILE_BYTES_WRITTEN=228

 

                HDFS_BYTES_READ=16

 

                HDFS_BYTES_WRITTEN=1850

 

        Job Counters 

 

                Data-local map tasks=1

 

                Launched map tasks=1

 

                Launched reduce tasks=1

 

        Map-Reduce Framework

 

                Combine input records=0

 

                Combine output records=0

 

                Map input records=4

 

                Map output bytes=84

 

                Map output records=4

 

                Reduce input groups=1

 

                Reduce input records=4

 

                Reduce output records=4

 

                Reduce shuffle bytes=98

                  Spilled Records=8

 

提示插入了4条记录，下面就切换到sqlplus中验证一下吧：

 

 

SQL> select * from t1;

 

 

 

        RN OBJECT_NAME

 

---------- --------------------

 

         1 a

 

         2 b

 

         3 c

           4 d

 

成功喽。

 

另外，如果不想配置MyConf.xml，也可以将参数放到hadoop命令中执行，例如：

 

 

$ hadoop jar \

 

    ${ORAHDFS_JAR} oracle.hadoop.loader.OraLoader  \

 

    -D mapreduce.inputformat.class=oracle.hadoop.loader.lib.input.DelimitedTextInputFormat \

 

    -D mapred.input.dir=olh \

 

    -D mapreduce.outputformat.class=oracle.hadoop.loader.lib.output.JDBCOutputFormat \

 

    -D mapred.output.dir=olh_out \

 

    -D oracle.hadoop.loader.loaderMapFile=file:///home/grid/OLH/loaderMap.xml \

 

    -D oracle.hadoop.loader.connection.url="jdbc:oracle:thin:@//192.168.30.244:1521/jssdb" \

 

    -D oracle.hadoop.loader.connection.user=SCOTT \

 

    -D oracle.hadoop.loader.connection.password=tiger \

      -publish

 

功能是相同的，有兴趣的朋友可以自己试一下。