Access External Data Sources from Oracle Database

  • June 15, 2016

Oracle Shell for Hadoop Loaders (OHSH)

Oracle Shell for Hadoop Loaders (OHSH) is an intuitive
command line tool to move data between Hadoop and Oracle Database.

OHSH uses the concept of resources to identify data
sources and destinations.  

  • Hive resource: For executing Hive commands and
    specifying Hive as a source or destination
  • Hadoop resource: For executing HDFS commands to
    navigate HDFS and use HDFS as a source or destination
  • SQL resource: For executing SQL commands in a
    database schema
  • JDBC resource: To make JDBC connections to the

For example,
hive0 is a resource created when
OHSH starts up, and enables access to the Hive database default.

is a resource (also created when OHSH starts up) that enables access to HDFS.

Additional resources can be created to connect other Hive
databases, and to Oracle Database schemas.

Here are some examples you can use in the Oracle Big Data
Lite VM 4.5

# Startup OHSH
prompt> ohsh

# Create a hive resource for hive database MOVIEDEMO
ohsh> create hive resource
hive_moviedemo database="MOVIEDEMO"

# Create a SQL resource for Oracle Database user MOVIEDEMO
ohsh> create sqlplus resource sql
user="MOVIEDEMO" \ connectid="bigdatalite.localdomain:1521/orcl"

# Create a JDBC resource to connect to Oracle Database
ohsh> create oracle jdbc resource moviedemo
user="MOVIEDEMO" \ connectid="bigdatalite.localdomain:1521/orcl"

Now you can move data between the source and destination
identified by these resources.  Here is an example to run Oracle Loader for Hadoop.

# Load data from Hive table moviedemo.movieapp_log_stage
into a table in the

# OracleDatabase moviedemo.movie_local_tab
ohsh> load oracle table
moviedemo:MOVIE_LOCAL_TAB \

from hive table hive_moviedemo:movieapp_log_stage database=moviedemo \

using directpath

# From
within the same UI, check that the rows have been loaded into the Oracle
Database table

ohsh> %sql select count(*) from MOVIE_LOCAL_TAB;

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.