使用oracle的大数据工具ODCH访问HDFS数据文件

2022-10-30 10:51:32

软件下载

Oracle Big Data Connectors：ODCH

下载地址：

http://www.oracle.com/technetwork/bdc/big-data-connectors/downloads/index.html

使用oracle的大数据工具ODCH访问HDFS数据文件

Jave SE 下载地址：

http://www.oracle.com/technetwork/java/javase/downloads/jdk6u38-downloads-1877406.html

使用oracle的大数据工具ODCH访问HDFS数据文件

Oracle11g下载地址：

使用oracle的大数据工具ODCH访问HDFS数据文件

Oracle Enterprise Linux下载地址：需要注册oracle帐号才能下载：

https://edelivery.oracle.com/linux

使用oracle的大数据工具ODCH访问HDFS数据文件

安装Oracle服务器

在虚拟机或者物理机下安装都可以：

使用oracle的大数据工具ODCH访问HDFS数据文件

基本上就是一顿下一步。

然后安装oracle11g，这个真是折腾了好几天

最后按照这篇文章操作，安装成功。

http://wenku.baidu.com/view/fc804254be23482fb4da4c63.html

我觉得这块最好的文章就是oracle的官方给的教程：

http://docs.oracle.com/cd/E11882_01/install.112/e24326/toc.htm

安装hadoop

http://blog.csdn.net/lichangzai/article/details/8206834

遇到问题1：

ssh 免密码登录设置后还是提示需要输入密码

解决方法如下：

chmod  ~/.ssh/

chmod  /home/userName

chmod  ~/.ssh/authorized_keys

遇到的问题2：

Agent admitted failure to sign using the key

解決办法：

应用 ssh-add 指令將私钥加进来（名称可能不是 id_rsa）

＃ ssh-add   ~/.ssh/id_rsa

Hadoop/Oracle架构

机器部署情况：

主机名

IP

系统版本

Oracle node

Hadoop node

hadoop进程名

Master

192.168.74.153

OEL6.4

Oracle11.2.0.2

master

namenode,jobtracker

Node1

192.168.2.147

Ubuntu12.04

无

slave

datanode,tasktracker

Node2

192.168.2.148

Ubuntu12.04

无

slave

datanode,tasktracker

主要环境变量：

变量名

变量值

ORACLE_HOME

/oracle/11g

ORACLE_SID

orcl

HADOOP_HOME

/home/hadoop/hadoop-1.2

HADOOP_CONF_DIR

$HADOOP_HOME/conf

OSCH_HOME

/opt/odch/orahdfs-2.2.0

ORAHDFS_JAR

$OSCH_HOME/jlib/orahdfs.jar

HDFS_BIN_PATH

/opt/odch/orahdfs-2.2.0

HADOOP_CLASSPATH

$OSCH_HOME/jlib/*

按照以上配置。

安装ODCH并设置环境变量

在opt下建立文件夹odch，将文件传到这个目录下解压缩。

/opt/odch

解压缩：

unzip orahdfs-2.2.0.zip

然后添加hadoop\oracle\ODCH主要要环境变量

进入hadoop用户主目录，vi .bash_profile

我的配置如下（可参考）：

export ORACLE_BASE=/opt/oracle

export ORACLE_HOME=$ORACLE_BASE/11g

export ORACLE_SID=orcl

export PATH=/usr/sbin:$PATH

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/usr/X11R6/lib64/

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export HADOOP_HOME=/home/hadoop/hadoop/hadoop-1.2.

PATH=$PATH:$HOME/bin:/home/hadoop/hadoop/jdk1..0_21/bin:$HADOOP_HOME/bin

export HADOOP_CONF_DIR=$HADOOP_HOME/conf

export OSCH_HOME=/opt/odch/orahdfs-2.2.

export ORAHDFS_JAR=$OSCH_HOME/jlib/orahdfs.jar

export HDFS_BIN_PATH=$OSCH_HOME/bin

export JAVA_HOME=/home/hadoop/hadoop/jdk1..0_21

export PATH

修改用户组属性：

说明：因为之前的hadoop安装在hadoop系统用户下，而oracle安装在了oracle系统用户下，为了操作方便，使hadoop用户有操作oracle操作库的权限，现在修改hadoop系统用户属组，添加到和oracle同样的用户组下。

--查看之前的用户属组

[hadoop@master ~]$ id hadoop

uid=(hadoop) gid=(hadoop) groups=(hadoop)

[hadoop@master ~]$ id oracle

uid=(oracle) gid=(oinstall) groups=(oinstall),(dba)

--修改用户属组

vi /etc/group

--再查看用户组属性

[hadoop@master ~]$ id hadoop

uid=(hadoop) gid=(hadoop) groups=(hadoop),(oinstall),(dba)

然后重启一下计算机。

启动oracle并测试

连接oracle启动数据库

[hadoop@master Desktop]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.1. Production on Sat Aug  ::

Copyright (c) , , Oracle.  All rights reserved.

Connected to an idle instance.

//启动数据库

SQL> startup;

ORACLE instance started.

Total System Global Area  bytes

Fixed Size             bytes

Variable Size           bytes

Database Buffers       bytes

Redo Buffers             bytes

Database mounted.

Database opened.

//显示数据库实例名

SQL> show parameter db_name;

NAME                     TYPE     VALUE

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

db_name                  string     orcl

然后启动oracle的listener

[hadoop@master Desktop]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1. - Production on -AUG- ::

Copyright (c) , , Oracle.  All rights reserved.

Starting /opt/oracle/11g/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1. - Production

System parameter file is /opt/oracle/11g/network/admin/listener.ora

Log messages written to /opt/oracle/diag/tnslsnr/master/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=master)(PORT=)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.1. - Production

Start Date                -AUG- ::

Uptime                     days  hr.  min.  sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /opt/oracle/11g/network/admin/listener.ora

Listener Log File         /opt/oracle/diag/tnslsnr/master/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=master)(PORT=)))

The listener supports no services

The command completed successfully

//检查一下状态

[hadoop@master Desktop]$ lsnrctl stat

LSNRCTL for Linux: Version 11.2.0.1. - Production on -AUG- ::

Copyright (c) , , Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.1. - Production

Start Date                -AUG- ::

Uptime                     days  hr.  min.  sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /opt/oracle/11g/network/admin/listener.ora

Listener Log File         /opt/oracle/diag/tnslsnr/master/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=master)(PORT=)))

The listener supports no services

The command completed successfully

配置HDFS_STREAM和建表

进入目录/opt/odch/orahdfs-2.2.0/bin，用vi打开hdfs_stream文件，加入如下配置：

export HADOOP_HOME=/home/hadoop/hadoop/hadoop-1.2.

export OSCH_HOME=/opt/odch/orahdfs-2.2.

export PATH=/usr/bin:/bin:$HADOOP_HOME/bin

确保oracle用户对$ODCH_LOG_DIR/logs目录有读写权限

因为Oracle用户需要在 {$ODCH_LOG_DIR} 目录中创建log/bad文件，所以要确保oracle用户对$ODCH_LOG_DIR/log目录有读写权限

--修改目录权限，测试读写文件

[root@gc opt]# chmod -R  odch/

[root@gc opt]# su - oracle

[oracle@gc ~]$ cd /opt/odch/orahdfs-2.2./log/

[oracle@gc log]$ touch ora_access_test

[oracle@gc log]$ rm ora_access_test

配置操作系统目录和数据库的Directory对象

--创建所用的系统目录

[root@gc ~]# mkdir -p /opt/odch/orahdfs-2.2./logs

[root@gc ~]# mkdir -p /opt/odch/orahdfs-2.2./extdir

[root@gc ~]# chmod  /opt/odch/orahdfs-2.2./logs

[root@gc ~]# chmod  /opt/odch/orahdfs-2.2./extdir

--创建oracle Directory对象

sqlplus "/as sysdba"

SQL> create or replace directory ODCH_LOG_DIR as '/opt/odch/orahdfs-2.2.0/logs';

Directory created.

SQL> grant read, write on directory ODCH_LOG_DIR to SCOTT;

Grant succeeded.

SQL> create or replace directory ODCH_DATA_DIR as '/opt/odch/orahdfs-2.2.0/extdir';

Directory created.

SQL> grant read, write on directory ODCH_DATA_DIR to SCOTT;

Grant succeeded.

SQL> create or replace directory HDFS_BIN_PATH as '/opt/odch/orahdfs-2.2.0/bin';

Directory created.

SQL> grant execute on directory HDFS_BIN_PATH to SCOTT;

Grant succeeded.

SQL> grant read, write on directory HDFS_BIN_PATH to SCOTT;

Grant succeeded.

--目录对象说明

HDFS_BIN_PATH:：hdfs_stream脚本所在目录.

HDFS_DATA_DIR：用来存放“位置文件”(location files)的目录。“位置文件”(location files) 是一个配置文件，里面包含HDFS的文件路径/文件名以及文件编码格式。

ODCH_LOG_DIR：Oracle用来存放外部表的log/bad等文件的目录.

创建oracle外部表：

conn scott/tiger

在登录的时候提示：

ORA-: the account is locked

ALTER USER scott  ACCOUNT UNLOCK;

连上之后然后创建表：

CREATE TABLE odch_ext_table  ( ID NUMBER ,OWNER VARCHAR2()  ,NAME VARCHAR2() ,MODIFIED DATE ,Val NUMBER ) ORGANIZATION EXTERNAL  (TYPE oracle_loader DEFAULT DIRECTORY ODCH_DATA_DIR ACCESS PARAMETERS  ( records delimited by newline preprocessor HDFS_BIN_PATH:hdfs_stream badfile ODCH_LOG_DIR:'odch_ext_table%a_%p.bad'  logfile ODCH_LOG_DIR:'odch_ext_table%a_%p.log'  fields terminated by ',' OPTIONALLY ENCLOSED BY '"' missing field values are null (  ID DECIMAL EXTERNAL, OWNER CHAR(), NAME CHAR(), MODIFIED CHAR DATE_FORMAT DATE MASK "YYYY-MM-DD HH24:MI:SS", Val DECIMAL EXTERNAL  ) ) LOCATION ('Tmpdata.csv') ) PARALLEL REJECT LIMIT UNLIMITED;

准备示例文件

--示例文件内容

在附件中Tmpdata.csv

,SYS,ORA$BASE,-AUG-,

,SYS,DUAL,-AUG-,

,PUBLIC,DUAL,-AUG-,

,PUBLIC,MAP_OBJECT,-AUG-,

文件的准备过程：

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

 -- 建立存储过程

 CREATE OR REPLACE PROCEDURE SQL_TO_CSV

     (

         P_QUERY IN VARCHAR2,                        -- PLSQL文

         P_DIR IN VARCHAR2,                          -- 导出的文件放置目录

         P_FILENAME IN VARCHAR2                      -- CSV名

 )

 IS

         L_OUTPUT UTL_FILE.FILE_TYPE;

         L_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;

         L_COLUMNVALUE VARCHAR2();

         L_STATUS INTEGER;

         L_COLCNT NUMBER := ;

         L_SEPARATOR VARCHAR2();

         L_DESCTBL DBMS_SQL.DESC_TAB;

         P_MAX_LINESIZE NUMBER := ;

 BEGIN

         --OPEN FILE

         L_OUTPUT := UTL_FILE.FOPEN(P_DIR, P_FILENAME, 'W', P_MAX_LINESIZE);

         --DEFINE DATE FORMAT

         EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''';

         --OPEN CURSOR

         DBMS_SQL.PARSE( L_THECURSOR, P_QUERY, DBMS_SQL.NATIVE );

         DBMS_SQL.DESCRIBE_COLUMNS( L_THECURSOR, L_COLCNT, L_DESCTBL );

         --DUMP TABLE COLUMN NAME

         FOR I IN  .. L_COLCNT LOOP

             UTL_FILE.PUT( L_OUTPUT, L_SEPARATOR || '”' || L_DESCTBL(I).COL_NAME || '”' );

             DBMS_SQL.DEFINE_COLUMN( L_THECURSOR, I, L_COLUMNVALUE,  );

             L_SEPARATOR := ',';

         END LOOP;

         UTL_FILE.NEW_LINE( L_OUTPUT );

         --EXECUTE THE QUERY STATEMENT

         L_STATUS := DBMS_SQL.EXECUTE(L_THECURSOR);

         --DUMP TABLE COLUMN VALUE

         WHILE ( DBMS_SQL.FETCH_ROWS(L_THECURSOR) >  ) LOOP

             L_SEPARATOR := '';

             FOR I IN  .. L_COLCNT LOOP

                 DBMS_SQL.COLUMN_VALUE( L_THECURSOR, I, L_COLUMNVALUE );

                 UTL_FILE.PUT( L_OUTPUT, L_SEPARATOR || '”' ||

                 TRIM(BOTH ' ' FROM REPLACE(L_COLUMNVALUE,'”','””')) || '”');

                 L_SEPARATOR := ',';

             END LOOP;

             UTL_FILE.NEW_LINE( L_OUTPUT );

         END LOOP;

         --CLOSE CURSOR

         DBMS_SQL.CLOSE_CURSOR(L_THECURSOR);

         --CLOSE FILE

         UTL_FILE.FCLOSE( L_OUTPUT );

 EXCEPTION

         WHEN OTHERS THEN

         RAISE;

 END;

 /

 -- 创建存放文件的目录

 CREATE OR REPLACE DIRECTORY MYDIR AS 'C:\';

 -- 执行块

  begin

     sql_to_csv('select * from au_jxs ','MYDIR','EXAMPLE.CSV');

  end;

 /

其中路径文件名等可根据实际情况修改。

启动hadoop

[hadoop@master ~]$ start-all.sh

Warning: $HADOOP_HOME is deprecated.

starting namenode, logging to /home/hadoop/hadoop/hadoop-1.2./libexec/../logs/hadoop-hadoop-namenode-master.out

node1: starting datanode, logging to /home/hadoop/hadoop/hadoop-1.2./libexec/../logs/hadoop-hadoop-datanode-node1.out

node2: starting datanode, logging to /home/hadoop/hadoop/hadoop-1.2./libexec/../logs/hadoop-hadoop-datanode-node2.out

master: starting secondarynamenode, logging to /home/hadoop/hadoop/hadoop-1.2./libexec/../logs/hadoop-hadoop-secondarynamenode-master.out

starting jobtracker, logging to /home/hadoop/hadoop/hadoop-1.2./libexec/../logs/hadoop-hadoop-jobtracker-master.out

node1: starting tasktracker, logging to /home/hadoop/hadoop/hadoop-1.2./libexec/../logs/hadoop-hadoop-tasktracker-node1.out

node2: starting tasktracker, logging to /home/hadoop/hadoop/hadoop-1.2./libexec/../logs/hadoop-hadoop-tasktracker-node2.out

--先在Hadoop中建立一个目录，然后把empxt*.dat放入该目录中

[hadoop@master ~]$ hadoop dfs -mkdir odch

Warning: $HADOOP_HOME is deprecated.

[hadoop@master ~]$ hadoop dfs -put Tmpdata.csv odch

Warning: $HADOOP_HOME is deprecated.

[hadoop@master ~]$ hadoop dfs -ls odch

Warning: $HADOOP_HOME is deprecated.

[hadoop@master ~]$ hadoop dfs -ls odch

Warning: $HADOOP_HOME is deprecated.

Found  items

-rw-r--r--    hadoop supergroup         -- : /user/hadoop/odch/Tmpdata.csv

生成位置文件

--执行下面的命令

export HADOOP_CLASSPATH="$OSCH_HOME/jlib/*"
[hadoop@master ~]$ 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.74.153:1521/orcl" \

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

> -publish

Warning: $HADOOP_HOME is deprecated.

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.2. - Production

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

[Enter Database Password:]

The publish command succeeded.

ALTER TABLE "SCOTT"."ODCH_EXT_TABLE"

LOCATION

(

  'osch-20130818094259-1319-1'

);

The following location files were created.

osch--- contains  URI,  bytes

          hdfs://master:9000/user/hadoop/odch/Tmpdata.csv

The following location files were deleted.

参数说明：

ExternalTable:使用hadoop ExternalTable命令工具

-D:指定相关参数

tableName:外部表名字

datasetPaths:源数据存放路径（HDFS）

datasetRegex:数据源格式

connection.url:oracle数据库连接串

connection.user:数据库用户名scott

命令执行后还要输入用户名密码：oracle

修改参数：

ALTER TABLE "SCOTT"."ODCH_EXT_TABLE"

LOCATION

(

'osch-20130818094259-1319-1'

);

查看位置文件内容和外链表信息

进入extdir目录：

[hadoop@master ~]$ cd /opt/odch/orahdfs-2.2./extdir/

[hadoop@master extdir]$ ls

osch---  osch---  osch---  osch---

[hadoop@master extdir]$ more osch---

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

<locationFile>

    <header>

        <version>1.0</version>

        <fileName>osch---</fileName>

        <createDate>--18T21::</createDate>

        <publishDate>--18T09::</publishDate>

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

        <productVersion>2.2.</productVersion>

    </header>

    <uri_list>

        <uri_list_item size="" compressionCodec="">hdfs://master:9000/user/hadoop/odch/Tmpdata.csv</uri_list_item

>

    </uri_list>

</locationFile>

可以看出上面有了到hadoop的指向信息。

查看外链到hadoop表信息：

在scott用户查看：

SQL> set line  pages ;

SQL> col owner for a10

SQL> col name for a20

SQL> select * from odch_ext_table;

    ID OWNER      NAME           MODIFIED        VAL

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

      SYS          ORA$BASE           -AUG-

      SYS          DUAL           -AUG-

      PUBLIC     DUAL           -AUG-

      PUBLIC     MAP_OBJECT       -AUG-

这些就是查询到的hadoop中的数据信息。

上一篇：微信小程序接入腾讯云IM即时通讯（会话列表）


下一篇：聚焦游戏安全，腾讯云GAME-TECH“空降”上海