Chapter 3. Introducing Oracle NoSQL Database Tables and Indexes

Table of Contents

Defining Tables
Executing DDL Statements using the CLI
Supported Table Data Types
Record Fields
Defining Tables using Existing Avro Schema
Defining Child Tables
Table Evolution
Creating Indexes

Using the Table API (in one of the supported languages) is the recommended method of coding an Oracle NoSQL Database client application. They allow you to manipulate data using a tables metaphor, in which data is organized in multiple columns of data. An unlimited number of subtables are supported by this API. You can also create indexes to improve query speeds against your tables.

Note

You should avoid any possibility of colliding keys if your store is accessed by a mix of clients that use both the Table and the Key/Value APIs.

Note

Throughout this manual, examples call TableAPI.getTable(). Be aware that this is a relatively expensive call because it requires a trip to the store to fulfill. For best results, call it sparingly in your code.

Defining Tables

Before an Oracle NoSQL Database client can read or write to a table in the store, the table must be created. There are several ways to do this, but this manual focuses on using Table DDL Statements. These statements can be submitted to the store using the command line interface (CLI), but the recommended approach is to submit them to the store programmatically. Both methods are described in this section.

The DDL language that you use to define tables is described in Table Data Definition Language Overview This section provides a brief overview of how to use that language.

As an introductory example, suppose you wanted to use a table named myTable with four columns per row: item, description, count, and percentage. To create this table, you would use the following statement:

CREATE TABLE myTable (
  item STRING,
  description STRING,
  count INTEGER,
  percentage DOUBLE,
  PRIMARY KEY (item) // Every table must have a primary key
) 

Note

Primary keys are a concept that have not yet been introduced in this manual. See Primary and Shard Key Design for a complete explanation on what they are and how you should use them.

To add the table definition to the store, you can add it programmatically using the KVStore.execute() or KVStore.executeSync() methods. (The latter method executes the statement synchronously.) In order to use these methods, you must establish a connection to the store. This is described in The KVStore Handle.

For example:

package kvstore.basicExample;

import oracle.kv.FaultException;
import oracle.kv.StatementResult;
import oracle.kv.KVStore;
import oracle.kv.table.TableAPI;
...

// store handle creation and open omitted

...

StatementResult result = null;
String statement = null;

public void createTable() {
    StatementResult result = null;
    String statement = null;

    try {

        /*
         * Add a table to the database.
         * Execute this statement asynchronously.
         */

        statement =
            "CREATE TABLE myTable (" +
            "item STRING," +
            "description STRING," +
            "count INTEGER," +
            "percentage DOUBLE," +
            "PRIMARY KEY (item))"; // Required"
        result = store.executeSync(statement);

        displayResult(result, statement);

    } catch (IllegalArgumentException e) {
        System.out.println("Invalid statement:\n" + e.getMessage());
    } catch (FaultException e) {
        System.out.println
            ("Statement couldn't be executed, please retry: " + e);
    }
}

private void displayResult(StatementResult result, String statement) {
    System.out.println("===========================");
    if (result.isSuccessful()) {
        System.out.println("Statement was successful:\n\t" + 
            statement);
        System.out.println("Results:\n\t" + result.getInfo());
    } else if (result.isCancelled()) {
        System.out.println("Statement was cancelled:\n\t" + 
            statement);
    } else {
        /*
         * statement was not successful: may be in error, or may still
         * be in progress.
         */
        if (result.isDone()) {
            System.out.println("Statement failed:\n\t" + statement);
            System.out.println("Problem:\n\t" + 
                result.getErrorMessage());
        } else {
            System.out.println("Statement in progress:\n\t" + 
                statement);
            System.out.println("Status:\n\t" + result.getInfo());
        }
    }
} 

Executing DDL Statements using the CLI

You can execute DDL statements using the CLI's execute command. This executes DDL statements synchronously. For example:

kv-> execute "CREATE TABLE myTable (
> item STRING, 
> description STRING, 
> count INTEGER, 
> percentage DOUBLE, 
> PRIMARY KEY (item))"
Statement completed successfully
kv-> 

Supported Table Data Types

You specify schema for each column in an Oracle NoSQL Database table. This schema can be a primitive data type, or complex data types that are handled as objects.

Supported data types for Oracle NoSQL Database are:

  • Array

    An array of values, all of the same type.

  • Binary

    Implemented as a byte array with no predetermined fixed size.

  • Boolean

  • Double

  • Enum

    An enumeration, represented as an array of strings.

  • Fixed Binary

    A fixed-sized binary type (byte array) used to handle binary data where each record is the same size. It uses less storage than an unrestricted binary field, which requires the length to be stored with the data.

  • Float

  • Integer

  • Json

    Any valid JSON data.

  • Long

  • Number

    A numeric type capable of handling any type of number of any value or precision.

  • Map

    An unordered map type where all entries are constrained by a single type.

  • Records

    See the following section.

  • String

  • Timestamp

    An absolute timestamp encapsulating a date and, optionally, a time value.

Record Fields

As described in Defining Child Tables, you can create child tables to hold subordinate information, such as addresses in a contacts database, or vendor contact information for an inventory system. When you do this, you can create an unlimited number of rows in the child table, and you can index the fields in the child table's rows.

However, child tables are not required in order to organize subordinate data. If you have very simple requirements for subordinate data, you can use record fields instead of a child tables. In general, you can use record fields instead of child tables if you only want a fixed, small number of instances of the record for each parent table row. For anything beyond trivial cases, you should use child tables. (Note that there is no downside to using child tables even for trivial cases.)

The assumption when using record fields is that you have a fixed known number of records that you will want to manage (unless you organize them as arrays). For example, for a contacts database, child tables allow you to have an unlimited number of addresses associated for each user. But by using records, you can associate a fixed number of addresses by creating a record field for each supported address (home and work, for example).

For example:

CREATE TABLE myContactsTable (
    uid STRING,
    surname STRING,
    familiarName STRING,
    homePhone STRING,
    workPhone STRING,
    homeAddress RECORD (street STRING, city STRING, state STRING,
                zip INTEGER),
    workAddress RECORD (street STRING, city STRING, state STRING,
                zip INTEGER),
    PRIMARY KEY(uid)) 

Alternatively, you can create an array of record fields. This allows you to create an unlimited number of address records per field. Note, however, that in general you should use child tables in this case.

CREATE TABLE myContactsTable (
    uid STRING,
    surname STRING,
    familiarName STRING,
    homePhone STRING,
    workPhone STRING,
    addresses ARRAY(RECORD (street STRING, city STRING, state STRING,
                zip INTEGER))),
    PRIMARY KEY(uid)) 

Defining Tables using Existing Avro Schema

If you are a user of the key/value API, then you probably have been using Avro schema to describe your record values. You can create a table based on Avro schema which currently exists in your store, and in so doing overlay the existing store records. You can then operate on that data using both the tables API and the key/value API so long as you do not evolve (change) the table definitions. This is intended as a migration aid from the key/value API to the tables API.

For example, suppose you have the following Avro schema defined in your store:

kv-> show schema -name com.example.myItemRecord
{
  "type" : "record",
  "name" : "myItemRecord",
  "namespace" : "com.example",
  "fields" : [ {
    "name" : "itemType",
    "type" : "string",
    "default" : ""
  }, {
    "name" : "itemCategory",
    "type" : "string",
    "default" : ""
  }, {
    "name" : "itemClass",
    "type" : "string",
    "default" : ""
  }, {
    "name" : "itemColor",
    "type" : "string",
    "default" : ""
  }, {
    "name" : "itemSize",
    "type" : "string",
    "default" : ""
  }, {
    "name" : "price",
    "type" : "float",
    "default" : 0.0
  }, {
    "name" : "inventoryCount",
    "type" : "int",
    "default" : 0
  } ]
} 

Then you can define a table using this schema. Note that the table's name must correspond directly to the first component of the key/value applications's keys.

kv-> table create -name myItemTable
myItemTable-> add-schema -name com.example.myItemRecord
myItemTable-> show
{
  "type" : "table",
  "name" : "myItemTable",
  "id" : "myItemTable",
  "r2compat" : true,
  "description" : null,
  "shardKey" : [ ],
  "primaryKey" : [ ],
  "fields" : [ {
    "name" : "itemType",
    "type" : "STRING"
  }, {
    "name" : "itemCategory",
    "type" : "STRING"
  }, {
    "name" : "itemClass",
    "type" : "STRING"
  }, {
    "name" : "itemColor",
    "type" : "STRING"
  }, {
    "name" : "itemSize",
    "type" : "STRING"
  }, {
    "name" : "price",
    "type" : "FLOAT",
    "default" : 0.0
  }, {
    "name" : "inventoryCount",
    "type" : "INTEGER"
  } ]
}
myItemTable-> 

At this point, you need to define your primary keys and, optionally, your shard keys in the same way you would any table. You also need to add the table to the store in the same way as always.

Note that in this case, the primary keys must be of type STRING and must also correspond to the key components used by the key/value application.

myItemTable->primary-key -field itemType -field itemCategory
myItemTable->exit
kv->plan add-table -name myItemTable -wait 

Tables Compatible with Key-Only Entries (-r2-compat)

If you are a user of the key/value API, you might have created store entries that have only keys. These entries have no schema. In fact, they have no data of any kind. In this case, you can create tables that are compatible with these legacy entries using the table create command's -r2-compat flag.

For example, suppose you have key-only entries of the format:

/User/<id>

where <id> is a unique string ID. You can create a table to overlay this key space by doing this:

kv-> table create -name User -r2-compat
User-> add-field -name id -type String
User-> primary-key -field id
User-> exit
Table User built.
kv-> plan add-table -name User -wait 

If you did not use the -r2-compat flag, the underlying keys generated for the table's entries would start with something other than User.

Note that when you create tables using existing Avro schema, the -r2-compat flag is automatically used.

Also note that as is the case when generating tables using Avro schema, the overlay only works so long as you do not evolve the tables.

Defining Child Tables

Oracle NoSQL Database tables can be organized in a parent/child hierarchy. There is no limit to how many child tables you can create, nor is there a limit to how deep the child table nesting can go.

By default, child tables are not retrieved when you retrieve a parent table, nor is the parent retrieved when you retrieve a child table.

To create a child table, you name the table using the format: <parentTableName>.<childTableName>. For example, suppose you had the trivial table called myInventory:

CREATE TABLE myInventory (
  itemCategory STRING,
  description STRING,
  PRIMARY KEY (itemCategory) 
) 

We can create a child table called itemDetails in the following way:

CREATE TABLE myInventory.itemDetails (
    itemSKU STRING,
    itemDescription STRING,
    price FLOAT,
    inventoryCount INTEGER,
    PRIMARY KEY (itemSKU)
) 

Note that when you do this, the child table inherits the parent table's primary key. In this trivial case, the child table's primary key is actually two fields: itemCategory and itemSKU. This has several ramifications, one of which is that the parent's primary key fields are retrieved when you retrieve the child table. See Retrieve a Child Table for more information.

Table Evolution

In the event that you must update your application at some point after it goes into production, there is a good chance that your tables will also have to be updated to either use new fields or remove existing fields that are no longer in use. You do this through the use of the ALTER TABLE statement. See Modify Table Definitions for details on this statement.

Note that you cannot remove a field if it is a primary key field, or if it participates in an index. You also cannot add primary key fields during table evolution.

Tables can only be evolved if they have already been added to the store.

For example, the following statements evolve the table that was created in the previous section. Note that these would be submitted to the store, one after another, using either the API or the CLI.

ALTER TABLE myInventory.itemDetails (ADD salePrice FLOAT)
ALTER TABLE myInventory.itemDetails (DROP inventoryCount)