Library Version 12.1.3.0

Chapter 4. Introducing Oracle NoSQL Database Tables and Indexes
Prev |  | Next

## Chapter 4. Introducing Oracle NoSQL Database Tables and Indexes

The Oracle NoSQL Database tables API is the recommended method of coding an Oracle NoSQL Database client application. It allows 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 tables and the key/value APIs.

Table creation, deletion and evolution is performed using the command line interface (CLI). You use the CLI to define tables, including the data types supported by each column in the table. You also use the CLI to define indexes. Once you have created your table(s) using the CLI, you then use the tables API to read and write the data stored in those tables.

## Defining Tables

Before an Oracle NoSQL Database client can read or write to a table in the store, the table must be created using the command line interface (CLI). While it is possible to define tables interactively using the CLI, doing so represents challenges when developing table definitions for use in an enterprise environment. In order to avoid typos as code moves from the development environment, to test, to production, it is best to perform table definitions using a script.

A CLI script is simply a series of CLI commands contained in a single text file, one command to a line. To run the script, you start the CLI and then use the `load` command.

For example, suppose you wanted to use a table named `myTable` with four columns per row: `item`, `count1`, `count2`, and `percentage`. To create this table, you can log into the CLI and use the `table create`, `add-field`, `primary-key`, and `plan add-table` commands interactively. Or you can just collect all these commands into a plain text file like this:

    ## Enter into table creation mode
    ## This enters into a submode for the CLI, which offers
    ## commands specifically used to define tables.
    table create -name myTable
    ## Now add the fields
    add-field -type STRING -name item
    add-field -type STRING -name description
    add-field -type INTEGER -name count
    add-field -type DOUBLE -name percentage
    ## A primary key must be defined for every table
    ## Here, we will define field 'item' as the primary key.
    primary-key -field item
    ## Exit table creation mode, returning to the
    ## main CLI commands.
    exit
    ## Add the table to the store. Use the -wait flag to
    ## force the script to wait for the plan to complete
    ## before doing anything else.
    plan add-table -name myTable -wait

### 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 run the script, start the CLI and then use the `load` command. Suppose you placed the above script into a file named `createTable.txt`:

    > java -Xmx256m -Xms256m \
    -jar KVHOME/lib/kvstore.jar runadmin -host <hostName> \
    -port <port> -store <storeName>
    kv-> load -file createTable.txt
    Table myTable built
    Executed plan 8, waiting for completion...
    Plan 8 ended successfully

    kv->

### Note

The above example assumes you are connecting to a nonsecure store. If you are using a secure store, then you will have to authenticate when you start the CLI. You do this using the `-admin-security` command line option.

By performing all your table and index manipulation in this way, you can ensure a consistent store environment all the way through your development/test/product deployment cycle.

### 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 simple data types are:

- Boolean

- Double

- Float

- Integer

- Long

- Java byte array

- Java String

Complex data types are non-atomic in nature:

- Array

  An array of values, all of the same type.

- Enum

  An enumeration, represented as an array of strings.

- Fixed Binary

  A fixed-sized binary type 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.

- Map

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

- Records

  See the following section.

### 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, and you do not want to index the fields in the 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 allows 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).

You create a record field using `add-record-field`, which puts you into a special submode within the CLI that you must exit or quit just as you do when creating a table:

    ## Enter into table creation mode
    table create -name myContactsTable
    ## Now add the fields
    add-field -type STRING -name uid
    add-field -type STRING -name surname
    add-field -type STRING -name familiarName
    add-field -type STRING -name homePhone
    add-field -type STRING -name workPhone

    ## Create a record field. This puts us into a new submode.
    add-record-field -name homeAddress
    add-field -type STRING -name street
    add-field -type STRING -name city
    add-field -type STRING -name state
    add-field -type INTEGER -name zip -min 00000 -max 99999
    ### Exit record field creation mode
    exit

    ## Add a second record field
    add-record-field -name workAddress
    add-field -type STRING -name street
    add-field -type STRING -name city
    add-field -type STRING -name state
    add-field -type INTEGER -name zip -min 00000 -max 99999
    ### Exit record field creation mode
    exit

    ## A primary key must be defined for every table
    primary-key -field uid
    ## Exit table creation mode
    exit
    ## Add the table to the store. Use the -wait flag to
    ## force the script to wait for the plan to complete
    ## before doing anything else.
    plan add-table -name myContactsTable -wait

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.

    ## Enter into table creation mode
    table create -name myContactsTable
    ## Now add the fields
    add-field -type STRING -name uid
    add-field -type STRING -name surname
    add-field -type STRING -name familiarName
    add-field -type STRING -name homePhone
    add-field -type STRING -name workPhone

    ## Create an array field. This puts us into a new submode.
    add-array-field -name addresses

    ## Create a record field for the array.
    ## This puts us into a new submode.
    add-record-field -name address
    add-field -type ENUM -name addressType -enum-values home,work,other
    add-field -type STRING -name street
    add-field -type STRING -name city
    add-field -type STRING -name state
    add-field -type INTEGER -name zip -min 00000 -max 99999
    ### Exit record field creation mode
    exit

    ### Exit array field creation mode
    exit

    ## A primary key must be defined for every table
    primary-key -field uid
    ## Exit table creation mode
    exit
    ## Add the table to the store. Use the -wait flag to
    ## force the script to wait for the plan to complete
    ## before doing anything else.
    plan add-table -name myContactsTable -wait

### Note

When you use embedded record fields, or arrays of records, you cannot index fields within the records. Indexing can only be performed on top-level fields. So if you wanted to create an index on, say, the `zip` field, you should manage this information in the form of a child table.

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

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, we previously showed how to create a trivial table called `myTable`:

    ## Enter into table creation mode
    table create -name myTable
    ## Now add the fields
    add-field -type STRING -name itemCategory
    add-field -type STRING -name description
    ## A primary key must be defined for every table
    ## Here, we will define field 'itemCategory' as the primary key.
    primary-key -field itemCategory
    ## Exit table creation mode
    exit
    ## Add the table to the store. Use the -wait flag to
    ## force the script to wait for the plan to complete
    ## before doing anything else.
    plan add-table -name myTable -wait

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

    ## Enter into table creation mode
    ## This 'table create' will fail if 'plan add-table -name myTable'
    ## is not run first.
    table create -name myTable.myChildTable
    ## Now add the fields
    add-field -type STRING -name itemSKU
    add-field -type STRING -name itemDescription
    add-field -type FLOAT -name price
    add-field -type INTEGER -name inventoryCount
    ## Define the primary key
    primary-key -field itemSKU
    ## Exit table creation mode
    exit
    ## Add the table to the store.
    plan add-table -name myTable.myChildTable -wait

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 the 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 `table evolve` and `plan evolve-table` commands.

Note that you cannot remove a field if it is a primary key field. You also cannot add primary key field during table evolution.

Tables can only be evolved if they have already been added to the store using either the `plan add-table` or `plan evolve-table` commands.

For example, the following script evolves the table that was created in the previous section. It adds a field and deletes another one. Again, we use a script to evolve our table so as to ensure consistency across engineering, test and production.

    ## Enter into table evolution mode
    table evolve -name myTable
    ## Add a field
    add-field -type STRING -name itemCategory
    ## Remove a field.
    remove-field -name percentage
    ## Exit table creation mode
    exit
    plan evolve-table -name myTable -wait

### Table Manipulation Commands

This section briefly describes the CLI commands that you use to manipulate tables. This section is for advertisement purposes only.

An exhaustive list of all CLI commands, and their syntax, can be found in the KVStore Command Reference. You can also see the command syntax for all CLI commands using the CLI `help` command.

- `plan add-table`

  Adds a table to the store that has been created but not yet added. Use the `table create` command to create the table.

- `plan evolve-table`

  Adds a table to the store that has been evolved using the `table evolve` command.

- plan remove-table

  Removes an existing table from the store.

- show tables

  Shows all tables and child tables that have been added to the store.

- table clear

  Clears a table of all schema. This command works only on tables that have been created using the `table create` command, but not yet added to the store using the `plan add-table` command.

- table create

  Enters into table creation mode in which you can design the schema for the table. This mode offers a series of sub-commands:

  - add-array-field

    Adds a field to the table that accepts an array.

  - add-field

    Adds a field that accepts data of a simple type. You must identify the data's type. For example, INTEGER, LONG, DOUBLE, etc.

  - add-map-field

    Adds a field that accepts a map.

  - add-schema

    Build the table using specified Avro schema.

  - cancel

    Cancels the table creation. This returns you to the main admin prompt, abandoning any work that you might have performed when creating the table.

  - exit

    Saves the work you performed when creating the table, and returns you to the main admin prompt. After exiting table creation mode, you must issue the `plan add-table` command to add the table to your store.

  - primary-key

    The identified field is the table's primary key. Every table must have at least one field identified as its primary key. See Primary and Shard Key Design for more information.

  - remove-field

    Removes the identified field from the table.

  - set-description

    Sets a plain-text description of the table. Use this to document what the table is used for.

  - shard-key

    Sets a shard key for the table. See Primary and Shard Key Design for more information.

  - show

    Shows the current fields defined for the table.

- table evolve

  Allows you to evolve (modify) a table that has been added to the store. This command puts you into a special mode that allows you to add and remove fields in much the same way as you can when using `table create`. Upon completing this command, you must add your evolved table to the store using the `plan evolve-table` command.

- table list

  Lists all the table that have been built but not yet created or evolved using `plan add-table` or `plan evolve-table.`.

Prev |  | Next
Using the Authentication APIs | Home | Creating Indexes