1. Integrations Guide
  2. Integration with Oracle Big Data SQL
  3. Appendix
  4. Configuring Oracle Big Data SQL For Querying Oracle NoSQL Database

Configuring Oracle Big Data SQL For Querying Oracle NoSQL Database

In order to use Oracle Big Data SQL to query data in an Oracle NoSQL Database table, the Hive integration classes and other third party supporting classes provided by Oracle NoSQL Database must be made available to the Java VMs launched by Oracle Big Data SQL. This is accomplished by setting the value of the java.classpath.oracle system property to include each of the following JAR files provided with the Enterprise Edition of Oracle NoSQL Database:

  • kvclient.jar
  • kvstore-ee.jar
  • jackson-core.jar
  • jackson-databind.jar
  • jackson-annotations.jar

In an Oracle Big Data SQL system, the value of the java.classpath.oracle system property is set in the configuration file named, bigdata.properties; which is located in a directory of the form, 

/opt/oracle/product/18c/dbhome_1/bigdatasql/databases/ORCLCDB/bigdata_config

The subdirectory ORCLCDB is the instance name of the Oracle 18c Database deployed to the Oracle Big Data SQL system on which the examples presented here were developed. You may need to adjust some of the path components for your particular environment.

Upon examining the contents of the bigdata.properties file, you should see an entry that looks like, 

java.classpath.oracle=\
    /opt/oracle/product/18c/dbhome_1/bigdatasql/jlib/*:\
    /opt/oracle/product/18c/dbhome_1/jlib/orai18n.jar

Although you can explicitly add the necessary Oracle NoSQL Database libraries to the java.classpath.oracle system property by modifying the above entry in the bigdata.properties configuration file, the recommended way is to copy those libraries in the directory, /opt/oracle/product/18c/dbhome_1/bigdatasql/jlib ; that is, 

cd /opt/oracle/product/18c/dbhome_1/bigdatasql/jlib

cp /opt/oracle/kv-ee/lib/kvclient.jar kvclient.jar
cp /opt/oracle/kv-ee/lib/kvstore-ee.jar kvstore-ee.jar
cp /opt/oracle/kv-ee/lib/jackson-core.jar jackson-core.jar
cp /opt/oracle/kv-ee/lib/jackson-databind.jar jackson-databind.jar
cp /opt/oracle/kv-ee/lib/jackson-annotations.jar jackson-annotations.jar

To integrate Oracle NoSQL Database with the Big Data SQL query mechanism, it is important to copy the libraries shipped with Oracle NoSQL Database rather than linking to those libraries in the system's bigdatasql/jlib directory. Copying the libraries shown above will prevent possible ClassLoader conflict errors that can be caused by older versions of third party libraries included in the system's classpath.

Note that copying the libraries in the manner shown above is required for executing Big Data SQL queries against data in an Oracle NoSQL Database. But if you will also be executing Hive queries from one of the Big Data SQL system's database nodes, then in addition to copying the Oracle NoSQL Database libraries into /opt/oracle/product/18c/dbhome_1/bigdatasql/jlib, you must also copy those same libraries into the following directories on the database node from which the Hive queries will be executed: 

    /opt/oracle/bigdatasql/bdcell-12.1/jlib-bds
and
    /opt/oracle/bigdatasql/bdcell-12.2/jlib-bds