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