Use Case 3: Contract a Multi-Region Table

An organization deploys three on-premise KVStores, one each at Frankfurt, London, and Dublin. As per their requirement, they created a few MR Tables in all three regions. The users table is one of the many MR tables created and maintained by this organization. As per some changes in their business requirements, they decided to remove the users table from the Dublin region. In the next few topics, you learn how to contract an MR Table, that is, how to remove an MR Table from specific regions.

If you have not created the users MR table earlier, execute the steps outlined in Use Case 1: Set up Multi-Region Environment.

If you have not added the Dublin region to the users MR table, execute the steps outlined in Use Case 2: Expand a Multi-Region Table.

Alter MR Table to Drop Regions

Learn how to contract a Multi-Region table and reduce the regions it spans across.

Steps:

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

Execute the following command from the sql prompt.

ALTER TABLE <table name> DROP REGIONS <comma separated list of regions>

Optionally, you can execute the following command from the kv prompt to verify that the region is dropped successfully.
```
SHOW TABLE -NAME <table name>
```

Note:

Suppose you drop region A from an MR table created in region B. Then:

Region B can't see any new writes on this MR table from the region A.
Region A continues to see the writes on this MR Table from the region B.

If you want to isolate the MR table in the region A from other regions, you must drop those regions from the MR table created in region A. This is only a recommendation and not a mandatory step in contracting an MR Table.

Example:

Drop the Dublin region from the users MR table in the other two 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

–- drop the 'dub' region from the 'users' MR table
sql-> ALTER TABLE users DROP REGIONS dub;
Statement completed successfully

# 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

–- drop the 'dub' region from the 'users' MR table
sql-> ALTER TABLE users DROP REGIONS dub;
Statement completed successfully