Create Multi-Region Tables
You must create an MR Table on each KVStore in the connected graph,
and specify the list of regions that the table should span. For the use case under discussion,
you must create the
users
table as an MR Table at both the regions, in any
order.
Steps:
To create an MR Table:
- To create a table definition, use a
CREATE TABLE
statement. See Create Table in the SQL Reference Guide. - Optionally, you can verify the regions associated with the MR Table by executing
the following command from the
kv
prompt.kv-> SHOW TABLE -NAME <table name>
Example:
Create an MR Table called users
in both the regions, Frankfurt and
London.
# Connect to the KVStore deployed in the 'fra' region from the SQL shell
[~]$java -jar $KVHOME/lib/sql.jar \
-helper-hosts host1:5000,host2:5000,host3:5000 \
-store mrtstore
-- Create the users MR Table
sql-> CREATE TABLE users(
-> id INTEGER,
-> name STRING,
-> team STRING,
-> PRIMARY KEY (id))
-> IN REGIONS fra,lnd;
Statement completed successfully
# Connect to the KVStore deployed in the 'fra' region from the kv prompt
[~]$java -jar $KVHOME/lib/kvstore.jar runadmin \
-helper-hosts host1:5000,host2:5000,host3:5000 \
-store mrtstore
# Verify the regions associated with the users MR table
kv-> SHOW TABLE -NAME 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
}
],
"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,
-> PRIMARY KEY (id))
-> IN REGIONS lnd,fra;
Statement completed successfully
# Connect to the KVStore deployed in the 'lnd' region from the kv prompt
[~]$java -jar $KVHOME/lib/kvstore.jar runadmin \
-helper-hosts host4:5000,host5:5000,host6:5000 \
-store mrtstore
# Verify the regions associated with the users MR table
kv-> SHOW TABLE -NAME 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
}
],
"primaryKey": [
"id"
],
"shardKey": [
"id"
]
}