Example of Using Storage Groups and Table Partitions

Assumptions:

This is our existing domain and the storage groups available. You can see that we have one TE/SM pair running in region us-east-2 and the other TE/SM pair running in region us-west-2. There are two storage groups, ALL and UNPARTITIONED. Both SMs service the UNPARTITIONED storage group but neither services ALL storage groups.

nuodb [domain] > show domain summary
 
Hosts:
[broker] * ec2-54-200-184-21.us-east-2.compute.amazonaws.com/54.200.184.21:48004 (us-east-2) CONNECTED
[broker] ip-172-31-2-230/54.200.185.240:48004 (us-west-2) CONNECTED
 
Database: companydata,(unmanaged), processes [2 TE, 2 SM], ACTIVE
[SM] ip-172-31-5-193/54.200.184.21:48005 (us-east-2) [ pid = 10464 ] [ nodeId = 1 ] RUNNING
[TE] ip-172-31-5-193/54.200.184.21:48006 (us-east-2) [ pid = 10481 ] [ nodeId = 2 ] RUNNING
[SM] ip-172-31-2-230/54.200.185.240:48005 (us-west-2) [ pid = 9525 ] [ nodeId = 3 ] RUNNING
[TE] ip-172-31-2-230/54.200.185.240:48006 (us-west-2) [ pid = 9554 ] [ nodeId = 4 ] RUNNING
 
nuodb [domain] > show storageGroups
Database: companydata
  [id=2] UNPARTITIONED Available
    Serviced by:
    [SM] ip-172-31-5-193/54.200.184.21:48005 (us-east-2) [ pid = 10464 ] [ nodeId = 1 ] RUNNING
    [SM] ip-172-31-2-230/54.200.185.240:48005 (us-west-2) [ pid = 9525 ] [ nodeId = 3 ] RUNNING
 
  [id=1] ALL Available
    Serviced by:
nuodb [domain] > add storageGroup   
Storage group name: sgeast
Host: 54.200.184.21
Process ID: 10464
Success
nuodb [domain] > add storageGroup
Storage group name: sgwest
Host: 54.200.185.240
Process ID: 9525
Success
nuodb [domain] > show storageGroups
Database: companydata
  [id=2] UNPARTITIONED Available
    Serviced by:
    [SM] ip-172-31-5-193/54.200.184.21:48005 (us-east-2) [ pid = 10464 ] [ nodeId = 1 ] RUNNING
    [SM] ip-172-31-2-230/54.200.185.240:48005 (us-west-2) [ pid = 9525 ] [ nodeId = 3 ] RUNNING
 
  [id=3] SGEAST Available
    Serviced by:
    [SM] ip-172-31-5-193/54.200.184.21:48005 (us-east-2) [ pid = 10464 ] [ nodeId = 1 ] RUNNING
 
  [id=4] SGWEST Available
    Serviced by:
    [SM] ip-172-31-2-230/54.200.185.240:48005 (us-west-2) [ pid = 9525 ] [ nodeId = 3 ] RUNNING
 
  [id=1] ALL Available
    Serviced by:

This same scenario can be created using start process commandsby using the storage-group option as in the following commands:

nuodb [domain] > show domain summary
 
Hosts:
[broker] * ec2-54-200-184-21.us-east-2.compute.amazonaws.com/54.200.184.21:48004 (us-east-2) CONNECTED
[broker] ip-172-31-2-230/54.200.185.240:48004 (us-west-2) CONNECTED
 
# Start an SM servicing Storage Group sgeast and a TE on the same host
nuodb [domain] > start process sm
Database: companydata
Host: 52.200.184.21
Process command-line options (optional): storage-group sgeast
Archive directory: /var/opt/nuodb/production-archives/companydata
Initialize archive (optional): true
Started: [SM] ec2-54-200-184-21.us-east-2.compute.amazonaws.com/54.200.184.21:48004 (us-east-2) [ pid = 26779 ] [ db = companydata ] [ nodeId = 1 ] ACTIVE
nuodb [domain/companydata] > start process te
Host: 52.200.184.21
Process command-line options (optional): dba-user dba dba-password dba
Started: [TE] ec2-54-200-184-21.us-east-2.compute.amazonaws.com/54.200.184.21:48004 (us-east-2) [ pid = 26819 ] [ db = companydata ] [ nodeId = 3 ] ACTIVE
 
# Start an SM servicing Storage Group sgwest and a TE on the same host
nuodb [domain/companydata] > start process sm
Host: 54.200.185.240
Process command-line options (optional): storage-group sgwest
Archive directory: /var/opt/nuodb/production-archives/companydata
Initialize archive (optional): true
Started: [SM] ip-172-31-2-230/54.200.185.240:48004 (us-west-2) [ pid = 30231 ] [ db = companydata ] [ nodeId = 5 ] ACTIVE
nuodb [domain/companydata] > start process te
Host: 54.200.185.240
Process command-line options (optional): dba-user dba dba-password dba
Started: [TE] ip-172-31-2-230/54.200.185.240:48004 (us-west-2) [ pid = 30242 ] [ db = companydata ] [ nodeId = 7 ] ACTIVE

Now we can switch to nuosql and create a partitioned table.

SQL> CREATE DOMAIN zipcode CHAR(5);
SQL> CREATE TABLE users (
first STRING, last STRING, zip ZIPCODE )
PARTITION BY RANGE (zip)
(PARTITION p_east VALUES LESS THAN ('70000') STORE IN sgeast
PARTITION p_west VALUES LESS THAN (MAXVALUE) STORE IN sgwest);
SQL> INSERT INTO users VALUES ('Joe','Friendly', '02180');
SQL> INSERT INTO users VALUES ('Nancy','Nike', '97008');

User data for Joe Friendly is stored with the SM running on the machine in region us-east-2 and the user data for Nancy Nike is stored with the SM running on the machine in region us-west-2.

Alternatively, we could have created the same partitioned table by mapping the partition name to the storage group name before creating the table using MAP PARTITION and omitting the STORE IN part of the PARTITION clause:

SQL> CREATE DOMAIN zipcode CHAR(5);
SQL> MAP PARTITION p_east STORE IN sgeast;
SQL> MAP PARTITION p_west STORE IN sgwest;
SQL> CREATE TABLE users (
first STRING, last STRING, zip ZIPCODE )
PARTITION BY RANGE (zip)
(PARTITION p_east VALUES LESS THAN ('70000')
PARTITION p_west VALUES LESS THAN (MAXVALUE));

We can still insert data with a zip code value greater than or equal to '70000', but we can no longer insert data with a zip code less than '70000'.

SQL> INSERT INTO users VALUES ('Stu','Cormier','87020');
SQL> INSERT INTO users VALUES ('Betty','Boop','06430');
While processing exception (insufficient number of storage managers servicing 
storage group to commit transaction. SG: SGEAST, require 1, have 0), exception 
(A system error has occurred which makes the transaction unable to commit) was 
also generated