Modifying Singleton Tables Using APIs
Learn how to modify a singleton table using APIs.
- Add new fields to an existing table
- Delete currently existing fields in a table
- To change the default TTL value
- Modify table limits
Using TableRequest API to alter table
You can use TableRequest API to change the definition of a NoSQL table.
The TableRequest
class is used to modify 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.
/**
* Alter the table stream_acct and add a column
*/
private static void alterTab(NoSQLHandle handle) throws Exception {
String alterTableDDL = "ALTER TABLE " + tableName +"(ADD acctname STRING)";
TableRequest treq = new TableRequest().setStatement(alterTableDDL);
System.out.println("Altering table " + tableName);
TableResult tres = handle.tableRequest(treq);
tres.waitForCompletion(handle, 60000, /* wait 60 sec */
1000); /* delay ms for poll */
System.out.println("Table " + tableName + " is altered");
}
The borneo.TableRequest
class is used to modify
tables. 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 alter_table(handle):
statement = '''ALTER TABLE stream_acct(ADD acctname STRING)'''
request = TableRequest().set_statement(statement)
table_result = handle.do_table_request(request, 40000, 3000)
table_result.wait_for_completion(handle, 40000, 3000)
print('Table stream_acct is altered')
The TableRequest
class is used to modify tables.
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.
//alter an existing table and add a column
func alterTable(client *nosqldb.Client, err error, tableName string)(){
stmt := fmt.Sprintf("ALTER TABLE %s (ADD acctName STRING)",tableName)
tableReq := &nosqldb.TableRequest{
Statement: stmt,
}
tableRes, err := client.DoTableRequest(tableReq)
if err != nil {
fmt.Printf("cannot initiate ALTER TABLE request: %v\n", err)
return
}
// The alter table request is asynchronous, wait for table alteration to complete.
_, err = tableRes.WaitForCompletion(client, 60*time.Second, time.Second)
if err != nil {
fmt.Printf("Error finishing ALTER TABLE request: %v\n", err)
return
}
fmt.Println("Altered table ", tableName)
return
}
You can use execute the tableDDL
method to modify a
table. This method is asynchronous and it returns a Promise of
TableResult
. The TableResult
is a plain
JavaScript object that encapsulates the state of the table after the DDL
operation. For method details, see NoSQLClient class.
//alter a table and add a column
async function alterTable(handle) {
const alterDDL = `ALTER TABLE ${TABLE_NAME} (ADD acctname STRING)`;
let res = await handle.tableDDL(alterDDL);
console.log('Table altered: ' + TABLE_NAME);
}
You can use either of the two methods
ExecuteTableDDLAsync
and
ExecuteTableDDLWithCompletionAsync
to modify a table. Both
the methods return Task<TableResult>
.
TableResult
instance encapsulates the state of the table
after the DDL operation. See Oracle NoSQL Dotnet SDK API Reference
for more details on these methods.
private static async Task alterTable(NoSQLClient client){
var sql = $@"ALTER TABLE {TableName}(ADD acctname STRING)";
var tableResult = await client.ExecuteTableDDLAsync(sql);
// Wait for the operation completion
await tableResult.WaitForCompletionAsync();
Console.WriteLine(" Table {0} is altered", tableResult.TableName);
}