This guide walks you through the use 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) NoSQL Database data while accessing 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:
sysdba
privileges on that instance.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.
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.
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;
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. SQL>For more information on External Tables and the parameters, see External Tables Access Parameters.
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
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.
<KVHOME>/examples/externaltables/config.xml
or
<KVHOME>/examples/externaltables/config.table.xml
and edit your site-specific values for the
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.
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> -publishIf 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.
nosql_stream
Script<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
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
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 ... SQL>