Category Archives: OTA4H

Data Query between BDA and Exadata (Part 2): Query Oracle Table from Hive on BDA

Posted on by

3

mouse_dancing
In the last post (Data Query between BDA and Exadata (Part 1): Query Hive Table from Oracle on Exadata), I show the way to use Oracle Big Data SQL from Oracle table to access hive table on BDA. In this post, I am going to show the data flow in opposite direction: Query data in an Oracle Table on Exadata from Hive on BDA.

Step 1: Load some data to an Oracle table on Exadata
On one of DB nodes on Exadata, run the following in SQLPlus.

sqlplus wzhou/wzhou
CREATE TABLE ORA_STOCK_PRICE1
(    
    STOCK_DATE  VARCHAR2(20),
    CLOSE_PRICE NUMBER(8,3),
    VOLUME NUMBER(8),
    OPEN_PRICE NUMBER(8,3),
    HIGH_PRICE NUMBER(8,3),
    LOW_PRICE NUMBER(8,3)               
);
INSERT INTO ORA_STOCK_PRICE1 values ( '6/18/16', 23.6,320000,23.62,23.74,23.5 );
INSERT INTO ORA_STOCK_PRICE1 values ( '6/19/16', 23.72,350000,23.73,23.8,23.38 );
COMMIT;

Let’s check out the content in the table.

WZHOU:SQL> set lines 120
WZHOU:SQL> select * from ORA_STOCK_PRICE1; 

STOCK_DATE           CLOSE_PRICE      VOLUME  OPEN_PRICE  HIGH_PRICE   LOW_PRICE
-------------------- ----------- ----------- ----------- ----------- -----------
6/18/16                     23.6      320000       23.62       23.74        23.5
6/19/16                    23.72      350000       23.73        23.8       23.38

Step 2: Create the script for the external table in Hive.
To access data in Oracle table, one way is using Oracle Table Access for Hadoop and Spark (OTA4H). OTA4H is an Oracle BDA feature that converts Oracle tables to Hadoop datasources. It allows direct access to master data in Oracle database using Hive SQL.

On BDA node, create a Hive external table.

vi hive_create_ext_ora_to_stock_price_test1.hql
DROP TABLE EXT_ORA_STOCK_PRICE_TEST1;
CREATE EXTERNAL TABLE IF NOT EXISTS EXT_ORA_STOCK_PRICE_TEST1 (
    stock_date string,
    close_price float,
    volume int,
    open_price float,
    high_price float,
    low_price float
)
STORED BY 'oracle.hcat.osh.OracleStorageHandler'
WITH SERDEPROPERTIES (
    'oracle.hcat.osh.columns.mapping' = 'stock_date,close_price,volume,open_price,high_price,low_price')
TBLPROPERTIES(
    'mapreduce.jdbc.url'='jdbc:oracle:thin:@enkx3-scan:1521/bigdatadb',
    'mapreduce.jdbc.username'='wzhou',
    'mapreduce.jdbc.password'='wzhou',
    'mapreduce.jdbc.input.table.name'='ORA_STOCK_PRICE1'
);

Step 3: Create the hive table on HDFS 

[oracle@enkbda1node01 test]$ export HIVE_AUX_JARS_PATH=/opt/oracle/ota4h/jlib/osh.jar:/opt/oracle/ota4h/jlib/ucp.jar:/opt/oracle/ota4h/jlib/ojdbc7.jar
[oracle@enkbda1node01 test]$ hive -f hive_create_ext_ora_to_stock_price_test1.hql
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0
Java HotSpot(TM) 64-Bit Server VM warning: Using incremental CMS is deprecated and will likely be removed in a future release
16/11/02 14:29:29 WARN mapreduce.TableMapReduceUtil: The hbase-prefix-tree module jar containing PrefixTreeCodec is not present.  Continuing without it.
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0

Logging initialized using configuration in jar:file:/opt/cloudera/parcels/CDH-5.7.0-1.cdh5.7.0.p1464.1349/jars/hive-common-1.1.0-cdh5.7.0.jar!/hive-log4j.properties
OK
Time taken: 0.642 seconds
OK
Time taken: 0.381 seconds

Step 4: Verify the result
We should see two rows in the result.

[oracle@enkbda1node01 test]$ hive
hive> select * from EXT_ORA_STOCK_PRICE_TEST1;
OK
6/18/16	23.6	320000	23.62	23.74	23.5
6/19/16	23.72	350000	23.73	23.8	23.38
Time taken: 2.093 seconds, Fetched: 2 row(s)

Ok, the result is exactly what I expect.