Use Case 2: Expand a Multi-Region Table
An organization deploys two on-premise KVStores, one each at Frankfurt and
London. As per their requirement, they create a few MR Tables in both the regions. The
users
table is one of the many MR Tables created and maintained by
this organization. Now, they decide to expand their organization by adding another NoSQL
Database in Dublin. After creating Dublin as the new region, they want to expand the
existing MR Tables to the new region. In the next few topics, you learn how to add the
Dublin region to the users
table that you already created in the
previous use case.
If you have not created the users
MR Table earlier, execute
the steps outlined in Use Case 1: Set up Multi-Region Environment.
Prerequisites
Steps:
users
table to the new region, you must
have set up the new region by executing the following tasks:
- Set up a multi-region NoSQL Database with two regions Frankfurt
(
fra
) and London (lnd
). See Use Case 1: Set up Multi-Region Environment. - Deploy a local KVStore with store name as
dubstore
in the new region. See Configuration Overview. - Set the new region's local region name to
dub
. See Set Local Region Name. - Configure and start the XRegion Service in the
dub
region. See Configure XRegion Service and Start XRegion Service. - Update the
json.config
file in the existing regions, that is, Frankfurt (fra
) and London (lnd
) to includedub
(Dublin) as a remote region.Note:
You must restart the agent at existing regions to pick up the new region (dub
) from thejson.config
file. - Create two remote regions,
fra
andlnd
in the new regiondub
. See Create Remote Regions.
Example:
- Set the local region name for the new region,
Dublin.
# Connect to the KVStore deployed at host7, host8, and host9 from the SQL shell [~]$java -jar $KVHOME/lib/sql.jar \ -helper-hosts host7:5000,host8:5000,host9:5000 \ -store dubstore -- Set the local region name to 'dub' sql-> SET LOCAL REGION dub; Statement completed successfully -- List the regions sql-> SHOW REGIONS; regions dub (local, active)
- Create a
json.config
file for the new region, Dublin.# Contents of the configuration file in the 'dub' Region { "path": "<entire path to the home directory for the XRegion Service>", "agentGroupSize": 1, "agentId": 0, "region": "dub", "store": "<storename at the dub region>", "security": "<path to the security file>", "helpers": [ "host7:5000", "host8:5000", "host9:5000" ], "regions": [ { "name": "fra", "store": "<store name at the fra region>", "security": "<path to the security file>", "helpers": [ "host1:5000", "host2:5000", "host3:5000" ] }, { "name": "lnd", "store": "<store name at the lnd region>", "security": "<path to the security file>", "helpers": [ "host4:5000", "host5:5000", "host6:5000" ] } ] }
- Start the XRegion Service in the new region,
Dublin.
# Start the XRegion Service in the 'dub' Region [oracle@host7 xrshome]$nohup java -Xms256m -Xmx2048m -jar $KVHOME/lib/kvstore.jar xrstart \ -config <path to the json config file> > \ <path to the home directory of the xregion service>/nohup.out & [1] 24123 [oracle@host7 xrshome]$ nohup: ignoring input and redirecting stderr to stdout # View the status of the xrstart command in the 'dub' Region [oracle@host7 xrshome]$ cat nohup.out Cross-region agent (region=fra,store=mrtstore, helpers=[host7:5000, host8:5000, host9:5000]) starts up from config file=/home/oracle/xrshome/ json.config at 2020-11-07 08:57:34 UTC
- Modify the
json.config
files in the existing regions (Frankfurt and London) to include Dublin as a remote region.# Contents of the configuration file in the 'fra' Region { "path": "<path to the json config file>", "agentGroupSize": 1, "agentId": 0, "region": "fra", "store": "<storename at the fra region>", "security": "<path to the security file>", "helpers": [ "host1:5000", "host2:5000", "host3:5000" ], "regions": [ { "name": "lnd", "store": "<storename at the lnd region>", "security": "<path to the security file>", "helpers": [ "host4:5000", "host5:5000", "host6:5000" ] }, { "name": "dub", "store": "<storename at the dub region>", "security": "<path to the security file>", "helpers": [ "host7:5000", "host8:5000", "host9:5000" ] } ] }
# Contents of the configuration file in the 'lnd' Region { "path": "<path to the json config file>", "agentGroupSize": 1, "agentId": 0, "region": "lnd", "store": "<storename at the lnd region>", "security": "<path to the security file>", "helpers": [ "host4:5000", "host5:5000", "host6:5000" ], "regions": [ { "name": "fra", "store": "<storename at the fra region>", "security": "<path to the security file>", "helpers": [ "host1:5000", "host2:5000", "host3:5000" ] }, { "name": "dub", "store": "<storename at the dub region>", "security": "<path to the security file>", "helpers": [ "host7:5000", "host8:5000", "host9:5000" ] } ] }
- Create two remote regions,
fra
andlnd
in the new region, Dublin.# Connect to the KVStore deployed in the 'dub' region from the SQL shell [~]$java -jar $KVHOME/lib/sql.jar \ -helper-hosts host7:5000,host8:5000,host9:5000 \ -store dubstore –- Create remote regions 'fra' and 'lnd' sql-> CREATE REGION fra; Statement completed successfully sql-> CREATE REGION lnd; Statement completed successfully – List the regions sql-> SHOW REGIONS; regions dub (local, active) fra (remote, active) lnd (remote, active)
Create MR Table in New Region
Steps:
CREATE TABLE
statement. See Create Multi-Region Tables.
Note:
Creating the MR Table in the new region alone does not ensure replicating the data from the existing regions. This is because you have not yet linked the new region to this MR Table from the existing regions.Example:
Create the users
MR Table in the new region, Dublin.
# Connect to the KVStore deployed in the 'dub' region from the SQL shell
[~]$java -jar $KVHOME/lib/sql.jar \
-helper-hosts host7:5000,host8:5000,host9:5000 \
-store dubstore
-- Create the users MR Table
sql-> CREATE TABLE users(
-> id INTEGER,
-> name STRING,
-> team STRING,
-> PRIMARY KEY (id))
-> IN REGIONS dub,fra,lnd;
Statement completed successfully
# Connect to the KVStore deployed in the 'dub' region from the kv prompt
[~]$java -jar $KVHOME/lib/kvstore.jar runadmin \
-helper-hosts host7:5000,host8:5000,host9:5000 \
-store dubstore
# Verify the regions associated with the users MR table
kv-> SHOW TABLE -NAME users
{
"json_version": 1,
"type": "table",
"name": "users",
"regions": {
"1": "dub",
"2": "fra"
"3": "lnd"
},
"fields": [
{
"name": "id",
"type": "INTEGER",
"nullable": false
},
{
"name": "name",
"type": "STRING",
"nullable": true
},
{
"name": "team",
"type": "STRING",
"nullable": true
}
],
"primaryKey": [
"id"
],
"shardKey": [
"id"
]
}
Add New Region to Existing Regions
Steps:
Execute the following steps from each existing region:
- Add the new region as a remote region. See Create Remote Regions.
- Associate the new region with the existing MR Table using the DDL command shown
below.
ALTER TABLE <table name> ADD REGIONS <region name>;
Example:
- Add the new region, Dublin as a remote region from the existing
regions, Frankfurt and
London.
# Connect to the KVStore deployed in the 'fra' region from the SQL shell [~]$java -jar $KVHOME/lib/sql.jar \ -helper-hosts host1:5000,host2:5000,host3:5000 \ -store mrtstore –- Create a remote region 'dub' sql-> CREATE REGION dub; Statement completed successfully – List the regions sql-> SHOW REGIONS; regions fra (local, active) lnd (remote, active) dub (remote, active)
# Connect to the KVStore deployed in the 'lnd' region from the SQL shell [~]$java -jar $KVHOME/lib/sql.jar \ -helper-hosts host4:5000,host5:5000,host6:5000 \ -store mrtstore –- Create a remote region 'dub' sql-> CREATE REGION dub; Statement completed successfully – List the regions sql-> SHOW REGIONS; regions lnd (local, active) fra (remote, active) dub (remote, active)
- In the existing regions, alter the
users
MR Table to add the new region, Dublin.# Connect to the KVStore deployed in the 'fra' region from the SQL shell [~]$java -jar $KVHOME/lib/sql.jar \ -helper-hosts host1:5000,host2:5000,host3:5000 \ -store mrtstore –- Add the 'dub' region to the users MR Table sql-> ALTER TABLE users ADD REGIONS dub; Statement completed successfully # Connect to the KVStore deployed in the 'fra' region from the kv prompt [~]$java -jar $KVHOME/lib/kvstore.jar runadmin \ -helper-hosts host1:5000,host2:5000,host3:5000 \ -store mrtstore # Verify the regions associated with the users MR table kv-> SHOW TABLE -NAME users { "json_version": 1, "type": "table", "name": "users", "regions": { "1": "fra", "2": "lnd" "3": "dub" }, "fields": [ { "name": "id", "type": "INTEGER", "nullable": false }, { "name": "name", "type": "STRING", "nullable": true }, { "name": "team", "type": "STRING", "nullable": true } ], "primaryKey": [ "id" ], "shardKey": [ "id" ] }
# Connect to the KVStore deployed in the 'lnd' region from the SQL shell [~]$java -jar $KVHOME/lib/sql.jar \ -helper-hosts host4:5000,host5:5000,host6:5000 \ -store mrtstore –- Add the 'dub' region to the users MR Table sql-> ALTER TABLE users ADD REGIONS dub; Statement completed successfully # Connect to the KVStore deployed in the 'lnd' region from the kv prompt [~]$java -jar $KVHOME/lib/kvstore.jar runadmin \ -helper-hosts host4:5000,host5:5000,host6:5000 \ -store mrtstore # Verify the regions associated with the users MR table kv-> SHOW TABLE -NAME users { "json_version": 1, "type": "table", "name": "users", "regions": { "1": "lnd", "2": "fra" "3": "dub" }, "fields": [ { "name": "id", "type": "INTEGER", "nullable": false }, { "name": "name", "type": "STRING", "nullable": true }, { "name": "team", "type": "STRING", "nullable": true } ], "primaryKey": [ "id" ], "shardKey": [ "id" ] }
Access MR Table in New and Existing Regions
After performing the tasks discussed in the previous sections, you can perform read/write operations on the MR Table from the new region without any disruption. However, the table may not return the complete data from the existing regions until the initialization completes in the background. Especially if the MR Table has a huge volume of data in the existing regions, it may take a while for the new table to see the data from the remote regions.
Similarly, you can continue performing read/write operations on the MR Table from the existing regions without any disruption. Adding a new region is transparent to the customers accessing the MR Table from the existing regions. However, the MR Table at the existing regions may also need initialization to see the writes from the new region. If the table at the new region is empty or small, the existing regions will quickly sync up with it. To learn how to access the MR Tables, see Access and Manipulate Multi-Region Tables.