Creating Tables and Indexes

Learn how to create tables and indexes.

Creating a table is the first step of developing your application.

You use the API class and methods to execute all DDL statements, such as, creating, modifying, and dropping tables. You can also set table limits using the API method.

Examples of DDL statements are:

/* Create a new table called users */
CREATE TABLE IF NOT EXISTS users(id INTEGER,
 name STRING,
 PRIMARY KEY(id))

/* Create a new table called users and set the TTL value to 4 days */
CREATE TABLE IF NOT EXISTS users(id INTEGER,
 name STRING,
 PRIMARY KEY(id))
USING TTL 4 days
/* Create a new multi-region table called users with two regions, and set the TTL value to 4 days */
CREATE TABLE users(
 id INTEGER,
 name STRING,
 team STRING,
 primary key(id))
USING TTL 4 DAYS IN REGIONS fra, lnd
/* Create a new index called nameIdx on the name field in the users table */
CREATE INDEX IF NOT EXISTS nameIdx ON users(name)

Create a table and index using the TableRequest and its methods.

/* Create a simple table with an integer key and a single json data
 * field  and set your desired table capacity.
 * Set the table TTL value to 3 days.
 */
String createTableDDL = "CREATE TABLE IF NOT EXISTS users " +
 "(id INTEGER, name STRING, " +
 "PRIMARY KEY(id)) USING TTL 3 days";
/* Call the appropriate constructor for 
 * 1) Provisioned Capacity
 * TableLimits limits = new TableLimits(200, 100, 5);
 * 2) On-demand Capacity - only set storage limit
 * TableLimits limits = new TableLimits( 5 );   
 * In this example, we will use Provisioned Capacity 
*/

TableRequest treq = new TableRequest().setStatement(createTableDDL);
// start the asynchronous operation
TableResult tres = handle.tableRequest(treq);


// The table request is asynchronous, so wait for the table to become active.
TableResult.waitForState(handle, tres.getTableName(),
 TableResult.State.ACTIVE,
 60000, // wait for 60 sec
 1000); // delay in ms for poll 

// Create an index called nameIdx on the name field in the users table.
treq = new TableRequest().setStatement("CREATE INDEX 
  IF NOT EXISTS nameIdx ON users(name)
  ");

// start the asynchronous operation
  handle.tableRequest(treq);

DDL statements are executed using the borneo.TableRequest class. 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.

#Create a simple table with an integer key and a single 
#json data field and set your desired table capacity. 
#Set the table TTL value to 3 days.
from borneo import TableLimits,
TableRequest statement = 'create table if not exists users(id integer, 
                                              name string,
                                              ' + 'primary key(id) 
                                                 USING TTL 3 DAYS'
# In the Cloud Service TableLimits is a required object for table 
#creation. It specifies the throughput and capacity for the table in 
#ReadUnits,  WriteUnits, GB
# Call the appropriate constructor for 
# 1) Provisioned Capacity
# TableLimits(50, 50, 25);
#  2) On-demand Capacity - only set storage limit
# TableLimits( 25 );  
# In this example, we will use Provisioned Capacity 
request = TableRequest().set_statement(statement).
                        set_table_limits( TableLimits(50, 50, 25))
# assume that a handle has been created, as handle, make the request 
#wait for 60 seconds, polling every 1 seconds
result = handle.do_table_request(request, 60000, 1000) 
# the above call to do_table_request is equivalent to 
# result = handle.table_request(request)
result.wait_for_completion(handle, 60000, 1000)

#Create an index called nameIdx on the name field in the users table.
request = TableRequest().set_statement("CREATE INDEX IF NOT EXISTS nameIdx 
                                        ON users(name)")
# assume that a handle has been created, as handle, make the request 
#wait for 60 seconds, polling every 1 seconds
result = handle.do_table_request(request, 60000, 1000) 
# the above call to do_table_request is equivalent to
# result = handle.table_request(request) 
result.wait_for_completion(handle, 60000, 1000)

The following example creates a simple table with an integer key and a single STRING field. The create table request is asynchronous. You wait for the table creation to complete.

// Create a simple table with an integer key and a single
// json data field and set your desired table capacity.
// Set the table TTL value to 3 days.
tableName := "users"
stmt := fmt.Sprintf("CREATE TABLE IF NOT EXISTS %s "+
    "(id integer, name STRING, PRIMARY KEY(id) "+
    "USING TTL 3 DAYS)", tableName)
// Call the appropriate constructor for
// 1) Provisioned Capacity
// &nosqldb.TableLimits(ReadUnits: 50, WriteUnits: 50, StorageGB: 25);
// 2) On-demand Capacity - only set storage limit
// &nosqldb.TableLimits(StorageGB: 25 );   
// In this example, we will use Provisioned Capacity
tableReq := &nosqldb.TableRequest{
    Statement: stmt,
    TableLimits: &nosqldb.TableLimits{
        ReadUnits:  50,
        WriteUnits: 50,
        StorageGB:  25,
    },
}
tableRes, err := client.DoTableRequest(tableReq)
if err != nil {
    fmt.Printf("cannot initiate CREATE TABLE request: %v\n", err)
    return
}
_, 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)

//Create an index called nameIdx on the name field in the users table
stmt_ind := fmt.Sprintf("CREATE INDEX IF NOT EXISTS nameIdx ON users(name)")
tableReq := &nosqldb.TableRequest{Statement: stmt_ind}
tableRes, err := client.DoTableRequest(tableReq)
if err != nil {
    fmt.Printf("cannot initiate CREATE INDEX request: %v\n", err)
    return
}
_, err = tableRes.WaitForCompletion(client, 60*time.Second, time.Second)
if err != nil {
    fmt.Printf("Error finishing CREATE INDEX request: %v\n", err)
    return
}
fmt.Println("Created index nameIdx ")

Table DDL statements are executed by tableDDL method. Like most other methods of NoSQLClient class, this method is asynchronous and it returns a Promise of TableResult. TableResult is a plain JavaScript object that contains status of DDL operation such as its TableState, name, schema and its TableLimit.

tableDDL method takes opt object as the 2nd optional argument. When you are creating a table, you must specify its TableLimits as part of the opt argument. TableLimits specifies maximum throughput and storage capacity for the table as the amount of read units, write units, and Gigabytes of storage.

Note that tableDDL method only launches the specified DDL operation in the underlying store and does not wait for its completion. The resulting TableResult will most likely have one of intermediate table states such as TableState.CREATING, TableState.DROPPING or TableState.UPDATING (the latter happens when table is in the process of being altered by ALTER TABLE statement, table limits are being changed or one of its indexes is being created or dropped).

When the underlying operation completes, the table state should change to TableState.ACTIVE or TableState.DROPPED (the latter if the DDL operation was DROP TABLE).

const NoSQLClient = require('oracle-nosqldb').NoSQLClient;
const TableState = require('oracle-nosqldb').TableState;
const client = new NoSQLClient('config.json');

async function createUsersTable() {
    try {
        const statement = 'CREATE TABLE IF NOT EXISTS users(id INTEGER, ' +
            'name STRING, PRIMARY KEY(id))';
// Call the appropriate constructor for
// 1) Provisioned Capacity
//  tableLimits: {readUnits: 20, writeUnits: 10, storageGB: 5);
// 2) On-demand Capacity - only set storage limit
//  tableLimits: {storageGB: 5 );   
// In this example, we will use Provisioned Capacity
        let result = await client.tableDDL(statement, {
            tableLimits: {
                readUnits: 20,
                writeUnits: 10,
                storageGB: 5
            }
        });
        result = await client.forCompletion(result);
        console.log('Table users created');
    } catch(error) {
        //handle errors
    }
}

After the above call returns, result will reflect final state of the operation. Alternatively, to use complete option, substitute the code in try-catch block above with the following:

const statement = 'CREATE TABLE IF NOT EXISTS users(id INTEGER, ' +
        'name STRING, PRIMARY KEY(id))';
// Call the appropriate constructor for
// 1) Provisioned Capacity
//  tableLimits: {readUnits: 20, writeUnits: 10, storageGB: 5);
// 2) On-demand Capacity - only set storage limit
//  tableLimits: {storageGB: 5 );   
// In this example, we will use Provisioned Capacity
    let result = await client.tableDDL(statement, {
        tableLimits: {
            readUnits: 20,
            writeUnits: 10,
            storageGB: 5
        },
        complete: true
    });
    console.log('Table users created');

You need not specify TableLimits for any DDL operation other than CREATE TABLE. You may also change table limits of the table after it has been created by calling setTableLimits method. This may also require waiting for the completion the operation in the same way as waiting for completion of operations initiated by tableDDL.

// Create an index called nameIdx on the name field in the users table.
try {
   const statement = 'CREATE INDEX IF NOT EXISTS nameIdx ON users(name))';
   let result = await client.tableDDL(statement);
   result = await client.forCompletion(result);
   console.log('Index nameIdx created');
} catch(error){
  //handle errors 
}

To create tables and execute other Data Definition Language (DDL) statements, such as creating, modifying and dropping tables as well as creating and dropping indexes, use methods ExecuteTableDDLAsync and ExecuteTableDDLWithCompletionAsync. Methods ExecuteTableDDLAsync and ExecuteTableDDLWithCompletionAsync return Task<TableResult>. TableResult instance contains status of DDL operation such as TableState, table schema and TableLimits. Each of these methods comes with several overloads. In particular, you may pass options for the DDL operation as TableDDLOptions.

When creating a table, you must specify its TableLimits. Table limits specify maximum throughput and storage capacity for the table as the amount of read units, write units and Gigabytes of storage. You may use an overload that takes tableLimits parameter or pass table limits as TableLimits property of TableDDLOptions. Note that these are potentially long running operations. The method ExecuteTableDDLAsync only launches the specified DDL operation by the service and does not wait for its completion. You may asynchronously wait for table DDL operation completion by calling WaitForCompletionAsync on the returned TableResult instance.

var client = new NoSQLClient("config.json");
try { 
  var statement = "CREATE TABLE IF NOT EXISTS users(id INTEGER,"
    + "name STRING, PRIMARY KEY(id))";
// Call the appropriate constructor for
// 1) Provisioned Capacity
// new TableLimits(20, 10, 5);
// 2) On-demand Capacity - only set storage limit
// new TableLimits( 5 );   
// In this example, we will use Provisioned Capacity
  var result = await client.ExecuteTableDDLAsync(statement, new
    TableLimits(20, 10, 5)); 
  await result.WaitForCompletionAsync(); 
  Console.WriteLine("Table users created."); 
} catch(Exception ex) {
   // handle exceptions
}

Note that WaitForCompletionAsync will change the calling TableResult instance to reflect the operation completion.

Alternatively you may use ExecuteTableDDLWithCompletionAsync. Substitute the statements in the try-catch block with the following:

var statement = "CREATE TABLE IF NOT EXISTS users(id INTEGER,"
    + "name STRING, PRIMARY KEY(id))"; 
// Call the appropriate constructor for
// 1) Provisioned Capacity
// new TableLimits(20, 10, 5);
// 2) On-demand Capacity - only set storage limit
// new TableLimits( 5 );   
// In this example, we will use Provisioned Capacity
await client.ExecuteTableDDLWithCompletionAsync(statement, 
       new  TableLimits(20, 10, 5)); 
Console.WriteLine("Table users created.");

You need not specify TableLimits for any DDL operation other than CREATE TABLE. You may also change table limits of an existing table by calling SetTableLimitsAsync or SetTableLimitsWithCompletionAsync methods.