Using Oracle External Tables To Access Oracle NoSQL Database Data

Before You Begin

This 20-minute tutorial walks you through the use of Oracle External Tables to read data from an Oracle NoSQL Database while accessing Oracle Database. The end result of this tutorial is that you can query (read) NoSQL Database data while accessing Oracle Database.

This document only provides an example walk-through of this feature.

Background

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:

Connection information for both the Oracle Database and Oracle NoSQL Database instances.
The name of the Oracle Database External Table.
Selection information about which records in the NoSQL Database to process.
(optionally) The name of a Formatter class to be used for converting the K/V pairs from the NoSQL record format to the External Table format.

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.

What Do You Need?

Accessible and running Oracle Database instance
sysdba privileges on the Oracle Database instance
Familiarity with Oracle Database External Table concepts
Familiarity with compiling and executing a Java program
Accessible and running Oracle NoSQL Database (either the single-process "lite" version or a multi-node cluster)
Familiarity with NoSQL Database Java API

Define An External Table

Perform the following steps to create an External Table:

Create directory objects in Oracle where the External Table location files will reside and the NoSQL Database exttab/bin directory where the nosql_stream script can be found.
NOTE: Consult with your Oracle DBA regarding the appropriate locations and permissions for these directories. It may be desireable from a security point of view to copy the nosql_stream script from the <KVHOME>/exttab/bin/ directory to some other location.
```
sqlplus / as sysdba
SQL> CREATE DIRECTORY ext_tab AS '<exttab_pathname>';
SQL> CREATE DIRECTORY nosql_bin_dir AS '<bin_pathname>';
```
exttab_pathname is the directory containing the Location File(s) for the External Table. This directory does not require a large amount of storage as the Location Files will only ever hold a small amount of configuration data. bin_pathname refers to the exttab/bin/ directory of the NoSQL Database installation.

Grant appropriate permissions to Oracle users needing access to the External Table. In this example, the user accessing the NoSQL Database data with an External Table is nosqluser.

sqlplus / as sysdba
SQL> CREATE USER nosqluser IDENTIFIED BY password;
SQL> GRANT CREATE SESSION TO nosqluser;
SQL> GRANT EXECUTE ON SYS.UTL_FILE TO nosqluser;
SQL> GRANT READ, WRITE ON DIRECTORY ext_tab TO nosqluser;
SQL> GRANT READ, EXECUTE ON DIRECTORY nosql_bin_dir TO nosqluser;
SQL> GRANT CREATE TABLE TO nosqluser;

Define the External Table:

You should do this as the user who will be accessing the External Table, not as sysdba. In this example, create the table as nosqluser.

SQL> CONNECT nosqluser/password
SQL> CREATE TABLE nosql_data (email VARCHAR2(30),
  2                           name VARCHAR2(30),
  3                           gender CHAR(1),
  4                           address VARCHAR2(40),
  5                           phone VARCHAR2(20))
  6      ORGANIZATION EXTERNAL
  7          (type oracle_loader
  8           default directory ext_tab
  9           access parameters (records delimited by newline
 10           preprocessor nosql_bin_dir:'nosql_stream'
 11           fields terminated by '|' missing field values are null)
 12      LOCATION ('nosql.dat'))
 13      REJECT LIMIT UNLIMITED
 14      PARALLEL;

Table created.

Create Some Sample Data In NoSQL Database

Compile the LoadCookbookData program in the <KVHOME>/examples/externaltables directory:
```
cd <KVHOME>>
javac -classpath lib/kvstore.jar:examples examples/externaltables/*.java
```
If you are using the Table API, use the LoadCookbookTable program in the <KVHOME>/examples/externaltables/table directory. See the comments in that example for information on how to define the required table in the store. To compile the LoadCookbookTable program in the <KVHOME>/examples/externaltables/table directory:
```
cd <KVHOME>
javac -classpath lib/kvstore.jar:examples examples/externaltables/table/LoadCookBookTable.java
```
Run the LoadCookbookData program to load some data into the KV Store:
```
java -classpath lib/kvclient.jar:examples externaltables.LoadCookbookData \
     -store <storename> -host <hostname> -port <port> -nops 10
```
or
```
java -classpath lib/kvclient.jar:examples externaltables.table.LoadCookbookTable \
     -store <storename> -host <hostname> -port <port> -nops 10
```
-nops specifies the number of records to create. A small number is all that is necessary in order to demonstrate access from External Tables. Optionally, you can pass -delete if you want to delete all previously created data in your Oracle NoSQL Database Store.

Edit The Configuration File

Make a copy of the configuration file in <KVHOME>/examples/externaltables/config.xml or <KVHOME>/examples/externaltables/config.table.xml and edit your site-specific values for the following properties based on your Oracle Database and Oracle NoSQL Database installations:
- oracle.kv.exttab.connection.url,
- oracle.kv.exttab.connection.user,
- oracle.kv.exttab.connection.wallet_location (optional),
- oracle.kv.kvstore, and
- oracle.kv.hosts
In addition, if you are using the Table API, you also need to specify the name of the Oracle NoSQL Database table from which you want to load data into Oracle External Table. The site-specific value for the table name is specified in the oracle.kv.table property.

Publish The Configuration

Run the oracle.kv.exttab.Publish utility to publish the configuration to the External Table Location files.
```
cd <KVHOME>
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

Edit the <KVHOME>/exttab/bin/nosql_stream script to have the correct values for PATH, KVHOME, and CLASSPATH when the script is run in the execution environment of the Oracle Database server. For this example, CLASSPATH should include the KVHOME/examples directory (in addition to the kvstore.jar). PATH should include the location to the nosql_stream script and Java. For example:
```
PATH = <KVHOME>/exttab/bin:<JAVAHOME>/bin
```

Test the nosql_stream script by running it in a shell:

$ <KVHOME>/exttab/bin/nosql_stream <exttab_pathname>/nosql.dat

where <exttab_pathname> is the path of the LOCATION files specified earlier in the CREATE DIRECTORY command. You should see output similar to the following:

user6@example.com|Ms. Number-006|F|006 Example St, Example Town, AZ|666.666.6666
user1@example.com|Mr. Number-001|M|001 Example St, Example Town, AZ|111.111.1111
user9@example.com|Mr. Number-009|M|009 Example St, Example Town, AZ|999.999.9999
user0@example.com|Ms. Number-000|F|000 Example St, Example Town, AZ|000.000.0000
user7@example.com|Mr. Number-007|M|007 Example St, Example Town, AZ|777.777.7777
user8@example.com|Ms. Number-008|F|008 Example St, Example Town, AZ|888.888.8888
user5@example.com|Mr. Number-005|M|005 Example St, Example Town, AZ|555.555.5555
user2@example.com|Ms. Number-002|F|002 Example St, Example Town, AZ|222.222.2222
user4@example.com|Ms. Number-004|F|004 Example St, Example Town, AZ|444.444.4444
user3@example.com|Mr. Number-003|M|003 Example St, Example Town, AZ|333.333.3333

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                                NAME G
--------------------------- - ------------------------------------
ADDRESS                              PHONE
--------------------------- - ------------------------------------
user1@example.com                    Mr. Number-001 M
001 Example St, Example Town, AZ     111.111.1111

user9@example.com                    Mr. Number-009 M
009 Example St, Example Town, AZ     999.999.9999

user0@example.com                    Ms. Number-000 F
000 Example St, Example Town, AZ     000.000.0000
...