Previous Next JavaScript must be enabled to correctly display this content

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

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;