Previous Next JavaScript must be enabled to correctly display this content

## `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.