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.
BaggageInfo
table that holds baggage information of passengers in
an airline
system.CREATE TABLE BaggageInfo (
ticketNo LONG,
fullName STRING,
gender STRING,
contactPhone STRING,
confNo STRING,
bagInfo JSON,
PRIMARY KEY (ticketNo)
)
stream_acct
table that holds data from a TV streaming
application.CREATE TABLE stream_acct(
acct_id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(1),
acct_data JSON,
PRIMARY KEY(acct_id)
)
stream_acct_new
table that holds data from a TV streaming
application. The rows of the table expire in 2
days.CREATE TABLE stream_acct_new(
acct_id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(1),
acct_data JSON,
PRIMARY KEY(acct_id)) USING TTL 2 days
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.