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.

Guidelines for creating a table:
  • 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.
Create a table :

Using SQL commands

You can use CREATE TABLE command in SQL to create NoSQL tables.

Example 1: The following CREATE TABLE statement defines a 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)
)
Example 2: The following CREATE TABLE statement defines a 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)
)
Example 3: The following CREATE TABLE statement defines a 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.

Download the full code CreateTable.java from the examples here.
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.

While creating 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.

Download the full code CreateTable.py from the examples here.
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.

While creating 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.

Download the full code CreateTable.go from the examples here.
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.

While creating 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);
}
After the above call returns, the result will reflect the final state of the operation. Alternatively, to use the complete option, substitute the code in the try-catch block above with the following code sample.
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.

While creating 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.

Download the full code CreateTable.cs from the examples here.
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.

While creating 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.