Creating a region

Oracle NoSQL Database supports Multi-Region architecture in which you can create tables in multiple KVStores and Oracle NoSQL Database will automatically replicate inserts, updates, and deletes in a multi-directional fashion across all regions for which the table spans. Each KVStore cluster in a Multi-Region NoSQL Database setup is called a Region.

Example 1: The following CREATE REGION statement creates a remote region named my_region1.

CREATE REGION my_region1

In a Multi-Region Oracle NoSQL Database setup, you must define all the remote regions for each local region. For example, if there are three regions in a Multi-Region setup, you must define the other two regions from each participating region. You use the CREATE REGION statement to define remote regions in the Multi-Region Oracle NoSQL Database.

Example 2: Create a table in a region.

CREATE TABLE stream_acct_region(acct_id INTEGER,
acct_data JSON,
PRIMARY KEY(acct_id)) IN REGIONS my_region1

Note:

The region my_region1 should be set as the local region before creating the table.

Using APIs to create regions:

• Java
• Python
• Go
• Node.js
• C#

You can create a region using SystemRequest class. The SystemRequest class is used to perform any table-independent administrative operation. Once the region is created, a table can be created and added to the region using the TableRequest class.

Download the full code Regions.java from the examples here.

/* Create a remote region and a local region*/
private static void crtRegion(NoSQLHandle handle) throws Exception {
    // Create a remote region
    String createRemRegDDL = "CREATE REGION "+ remRegName;
    SystemRequest sysreq1 = new SystemRequest();
    sysreq1.setStatement(createRemRegDDL.toCharArray());
    SystemResult sysres1 = handle.systemRequest​(sysreq1);
    sysres1.waitForCompletion​(handle, 60000,1000);
    System.out.println(" Remote Region " + remRegName + " is created");
    // Create a local region
    String createLocRegDDL = "SET LOCAL REGION "+ localRegName;
    SystemRequest sysreq2 = new SystemRequest();
    sysreq2.setStatement(createLocRegDDL.toCharArray());
    SystemResult sysres2 = handle.systemRequest​(sysreq2);
    sysres2.waitForCompletion​(handle, 60000,1000);
    System.out.println(" Local Region " + localRegName + " is created");
}

/**
 * Create a table and add the table in a region
 */
private static void crtTabInRegion(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)) IN REGIONS FRA";

    TableRequest treq = new TableRequest().setStatement(createTableDDL);
    TableResult tres = handle.tableRequest(treq);
    /* The request is async,
     * so wait for the table to become active.
     */
    System.out.println("Table " + tableName + " is active");
}

You can create a region using SystemRequest class. The SystemRequest class is used to perform any table-independent administrative operation. After a region is created, a table can be created and added to the region using the borneo.TableRequest class.

Download the full code Regions.py from the examples here.

# create a remote and local region
def create_region(handle):
   #Create a remote region
   statement = '''CREATE REGION LON'''
   sysreq = SystemRequest().set_statement(statement)
   sys_result = handle.system_request(sysreq)
   sys_result.wait_for_completion(handle, 40000, 3000)
   print('Remote region LON is created')
   #Create a local region
   statement1 = '''SET LOCAL REGION FRA'''
   sysreq1 = SystemRequest().set_statement(statement1)
   sys_result1 = handle.system_request(sysreq1)
   sys_result1.wait_for_completion(handle, 40000, 3000)
   print('Local region FRA is created')

#Create a table in the local region
def create_tab_region(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)) IN REGIONS FRA'''
   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):
      print('Created table: stream_acct')
   else:
      raise NameError('Table stream_acct is in an unexpected state ' + str(table_result.get_state()))

You can create a region using SystemRequest class. The SystemRequest class is used to perform any table-independent administrative operations. After a region is created, a table can be created and added to the region using the TableRequest class.

Download the full code Regions.go from the examples here.

//Creates a remote and a local region
func crtRegion(client *nosqldb.Client, err error)(){
   // Create a remote region
   stmt := fmt.Sprintf("CREATE 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 CREATE REGION request: %v\n", err)
      return
   }
   fmt.Println("Created REGION LON ")
   // Create a local region
   stmt1 := fmt.Sprintf("SET LOCAL REGION FRA")
   sysReq1 := &nosqldb.SystemRequest{
		Statement: stmt1,
   }
   sysRes1, err1 := client.DoSystemRequest(sysReq1)
   _, err1 = sysRes1.WaitForCompletion(client, 60*time.Second, time.Second)
   if err1 != nil {
      fmt.Printf("Error finishing CREATE REGION request: %v\n", err)
      return
   }
   fmt.Println("Created REGION FRA ")
   return
}

//creates a table in a specific region
func crtTabInRegion(client *nosqldb.Client, err error, tableName string)(){
   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)) IN REGIONS FRA",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
   }
   // 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
}

You can create a region using adminDDL method. The adminDDL method is used to perform any table-independent administrative operation. After a region is created, a table can be created and added to the region using the tableDDL method.

Download the full JavaScript code Regions.js from the examples here and the full TypeScript code Regions.ts from the examples here.

//creates a remote and a local region
async function createRegion(handle) {
   // Create a remote region
   const crtRemReg = `CREATE REGION LON`;
   let res = await handle.adminDDL(crtRemReg);
   console.log('Remote region created: LON' );
   // Create a local region
   const crtLocalReg = `SET LOCAL REGION FRA`;
   let res1 = await handle.adminDDL(crtLocalReg);
   console.log('Local region created: FRA' );
}

//creates a table in a given region
async function crtTabInRegion(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)) IN REGIONS FRA`;
   let res =  await handle.tableDDL(createDDL, {
            complete: true
   });
   console.log('Table created: ' + TABLE_NAME);
}

You can create a region using ExecuteAdminSync module. The ExecuteAdminSync method is used to perform any table-independent administrative operations. After a region is created, a table can be created and added to the region using either ExecuteTableDDLAsync or ExecuteTableDDLWithCompletionAsync methods.

Download the full code Regions.cs from the examples here.

private static async Task createRegion(NoSQLClient client){
   // Create a remote region
   var sql = $@"CREATE REGION LON";
   var adminResult = await client.ExecuteAdminAsync(sql);
   // Wait for the operation completion
   await adminResult.WaitForCompletionAsync();
   Console.WriteLine("  Created remote REGION LON");
   // Create a local region
   var sql1 = $@"SET LOCAL REGION FRA";
   var adminResult1 = await client.ExecuteAdminAsync(sql1);
   // Wait for the operation completion
   await adminResult1.WaitForCompletionAsync();
   Console.WriteLine("  Created local REGION FRA");
}

private static async Task createTabInRegion(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)) IN REGIONS FRA";
   Console.WriteLine("\nCreate table {0}", TableName);
   var tableResult = await client.ExecuteTableDDLAsync(sql);
   // Wait for the operation completion
   await tableResult.WaitForCompletionAsync();
   Console.WriteLine("  Table {0} is created",tableResult.TableName);
   Console.WriteLine("  Table state: {0}", tableResult.TableState);
}