Example of Creating Table Partitions and Storage Groups

This example shows the steps to create table partitioning and storage groups for data stored in a database.

Assumptions

The user data is stored in a database called companydata and it is serviced by one Transaction Engine/Storage Manager (TE/SM) pair in California and one TE/SM pair in Massachusetts.
There are two data centers, one in San Diego, California and the other in Cambridge, Massachusetts.
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.

Create a Partitioned Table

Connect to the database using the nuosql command.

Create a partitioned table.

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

Insert data into the partitioned table.
```
INSERT INTO users VALUES ('Joe','Friendly', '02180');
```
```
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.

In this example, if one of the SM stops, 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.

INSERT INTO users VALUES ('Stu','Cormier','87020');

INSERT INTO users VALUES ('Betty','Boop','06430');

insufficient number of Storage Managers servicing storage group to commit transaction. SG: SGEAST, require 1, have 0

Check the Status of the Storage Groups

Review the existing domain.

nuocmd show domain

server version: 6.0-1-fc6a857de9, server license: Enterprise
server time: 2023-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.

For more information on nuocmd and other command-line tools, see Command Line Tools.

Display the archives associated with the companydata database.

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.

Check the status of the storage groups connected to the database.

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.

Configure Storage Groups to an Archive

Configure our domain so that one archive is associated with the storage group sgwest and the other archive is associated with the storage group sgeast.

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

Check the status of the storage groups.

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.