Managing Tables & Regions

You will first create a sample table. Then you will learn different ways to alter the table. At the end of the section, you will drop the table. You will also learn to view the existing regions and drop a particular region.

Start your KVSTORE or KVLite and open the SQL shell.

java -jar lib/kvstore.jar kvlite -secure-config disable
java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore

Alter Table

You can use the alter table command to perform the following operations.

Add schema fields to the table schema
Remove schema fields from the table schema
Add a region
Remove a region
Modify the Time-To-Live value of the table

Note:

You can specify only one type of operation in a single command. For example, you cannot remove a schema field and set the TTL value together.

Create a sample table :

CREATE TABLE demo_acct(
acct_id INTEGER,
acct_data JSON, 
PRIMARY KEY(acct_id)
)

Example 1: Add schema field to the table schema.

ALTER TABLE demo_acct(ADD acct_balance INTEGER)

Explanation: Adding a field does not affect the existing rows in the table. If a field is added, its default value or NULL will be used as the value of this field in existing rows that do not contain it. The field to add maybe a top-level field (i.e. A table column) or it may be deeply nested inside a hierarchical table schema. As a result, the field is specified via a path.

Example 2: Remove schema fields in the table schema.

ALTER TABLE demo_acct(DROP acct_balance)

Explanation: You can drop any field in the schema other than the primary key. If you try removing the primary key field, you get an error as shown below.

ALTER TABLE demo_acct(DROP acct_id)

Output( showing error):

Error handling command ALTER TABLE demo_acct(DROP acct_id):
Error: at (1, 27) Cannot remove a primary key field: acct_id

Example 3: Add a region

The add regions clause lets you link an existing Multi-Region Table (MR Table) with new regions in a multi-region Oracle NoSQL Database environment. You use this clause to expand MR Tables to new regions.

Associate a new region with an existing MR Table using the DDL command shown below.

ALTER TABLE <table_name> ADD REGIONS <region_name>

Explanation: Here, table_name is an MR table and region_name is an existing region.

Example 4: Remove a region

The drop regions clause lets you disconnect an existing MR Table from a participating region in a multi-region Oracle NoSQL Database environment. You use this clause to contract MR Tables to fewer regions.

To remove an MR Table from a specific region in a Multi-Region NoSQL Database setup, you must run the following steps from all the other participating regions.

ALTER TABLE <table_name> DROP REGIONS <comma_separated_list_of_regions>

Here, table_name is a MR Table and comma_separated_list_of_regions is a list of regions to be dropped.

Example 5: Modify the Time-To-Live value of the table

Time-to-Live (TTL) is a mechanism that allows you to set a time frame on table rows, after which the rows expire automatically, and are no longer available. By default, every table that you create has a TTL value of zero, indicating that it has no expiration time.

You can use ALTER TABLE command to change this value for any table. You can specify the TTL with a number, followed by either HOURS or DAYS.

ALTER TABLE demo_acct USING TTL 5 days

Note:

Altering the TTL value for a table does not change the TTL value for existing rows in the table. Rather, it will only change the default TTL value placed in rows created subsequent to the alter table. To modify the TTL of every record in a table, you must iterate through each record of the table and update its TTL value.

Using APIs to alter tables:

Use the Oracle NoSQL Database SDK for Java to alter a table. Download the full code AlterTable.java from the examples here.

/**
* 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");
}

Use the Oracle NoSQL Database Python SDK to alter a table. Download the full code AlterTable.py from the examples here.

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

Use the Oracle NoSQL Database Go SDK to alter a table. Download the full code AlterTable.go from the examples here.

//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
}

Use the Oracle NoSQL Database Node.js SDK to alter a table. Download the full code AlterTable.js from the examples here.

//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);
}

Use the Oracle NoSQL Database Dotnet SDK to alter a table. Download the full code AlterTable.cs from the examples here.

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);
}

Drop Table

The drop table statement removes the specified table and all its associated indexes from the database. By default, if the named table does not exist then this statement fails. You don't get an error if the optional IF EXISTS clause is specified and the table does not exist.

DROP TABLE demo_acct

Note:

To drop a MR Table, first drop all of its child tables. Otherwise, the DROP statement results in an error.

Using APIs to drop a table:

Use the Oracle NoSQL Database SDK for Java to drop a table. Download the full code AlterTable.java from the examples here.

/*Drop the table*/
private static void dropTab(NoSQLHandle handle) throws Exception {
   String dropTableDDL = "DROP TABLE " + tableName;
   TableRequest treq = new TableRequest().setStatement(dropTableDDL);
   TableResult tres = handle.tableRequest(treq);
   tres.waitForCompletion(handle, 60000, /* wait 60 sec */
       1000); /* delay ms for poll */
   System.out.println("Table " + tableName + " is dropped");
}

Use the Oracle NoSQL Database Python SDK to drop a table. Download the full code AlterTable.py from the examples here.

def drop_table(handle):
   statement = '''DROP TABLE stream_acct'''
   request = TableRequest().set_statement(statement)
   table_result = handle.do_table_request(request, 40000, 3000)
   table_result.wait_for_completion(handle, 40000, 3000)
   print('Dropped table: stream_acct')

Use the Oracle NoSQL Database Go SDK to drop a table. Download the full code AlterTable.go from the examples here.

//drop an existing table
func dropTable(client *nosqldb.Client, err error, tableName string)(){
   stmt := fmt.Sprintf("DROP TABLE %s",tableName)
   tableReq := &nosqldb.TableRequest{
		Statement: stmt,
   }
   tableRes, err := client.DoTableRequest(tableReq)
   if err != nil {
      fmt.Printf("cannot initiate DROP TABLE request: %v\n", err)
      return
   }
   // The drop table request is asynchronous, wait for table drop to complete.
   _, err = tableRes.WaitForCompletion(client, 60*time.Second, time.Second)
   if err != nil {
      fmt.Printf("Error finishing DROP TABLE request: %v\n", err)
      return
   }
   fmt.Println("Dropped table ", tableName)
   return
}

Use the Oracle NoSQL Database Node.js SDK to drop a table. Download the full code AlterTable.js from the examples here.

//drop a table
async function dropTable(handle) {
   const dropDDL = `DROP TABLE ${TABLE_NAME}`;
   let res =  await handle.tableDDL(dropDDL);
   console.log('Table dropped: ' + TABLE_NAME);
}

Use the Oracle NoSQL Database Dotnet SDK to drop a table. Download the full code AlterTable.cs from the examples here.

private static async Task dropTable(NoSQLClient client){
   var sql = $@"DROP TABLE {TableName}";
   var tableResult = await client.ExecuteTableDDLAsync(sql);
   // Wait for the operation completion
   await tableResult.WaitForCompletionAsync();
   Console.WriteLine("  Table {0} is dropped", tableResult.TableName);
}

Manage regions

The show regions statement provides the list of regions present in the Multi-Region Oracle NoSQL Database. You need to specify "AS JSON" if you want the output to be in JSON format.

Example 1: The following statement lists all the existing regions.

SHOW REGIONS

The following statement lists all the existing regions in JSON format.

SHOW AS JSON REGIONS

In a Multi-Region Oracle NoSQL Database environment, the drop region statement removes the specified remote region from the local region. See Set up Multi-Region Environment for more details on the local regions and remote regions in a Multi-Region setup.

Note:

This region must be different from the local region where the command is executed.

The following drop region statement removes a remote region named my_region1.

DROP REGION my_region1

Using APIs to drop regions:

Use the Oracle NoSQL Database SDK for Java to drop a region. Download the full code Regions.java from the examples here.

/* Drop a table from a region*/
private static void dropTabInRegion(NoSQLHandle handle) throws Exception {
   String dropTableDDL = "DROP TABLE " + tableName;
   TableRequest treq = new TableRequest().setStatement(dropTableDDL);
   TableResult tres = handle.tableRequest(treq);
   tres.waitForCompletion(handle, 60000, /* wait 60 sec */
   1000); /* delay ms for poll */
   System.out.println("Table " + tableName + " is dropped");
}
   /* Drop a region*/
   private static void dropRegion(NoSQLHandle handle, String regName) throws Exception {
      String dropNSDDL = "DROP REGION " + regName;
      SystemRequest sysreq = new SystemRequest();
      sysreq.setStatement(dropNSDDL.toCharArray());
      SystemResult sysres = handle.systemRequest(sysreq);
      sysres.waitForCompletion(handle, 60000,1000);
      System.out.println("Region " + regName + " is dropped");
   }

Use the Oracle NoSQL Database Python SDK to drop a region. Download the full code Regions.py from the examples here.

#Drop the table from a region
def drop_tab_region(handle):
   statement = '''DROP TABLE stream_acct'''
   request = TableRequest().set_statement(statement)
   table_result = handle.do_table_request(request, 40000, 3000)
   table_result.wait_for_completion(handle, 40000, 3000)
   print('Dropped table: stream_acct')

#Drop the region
def drop_region(handle):
   statement = '''DROP REGION LON'''
   sysreq = SystemRequest().set_statement(statement)
   sys_result = handle.system_request(sysreq)
   sys_result.wait_for_completion(handle, 40000, 3000)
   print('Region LON is dropped')

Use the Oracle NoSQL Database Go SDK to drop a region. Download the full code Regions.go from the examples here.

//drops a table from a region
func drpTabInRegion(client *nosqldb.Client, err error, tableName string)(){
   stmt := fmt.Sprintf("DROP TABLE %s",tableName)
   tableReq := &nosqldb.TableRequest{
		Statement: stmt,
   }
   tableRes, err := client.DoTableRequest(tableReq)
   if err != nil {
      fmt.Printf("cannot initiate DROP TABLE request: %v\n", err)
      return
   }
   _, err = tableRes.WaitForCompletion(client, 60*time.Second, time.Second)
   if err != nil {
      fmt.Printf("Error finishing DROP TABLE request: %v\n", err)
      return
   }
   fmt.Println("Dropped table ", tableName)
   return
}

//drop a region
func dropRegion(client *nosqldb.Client, err error)(){
   stmt := fmt.Sprintf("DROP REGION LON")
   sysReq := &nosqldb.SystemRequest{
		Statement: stmt,
   }
   sysRes, err := client.DoSystemRequest(sysReq)
   _, err = sysRes.WaitForCompletion(client, 60*time.Second, time.Second)
   if err != nil {
      fmt.Printf("Error finishing DROP REGION request: %v\n", err)
      return
   }
   fmt.Println("Dropped REGION LON ")
   return
}

Use the Oracle NoSQL Database Node.js SDK to drop a region. Download the full code Regions.js from the examples here.

//drop a table from a region
async function dropTabInRegion(handle) {
   const dropDDL = `DROP TABLE ${TABLE_NAME}`;
   let res =  await handle.tableDDL(dropDDL);
   console.log('Table dropped: ' + TABLE_NAME);
}

//drop a region
async function dropRegion(handle) {
   const dropReg = `DROP REGION LON`;
   let res = await handle.adminDDL(dropReg);
   console.log('Region dropped: LON' );
}

Use the Oracle NoSQL Database Dotnet SDK to drop a region. Download the full code Regions.cs from the examples here.

private static async Task dropTabInRegion(NoSQLClient client){
   var sql = $@"DROP TABLE {TableName}";
   var tableResult = await client.ExecuteTableDDLAsync(sql);
   // Wait for the operation completion
   await tableResult.WaitForCompletionAsync();
   Console.WriteLine("  Table {0} is dropped", tableResult.TableName);
}

private static async Task dropRegion(NoSQLClient client){
   var sql = $@"DROP REGION LON";
   var adminResult = await client.ExecuteAdminAsync(sql);
   // Wait for the operation completion
   await adminResult.WaitForCompletionAsync();
   Console.WriteLine("  Dropped region LON");
}