1 Getting Started with Oracle Big Data Connectors

On the Hadoop cluster:

• Cloudera's Distribution including Apache Hadoop version 5 (CDH5), or, Hortonworks Data Platform 2.4.0 and 2.5.0.

• Java Development Kit (JDK). Consult the distributor of your Hadoop software (Cloudera or Apache) for the recommended version.

• Hive 1.1.0, or 1.2.1 (required for Hive table access, otherwise optional)

On the Oracle Database system and Hadoop client systems:

• Oracle Database 12c (12.2.0.1 and 12.1.0.2) , Oracle Database 11g release 2 (11.2.0.4 or later).

• The same version of Hadoop as your Hadoop cluster: CDH5, or Hortonworks Data Platform 2.4.0 and 2.5.0.

  If you have a secure Hadoop cluster configured with Kerberos, then the Hadoop client on the database system must be set up to access a secure cluster. See "Using Oracle SQL Connector for HDFS on a Secure Hadoop Cluster."

• The same version of JDK as your Hadoop cluster.

Oracle SQL Connector for HDFS requires a Hadoop client on the Oracle Database System. The Hadoop installation can be minimally configured for Hadoop client use only. The full configuration of Hadoop is not needed. The only parts of Hadoop needed for Oracle SQL Connector for HDFS are the Hadoop JAR files and the configuration files from the Hadoop installation.

For Oracle RAC systems including Oracle Exadata Database Machine, you must install and configure Oracle SQL Connector for HDFS using identical paths on all systems running Oracle instances.

Adding a Hadoop Client for use with Oracle Big Data Appliance

Oracle Big Data Appliance requires that you follow its own system-supported procedures for installing a Hadoop client. If your Hadoop system is an Oracle Big Data Appliance, see Providing Remote Access to CDH in the Oracle Big Data Appliance Software User's Guide. This section describes how to install the CDH client, configure it for use in a Kerberos-secured or non-secured environment, and verify HDFS access.

Adding a Hadoop Client for use with Other Hadoop Systems

For connections to Hadoop systems other than Oracle Big Data Appliance, download and install the Hadoop client provide by the Hadoop distributor (Cloudera or Apache). The following example shows how to connect a Hadoop client to a CDH system that is not an Oracle Big Data Appliance. You can use these steps to install the client, configure it for use in Kerberos-secured or a non-secured environment, and test to verify HDFS access.

In this case also you need set up Kerberos access (if Kerberos is installed) and should include a final test to make sure HDFS access is working.

1. Use one of these methods to obtain the files:

   • Download the tarball from the Cloudera tarball downloads page. Check that the Hadoop version in the filename (as in hadoop-2.5.0-cdh5.2.5.tar.gz) that matches the version of the Hadoop cluster.

   • Click on the hdfs service in Cloudera Manager, and select the action Download Client Configuration.

2. Extract the files and copy them to a permanent path of your choice on the database system.

3. Set the HADOOP_HOME environment variable to this path and add HADOOP_HOME/bin to the PATH variable.

4. Ensure that JAVA_HOME points to a JDK installation with the version required by the Hadoop installation.

5. If your cluster is secured with Kerberos, then configure the Oracle system to permit Kerberos authentication. See "Using Oracle SQL Connector for HDFS on a Secure Hadoop Cluster."

6. Test HDFS access from the Oracle Database system:

   1. Log in to the system where Oracle Database is running by using the Oracle Database account.

   2. Open a Bash shell and enter this command:

      $ hdfs dfs -ls /user
      

      You might need to add the directory containing the Hadoop executable file to the environment variable. The default path for CDH is /usr/bin.

      You should see the same list of directories that you see when you run the command directly on the Hadoop cluster. If not, then first ensure that the Hadoop cluster is up and running. If the problem persists, then you must correct the Hadoop client configuration so that Oracle Database has access to the Hadoop cluster file system.

7. For an Oracle RAC system, repeat this procedure for every Oracle Database instance.

Setting up Hadoop Clients on Additional Systems

You have the option to set up Hadoop clients on other servers in addition to the Oracle Database system. If you do, use the procedure provided in this section and install the same version of CDH or Apache Hadoop consistently.

Follow this procedure to install Oracle SQL Connector for HDFS on the Oracle Database system.

In addition to this required installation on the database system, you can also install Oracle SQL Connector for HDFS on any system configured as a compatible Hadoop client. This will give you the option to create Oracle Database external tables from that node.

To install Oracle SQL Connector for HDFS on the Oracle Database system:

1. Download the zip file to a directory on the system where Oracle Database runs.

2. Unpack the content of oraosch-<version>.zip.

   $ unzip oraosch-<version>.zip
   Archive:  oraosch-<version>.zip
    extracting: orahdfs-<version>.zip
     inflating: README.txt
   

3. Unpack orahdfs-<version>.zip into a permanent directory:

   $ unzip orahdfs-<version>.zip
   unzip orahdfs-<version>.zip
   Archive:  orahdfs-<version>.zip
      creating: orahdfs-<version>/
      creating: orahdfs-<version>/log/
      creating: orahdfs-<version>/examples/
      creating: orahdfs-<version>/examples/sql/
     inflating: orahdfs-<version>/examples/sql/mkhive_unionall_view.sql
      creating: orahdfs-<version>/doc/
     inflating: orahdfs-<version>/doc/README.txt
      creating: orahdfs-<version>/jlib/
     inflating: orahdfs-<version>/jlib/osdt_cert.jar
     inflating: orahdfs-<version>/jlib/oraclepki.jar
     inflating: orahdfs-<version>/jlib/osdt_core.jar
     inflating: orahdfs-<version>/jlib/ojdbc7.jar
     inflating: orahdfs-<version>/jlib/orahdfs.jar
     inflating: orahdfs-<version>/jlib/ora-hadoop-common.jar
      creating: orahdfs-<version>/bin/
     inflating: orahdfs-<version>/bin/hdfs_stream
     inflating: orahdfs-<version>/bin/hdfs_stream.cmd
   

   The unzipped files have the structure shown in Example 1-1.

4. Open the orahdfs-<version>/bin/hdfs_stream Bash shell script in a text editor, and make the changes indicated by the comments in the script, if necessary

   The hdfs_stream script does not inherit any environment variable settings, and so they are set in the script if Oracle SQL Connector for HDFS needs them:

   • PATH: If the hadoop script is not in /usr/bin:bin (the path initially set in hdfs_stream), then add the Hadoop bin directory, such as /usr/lib/hadoop/bin.

   • JAVA_HOME: If Hadoop does not detect Java, then set this variable to the Java installation directory. For example, /usr/bin/java.

   See the comments in the script for more information about these environment variables.

   The hdfs_stream script is the preprocessor for the Oracle Database external table created by Oracle SQL Connector for HDFS.

5. If your cluster is secured with Kerberos and the account does not already have a Kerberos ticket, then obtain one:

   $ kinit
   

   See Using Oracle SQL Connector for HDFS on a Secure Hadoop Cluster in this guide for information on acquiring and maintaining Kerberos tickets.

6. Run hdfs_stream from the Oracle SQL Connector for HDFS /bin directory. You should see this usage information:

   $ ./hdfs_stream
   Usage: hdfs_stream locationFile
   

   If you do not see the usage statement, then ensure that the operating system user that Oracle Database is running under (such as oracle) has the following permissions:

   • Read and execute permissions on the hdfs_stream script:

     $ ls -l OSCH_HOME/bin/hdfs_stream
     -rwxr-xr-x 1 oracle oinstall Nov 27 15:51 hdfs_stream
     

   • Read permission on orahdfs.jar.

     $ ls -l OSCH_HOME/jlib/orahdfs.jar
     -rwxr-xr-x 1 oracle oinstall Nov 27 15:51 orahdfs.jar
     

   If you do not see these permissions, then enter a chmod command to fix them, for example:

   $ chmod 755 OSCH_HOME/bin/hdfs_stream
   

   In the previous commands, OSCH_HOME represents the Oracle SQL Connector for HDFS home directory.

7. For an Oracle RAC system, repeat the previous steps for every Oracle instance, using identical path locations.

8. Log in to Oracle Database and create a database directory for the orahdfs-<version>/bin directory where hdfs_stream resides. For Oracle RAC systems, this directory must be accessible by all Oracle instances through identical paths.

   In this example, Oracle SQL Connector for HDFS is installed in /etc:

   SQL> CREATE OR REPLACE DIRECTORY osch_bin_path AS '/etc/orahdfs-<version>/bin';
   

9. To support access to Hive tables:

   1. Ensure that the system is configured as a Hive client.

   2. Add the Hive JAR files and the Hive conf directory to the HADOOP_CLASSPATH environment variable. To avoid JAR conflicts among the various Hadoop products, Oracle recommends that you set HADOOP_CLASSPATH in your local shell initialization script instead of making a global change to HADOOP_CLASSPATH. If there are multiple JAR file paths in HADOOP_CLASSPATH ensure that the JARs for the current product are listed first.

The following figure illustrates shows the flow of data and the components locations.

Figure 1-1 Oracle SQL Connector for HDFS Installation for HDFS and Data Pump Files

Description of "Figure 1-1 Oracle SQL Connector for HDFS Installation for HDFS and Data Pump Files"

The unzipped files have the structure shown in the following example.

Oracle Database users require these privileges in order to use Oracle SQL Connector for HDFS to create external tables:

• CREATE SESSION

• CREATE TABLE

• CREATE VIEW

• EXECUTE on the UTL_FILE PL/SQL package

• READ and EXECUTE on the OSCH_BIN_PATH directory created during the installation of Oracle SQL Connector for HDFS. Do not grant write access to anyone. Grant EXECUTE only to those who intend to use Oracle SQL Connector for HDFS.

• READ and WRITE on a database directory for storing external tables, or the CREATE ANY DIRECTORY system privilege. For Oracle RAC systems, this directory must be on a shared disk that all Oracle instances can access.

• A tablespace and quota for copying data into the Oracle database. Optional.

The following example shows the SQL commands granting these privileges to HDFSUSER.

Wherever Oracle SQL Connector for HDFS is installed (on the Oracle Database system, a Hadoop cluster node, or a separate system set up as a Hadoop client), the OS-level user account that logs in to use OSCH requires access to the shell variable HADOOP_CLASSPATH. This variable must include the OSCH path on the Hadoop cluster – path/orahdfs-<version>/jlib/*

Set the HADOOP_CLASSPATH as shown in the following example, where the OSCH path is prepended to the current HADOOP_CLASSPATH. Putting OSCH first gives it precedence over other JARs in the path.

$ export HADOOP_CLASSPATH="/etc/orahdfs-<version>/jlib/*:$HADOOP_CLASSPATH"

When users access an external table that was created using Oracle SQL Connector for HDFS, the external table behaves like a Hadoop client. On the system where the Oracle database is running, it connects as the OS user of the Oracle process (usuallyoracle). For OSCH to work, this account requires read permission on the files of all OSCH users. On a non-secure cluster these files are world-readable, but on a Kerberos-secured cluster this access requires a Kerberos ticket.

For a user to authenticate using kinit:

• A Hadoop administrator must register the operating system user (such as oracle) and password in the Key Distribution Center (KDC) for the cluster.

• A system administrator for the Oracle Database system must configure /etc/krb5.conf and add a domain definition that refers to the KDC managed by the secure cluster.

These steps enable the operating system user to authenticate with the kinit utility before submitting Oracle SQL Connector for HDFS jobs. The kinit utility typically uses a Kerberos keytab file for authentication without an interactive prompt for a password.

The system should run kinit on a regular basis, before letting the Kerberos ticket expire, to enable Oracle SQL Connector for HDFS to authenticate transparently. Use cron or a similar utility to run kinit. For example, if Kerberos tickets expire every two weeks, then set up a cron job to renew the ticket weekly.

Be sure to schedule the cron job to run when Oracle SQL Connector for HDFS is not actively being used.

Do not call kinit within the Oracle SQL Connector for HDFS preprocessor script (hdfs_stream), because it could trigger a high volume of concurrent calls to kinit and create internal Kerberos caching errors.

Oracle Loader for Hadoop requires the following software:

• A target database system running one of the following:

  • Oracle Database 12c (12.1.0.2 or 12.2.0.1)

  • Oracle Database 11g release 2 (11.2.0.4)

• Cloudera's Distribution including Apache Hadoop version 5 (CDH5), or Hortonworks Data Platform 2.4.0 or 2.5.0.

• Apache Hive 0.12.0, 0.13.0, 0.13.1, 1.1.0, or 1.2.1 if you are loading data from Hive tables.

Oracle Loader for Hadoop is packaged with the Oracle Database 12c (12.1.0.2 and 12.2.0.1) client libraries and Oracle Instant Client libraries for connecting to Oracle Database 11.2.0.4, 12.1.0.2, or 12.2.0.1.

To install Oracle Loader for Hadoop:

In a typical installation, Oracle Loader for Hadoop can connect to the Oracle Database system from the Hadoop cluster or a Hadoop client. If this connection is impossible—for example, the systems are located on distinct networks—then you can use Oracle Loader for Hadoop in offline database mode.

To support offline database mode, you must install Oracle Loader for Hadoop on two systems:

• The Hadoop cluster or a system set up as a Hadoop client.

• The Oracle Database system or a system with network access to Oracle Database, as described in the following procedure.

To support Oracle Loader for Hadoop in offline database mode:

A secure Hadoop cluster has Kerberos installed and configured to authenticate client activity. An operating system user must be authenticated before initiating an Oracle Loader for Hadoop job to run on a secure Hadoop cluster. For authentication, the user must log in to the operating system where the job will be submitted and use the standard Kerberos kinit utility.

For a user to authenticate using kinit:

• A Hadoop administrator must register the operating system user and password in the Key Distribution Center (KDC) for the cluster.

• A system administrator for the client system, where the operating system user will initiate an Oracle Loader for Hadoop job, must configure /etc/krb5.conf and add a domain definition that refers to the KDC managed by the secure cluster.

Typically, the kinit utility obtains an authentication ticket that lasts several days. Subsequent Oracle Loader for Hadoop jobs authenticate transparently using the unexpired ticket.

Oracle Big Data Appliance configures Kerberos security automatically as a configuration option.

Oracle Big Data Appliance Release 4.3 and later releases meet the requirements below. However, if you are installing Oracle XQuery for Hadoop on a third-party cluster, then you must ensure that these components are installed.

• Java 8.x or 7.x.

• Cloudera's Distribution including Apache Hadoop Version 4.1.2 and above (including CDH 5.x)

• Oracle NoSQL Database 3.x or 2.x to support reading and writing to Oracle NoSQL Database

• Oracle Loader for Hadoop 3.8.0 or greater to support writing tables in Oracle databases

Take the following steps to install Oracle XQuery for Hadoop.

To install Oracle XQuery for Hadoop:

1. Unpack the contents of oxh-<version>.zip into the installation directory:

   $ unzip oxh-<version>-cdh-<version>.zip
   Archive:  oxh-<version>-cdh-<version>.zip
      creating: oxh-<version>-cdh<version>/
      creating: oxh-<version>-cdh<version>/lib/
      creating: oxh-<version>-cdh<version>/oozie/
      creating: oxh-<version>-cdh<version>/oozie/lib/
     inflating: oxh-<version>-cdh<version>/lib/ant-launcher.jar
     inflating: oxh-<version>-cdh<version>/lib/ant.jar
        .
        .
        .
   

   You can now run Oracle XQuery for Hadoop.

2. For the fastest execution time, copy the libraries into the Hadoop distributed cache:

   1. Copy all Oracle XQuery for Hadoop and third-party libraries into an HDFS directory. To use the -exportliboozie option to copy the files, see "Oracle XQuery for Hadoop Options". Alternatively, you can copy the libraries manually using the HDFS command line interface.

      If you use Oozie, then use the same folder for all files. See "Configuring Oozie for the Oracle XQuery for Hadoop Action"

   2. Set the oracle.hadoop.xquery.lib.share property or use the -sharelib option on the command line to identify the directory for the Hadoop distributed cache.

3. To support data loads into Oracle Database, install Oracle Loader for Hadoop:

   1. Unpack the content of oraloader-<version>.x86_64.zip into a directory on your Hadoop cluster or on a system configured as a Hadoop client. This archive contains an archive and a README file.

   2. Unzip the archive into a directory on your Hadoop cluster:

      unzip oraloader-<version>-h2.x86_64.zip
      

      A directory named oraloader-<version>-h2 is created with the following subdirectories:

      doc
      jlib
      lib
      examples
      

   3. Create an environment variable named OLH_HOME and set it to the installation directory. Do not set HADOOP_CLASSPATH.

4. To support data loads into Oracle NoSQL Database, install it, and then set an environment variable named KVHOMEto the Oracle NoSQL Database installation directory.

   Note:

   Do not add NoSQL Database jar files to a HADOOP_CLASSPATH.

5. To support indexing by Apache Solr:

   1. Ensure that Solr is installed and configured in your Hadoop cluster. Solr is included in Cloudera Search, which is installed automatically on Oracle Big Data Appliance.

   2. Create a collection in your Solr installation into which you will load documents. To create a collection, use the solrctl utility.

      See Also:

      For the solrctl utility, Cloudera Search User Guide at

      http://www.cloudera.com/content/cloudera-content/cloudera-docs/Search/latest/Cloudera-Search-User-Guide/csug_solrctl_ref.html

   3. Configure Oracle XQuery for Hadoop to use your Solr installation by setting the OXH_SOLR_MR_HOME environment variable to the local directory containing search-mr-<version>.jar and search-mr-<version>-job.jar. For example:

      $ export OXH_SOLR_MR_HOME="/usr/lib/solr/contrib/mr"
      

      Note:

      Configure Oracle XQuery for Hadoop and set the OXH_SOLR_MR_HOME environment variable to the local directory before using Apache Tika adapter as well.

If Oracle XQuery for Hadoop fails to find its own or third-party libraries when running queries, first ensure that the environment variables were set correctly during Oracle XQuery for Hadoop installation.

If they are set correctly, then you may need to edit lib/oxh-lib.xml. This file identifies the location of Oracle XQuery for Hadoop system JAR files and other libraries, such as Avro, Oracle Loader for Hadoop, and Oracle NoSQL Database.

If necessary, you can reference environment variables in this file as ${env.variable}, such as ${env.OLH_HOME}. You can also reference Hadoop properties as ${property}, such as ${mapred.output.dir}.

You can use Apache Oozie workflows to run your queries, as described in "Running Queries from Apache Oozie". The software is already installed and configured on Oracle Big Data Appliance.

For other Hadoop clusters, you must first configure Oozie to use the Oracle XQuery for Hadoop action. These are the general steps to install the Oracle XQuery for Hadoop action:

The specific steps depend on your Oozie installation, such as whether Oozie is already installed and which version you are using.

Oracle Big Data Appliance supports Oracle R Advanced Analytics for Hadoop without any additional software installation or configuration. However, to use Oracle R Advanced Analytics for Hadoop on a third-party Hadoop cluster, you must create the necessary environment.

Your Hadoop cluster must have libpng-devel installed on every node. If you are using a cluster running on commodity hardware, then you can follow the same basic procedures. However, you cannot use the dcli utility to replicate the commands across all nodes. See Oracle Big Data Appliance Owner's Guide for the syntax of the dcli utility.

To install libpng-devel:

1. Log in as root to any node in your Hadoop cluster.

2. Check whether libpng-devel is already installed:

   # dcli rpm -qi libpng-devel
   bda1node01: package libpng-devel is not installed
   bda1node02: package libpng-devel is not installed
        .
        .
        .
   

   If the package is already installed on all servers, then you can skip this procedure.

3. If you need a proxy server to go outside a firewall, then set the HTTP_PROXY environment variable. This example uses dcli, which is available only on Oracle Big Data Appliance:

   # dcli export HTTP_PROXY="http://proxy.example.com"
   

4. Change to the yum directory:

   # cd /etc/yum.repos.d
   

5. Download and configure the appropriate configuration file for your version of Linux:

   For Enterprise Linux 5 (EL5):

   1. Download the yum configuration file:

      # wget http://public-yum.oracle.com/public-yum-el5.repo
      

   2. Open public-yum-el5.repo in a text editor and make these changes:

      Under el5_latest, set enabled=1

      Under el5_addons, set enabled=1

   3. Save your changes and exit.

   4. Copy the file to the other Oracle Big Data Appliance servers:

      # dcli -d /etc/yum.repos.d -f public-yum-el5.repo
      

   For Oracle Linux 6 (OL6):

   1. Download the yum configuration file:

      # wget http://public-yum.oracle.com/public-yum-ol6.repo
      

   2. Open public-yum-ol6.repo in a text editor and make these changes:

      Under ol6_latest, set enabled=1

      Under ol6_addons, set enabled=1

   3. Save your changes and exit.

   4. Copy the file to the other Oracle Big Data Appliance servers:

      # dcli -d /etc/yum.repos.d -f public-yum-ol6.repo
      

6. Install the package on all servers:

   # dcli yum -y install libpng-devel
   bda1node01: Loaded plugins: rhnplugin, security
   bda1node01: Repository 'bda' is missing name in configuration, using id
   bda1node01: This system is not registered with ULN.
   bda1node01: ULN support will be disabled.
   bda1node01: http://bda1node01-master.abcd.com/bda/repodata/repomd.xml:
   bda1node01: [Errno 14] HTTP Error 502: notresolvable
   bda1node01: Trying other mirror.
        .
        .
        .
   bda1node01: Running Transaction
   bda1node01: Installing     : libpng-devel                    1/2
   bda1node01: Installing     : libpng-devel                    2/2
    
   bda1node01: Installed:
   bda1node01: libpng-devel.i386 2:1.2.10-17.el5_8  ibpng-devel.x86_64 2:1.2.10-17.el5_8
    
   bda1node01: Complete!
   bda1node02: Loaded plugins: rhnplugin, security
        .
        .
        .
   

7. Verify that the installation was successful on all servers:

   # dcli rpm -qi libpng-devel
   bda1node01: Name        : libpng-devel Relocations: (not relocatable)
   bda1node01: Version     : 1.2.10       Vendor: Oracle America
   bda1node01: Release     : 17.el5_8      Build Date: Wed 25 Apr 2012 06:51:15 AM PDT
   bda1node01: Install Date: Tue 05 Feb 2013 11:41:14 AM PST  Build Host: ca-build56.abcd.com
   bda1node01: Group       : Development/Libraries  Source RPM: libpng-1.2.10-17.el5_8.src.rpm
   bda1node01: Size        : 482483                 License: zlib
   bda1node01: Signature   : DSA/SHA1, Wed 25 Apr 2012 06:51:41 AM PDT, Key ID 66ced3de1e5e0159
   bda1node01: URL         : http://www.libpng.org/pub/png/
   bda1node01: Summary     : Development tools for programs to manipulate PNG image format files.
   bda1node01: Description :
   bda1node01: The libpng-devel package contains the header files and static
   bda1node01: libraries necessary for developing programs using the PNG (Portable
   bda1node01: Network Graphics) library.
        .
        .
        .