Example of Using Storage Groups and Table Partitions

Assumptions:

We have a group of users in our data and that they all live in the United States.
We have two data centers, one in San Diego, CA and the other in Cambridge, MA.
We’ve decided, for locality of our data, that we will store user data in one data center or the other, based on the user’s zip code.
Assume we have a database called companydata and it is serviced by one TE/SM pair in CA and one TE/SM pair in MA.

To review the existing domain and the storage groups available, run the show domain command.

The the show domain command is issued using NuoDB Command (nuocmd). For more information on NuoDB Command and other command line tools, see Command Line Tools.

nuocmd show domain
server version: 4.0.rel40dev-40-142e911b90, server license: Enterprise
server time: 2019-07-03T16:03:36.713, client token: ef8a25e299d3b031702629982b88abb4b3593e7d
Servers:
  [r0db0] 172.31.32.99:48005 [last_ack = 9.97] [member = ADDED] [raft_state = ACTIVE] (LEADER, Leader=r0db0, log=4/34/34) Connected *
  [r0db1] 172.31.37.71:48005 [last_ack = 9.97] [member = ADDED] [raft_state = ACTIVE] (FOLLOWER, Leader=r0db0, log=4/34/34) Connected
  [r1db0] 172.31.31.231:48005 [last_ack = 9.97] [member = ADDED] [raft_state = ACTIVE] (FOLLOWER, Leader=r0db0, log=4/34/34) Connected
  [r1db1] 172.31.27.89:48005 [last_ack = 9.97] [member = ADDED] [raft_state = ACTIVE] (FOLLOWER, Leader=r0db0, log=4/34/34) Connected
Databases:
  companydata [state = RUNNING]
    [SM] 3a835d43-f771-4bed-a56c-b13498e0dd99-r0db0/172.31.32.99:48006 [start_id = 0] [server_id = r0db0] [pid = 17196] [node_id = 4] [last_ack =  0.06] MONITORED:RUNNING
    [SM] 3a835d43-f771-4bed-a56c-b13498e0dd99-r1db0/172.31.31.231:48006 [start_id = 1] [server_id = r1db0] [pid = 16960] [node_id = 1] [last_ack =  2.06] MONITORED:RUNNING
    [TE] 3a835d43-f771-4bed-a56c-b13498e0dd99-r0db1/172.31.37.71:48006 [start_id = 2] [server_id = r0db1] [pid = 16948] [node_id = 2] [last_ack =  9.07] MONITORED:RUNNING
    [TE] 3a835d43-f771-4bed-a56c-b13498e0dd99-r1db1/172.31.27.89:48006 [start_id = 3] [server_id = r1db1] [pid = 16950] [node_id = 3] [last_ack =  8.09] MONITORED:RUNNING

This is our existing domain and the storage groups available. You can see that we have two separate TE/SM pairs. There are two storage groups, ALL and UNPARTITIONED. There are two archives associated with the Companydata database. Both archives service the UNPARTITIONED storage group but neither services ALL storage group.

nuocmd show domain
server version: 4.0.rel40dev-40-142e911b90, server license: Enterprise
server time: 2019-07-03T16:03:36.713, client token: ef8a25e299d3b031702629982b88abb4b3593e7d
Servers:
  [r0db0] 172.31.32.99:48005 [last_ack = 9.97] [member = ADDED] [raft_state = ACTIVE] (LEADER, Leader=r0db0, log=4/34/34) Connected *
  [r0db1] 172.31.37.71:48005 [last_ack = 9.97] [member = ADDED] [raft_state = ACTIVE] (FOLLOWER, Leader=r0db0, log=4/34/34) Connected
  [r1db0] 172.31.31.231:48005 [last_ack = 9.97] [member = ADDED] [raft_state = ACTIVE] (FOLLOWER, Leader=r0db0, log=4/34/34) Connected
  [r1db1] 172.31.27.89:48005 [last_ack = 9.97] [member = ADDED] [raft_state = ACTIVE] (FOLLOWER, Leader=r0db0, log=4/34/34) Connected
Databases:
  companydata [state = RUNNING]
    [SM] 3a835d43-f771-4bed-a56c-b13498e0dd99-r0db0/172.31.32.99:48006 [start_id = 0] [server_id = r0db0] [pid = 17196] [node_id = 4] [last_ack =  0.06] MONITORED:RUNNING
    [SM] 3a835d43-f771-4bed-a56c-b13498e0dd99-r1db0/172.31.31.231:48006 [start_id = 1] [server_id = r1db0] [pid = 16960] [node_id = 1] [last_ack =  2.06] MONITORED:RUNNING
    [TE] 3a835d43-f771-4bed-a56c-b13498e0dd99-r0db1/172.31.37.71:48006 [start_id = 2] [server_id = r0db1] [pid = 16948] [node_id = 2] [last_ack =  9.07] MONITORED:RUNNING
    [TE] 3a835d43-f771-4bed-a56c-b13498e0dd99-r1db1/172.31.27.89:48006 [start_id = 3] [server_id = r1db1] [pid = 16950] [node_id = 3] [last_ack =  8.09] MONITORED:RUNNING

nuocmd show archives
[0] r0db0 : /var/opt/nuodb/production-archives/companydata @ companydata [journal_path = ] [snapshot_archive_path = ] RUNNING
[SM] 3a835d43-f771-4bed-a56c-b13498e0dd99-r0db0/172.31.32.99:48006 [start_id = 0] [server_id = r0db0] [pid = 17196] [node_id = 4] [last_ack =  5.81] MONITORED:RUNNING
[1] r1db0 : /var/opt/nuodb/production-archives/companydata @ companydata [journal_path = ] [snapshot_archive_path = ] RUNNING
[SM] 3a835d43-f771-4bed-a56c-b13498e0dd99-r1db0/172.31.31.231:48006 [start_id = 1] [server_id = r1db0] [pid = 16960] [node_id = 1] [last_ack =  9.97] MONITORED:RUNNING

nuocmd get storage-groups --db-name companydata
StorageGroup(archive_states={}, db_name=companydata, id=1, leader_candidates=[], name=ALL, process_states={}, state=Available)
StorageGroup(archive_states={}, db_name=companydata, id=2, leader_candidates=[0, 1], name=UNPARTITIONED, process_states={1: RUNNING, 0: RUNNING}, state=Available)

First we need to configure our domain such that one archive is associated with the storage group sgwest and the other archive is associated with the storage group sgeast.Then the get storage-groups command shows that we still have the two archives servicing UNPARTITIONED and now the archive 0 SM services a storage group called sgeast and the archive 1 SM services a storage group called sgwest.

nuocmd add storage-group --db-name companydata --sg-name sgeast --archive-id 0

nuocmd add storage-group --db-name companydata --sg-name sgwest --archive-id 1

nuocmd get storage-groups --db-name companydata
StorageGroup(archive_states={}, db_name=companydata, id=1, leader_candidates=[], name=ALL, process_states={}, state=Available)
StorageGroup(archive_states={0: ADDED}, db_name=companydata, id=10, leader_candidates=[0], name=SGEAST, process_states={0: RUNNING}, state=Available)
StorageGroup(archive_states={1: ADDED}, db_name=companydata, id=11, leader_candidates=[1], name=SGWEST, process_states={1: RUNNING}, state=Available)
StorageGroup(archive_states={}, db_name=companydata, id=2, leader_candidates=[0, 1], name=UNPARTITIONED, process_states={1: RUNNING, 0: RUNNING}, state=Available)

Now we can switch to NuoDB SQL 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 using archive 0 and the user data for Nancy Nike is stored with the SM running using archive 1.

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));
SQL> INSERT INTO users VALUES ('Joe','Friendly', '02180');
SQL> INSERT INTO users VALUES ('Nancy','Nike', '97008');

In this example, if for any reason one of the SMs is stopped, the database remains in a RUNNING state. For example, if the SM using archive 0 is stopped, it is still possible to 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