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.

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.

Connect to the kv prompt, and connect to the local KVStore.

Execute the following command from the kv prompt.

kv-> execute 'alter table <table name> drop regions <comma separated region names>'

Optionally, you can execute the following command to verify that the region is dropped successfully.
```
kv-> show table -name <table name>
```
Note:
After dropping Dublin from the Users table in Frankfurt and London, those regions will not see any writes on the Users table from Dublin. However, the Users table in Dublin continues to see writes from the other two regions, that is, Frankfurt and London. If you want to isolate the Users table in the Dublin region from the other two regions, you must drop those regions, that is, Frankfurt and London from the Users table in Dublin. However, this is only a recommendation but not a mandatory step in contracting the Users MR Table.

Dropping the Dublin (DUB) region from the Users MR Table (from all the other regions, that is, Frankfurt and London):

kv-> execute 'alter table Users drop regions DUB'

Verifying that the DUB region is dropped from the Users table in the Frankfurt (FRA) region:

kv-> show table -name Users 
{ 
  "json_version" : 1,
   "type" : "table",
   "name" : "Users",
   "shardKey" : [ "id" ],
   "primaryKey" : [ "id" ],
   "fields" : [ {
     "name" : "id",
     "type" : "INTEGER",
     "nullable" : false,
     "default" : null
   }, {
     "name" : "name",
     "type" : "STRING",
     "nullable" : true,
     "default" : null
   }, {
     "name" : "team",
     "type" : "STRING",
     "nullable" : true,
     "default" : null
   } ],
   "regions" : {
     "2" : "LND",
     "1" : "FRA"
   }
 }

Verifying that the DUB region is dropped from the Users table in the London (LND) region:

kv-> show table -name Users 
{ 
  "json_version" : 1, 
  "type" : "table", 
  "name" : "Users", 
  "shardKey" : [ "id" ], 
  "primaryKey" : [ "id" ], 
  "fields" : [ { 
    "name" : "id", 
    "type" : "INTEGER", 
    "nullable" : false, 
    "default" : null 
  }, { 
    "name" : "name", 
    "type" : "STRING", 
    "nullable" : true,
     "default" : null
   }, {
     "name" : "team",
     "type" : "STRING",
     "nullable" : true,
     "default" : null
   } ],
   "regions" : {
     "2" : "FRA",
     "1" : "LND"
   }
 }