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:
- 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
- ssh to a BDCS node.
- Start up the CLI
$ ohsh - 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.