Example of Using Storage Groups and Table Partitions

Assumptions:

The user data is stored in a database called companydata and it is serviced by one Transacytion Engine/Storage Manager (TE/SM) pair in CA and one TE/SM pair in MA.
There are two data centers, one in San Diego, CA and the other in Cambridge, MA.
The user data is stored in the data center, based on the user’s zip code.
The users are residents of the United States of America.

Steps for Managing Storage Groups and Table Partitioning

To review the existing domain, run the show domain command.

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

The database is configured using two separate TE and SM pairs.

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.

To display the archives associated with the companydata database, run the show archive command.

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

There are two archives associated with the companydata database.

To check the status of the storage groups connected to the database, run get storage-groups command.

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)

Both the archives serve only the UNPARTITIONED storage group and not the ALL storage group as indicated by the process_states={1: RUNNING, 0: RUNNING} attribute listed on the UNPARTITIONED storage group.

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, run add storage-group command.
```
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
```

To check the status of the storage groups, run the get storage-groups command.

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)

Both the archives service UNPARTITIONED storage group. The archive 0 SM services a storage group sgeast and the archive 1 SM services a storage group sgwest.

Switch to NuoDB SQL using nuosql command and create a partitioned table.

SQL> CREATE TABLE users (first STRING, last STRING, zip CHAR(5) ) 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, you can create 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> MAP PARTITION p_east STORE IN sgeast;
SQL> MAP PARTITION p_west STORE IN sgwest;
SQL> CREATE TABLE users (first STRING, last STRING, zip CHAR(5) ) 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 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 you 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');
insufficient number of Storage Managers servicing storage group to commit transaction. SG: SGEAST, require 1, have 0