Written by Deepak Vohra

Oracle NoSQL database stores data as key-value pairs, each key-value pair being a record. Oracle NoSQL database provides Avro schemas to define the data schema for a record’s value serialized/deserialized with Oracle NoSQL database. The Avro schemas are stored in the Oracle NoSQL database.

An Avro schema is in JavaScript Object Notation (JSON) format and describes the fields allowed in a value including their data types. An Avro schema is applied to the value of a record using Avro bindings during serialization/deserialization. Avro makes use of Jackson APIs for parsing JSON. A schema describes a record stored in Oracle NoSQL database and has the following format.

{
 "type": "record",
 "namespace": "schema.namespace",
 "name": "schema.name",
 "fields": [
 { "name": "field1", "type": "string" },
 { "name": "field2", "type": "int" }
 ]
}

The record has four fields: type, namespace, name, and fields. The type describes the JSON field type and is “record” for Avro schemas. The namespace differentiates one schema group from another. The name is the schema name. The fields are the actual schema definition and describe the fields in a record value including their types. A field type is a primitive type (null, int, long, string, boolean, float, double, bytes). A schema definition may consist of a single field or multiple fields.  

Defining Schemas

In this article we shall store record values in Oracle NoSQL database using both a single field example and a multi-field example.

For the single-field example we shall use the following schema (singlefieldschema.avsc), which has only a single field of type string.

{
 "type" : "record",
 "name" : "catalog",
 "namespace" : "example.schema",
 "fields" : [{"name" : "catalog",
 "type" : "string",
 "default" : "NONE"}]
}

For the multi-field example we shall use the following schema (multifieldschema.avsc), which has five fields; journal, publisher, edition, title and author, each of type string. The default attribute is recommended to be specified and is not the default value used if a value is not provided but is used in schema evolution. We have used the same name for the two schemas and therefore we must provide a different namespace for each schema to differentiate the two schemas. When referring to a schema the qualified name of the schema is used, which is the name prefixed with the namespace.

{
 "type" : "record",
 "name" : "catalog",
 "namespace" : "catalog.example.schema",
 "fields" : [{"name" : "journal",
 "type" : "string",
 "default" : "NONE"},
 {"name" : "publisher",
 "type" : "string",
 "default" : "NONE"},
 {"name" : "edition",
 "type" : "string",
 "default" : "NONE"},
 {"name" : "title",
 "type" : "string",
 "default" : "NONE"},
 {"name" : "author",
 "type" : "string",
 "default" : "NONE"}]
}

Setting the Environment

We need to download and install the following software:

  1. Oracle NoSQL Database, Server, kv-ce-2.1.54.zip from http://www.oracle.com/technetwork/database/database-technologies/nosqldb/downloads/index.html?ssSourceSiteId=ocomen. Extract kv-ce-2.1.54.zip to a directory, extracts to kv-ce-2.1.54 directory by default.
     
  2. Oracle NoSQL Database, Client Driver Java kv-client-2.1.54.zip from http://www.oracle.com/technetwork/database/database- technologies/nosqldb/downloads/index.html?ssSourceSiteId=ocomen. Extract to kv-client-2.1.54 directory.

We also need to download and install Eclipse IDE for Java EE Developers from http://www.eclipse.org/downloads/. We have used Windows environment.

Creating a KVlite Store

KVlite is a lightweight, single-process version of Oracle NoSQL database. KVlite is not performance tuned but provides access to a simple Key/Value data store. The KVlite NoSQL database may be created with the following command.

java -jar C:/OracleNoSQLDatabase/kv-ce-2.1.54/kvstore.jar kvlite

The output from the command indicates that the kvlite store has been created.

 

Adding Schemas to Oracle NoSQL Database

To connect to KVlite kvstore created in the preceding section run the following command to start the Command Line Interface (CLI) for Oracle NoSQL database.

java -Xmx256m -Xms256m -jar C:/OracleNoSQLDatabase/kv-ce-2.1.54/kvstore.jar runadmin -port 5000 -host localhost

The kv> prompt is the Oracle NoSQL database command shell.

Add the singlefieldschema.avsc schema to the Oracle NoSQL database using the following command.

ddl add-schema -file singlefieldschema.avsc

Add the multifieldschema.avsc schema using the following command.

ddl add-schema -file multifieldschema.avsc

The messages generated in the CLI indicate that the two schemas get added to the Oracle NoSQL database. The schema name stored is the qualified schema name suffixed with the schema count.

  

Creating a Java Project in Eclipse

The Java Client Driver for Oracle NoSQL Database provides a Java API to access Oracle NoSQL database. We shall use a Java application to access   the Oracle NoSQL database and store documents in it.   Start Eclipse IDE. Create a Java project for the Oracle NoSQL database. To create a Java project select File>New>Other. In the New Gallery select Java>Java Project and click on Next. In New Java Project specify a Project Name (OracleNoSQL) and click on Next.

Select the default Java Settings for Source. Select the checkbox “Allow output folders for source folders” with Default output folder as OracleNoSQL/bin and click on Finish.

  

A Java Project gets created in Eclipse IDE.

Next, add Java classes for   Oracle NoSQL database. Create two Java classes OracleNoSQLSingleField.java and OracleNoSQLMultiField.java. To create a Java class select File>New>Other. In New gallery select Java>Class and click on Next. In New Java Class wizard select OracleNoSQL/src as the Source folder, specify a Package (oraclenosql) and a class Name. Select the main method stub to create and click Finish.

  

Next, add the Oracle NoSQL database Java client JAR files from the kv-client-2.1.54 directory (avro.jar, jackson-core-asl.jar, jackson-mapper-asl.jar, and kvclient.jar) to the Java Build path.

To add a JAR file to Eclipse IDE Java Build Path right-click on the project node in Package Explorer and select Properties and in Properties select Java Build Path. Add JAR files with the Add External JARs button. The JARs in the Java Build Path are shown in Properties.

  

The directory structure of the OracleNoSQL project and the Java classes in the project are shown in Package Explorer.

Single Field Schema

The single-field schema singlefieldschema.avsc was added to Oracle NoSQL database using the CLI. The binding classes for serializing/deserializing values as Avro binary data are defined in the oracle.kv.avro package. First, create a parser for JSON format schema.

final Schema.Parser parser = new Schema.Parser();

Parse the Avro schema with a single field schema definition using the parse(File) method.

parser.parse(new File("C:/OracleNoSQLDatabase/singlefieldschema.avsc"));

Obtain a Map of defined types in the schema using the getTypes() method and subsequently obtain the value associated with the key example.schema.catalog using the get(Object) method.

final Schema catalogSchema = parser.getTypes().get("example.schema.catalog");

To connect to the Oracle NoSQL store we need to create an instance of KVStore using the KVStoreFactory.getStore(KVStoreConfig) method for which we need to specify the store name, host name and port name.

String storeName = "kvstore";
String hostName = "localhost";
int hostPort = 5000;
KVStore store = KVStoreFactory.getStore(new KVStoreConfig(storeName, hostName + ":" + hostPort));

Get the catalog for the Avro schema, represented with the AvroCatalog interface, and the bindings for the key-value (KV) store using the getAvroCatalog() method.

final AvroCatalog catalog = store.getAvroCatalog();

GenericAvroBinding is the generic interface used for serializing/deserializing values in Avro data format in which values are represented by the GenericRecord interface. Create an instance of GenericAvroBinding by invoking the getGenericBinding(Schema schema) method in the AvroCatalog object.

final GenericAvroBinding binding = catalog.getGenericBinding(catalogSchema);

Next, create an instance of GenericRecord using its default implementation GenericData.Record and the GenericData.Record(Schema schema)  constructor.

final GenericRecord catalogRecord = new GenericData.Record(catalogSchema);

Create a String for a JSON object and add the JSON object String to the GenericRecord instance using the put(String key, Object v) method.

String json = "{'catalog1': {'journal': 'Oracle Magazine', 'publisher': 'Oracle Publishing', 'edition': 'November December 2013', 'title':'Engineering as a Service','author':'David A. Kelly'}}";
catalogRecord.put("catalog", json);

Next, store the key-value record in the Oracle NoSQL database using the put(Key key, Value value) method in KVStore. A key in a key-value store is represented by the oracle.kv.Key class and a value is represented by the oracle.kv.Value class. The Key instance is created using the static method Key.createKey(String) and the Value instance is obtained using the GenericAvroBinding method toValue(GenericRecord object).

final String keyString = "catalog1";
store.put(Key.createKey(keyString), binding.toValue(catalogRecord));

To find the key-value stored in Oracle NoSQL database invoke the get(Key key) method in KVStore to obtain a ValueVersion instance, which holds a value and a version associated with a key. Subsequently, obtain and output the value using the getValue()  method in ValueVersion.

final ValueVersion valueVersion = store.get(Key.createKey(keyString)); System.out.println(keyString + " "+ new String(valueVersion.getValue().getValue()) + "\n ");

The OracleNoSQLSingleField class is listed below.

package oraclenosql;

import java.io.File;
import java.io.IOException;

import oracle.kv.KVStore;
import oracle.kv.KVStoreConfig;
import oracle.kv.KVStoreFactory;
import oracle.kv.Key;
import oracle.kv.ValueVersion;
import oracle.kv.avro.AvroCatalog;
import oracle.kv.avro.GenericAvroBinding;

import org.apache.avro.Schema;
import org.apache.avro.generic.GenericData;
import org.apache.avro.generic.GenericRecord;

public class OracleNoSQLSingleField {

    private static KVStore store;

    public static void main(String[] args) {

        store();
        retrieve();
    }

    private static void store() {
        final Schema.Parser parser = new Schema.Parser();

        try {
            parser.parse(new File(
                    "C:/OracleNoSQLDatabase/singlefieldschema.avsc"));
        } catch (IOException e) {

            e.printStackTrace();
        }
        final Schema catalogSchema = parser.getTypes().get(
                "example.schema.catalog");
        String storeName = "kvstore";
        String hostName = "localhost";
        int hostPort = 5000;
        store = KVStoreFactory.getStore(new KVStoreConfig(storeName, hostName
                + ":" + hostPort));
        final AvroCatalog avroCatalog = store.getAvroCatalog();
        final GenericAvroBinding binding = avroCatalog
                .getGenericBinding(catalogSchema);
        final GenericRecord catalogRecord = new GenericData.Record(
                catalogSchema);

        String json = "{'catalog1': {'journal': 'Oracle Magazine', 'publisher': 'Oracle Publishing', 'edition': 'November December 2013', 'title':'Engineering as a Service','author':'David A. Kelly'}}";
        catalogRecord.put("catalog", json);

        final String keyString = "catalog";
        store.put(Key.createKey(keyString), binding.toValue(catalogRecord));

    }

    private static void retrieve() {

        final String keyString = "catalog";
        final ValueVersion valueVersion = store.get(Key.createKey(keyString));

        System.out.println(keyString + " "
                + new String(valueVersion.getValue().getValue()) + "\n ");

    }
}

Next, run the OracleNoSQLSingleField class in Eclipse IDE. Right-click on OracleNoSQLSingleField.java and select Run As>Java Application.

  

The JSON String gets stored in Oracle NoSQL database as a single key/value pair. Subsequently, the key-value pair gets output in the Eclipse Console. The Value object stored in Oracle NoSQL database gets output as a JSON String.

Multi Field Schema

The procedure for storing a document in Oracle NoSQL database is similar for a multi-field schema. The multi-field schema multifieldschema.avsc was added to Oracle NoSQL database using the CLI.   First, create a parser for JSON format schema.

final Schema.Parser parser = new Schema.Parser();

Parse the Avro schema with a multi field schema definition.

parser.parse(new File("C:/OracleNoSQLDatabase/multifieldschema.avsc"));

Obtain a Map of defined types and subsequently obtain the value associated with the key catalog.example.schema.catalog.

final Schema catalogSchema = parser.getTypes().get("catalog.example.schema.catalog");

Create an instance of KVStore for which we need the store name, host name and port name.

String storeName = "kvstore";
String hostName = "localhost";
int hostPort = 5000;
KVStore store = KVStoreFactory.getStore(new KVStoreConfig(storeName, hostName + ":" + hostPort));

Get the catalog for the Avro schema, and the bindings for the key-value (KV) store.

final AvroCatalog catalog = store.getAvroCatalog();

JsonAvroBinding is the interface used for serializing/deserializing values in Avro data format in which values are represented by the JsonRecord interface. The JsonAvroBinding is used instead of GenericAvroBinding for interoperability with the JSON objects. Create an instance of JsonAvroBinding by invoking the AvroCatalog.getJsonBinding(org.apache.avro.Schema) method in the AvroCatalog object.

JsonAvroBinding jsonBinding = catalog.getJsonBinding(catalogSchema);

Next, create an instance of JsonRecord  instead of GenericRecord using JsonRecord(JsonNode jsonNode, Schema schema) constructor. Create an ObjectNode instance, which implements the JsonNode interface by first getting an instance of JsonNodeFactory using the static method instance() and subsequently invoking the objectNode() method.

ObjectNode objectNode = JsonNodeFactory.instance.objectNode();
JsonRecord jsonRecord = new JsonRecord(objectNode, catalogSchema);

Set the JSON field values in the ObjectNode instance using the put(java.lang.String fieldName, java.lang.String v) method.

objectNode.put("journal", "Oracle Magazine");
objectNode.put("publisher", "Oracle Publishing");
objectNode.put("edition", "November December 2013");
objectNode.put("title", "Engineering as a Service");
objectNode.put("author", "David A. Kelly");

Next, store the key-value record in the Oracle NoSQL database using the put(Key key, Value value) method in KVStore. A key in a key-value store is represented by the oracle.kv.Key class and a value is represented by the oracle.kv.Value class. The Key instance is created using the static method Key.createKey(String) and the Value instance is obtained using the JsonAvroBinding method toValue(JsonRecord object).

final String keyString = "catalog";
store.put(Key.createKey(keyString), jsonBinding.toValue(jsonRecord));

The JSON document gets stored in Oracle NoSQL database. To find the key-value stored in Oracle NoSQL database invoke the get(Key key) method in KVStore to obtain a ValueVersion instance, which holds a value and a version associated with a key. Subsequently, obtain the JsonRecord using the JsonAvroBinding method toObject(Value value) method. Create a org.codehaus.jackson.JsonNode object, which is the base class for JSON nodes, from the JsonRecord instance and output the JsonNode as String.

final ValueVersion valueVersion = store.get(Key.createKey(keyString));

        JsonRecord jsonRecord2 = jsonBinding.toObject(valueVersion.getValue());
        JsonNode jsonNode = jsonRecord2.getJsonNode();
        System.out.println(keyString + " " + jsonNode + "\n ");

Output the field values in the JSONObject by obtaining the field values using the get(java.lang.String fieldname) method.

JsonNode journal = jsonNode.get("journal");
        JsonNode publisher = jsonNode.get("publisher");
        JsonNode edition = jsonNode.get("edition");
        JsonNode title = jsonNode.get("title");
        JsonNode author = jsonNode.get("author");

        System.out.println("Journal" + " " + journal + "\n ");
        System.out.println("Publisher" + " " + publisher + "\n ");
        System.out.println("Edition" + " " + edition + "\n ");
        System.out.println("Title" + " " + title + "\n ");
        System.out.println("Author" + " " + author + "\n ");

The OracleNoSQLMultiField class is listed below.

package oraclenosql;

import java.io.File;
import java.io.IOException;
import oracle.kv.KVStoreConfig;
import oracle.kv.KVStoreFactory;
import oracle.kv.Key;
import oracle.kv.ValueVersion;
import oracle.kv.avro.AvroCatalog;
import oracle.kv.avro.JsonAvroBinding;
import oracle.kv.avro.JsonRecord;

import org.apache.avro.Schema;
import org.codehaus.jackson.JsonNode;
import org.codehaus.jackson.node.JsonNodeFactory;
import org.codehaus.jackson.node.ObjectNode;

import oracle.kv.KVStore;

public class OracleNoSQLMultiField {

    private static KVStore store;
    private static JsonAvroBinding jsonBinding;

    public static void main(String[] args) {
        store();
        retrieve();
    }

    private static void store() {
        final Schema.Parser parser = new Schema.Parser();

        try {
            parser.parse(new File(
                    "C:/OracleNoSQLDatabase/multifieldschema.avsc"));
        } catch (IOException e) {

            e.printStackTrace();
        }
        final Schema catalogSchema = parser.getTypes().get(
                "catalog.example.schema.catalog");

        String storeName = "kvstore";
        String hostName = "localhost";
        int hostPort = 5000;
        store = KVStoreFactory.getStore(new KVStoreConfig(storeName, hostName
                + ":" + hostPort));
        final AvroCatalog avroCatalog = store.getAvroCatalog();
        jsonBinding = avroCatalog.getJsonBinding(catalogSchema);
        ObjectNode objectNode = JsonNodeFactory.instance.objectNode();
        JsonRecord jsonRecord = new JsonRecord(objectNode, catalogSchema);

        objectNode.put("journal", "Oracle Magazine");
        objectNode.put("publisher", "Oracle Publishing");
        objectNode.put("edition", "November December 2013");
        objectNode.put("title", "Engineering as a Service");
        objectNode.put("author", "David A. Kelly");

        final String keyString = "catalog1";
        store.put(Key.createKey(keyString), jsonBinding.toValue(jsonRecord));

    }

    private static void retrieve() {

        final String keyString = "catalog1";
        final ValueVersion valueVersion = store.get(Key.createKey(keyString));

        JsonRecord jsonRecord2 = jsonBinding.toObject(valueVersion.getValue());
        JsonNode jsonNode = jsonRecord2.getJsonNode();
        System.out.println(keyString + " " + jsonNode + "\n ");
        JsonNode journal = jsonNode.get("journal");
        JsonNode publisher = jsonNode.get("publisher");
        JsonNode edition = jsonNode.get("edition");
        JsonNode title = jsonNode.get("title");
        JsonNode author = jsonNode.get("author");

        System.out.println("Journal" + " " + journal + "\n ");
        System.out.println("Publisher" + " " + publisher + "\n ");
        System.out.println("Edition" + " " + edition + "\n ");
        System.out.println("Title" + " " + title + "\n ");
        System.out.println("Author" + " " + author + "\n ");
        store.close();

    }

}

Next, run the OracleNoSQLMultiField class in Eclipse IDE. Right-click on OracleNoSQLMultiField.java and select Run As>Java Application. The JSON record gets stored in Oracle NoSQL database. Subsequently, the key-value pair gets output in the Eclipse Console; the JsonNode object stored in Oracle NoSQL database gets output as a String. The individual field values in the JsonNode also are output.

In this article we stored a document in Oracle NoSQL database using a single field schema and a multi field schema.