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" : 2,
  "type" : "table",
  "name" : "users",
  "id" : "61",
  "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"]
}

# 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.

You can use the MR_COUNTER data type in a schema-less JSON field, which means if your Multi-Region table has a JSON column, you can use MR_COUNTER data type inside the JSON column. One or more fields in the JSON column can be of MR_COUNTER data type. The MR_COUNTER data type is a subtype of the INTEGER or LONG or NUMBER data type.

Example:

CREATE TABLE demoJSONMR(name STRING,
  jsonWithCounter JSON(counter as INTEGER MR_COUNTER,
                       person.count as LONG MR_COUNTER),
  PRIMARY KEY(name)) IN REGIONS fra,lnd;