CountTableRows Support Programs

Oracle NoSQL Database provides a separate distribution in Oracle Technology Network consisting of example programs and utility classes that you can use to explore various aspects of interacting with an Oracle NoSQL Database system. With respect to exploring the integration of Oracle NoSQL Database with MapReduce, in addition to providing the CountTableRows example program presented in this document, the Oracle NoSQL Database examples also provide the LoadTableVehicle program that you can use to create and populate an example table in the store you deploy.

The sections below describe the LoadVehicleTable program; including the schema employed when creating the table, as well as how to compile and execute the program.

Schema for the vehicleTable Example

To execute the CountTableRows MapReduce job, a table named vehicleTable having the schema shown in the table below must be created in the Oracle NoSQL Database store deployed for this example. The data types specified in the schema shown below are defined by the Oracle NoSQL Database Table API (see oracle.kv.table.FieldDef.Type) .

Table 4-1 Schema for vehicleTable

Field Name	Field Type	Primary Key	Shard Key
type	FieldDef.Type.STRING	Y	Y
make	FieldDef.Type.STRING	Y	Y
model	FieldDef.Type.STRING	Y	Y
class	FieldDef.Type.STRING	Y
color	FieldDef.Type.STRING
price	FieldDef.Type.DOUBLE
count	FieldDef.Type.INTEGER
dealerid	FieldDef.Type.NUMBER
delivered	FieldDef.Type.TIMESTAMP

The example vehicleTable consists of rows representing a particular vehicle a dealer might have in stock for purchase. Each such row contains fields specifying the "type" of vehicle (for example, car, truck, SUV, etc.), the "make" of the vehicle (Ford, GM, Chrysler, etc.), the "model" (Explorer, Camaro, Lebaron, etc.), the vehicle "class" (4WheelDrive, FrontWheelDrive, etc.), the "color" and "price" of the vehicle, the number of vehicles currently in stock (the "count") having those characteristics, a number that uniquely identifies the dealership selling those vehicles (the "dealerid"), and finally, the date and time those vehicles were "delivered" to the dealership.

Although you can enter individual commands in the store's admin CLI to create a table with the above schema, the preferred approach is to employ the Table Data Definition Language (DDL) to create the desired table. One way to accomplish this is to follow the instructions presented in the next sections to compile and execute the LoadVehicleTable program, which will populate the desired table after using the DDL to create it.

Create and Populate vehicleTable with Example Data

Assuming an Oracle NoSQL Database store (secure or non-secure) has been deployed with KVHOME equal to /opt/oracle/kv-ee, the LoadVehicleTable program that is supplied as a convenience with the CountTableRows example can be executed to create and populate the table named vehicleTable. Before executing LoadVehicleTable though, that program must first be compiled. To do this, assuming you have installed the example distribution under the base directory /opt/oracle/nosql/apps/kv/examples, type the following from your client node’s OS command line:

cd /opt/oracle/nosql/apps/kv
javac -classpath \
    /opt/oracle/kv-ee/lib/kvclient.jar:examples \
    examples/hadoop/table/LoadVehicleTable.java

This should produce the file:

/opt/oracle/nosql/apps/kv/examples/hadoop/table/LoadVehicleTable.class

Run LoadVehicleTable when the Store is Non-Secure

To execute LoadVehicleTable to create and populate the table named vehicleTable with example data in a store configured for non-secure access, type the following at the command line of the client node, which must have network connectivity with a node running the admin service of the non-secure store you deployed (for example, kv-host-1 itself):

cd /opt/oracle/nosql/apps/kv
java -classpath \
    /opt/oracle/kv-ee/lib/kvstore.jar:\
    /opt/oracle/kv-ee/lib/sklogger.jar:\
    /opt/oracle/kv-ee/lib/commonutil.jar:examples \
    hadoop.table.LoadVehicleTable -store example-store \
    -host kv-host-1 -port 5000 -nops 79 [-delete]

The following parameters are required: -store, -host, -port, and -nops, whereas the -delete parameter is optional.

In the example command line above, the argument -nops 79 requests that 79 rows be written to the vehicleTable. If more or less than that number of rows is desired, then the value of the -nops parameter should be changed.

If LoadVehicleTable is executed a second time and the optional -delete parameter is specified, then all rows added by any previous executions of LoadVehicleTable are deleted from the table prior to adding the requested new rows. Otherwise, all pre-existing rows are left in place, and the number of rows in the table will be increased by the requested -nops number of new rows.

Note:

Because of the way LoadVehicleTable generates records, it is possible that a given record has already been added to the table, either during a previous call to LoadVehicleTable, or during the current call. As a result, it is not uncommon for the number of unique rows added to be less than the number requested. Because of this, when processing has completed, LoadVehicleTable will display the number of unique rows that are actually added to the table, along with the total number of rows currently in the table (from previous runs).

Run LoadVehicleTable When the Store is Secure

To execute LoadVehicleTable against the secure store that you deployed and provisioned with a non-administrative user according to the steps presented in the Deploying a Secure Store appendix, an additional parameter must be added to the command line above. In this case, type the following on the command line:

scp <username>@kv-host-<n>:\
    /u01/nosql/sn1/kvroot/security/client.trust /tmp

cd /opt/oracle/nosql/apps/kv

java -classpath \
    /opt/oracle/kv-ee/lib/kvclient.jar:\
    /opt/oracle/kv-ee/lib/sklogger.jar:\
    /opt/oracle/kv-ee/lib/commonutil.jar:examples \
    hadoop.table.LoadVehicleTable -store example-store \
    -host kv-host-1 -port 5000 -nops 79 \
    -security /tmp/example-user-client-pwdfile.login \
    [-delete]

The client.trust file generated when the secure store was deployed must be installed in the /tmp directory of the client node from which LoadVehicleTable is executed. If the client node is different than any of the store nodes (kv-host-1, kv-host-2, kv-host-3), then the installation of client.trust is accomplished by performing a remote copy; using the appropriate username and the number 1 in place of the <n> token. On the other hand, if LoadVehicleTable is run from one of the nodes making up the store itself, then a local copy operation can be used for the installation.

The additional -security parameter in the command above specifies the location of the login properties file (associated with a password file in this case rather than an Oracle Wallet) for the given user or alias. All other parameters are the same as for the non-secure case.

To understand the -security parameter, recall from the Deploying a Secure Store appendix that a non-administrative user named example-user was created, and a number of credential files based on a password file (rather than an Oracle Wallet) were generated for that user and placed under the /tmp system directory. As a result, you should see the following files under the /tmp directory of the client node:

/tmp
    client.trust
    example-user-client-pwdfile.login
    example-user-server.login
    example-user.passwd

For this example, the user credential files must be co-located, where it doesn't matter which directory they are located in, as long as they all reside in the same directory accessible by the user. It is for this reason that the shared trust file (client.trust) is copied into /tmp above. Co-locating client.trust and example-user.passwd with the login file (example-user-client-pwdfile.login) allows relative paths to be used for the values of the system properties oracle.kv.ssl.trustStore and oracle.kv.auth.pwdfile.file that are specified in the login file (or oracle.kv.auth.wallet.dir if an Oracle Wallet is used to store the user password). If those files are not co-located with the login file, then absolute paths must be used for those properties.

Summary

At this point, the vehicleTable created in the Oracle NoSQL Database store you deployed whether non-secure or secure should be populated with the desired example data. And the MapReduce job initiated by CountTableRows can be run to count the number of rows in that table.