Access External Data Sources from Oracle Database

  • November 16, 2017

See How Easily You Can Move Data Between Apache Hadoop and Oracle Database in the Cloud - Part 1


The Oracle Big Data Cloud Service (BDCS) license includes tools to move data between Apache Hadoop and Oracle Database, such as Oracle Loader for Hadoop and Copy to Hadoop.   Here is how you can use these tools with the easy-to-use CLI (OHSH).   These tools are all installed and configured in BDCS.

There are just two additional configuration steps you need to perform:

  1. In the database cloud service enable the access rule to open the listener port 1521.

Access Cloudera Manager to note the Hiveserver2 host and port.   Edit /opt/oracle/ohsh-<ver>/bin/ohsh _config.sh to set these variables:

export OLH_HOME=/opt/oracle/oraloader-<version>

export OSCH_HOME=/opt/oracle/orahdfs-<version>

export CP2HADOOP_HOME=/opt/oracle/orahivedp-<version>

export HS2_HOST_PORT="<Hiveserver 2 host>:<Hiveserver 2 port>"   

And as necessary TNS_ADMIN and WALLET_LOCATION

Using the CLI

  1. ssh to a BDCS node.
  2. Start up the CLI
    $ ohsh
  3. Set up resources to connect to the database, and to Hive databases. 


Database resources

You will be prompted for database username/password when you run these commands.

ohsh> create sqlplus resource sql0 connectid=“bdctest.compute-xyz00.oraclecloud.internal:1521/pdb1.xyz00.oraclecloud.internal”

ohsh> create jdbc resource jdbc0 connectid=“bdctest.compute-xyz00.oraclecloud.internal:1521/pdb1.xyz00.oraclecloud.internal”

Hive resources

%hive0 is the resource for the database default in Hive.   Additional resources to connect to Hive databases:

ohsh> create hive resource hive_moviedemo connectid=“bdctest.compute-xyz00.oraclecloud.internal:1521/pdb1.xyz00.oraclecloud.internal/moviedemo”


Data movement examples

Load from Hadoop to Oracle Database

ohsh> load oracle table jdbc0:movie_ratings from hive table hive_oracletest:movie_ratings_delimitedtext using directpath

Load from Oracle Database to Hadoop

ohsh> create or replace hive table hive_moviedemo:movie_ratings_oracle from oracle table jdbc0:movie_ratings using directcopy


As easy as pie.



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.