Using Oracle External Tables To Access Oracle NoSQL Database Data

This guide walks you through the usage of Oracle External Tables to read data from an Oracle NoSQL Database into an Oracle Database. The end result is that you can query (read) the NoSQL Database data while accessing the Oracle Database.

This document only provides an example walk-through of this feature. For a more complete description of using External Tables to read an Oracle NoSQL Database, see the Package oracle.kv.exttab Description.


This guide assumes:


The Oracle Database External Table feature allows a user to create a table that reads data from one or more sources which are external to the Oracle Database. These external sources are usually one or more flat text files, but in this case you will be using an Oracle NoSQL Database as the external data source. The NoSQL database is read with the aid of a preprocessor utility.

To use this feature, you define an External Table with one or more Location Files and the nosql_stream utility specified as the External Table PREPROCESSOR. After the External Table is declared to Oracle, but prior to invoking a SELECT command, the user runs a NoSQL Database "publish" utility, passing it several parameters:

The publish utility then writes this information into the External Table Location File(s). Note that you only have to run the publish utility once. After that, you only need to run the publish utility again if you want to change the way the NoSQL Database is accessed (for example, using a different key prefix, or if you change the port or the rep nodes where you access the database).

Once the publish utility has been run, you can query the External Table in the same way as you would query any other Oracle database table.

Define An External Table

Perform the following steps to create an External Table:

Create Some Sample Data In NoSQL Database

Edit The Configuration File

Publish The Configuration

Run the oracle.kv.exttab.Publish utility to publish the configuration to the External Table Location files.
java -classpath lib/kvstore.jar:$ORACLE_HOME/jdbc/lib/ojdbc6.jar \
     oracle.kv.exttab.Publish \
     -config <pathname-to-edited-copy-of-config.xml> -publish
If you are using the Oracle wallet as an external password store, then you should also include $ORACLE_HOME/jlib/oraclepki.jar in your classpath. If the process executes successfully, there will be no output. If you have read access to the Location file(s), you can verify the Publish operation by looking inside one to see if the configuration XML is written there. You will see that two additional properties have been added to the XML: oracle.kv.exttab.totalExternalTableFiles and oracle.kv.exttab.externalTableFileNumber. Optionally, you can specify the -verbose argument to the Publish utility to see more verbose (i.e. debugging) output.

Test The nosql_stream Script

Use The External Table To Read Data From NoSQL Database

Using sqlplus (as nosqluser or whatever user you created the external table with), perform a SELECT on the nosql_data External Table.
SQL> select * from nosql_data;

EMAIL                          G ADDRESS
------------------------------ - ----------------------------------------
--------------------              F #6 Example St, Example Town, AZ
666.666.6666              M #1 Example St, Example Town, AZ
111.111.1111              M #9 Example St, Example Town, AZ