Table of Contents
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.
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.
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
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->
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.
Table, index and field names are case-preserving, but
case-insensitive. So you can, for example, create a field
named MY_NAME, and later reference it as
my_name without error. However, whenever
the field name is displayed, it will display as
MY_NAME.
Table and index names are limited to 32 characters. Field names can be 64 characters. All table, index and field names are restricted to alphanumeric characters, plus underscore ("_"). All names must start with a letter.
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:
Binary
Implemented as a Java byte array with no predetermined fixed size.
Boolean
Double
Float
Integer
Long
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 (Java 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.
Map
An unordered map type where all entries are constrained by a single type.
Records
See the following section.
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
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
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.
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.
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
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..