Defining Tables

Before an Oracle NoSQL Database client can read or write to a table in the store, you must first create the tables. There are several ways to do this, but this document focuses on using Table DDL statements. You can submit these statements to the store directly using both the Admin command line interface (CLI), with the execute command), or the SQL CLI. However, the recommended approach is to submit DDL statements to the store programmatically. This section describes both direct and programmatic methods.

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 want to create a table called myTable with four columns: item, description, count, and percentage. To create your table, use the following statement from the SQL CLI:

sql-> CREATE TABLE myTable (
    item STRING,
    description STRING,
    count INTEGER,
    percentage DOUBLE,
    PRIMARY KEY (item) // Every table must have a primary key
); 
Statement completed successfully

Note:

Primary keys are a concept that have not yet been introduced. See Primary and Shard Key Design for a complete explanation of what they are, and how you should use them. For now, be sure a primary key exists for every table you create, just as the previous example illustrates.

Executing DDL Statements Programmatically

To add the table definition to the store programmatically use the KVStore.execute() or KVStore.executeSync() methods. (The latter method executes the statement synchronously.)

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 From the Admin CLI

You can execute DDL statements using the Admin 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.

Oracle NoSQL Database tables support the following data types:

Data Type	Description
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	Implemented as a byte array with no predetermined fixed size.
Float
Integer
Json	Any valid JSON data.
Long
Number	A numeric type capable of handling any type of number or 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, you do not need to create child tables to organize subordinate data. If you have simple requirements for subordinate data, you can use record fields, instead of child tables. In general, you can use record fields instead of child tables if you want only a fixed, small number of instances of the record for each parent table row. For anything beyond trivial cases, use child tables.

Note:

There is no downside to using child tables for even trivial cases.

The assumption when using record fields is that you have a fixed, known number of records to manage (unless you organize them as arrays). For example, in a contacts database, child tables let you have an unlimited number of addresses associated for each user. By using records, rather than child tables, 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 lets you create an unlimited number of address records per field. In general, however, 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 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.

Defining Multi-Region Tables

A Multi-Region Table or MR Table is a global logical table that is stored and maintained in different regions or installations. It is a read-anywhere and write-anywhere table that lives in multiple regions.

Consider an Oracle NoSQL Database with three regions, Frankfurt, London, and Dublin. To create a table called users that stores user details for all the three regions, you must create an MR table on each KVStore in the connected graph, and specify the list of regions that the table should span.

For example, to create the users table in all the three regions, you must execute the following command from each region separately:

CREATE TABLE users (
  id INTEGER, 
  firstName STRING, 
  lastName STRING, 
  age INTEGER, 
  primary key (id)
) IN REGIONS fra, lnd, dub;

For information about MR Tables, see Life Cycle of MR Tables in the Concepts Guide.

Using CRDT datatype in a multi-region table

Overview of the MR_COUNTER data type

MR_Counter data type is a counter CRDT. CRDT stands for Conflict-free Replicated Data Type. In a multi-region setup of an Oracle NoSQL Database, a CRDT is a data type that can be replicated across servers where regions can be updated independently and it converges on a correct common state. Changes in the regions are concurrent and not synchronized with one another. In short, CRDTs provide a way for concurrent modifications to be merged across regions without user intervention. Oracle NoSQL Database currently supports the counter CRDT which is called MR_Counter. The MR_COUNTER datatype is a subtype of the INTEGER or LONG or NUMBER data type. You can also use the MR_COUNTER data type in a schema-less JSON field, which means one or more fields in a JSON document can be of MR_COUNTER data type.

Why do you need MR_Counter in a multi-region table?

In a multi-region database configuration, copies of the same data need to be stored in multiple regions. This configuration needs to deal with the fact that the data may be concurrently modified in different regions.

Take an example of a multi-region table in three different regions (where data is stored in three different Oracle NoSQL Database stores). Concurrent updates of the same data in multiple regions, without coordination between the machines hosting the regions, can result in inconsistencies between the regions, which in the general case may not be resolvable. Restoring consistency and data integrity when there are conflicts between updates may require some or all of the updates to be entirely or partially dropped. For example, in the current configuration of a multi-region table in the Oracle NoSQL Database, if the same column (a counter) of a multi-region table is updated across two regions at the same time with different values, a conflict arises.

Currently, the conflict resolution is that the latest write overwrites the value across regions. For example, Region 1 updates column1 with a value R1, and region2 updates column1 with a value R2, and if the region2 update happens after region1, the value of the column (counter) in both the regions becomes R2. This is not what is actually desired. Rather every region should update the column (a counter) at their end and also the system internally needs to determine the sum of the column across regions.

One way to handle this conflict is making serializable/linearizable transactions (one transaction is completed and changes are synchronized in all regions and only then the next transaction happens). A significant problem of having serializable transactions is performance. This is where MR_COUNTER datatype comes in handy. With MR_COUNTER datatype, we don't need serializable transactions and the conflict resolution is taken care of. That is, MR_COUNTER datatype ensures that though data modifications can happen simultaneously on different regions, the data can always be merged into a consistent state. This merge is performed automatically by MR_COUNTER datatype, without requiring any special conflict resolution code or user intervention.

Use-case for MR_COUNTER datatype

Consider a Telecom provider providing different services and packages to its customers. One such service is a "Family Plan" option where a customer and their family share the Data Usage plan. The customer is allocated a free data usage limit for a month which your the customer's entire family collectively uses. When the total usage of customer's family reaches 90 percent of the data limit, the telecom provider sends the customer an alert. Say there are four members in customer's family plan who are spread across different physical regions. The customer needs to get an alert from the telecom provider once the total consumption of their family reaches 90 percent of the free usage. The data is replicated in different regions to cater to latency, throughput, and better performance. That means there are four regions and each has a kvstore containing the details of the customer's data usage. The usage of their family members needs to be updated in different regions and at any point in time, the total usage should be monitored and an alert should be sent if the data usage reaches the limit.

An MR_COUNTER data type is ideal in such a situation to do conflict-free tracking of the data usage across different regions. In the above example, an increment counter in every data region's data store will track the data usage in that region. The consolidated data usage for all regions can be determined by the system at any point without any user intervention. That is the total data usage at any point in time can be easily determined by the system using an MR_COUNTER datatype.

Types of MR_COUNTER Datatype

Currently, Oracle NoSQL Database supports only one type of MR_COUNTER data type. which is Positive-Negative (PN) counter.

Positive-Negative (PN) Counter

A PN counter can be incremented or decremented. Therefore, these can serve as a general-purpose counter. For example, you can use these counters to count the number of users active on a social media website at any point. When the users go offline you need to decrement the counter.

To create a multi-region table with an MR_COUNTER column, See Create multi-region table with an MR_COUNTER column section in the Administrator's Guide.

Add MR_COUNTER datatype in a multi-region table

For example, to create a multi-region table myTable with a counter data type, you must execute the following command from each region.

CREATE Table myTable( name STRING,
                      count INTEGER AS MR_COUNTER,
                      PRIMARY KEY(name)) IN REGIONS DEN,LON;

Example using JSON MR_COUNTER data type:

Create a JSON MR_COUNTER data type in a multi-region table as shown below:

CREATE TABLE demoJSONMR(name STRING,
jsonWithCounter JSON(counter as INTEGER MR_COUNTER,
                     person.count as LONG MR_COUNTER),
PRIMARY KEY(name)) IN REGIONS FRA,LND;

In the statement above, you create a multi-region table with a STRING column and a column(JSON documents). You are identifying two of the fields in the JSON document as MR_COUNTER data type.. The first field is counter, which is an INTEGER MR_COUNTER data type. The second field is count within an embedded JSON document (person). The count field is of LONG MR_COUNTER data type.

Inserting JSON MR_COUNTER Values Programmatically

You can insert row ( and MR_COUNTER values) into your application table programmatically using PUT API.

Create a sample table:

CREATE TABLE exampleTable (id INTEGER,
jsonColumn JSON(counter AS INTEGER MR_COUNTER,
                person.score AS LONG MR_COUNTER),
PRIMARY KEY(id));

Insert row of data using PUT API:

KVStore store = KVStoreFactory.getStore(kvStoreConfig);
TableAPI api = store.getTableAPI();
Table table = api.getTable("exampleTable");
Row row = table.createRow();
String json = "{\"counter\": 0, \"person\": {\"score\" : 0}}";
row.put("id", 1);
row.putJson("jsonColumn", json);
api.put(row, null, null);

Note:

You need to include all defined json mr_counters when inserting a row. For example, in the above code, giving an empty JSON MR_COUNTER like INSERT INTO exampleTable VALUES (1, {}) will throw an error. The values of the JSON MR_COUNTER need not be 0 but it can be any atomic values.

Updating JSON MR_COUNTER values: You cannot update a JSON MR_COUNTER programatically using API. It can be done using UPDATE statement (DML) only.

Table Evolution

As your application is used over time, it's often necessary to update your tables to either add new fields or remove existing fields that are no longer required. Table evolution is the term used to update table definitions, adding or removing fields, or changing field properties, such as a default value. You may even add a particular kind of column, like an IDENTITY column, to increment some value automatically. Only tables that already exist in the store are candidates for table evolution.

Use the ALTER TABLE statement to perform table evolution. See Modify Table Definitions.

Note:

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.

For example, the following statements evolve the table that was created in the previous section. In this example, you would submit each statement to the store consecutively, using either the API or the CLI.

ALTER TABLE myInventory.itemDetails (ADD salePrice FLOAT)

ALTER TABLE myInventory.itemDetails (DROP inventoryCount)