Create multi-region table with an MR_COUNTER column

You can create a multi-region table containing a column of MR_COUNTER datatype. MR_COUNTER datatype is used in such situations to take care of conflict resolution that may arise when the same data is modified across different regions. MR_COUNTER ensures that though data modifications happen simultaneously in different regions, the data can always be merged into a consistent state. This merge is performed automatically by the MR_COUNTER data type without requiring any special conflict resolution code or user intervention. To learn more about MR_COUNTER datatype, see Using CRDT datatype in a multi-region table section in the Concepts Guide.

Example:

Create an MR Table called users with a MR_COUNTER datatype in both the regions, Frankfurt and London.

-- Create the users MR Table
sql-> CREATE TABLE users(
   -> id INTEGER,
   -> name STRING,
   -> team STRING,
   -> count INTEGER AS MR_COUNTER,
   -> PRIMARY KEY (id))
   -> IN REGIONS fra,lnd;
Statement completed successfully
# Verify the regions associated with the users MR table
sql-> DESC AS JSON TABLE users
{
   "json_version": 1,
   "type": "table",
   "name": "users",
   "regions": {
      "1": "fra",
      "2": "lnd"
   },
   "fields": [
   {
      "name": "id",
      "type": "INTEGER",
      "nullable": false
   },
   {
      "name": "name",
      "type": "STRING",
      "nullable": true
   },
   {
       "name": "team",
       "type": "STRING",
       "nullable": true
   },
   {
       "name" : "count",
       "type" : "INTEGER",
       "nullable" : false,
       "default" : 0,
       "MRCounter" : 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
-- Create the users MR Table
sql-> CREATE TABLE users(
   -> id INTEGER,
   -> name STRING,
   -> team STRING,
   -> count INTEGER AS MR_COUNTER,
   -> PRIMARY KEY (id))
   -> IN REGIONS lnd,fra;
Statement completed successfully
# Verify the regions associated with the users MR table
sql-> DESC AS JSON TABLE users
{
   "json_version": 1,
   "type": "table",
   "name": "users",
   "regions": {
      "2": "fra",
      "1": "lnd"
   },
   "fields": [
   {
      "name": "id",
      "type": "INTEGER",
      "nullable": false
   },
   {
      "name": "name",
      "type": "STRING",
      "nullable": true
   },
   {
       "name": "team",
       "type": "STRING",
       "nullable": true
   },
   {
        "name" : "count",
        "type" : "INTEGER",
        "nullable" : false,
        "default" : 0,
        "MRCounter" : true
   }
],
   "primaryKey": [
       "id"
   ],
   "shardKey": [
       "id"
   ]
}

To know more details about how to create and use an MR_COUNTER datatype, See Using the MR_COUNTER datatype section in the SQL Reference Guide.