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.

Before creating a table, see:

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 index called nameIdx on the name field in the users table */
CREATE INDEX IF NOT EXISTS nameIdx ON users(name)

The following example considers that the default compartment is specified in NoSQLHandleConfig while obtaining the NoSQL handle. See Obtaining a NoSQL Handle . To explore other options of specifying a compartment for the NoSQL tables, see About Compartments .

Create a table and index using the TableRequest and its methods. The TableRequest class lets you pass a DDL statement to the TableRequest.setStatement method.

/* 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(50, 50, 25);
 * 2) On-demand Capacity - only set storage limit
 * TableLimits limits = new TableLimits( 5 );   
 * In this example, we will use Provisioned Capacity 
*/
TableLimits limits = new TableLimits(50, 50, 25);
TableRequest treq = new TableRequest().setStatement(createTableDDL)
 .setTableLimits(limits);


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

// wait for completion of the operation
tres.waitForCompletion(handle,
 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);

// wait for completion of the operation
  tres.waitForCompletion(handle,
   60000, // wait for 60 sec
   1000); // delay in ms for poll

Creating a child table: You use the API class and methods to execute DDL statement to create a child table. While creating a child table, Table limits need not be explicitly set as a child table inherits the limits of a parent table.

final static String tableName = "users";
final static String childtableName = "userDetails";
String createchildTableDDL = "CREATE TABLE IF NOT EXISTS " +
            tableName + "."+ childtableName + "(address STRING, salary INTEGER, " +
            "PRIMARY KEY(address))";
    TableRequest treq = new TableRequest().setStatement(createchildTableDDL);
    System.out.println("Creating child table " + tableName);
    TableResult tres = handle.tableRequest(treq);
    /* The request is async,
    * so wait for the table to become active.
    */
    System.out.println("Waiting for "+ childtableName + " to become active");
    tres.waitForCompletion(handle, 60000, /* wait 60 sec */
         1000); /* delay ms for poll */
    System.out.println("Table " + childtableName + " is active");

Find the list of tables:

You can get a list of tables.

ListTablesRequest tablereq = new ListTablesRequest();
String [] tablelis = handle.listTables(tablereq).getTables();
if (tablelis.length == 0)   
   System.out.println("No tables avaiable");
else {  
   System.out.println("The tables available are");   
   for (int i=0;i< tablelis.length; i++)  {    
      System.out.println(tablelis[i]);  
   }
}

You can also fetch the schema of a table at any time.

GetTableRequest gettblreq = new GetTableRequest();
gettblreq.setTableName(tableName);
System.out.println("The schema details for the table is " 
+ handle.getTable(gettblreq).getSchema());

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)

Creating a child table:You use the API class and methods to execute DDL statement to create a child table. While creating a child table, Table limits need not be explicitly set as a child table inherits the limits of a parent table.

statement = 'create table if not exists users.userDetails (address STRING,
salary integer,  primary key(address))'
    print('Creating table: ' + statement)
    request = TableRequest().set_statement(statement)
    # Ask the cloud service to create the table, 
    # waiting for a total of 40000 milliseconds and polling the service 
    # every 3000 milliseconds to see if the table is active
    table_result = handle.do_table_request(request, 40000, 3000)
    table_result.wait_for_completion(handle, 40000, 3000)
    if (table_result.get_state() != State.ACTIVE):
        raise NameError('Table userDetails is in an unexpected state ' + 
str(table_result.get_state()))

Find the list of tables:

You can get a list of tables.

ltr = ListTablesRequest()
list(str)= handle.list_tables(ltr).getTables()
if list(str).len() = 0 
   print ("No tables available")
else 
   print('The tables available are: ' + list(str))

You can also fetch the schema of a table at any time.

request = GetTableRequest().set_table_name(table_name)
result = handle.get_table(request)
print('The schema details for the table is: ' + result.get_schema())

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 ")

Creating a child table:You use the API class and methods to execute DDL statement to create a child table. While creating a child table, Table limits need not be explicitly set as a child table inherits the limits of a parent table.

// Creates a simple child table with a string key and a single integer field.
    childtableName := "users.userDetails"
    stmt1 := fmt.Sprintf("CREATE TABLE IF NOT EXISTS %s ("+
    "address STRING, "+
    "salary INTEGER, "+
    "PRIMARY KEY(address))",
    childtableName)
    tableReq1 := &nosqldb.TableRequest{Statement: stmt1}
    tableRes1, err := client.DoTableRequest(tableReq1)
    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 = tableRes1.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 ", childtableName)

Find the list of tables:

You can get a list of tables.

req := &nosqldb.ListTablesRequest{Timeout: 3 * time.Second,}
res, err := client.ListTables(req)
if len(res.Tables)== 0{
   fmt.Printf("No tables in the given compartment"
   return
}
fmt.Printf("The tables in the given compartment are:\n" )
for i, table := range res.Tables {
   fmt.Printf(table)
}

You can also fetch the schema of a table at any time.

req := &nosqldb.GetTableRequest{
TableName: table_name, Timeout: 3 * time.Second, }
res, err := client.GetTable(req)
fmt.Printf("The schema details for the table is:state=%s, 
   limits=%v\n", res.State,res.Limits)

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: 50, writeUnits: 50, storageGB: 25);
// 2) On-demand Capacity - only set storage limit
//  tableLimits: {storageGB: 25 );   
// In this example, we will use Provisioned Capacity
        let result = await client.tableDDL(statement, {
            tableLimits: {
                readUnits: 50,
                writeUnits: 50,
                storageGB: 25
            }
        });

        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: 50, writeUnits: 50, storageGB: 25);
// 2) On-demand Capacity - only set storage limit
//  tableLimits: {storageGB: 25 );   
// In this example, we will use Provisioned Capacity
        let result = await client.tableDDL(statement, {
        tableLimits: {
            readUnits: 50,
            writeUnits: 50,
            storageGB: 25
        },
        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 
}

Creating a child table:You use the API class and methods to execute DDL statement to create a child table. While creating a child table, Table limits need not be explicitly set as a child table inherits the limits of a parent table.

/**
  * This function will create the child table userDetails with two columns,
  * one string column address which will be the primary key and one integer column 
  * which will be the salary.
  * @param {NoSQLClient} handle An instance of NoSQLClient
  */
const TABLE_NAME = 'users';
const CHILDTABLE_NAME = 'userDetails';
async function createChildTable(handle) {
    const createChildtblDDL = `CREATE TABLE IF NOT EXISTS ${TABLE_NAME}.${CHILDTABLE_NAME}
   (address STRING, salary INTEGER, PRIMARY KEY(address))`;
    console.log('Create table: ' + createChildtblDDL);
    let res =  await handle.tableDDL(createChildtblDDL, {complete: true});
}

Find the list of tables:

You can get a list of tables.

let varListTablesResult = await client.listTables();
console.log("The tables in the given compartment are:")
{res.send(varListTablesResult)}

You can also fetch the schema of a table at any time.

let resExistingTab = await client.getTable(tablename);
{ await client.forCompletion(resExistingTab);}
console.log("The  schema details for the table is:")
{ res.send(resExistingTab.schema)}

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 and table schema. 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(50, 50, 25);
// 2) On-demand Capacity - only set storage limit
// new TableLimits( 25 );   
// In this example, we will use Provisioned Capacity
  var result = await client.ExecuteTableDDLAsync(statement, new
    TableLimits(50, 50, 25)); 

  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(50, 50, 25);
// 2) On-demand Capacity - only set storage limit
// new TableLimits(25 );   
// In this example, we will use Provisioned Capacity
await client.ExecuteTableDDLWithCompletionAsync(statement, 
       new  TableLimits(50, 50, 25)); 

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.

Creating a child table:You use the API class and methods to execute DDL statement to create a child table. While creating a child table, Table limits need not be explicitly set as a child table inherits the limits of a parent table.

private const string TableName = "users";
private const string ChildTableName = "userDetails";
// Create a child table
var childtblsql = $"CREATE TABLE IF NOT EXISTS {TableName}.{ChildTableName}
address STRING, salary INTEGER, PRIMARY KEY(address))";
Console.WriteLine("\nCreate table {0}", ChildTableName);
var tableResult = await client.ExecuteTableDDLAsync(childtblsql);
Console.WriteLine("  Creating table {0}", ChildTableName);
Console.WriteLine("  Table state: {0}", tableResult.TableState);
// Wait for the operation completion
await tableResult.WaitForCompletionAsync();
Console.WriteLine("  Table {0} is created",tableResult.TableName);
Console.WriteLine("  Table state: {0}", tableResult.TableState);

Find the list of tables:

You can get a list of tables.

varresult = await client.ListTablesAsync(); 
console.WriteLine("The tables in the given compartment are:") 
foreach(var tableName inresult.TableNames){
   Console.WriteLine(tableName);
}

In Spring data applications, the tables are automatically created at the beginning of the application when the entities are initialized unless @NosqlTable.autoCreateTable is set to false.

Create a Users entity class to persist. This entity class represents a table in the Oracle NoSQL Database and an instance of this entity corresponds to a row in that table.

You can set the default TableLimits in the @NosqlDbConfig instance using NosqlDbConfig.getDefaultCapacityMode(), NosqlDbConfig.getDefaultStorageGB(), NosqlDbConfig.getDefaultReadUnits(), and NosqlDbConfig.getDefaultWriteUnits() methods. TableLimits can also be specified per table if @NosqlTable annotation is used, through capacityMode, readUnits, writeUnits, and storageGB fields.

Provide the @NosqlId annotation to indicate the ID field. The generated=true attribute specifies that the ID will be auto-generated. You can set the table level TTL by providing the ttl() and ttlUnit() parameters in the @NosqlTable annotation of the entity class. For details on all the Spring Data classes, methods, interfaces, and examples see SDK for Spring Data API Reference.

If the ID field type is a String, a UUID will be used. If the ID field type is int or long, a "GENERATED ALWAYS as IDENTITY (NO CYCLE)" sequence is used.


import com.oracle.nosql.spring.data.core.mapping.NosqlId;
import com.oracle.nosql.spring.data.core.mapping.NosqlTable;
 
/* Set the TableLimits and TTL values. */
@NosqlTable(readUnits = 50, writeUnits = 50, storageGB = 25, ttl = 10, ttlUnit = NosqlTable.TtlUnit.DAYS)
 
public class Users
{
    @NosqlId(generated = true)
    long id;
    String firstName;
    String lastName;
 
    /* public or package protected constructor required when retrieving from the database. */
    public Users() {
    }
 
    @Override
    public String toString()
    {
        return "Users{" +
            "id=" + id + ", " +
            "firstName=" + firstName + ", " +
            "lastName=" + lastName +
            '}';
    }
}

Create the following UsersRepository interface. This interface extends the NosqlRepository interface and provides the entity class and the data type of the primary key in that class as parameterized types to the NosqlRepository interface. This NosqlRepository interface provides methods that are used to store or retrieve data from the database.

import com.oracle.nosql.spring.data.repository.NosqlRepository;
 
/* The Users is the entity class and Long is the data type of the primary key in the Users class.
   This interface provides methods that return iterable instances of  the Users class. */
 
public interface UsersRepository extends NosqlRepository<Users, Long> {
    /* Search the Users table by the last name and return an iterable instance of the Users class.*/
    Iterable<Users> findByLastName(String lastname);
}

You can use Spring's CommandLineRunner interface to show the application code that implements the run method and has the main method.

Note

You can code the functionality as per your requirements by implementing any of the various interfaces that the Spring Data Framework provides. For more information on setting up a Spring boot application, see Spring Boot.

import com.oracle.nosql.spring.data.core.NosqlTemplate;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ConfigurableApplicationContext;
 
/* The @SpringBootApplication annotation helps you to build an application using Spring Data Framework rapidly.*/
@SpringBootApplication
public class App implements CommandLineRunner {
 
    /* The annotation enables Spring Data Framework to look up the configuration file for a matching bean.*/
    @Autowired
    private UsersRepository repo;
 
    public static void main(String[] args) {
        ConfigurableApplicationContext ctx =
                SpringApplication.run(App.class, args);
        SpringApplication.exit(ctx, () -> 0);
        ctx.close();
        System.exit(0);
    }
 
    @Override
    public void run(String... args) throws Exception {
    }
}

When a table is created through the Spring Data application, a schema is created automatically, which includes two columns - the primary key column (types String, int, long, or timestamp) and a JSON column called kv_json_.

Note

If a table exists already, it must comply with the generated schema.

To create an index on a field in the Users table, you use NosqlTemplate.runTableRequest().

Create the AppConfig class that extends AbstractNosqlConfiguration class to provide the connection details of the Oracle NoSQL Database. For details, see Obtaining a NoSQL connection.

In the application, you instantiate the NosqlTemplate class by providing the NosqlTemplate create (NosqlDbConfig nosqlDBConfig) method with the instance of the AppConfig class. You then modify the table using the NosqlTemplate.runTableRequest() method. You provide the NoSQL statement for the creation of the index in the NosqlTemplate.runTableRequest() method.

In this example, you create an index on the lastName field in the Users table.

import com.oracle.nosql.spring.data.core.NosqlTemplate;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ConfigurableApplicationContext;
 
/* Create an Index on the lastName field of the Users Table. */
 
try {
    AppConfig config = new AppConfig();
    NosqlTemplate idx = NosqlTemplate.create(config.nosqlDbConfig());
    idx.runTableRequest("CREATE INDEX IF NOT EXISTS nameIdx ON Users(kv_json_.lastName AS STRING)");
    System.out.println("Index created successfully");
} catch (Exception e) {
    System.out.println("Exception creating index" + e);
}

For more details on table creation, see Example: Accessing Oracle NoSQL Database Using Spring Data Framework in the Spring Data SDK Developers Guide.

Related Topics

About Time to Live

Oracle Cloud Infrastructure Documentation