Using APIs to create a Singleton Table
The table is the basic structure to hold user data. You use a SQL
command (CREATE TABLE statement) or TableRequest
API commands to create a new table.
You can also use Oracle NoSQL Cloud Infrastructure(OCI) console or OCI command line interface(OCI-cli) to create a table in NDCS.
- The table definition must include at least one field definition and exactly one primary key definition. For more information on primary key definition, see Create Table.
- The field definition specifies the name of the column, its data type, whether the column is nullable or not, an optional default value, whether or not the column is an IDENTITY column and an optional comment. All fields (other than the PRIMARY KEY) are nullable by default.
- The syntax for the primary key specification (key_definition) specifies the primary key columns of the table as an ordered list of field names.
- The Time-To-Live (TTL) value is used in computing the expiration time of a row. Expired rows are not included in query results and are eventually removed from the table automatically by the Oracle NoSQL Database. If you specify a TTL value while creating the table, it applies as the default TTL for every row inserted into this table.
Using SQL commands
You can use CREATE TABLE command in SQL to create NoSQL tables.
The following section highlights different options that can be used while creating a table using the CREATE TABLE DDL statement.
CREATE TABLE BaggageInfo (
ticketNo LONG,
fullName STRING,
gender STRING,
contactPhone STRING,
confNo STRING,
bagInfo JSON,
PRIMARY KEY (ticketNo)
)In the schema above, you use the CREATE TABLE statement to define a BaggageInfo table. The passenger's ticket number, ticketNo is the primary key of the table. The fullName, gender, contactPhone, and confNo (reservation number) fields store the passenger's information, which is part of fixed schema. The bagInfo column is a schema-less JSON array, which represents the tracking information of a passenger's checked-in baggage.
For more details on airline baggage tracking application, see Airline baggage tracking application.
CREATE TABLE stream_acct(
acct_id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(9),
acct_data JSON,
PRIMARY KEY(acct_id)
)USING TTL 5 DAYSIn the schema above, you use the CREATE TABLE statement to create a stream_acct table. The subscriber's account ID, acct_id field is the primary key in this table. The fields profile_name, account_expiry store the viewership details, which is a part of fixed schema. The acct_data column is a schema-less JSON field, which stores the details of the shows viewed by a subscriber.
For more details on streaming media service application, see Streaming Media Service - Persistent user profile store
You also specify a TTL value, after which the rows automatically expire and are not available anymore. The TTL value must be in either HOURS or DAYS. In this schema, the rows of the table expire after 5 days.
CREATE TABLE IF NOT EXISTS stream_acct(
acct_id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(9),
acct_data RECORD (
firstName STRING,
lastName STRING,
country STRING,
shows JSON
),
PRIMARY KEY(acct_id)
)acct_data field as a RECORD data type.
A record is an ordered collection of one or more key-item pairs. The keys in a record must be strings and the associated items can be of different data types. The fields in a record are a part of the fixed-schema and you will not be able to add or remove them. In the example above, the fields firstName, lastName, country, and shows are the keys for the acct_data record. Defining a record is helpful when you want to store data as part of a bigger data set. You can insert/update/fetch the whole subset in a record using the field step expressions.
CREATE TABLE IF NOT EXISTS stream_acct(
acct_id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(9),
acct_data RECORD (
firstName STRING,
lastName STRING,
country STRING,
shows RECORD (
showName STRING,
showId INTEGER,
type JSON,
numSeasons INTEGER,
seriesInfo ARRAY(JSON)
)
),
PRIMARY KEY(acct_id)
)The shows field is a nested RECORD type used to track the details of the viewed shows.
Example 4: Create a streaming media service table as a hierarchical table structure.
stream_acct table as a parent table and acct_data table as a child table:CREATE TABLE IF NOT EXISTS stream_acct(
acct_id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(9),
PRIMARY KEY(acct_id)) CREATE TABLE IF NOT EXISTS stream_acct.acct_data(
profile_id INTEGER,
user_name STRING,
firstName STRING,
lastName STRING,
country STRING,
shows JSON,
PRIMARY KEY(profile_id))With the parent-child table definition above, the streaming media service can support multiple user profiles under a single subscription.
You define the acct_data table as a child table with a primary key profile_id to identify a user's profile. In addition to defining a primary key for the table, the acct_data table implicitly inherits the acct_id primary key column of its parent stream_acct table.
You can define multiple child tables under the same stream_acct parent table. You can further define child tables for the acct_data table. All the tables in the hierarchy have the same shard key column, which is specified in the create table statement of the highest parent table in the hierarchy. In this example, the primary key acct_id of the parent table is also the shard key for the stream_acct and acct_data tables.
Related Topics
Using TableRequest API
You can use TableRequest API to create NoSQL tables.
The TableRequest class is used to create tables.
Execution of operations specified by this request is asynchronous. These are
potentially long-running operations. TableResult is returned
from TableRequest operations and it encapsulates the state of
the table. See Oracle NoSQL Java SDK API Reference for more details on
the TableRequest class and its methods.
private static void createTab(NoSQLHandle handle) throws Exception {
String createTableDDL =
"CREATE TABLE IF NOT EXISTS " + tableName +
"(acct_Id INTEGER," +
"profile_name STRING," +
"account_expiry TIMESTAMP(1) ," +
"acct_data JSON, " +
"PRIMARY KEY(acct_Id))";
TableLimits limits = new TableLimits(20, 20, 1);
TableRequest treq = new TableRequest()
.setStatement(createTableDDL)
.setTableLimits(limits);
TableResult tres = handle.tableRequest(treq);
/* The request is async,
* so wait for the table to become active.
*/
tres.waitForCompletion(handle, 60000,1000);
System.out.println("Created Table: " + tableName);
}Note:
Table limits are applicable for Oracle NoSQL Database Cloud Service only. If limits are set for an on-premises NoSQL Database they are silently ignored.Creating a child table: You use the same TableRequest
class and methods to execute DDL statement to create a child table.
- You need to specify the full name of the table (name_parent_table.name_child_table)
- Table limits need not be explicitly set as a child table inherits the limits of a parent table.
Download the full code TableJoins.java from the examples to understand how to create a parent-child table here.
The borneo.TableRequest class is used to create a
table. All calls to borneo.NoSQLHandle.table_request() are
asynchronous so it is necessary to check the result and call
borneo.TableResult.wait_for_completion() to wait for the
operation to complete. See Oracle NoSQL Python SDK API Reference
for more details on table_request and its methods.
def create_table(handle):
statement = '''create table if not exists
stream_acct (acct_Id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(1),
acct_data JSON,
primary key(acct_Id))'''
request = TableRequest().set_statement(statement)
.set_table_limits(TableLimits(20, 10, 1))
table_result = handle.do_table_request(request, 40000, 3000)
table_result.wait_for_completion(handle, 40000, 3000)
if (table_result.get_state() == State.ACTIVE):
print('Created table: stream_acct')
else:
raise NameError('Table stream_acct is in an unexpected state ' +
str(table_result.get_state()))Note:
Table limits are applicable for Oracle NoSQL Database Cloud Service only. If limits are set for an on-premises NoSQL Database they are silently ignored.Creating a child table: You use the same TableRequest
class and methods to execute DDL statement to create a child table.
- You need to specify the full name of the table (name_parent_table.name_child_table).
- Table limits need not be explicitly set as a child table inherits the limits of a parent table.
Download the full code TableJoins.py from the examples here.
The TableRequest class is used to create a table.
Execution of operations specified by TableRequest is
asynchronous. These are potentially long-running operations. This request is
used as the input of a Client.DoTableRequest() operation, which
returns a TableResult that can be used to poll until the table
reaches the desired state. See Oracle NoSQL Go SDK API Reference for
more details on the various methods of the TableRequest
class.
func createTable(client *nosqldb.Client, err error, tableName string)(){
// Creates a table
stmt := fmt.Sprintf("CREATE TABLE IF NOT EXISTS %s ("+
"acct_Id INTEGER," +
"profile_name STRING," +
"account_expiry TIMESTAMP(1) ," +
"acct_data JSON, " +
"PRIMARY KEY(acct_Id))",tableName)
tableReq := &nosqldb.TableRequest{
Statement: stmt,
TableLimits: &nosqldb.TableLimits{
ReadUnits: 20,
WriteUnits: 20,
StorageGB: 1,
},
}
tableRes, err := client.DoTableRequest(tableReq)
if err != nil {
fmt.Printf("cannot initiate CREATE TABLE request: %v\n", err)
return
}
// The create table request is asynchronous,
// wait for table creation to complete.
_, err = tableRes.WaitForCompletion(client, 60*time.Second, time.Second)
if err != nil {
fmt.Printf("Error finishing CREATE TABLE request: %v\n", err)
return
}
fmt.Println("Created table: ", tableName)
return
}Note:
Table limits are applicable for Oracle NoSQL Database Cloud Service only. If limits are set for an on-premises NoSQL Database they are silently ignored.Creating a child table: You use the same TableRequest
class and methods to execute DDL statement to create a child table.
- You need to specify the full name of the table (name_parent_table.name_child_table).
- Table limits need not be explicitly set as a child table inherits the limits of a parent table.
Download the full code TableJoins.go from the examples here.
You can create a table using the tableDDL method.
This method is asynchronous and it returns a Promise of
TableResult. The TableResult is a plain
JavaScript object that contains the status of the DDL operation such as its
TableState, name, schema, and its TableLimits. For method details, see NoSQLClient class.
The tableDDL method takes
the TableDDLOpt object as the second
optional argument. When you are creating a table, you must specify its
TableLimits as part of the opt argument. The
TableLimits specify the maximum throughput and storage
capacity for the table as the amount of read units, write units, and Gigabytes
of storage.
Download the full JavaScript code CreateTable.js from the examples here and the full TypeScript code CreateTable.ts from the examples here.
import {NoSQLClient, TableState } from 'oracle-nosqldb';
const client = new NoSQLClient('config.json');
const TABLE_NAME = 'stream_acct;
async function createTable(handle) {
const createDDL = `CREATE TABLE IF NOT EXISTS
${TABLE_NAME} (acct_Id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(1),
acct_data JSON,
primary key(acct_Id))`;
/* For Provisioned Capacity specify read units, write units, and storage limit as shown below*/
/* For On-demand Capacity - set only the storage limit and specify the mode as shown here.
* { storageGB: 25, mode: CapacityMode.ON_DEMAND };
*/
let res = await handle.tableDDL(createDDL, {
complete: true,
tableLimits: {
readUnits: 20,
writeUnits: 20,
storageGB: 1
}
});
console.log('Created table: ' + TABLE_NAME);
}const createDDL = `CREATE TABLE IF NOT EXISTS
${TABLE_NAME} (acct_Id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(1),
acct_data JSON,
primary key(acct_Id))`;
let res = await client.tableDDL(createDDL, {complete: true,});
console.log('Created table: ' + TABLE_NAME);Creating a child table: You use the same
TableRequest class and methods to execute DDL statement to
create a child table.
- You need to specify the full name of the table (name_parent_table.name_child_table)
- Table limits need not be explicitly set as a child table inherits the limits of a parent table.
Download the full JavaScript code TableJoins.js from the examples here and the full TypeScript code TableJoins.ts from the examples here.
To create a table use either of the methods
ExecuteTableDDLAsync or
ExecuteTableDDLWithCompletionAsync. Both these methods
return Task<TableResult>. TableResult
instance contains status of DDL operation such as TableState and table schema.
See Oracle NoSQL Dotnet SDK API Reference for more details on
these methods.
private static async Task createTable(NoSQLClient client){
// Create a table
var sql =
$@"CREATE TABLE IF NOT EXISTS
{TableName}(acct_Id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(1),
acct_data JSON,
primary key(acct_Id))";
var tableResult = await client.ExecuteTableDDLAsync(sql,
new TableDDLOptions{TableLimits = new TableLimits(20, 20, 1)});
// Wait for the operation completion
await tableResult.WaitForCompletionAsync();
Console.WriteLine(" Created table: ",tableResult.TableName);
}Note:
Table limits are applicable for Oracle NoSQL Database Cloud Service only. If limits are set for an on-premises NoSQL Database they are silently ignored.Creating a child table: You use the same TableRequest
class and methods to execute DDL statement to create a child table.
- You need to specify the full name of the table (name_parent_table.name_child_table)
- Table limits need not be explicitly set as a child table inherits the limits of a parent table.
Download the full code TableJoins.cs from the examples here.