In addition to the software used in Chapter 11, download and install the following software.
- Oracle Database 11g. This chapter assumes you have Oracle Database 11g (or later) installed.
- Oracle Loader for Hadoop 3.0.0. Download from www.oracle.com/technetwork/database/database-technologies/bdc/big-data-connectors/downloads/index.html as part of the “Big Data Collectors” download.
- Oracle Data Integrator 11g. Download Data Integrator 11g (or later) from www.oracle.com/technetwork/middleware/data-integrator/downloads/index.html.
Oracle Linux is used in this chapter as in Chapter 11. The installation procedure for Oracle Database 11g and Oracle Data Integrator 11g is too elaborate to be included in a chapter. Download Oracle Loader for Hadoop Release 3.0.0 oraloader-3.0.0.x86_64.zip from www.oracle.com/technetwork/database/database-technologies/bdc/big-data-connectors/downloads/index.html. Unzip the file to a directory. Two files get extracted oraloader-3.0.0-h1.x86_64.zip and oraloader-3.0.0-h2.x86_64.zip. The oraloader-3.0.0-h1.x86_64.zip file is for Apache Hadoop 1.x and oraloader-3.0.0-h2.x86_64.zip for CDH4 and CDH5. As we are using CDH4.6, extract oraloader-3.0.0-h2.x86_64.zip on Linux as user root with the following command.
root>unzip oraloader-3.0.0-h2.x86_64.zip
Oracle Loader for Hadoop 3.0.0 gets extracted to oraloader-3.0.0-h2 directory. Set the environment variables for Oracle Database, Oracle Data Integrator, Oracle Loader for Hadoop, Hadoop, Hive, Java, and MongoDB in the bash shell.
vi ~/.bashrc
export ODI_HOME=/home/dvohra/dbhome_1
export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=ORCL
export OLH_HOME=/mongodb/oraloader-3.0.0-h2
export HADOOP_PREFIX=/mongodb/hadoop-2.0.0-cdh4.6.0
export HADOOP_CONF=$HADOOP_PREFIX/etc/hadoop
export MONGO_HOME=/mongodb/mongodb-linux-i686-2.6.3
export HIVE_HOME=/mongodb/hive-0.10.0-cdh4.6.0
export HIVE_CONF=$HIVE_HOME/conf export JAVA_HOME=/mongodb/jdk1.7.0_55
export HADOOP_MAPRED_HOME=/mongodb/hadoop-2.0.0-cdh4.6.0/bin
export HADOOP_HOME=/mongodb/hadoop-2.0.0-cdh4.6.0/share/hadoop/mapreduce2
export HADOOP_CLASSPATH=$HADOOP_HOME/*:$HADOOP_HOME/lib/*:$HIVE_HOME/lib/*:$OLH_HOME/jlib/*:
/mongodb/mongo-java-driver-2.6.3.jar:/mongodb/hive-mongo-0.0.3-jar-with-dependencies.
jar:$HIVE_CONF:$HADOOP_CONF
export PATH=$PATH:$HADOOP_HOME/bin:$HADOOP_MAPRED_HOME::$HIVE_HOME/bin:$MONGO_HOME/ bin:$ORACLE_HOME/bin
We shall use the Hive table created over MongoDB in Chapter 11 to integrate MongoDB data to Oracle Database. Create the target database table in Oracle Database with the following SQL script.
CREATE TABLE OE.wlslog (time_stamp VARCHAR2(255), category VARCHAR2(255), type VARCHAR2(255), servername VARCHAR2(255), code VARCHAR2(255), msg VARCHAR2(255));
The output from the CREATE TABLE SQL statement is shown in Figure 12-1. Oracle Database table OE. WLSLOG has the same column names as the Hive external table from which data is to be loaded.
As discussed in Chapter 11, add data to MongoDB datastore in a Java application. Create a Hive external table wlslog also as discussed in Chapter 11. Start the Hive remote metastore with the following command.
hive –service hiveserver
Hive server gets started as shown in Figure 12-2.
As we are using Oracle Data Integrator (ODI), we need to install ODI, create the repositories, and connect to repositories for defining the physical and logical architecture and the models and the interface for mapping the source datastore to the target datastore. Oracle Data Integrator Studio is launched with the following command.
cd /home/dvohra/dbhome_1/oracledi/client
sh odi.sh
Source: Vohra Deepak (2015), Pro MongoDB™ Development, Apress; 1st ed. edition.