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.
/* Create a JSON collection table with an integer primary key*/
private static void createTable(NoSQLHandle handle) throws Exception {
String createTableDDL = "CREATE TABLE IF NOT EXISTS " + usersJSON + "(id INTEGER," + "PRIMARY KEY(id)) AS JSON COLLECTION";
TableRequest treq = new TableRequest().setStatement(createTableDDL);
System.out.println("Creating table");
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, 1000);
}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.
/* Create a JSON collection table with an integer primary key */
statement = 'create table if not exists usersJSON(id integer,' + 'primary key(id)) AS JSON COLLECTION'
print('Creating table: ' + statement)
request = TableRequest().set_statement(statement)
/* assume that a handle has been created, as the handle, make the request */
/* wait for 60 seconds, polling every 1 seconds */
result = handle.do_table_request(request, 60000, 1000)
result.wait_for_completion(handle, 60000, 1000)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.
/* Create a JSON collection table with an integer primary key with a TTL of 3 days*/
tableName := "usersJSON"
stmt := fmt.Sprintf("CREATE TABLE IF NOT EXISTS %s "+
"(id integer, PRIMARY KEY(id)) "+
"AS JSON COLLECTION USING TTL 3 DAYS", tableName)
tableReq := &nosqldb.TableRequest{
Statement: stmt, }
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) 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.
Download the full JavaScript code CreateTable.js from the examples here and the full TypeScript code CreateTable.ts from the examples here.
import { NoSQLClient, ServiceType } 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))`;
let res = await handle.tableDDL(createDDL, {
complete: true }
);
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.
import { NoSQLClient, ServiceType } from 'oracle-nosqldb';
const client = new NoSQLClient('config.json');
/* Create a JSON collection table with an integer primary key */
const TABLE_NAME = 'usersJSON';
async function createTable() {
const createDDL = `CREATE TABLE IF NOT EXISTS ${TABLE_NAME} (id INTEGER, PRIMARY KEY(id)) AS JSON COLLECTION`;
console.log('Create table: ' + createDDL);
let res = await client.tableDDL(createDDL, {
complete: true,
});
}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.
/* Create a JSON collection table with an integer primary key */
var client = new NoSQLClient("config.json");
try {
var statement = "CREATE TABLE IF NOT EXISTS usersJSON(id INTEGER,"
+ "PRIMARY KEY(id)) AS JSON COLLECTION";
var result = await client.ExecuteTableDDLAsync(statement);
await result.WaitForCompletionAsync();
Console.WriteLine("Table users created.");
} catch(Exception ex) {
// handle exceptions
}