At NuoDB, we’ve not only been working hard to improve upon our distributed SQL database engine, but also to make it easier to adopt NuoDB into CI/CD pipelines. NuoDB is a ANSI standard and ACID compliant SQL database that is suitable for all common deployment models, such as bare-metal, virtual machines, cloud and hybrid-cloud, and most recently container-based environments, such as Red Hat OpenShift Container Platform. At its core, NuoDB is designed to be highly scalable and fault-tolerant with the ability to load-balance across a distributed architecture.
If you would like to try NuoDB’s Community Edition (CE) in Docker, we have made available a NuoDB Docker container running the latest version of NuoDB. The container image is available on Docker Hub, and we’ll be continually making updates to improve ease of deployment into every cloud and container-based environment.
The NuoDB Community Edition (CE) is a full featured version of NuoDB, but limited to one Storage Manager (SM) and three Transaction Engine (TE) processes. The Community Edition is free of charge and allows users to try NuoDB at their own pace. For more information on each available deployment environment see the NuoDB Quick Start Guides in our documentation.
More about NuoDB Before We Get Started…
If you’re not familiar with NuoDB’s architecture, take a read through our architectural blog.
The NuoDB architecture distributes administrative and database processing across three separate components. Separating the Admin and database functions allows NuoDB to utilize services like AWS ’Auto Scaling Groups or Kubernetes’ autoscaling Pods to increase throughput and high availability of NuoDB’s Transaction Engines (TEs) and Storage Managers (SMs) processes. Because of this, in cloud-native environments, we recommend each NuoDB process run in its own container.
Each NuoDB container doesn’t have to be located on the same host, but will need to be on the same overlay network in order to communicate with each other. Alternatively, you can map host ports to the container to connect over the larger network. We also recommend placing the TE container on the same host as your application. This allows your application to communicate locally with your cached database with no network hops in between.
NuoDB processes:
- Admin Services are responsible for managing the domain and database state. The Admin service is also responsible for the load balancing function that connects client applications to NuoDB TE processes.
- Storage Managers (SMs) are responsible for ensuring on-disk data durability and replicating changes to new started SMs. Each SM is managed by a single Admin service.
- Transaction Engines (TEs) are responsible for connecting client applications to the database and processing SQL transactions. They also perform in-memory data caching of data tables to optimize SQL performance. Each TE is managed by a single Admin service.
Getting Started with NuoDB in Docker
Now that you have a good fundamental understanding of NuoDB architecture, let’s move on to deploying your first NuoDB database in Docker using docker run which will demonstrate the practical fundamentals of how to deploy a NuoDB database.
Note: if you are already using Docker Compose and would like to fast track to learn how to deploy NuoDB using docker-compose, please jump to Running NuoDB in Docker – Part 4: Using Docker Compose and revisit Docker blogs 2 and 3 later to learn more about visual monitoring and how to use common SQL execution GUI tools with NuoDB.
Step 1: Install Docker into your RHEL/CentOS, Ubuntu or MacOS environment
This demo is designed to run the NuoDB CE on a single server or desktop computer to demonstrate the value and benefits of using NuoDB. I’m using a MacBook and installed Docker from Docker for MacOS.
NOTE: You may need to configure Docker to allow your user account to run the docker command without requiring the sudo pre-fix.
Step 2: Pull a copy of the NuoDB container image
Once Docker is installed, pull the NuoDB CE container image into your local Docker repo. This command will take several minutes to complete.
docker pull nuodb/nuodb-ce:latest
Step 3: Create a Docker Network
docker network create nuodb-net
Step 4: Start the Admin Service
Let’s get NuoDB fired up by starting several NuoDB Admin services. An Admin service (and its peers) is the single fixed point for NuoDB and its domain administrative tier. When the SM and TE processes start, they need to know the name of the Admin Service process they are starting on.
From a command line, run the following command:
docker run -d --name nuoadmin1 --hostname nuoadmin1 --network nuodb-net --publish 8888:8888 --volume nuoadmin-raft-1:/var/opt/nuodb --env NUODB_DOMAIN_ENTRYPOINT=nuoadmin1 nuodb/nuodb-ce:latest nuoadmin
To create a fault tolerant admin management tier, create two more Admin services by running the following commands:
docker run -d --name nuoadmin2
--hostname nuoadmin2
--network nuodb-net
--publish 8889:8888
--volume nuoadmin-raft-2:/var/opt/nuodb
--env NUODB_DOMAIN_ENTRYPOINT=nuoadmin1
nuodb/nuodb-ce:latest nuoadmin
docker run -d --name nuoadmin3
--hostname nuoadmin3
--network nuodb-net
--publish 8890:8888
--volume nuoadmin-raft-3:/var/opt/nuodb
--env NUODB_DOMAIN_ENTRYPOINT=nuoadmin1
nuodb/nuodb-ce:latest nuoadmin
Note: When creating additional Admin Services, the first port number in the –publish argument increments by 1. By example your second Admin Service will have the value 8889:8888. Also, the NUODB_DOMAIN_ENTRYPOINT variable must remain nuoadmin1! This value indicates the leader Admin Service that other Admin services will peer into to join the NuoDB domain.
Following the example, go ahead and create a third Admin service. To confirm your NuoDB Admin service(s) were created successfully and are active and connected, type the commands highlighted in bold text.
$ docker exec -it nuoadmin1 nuocmd show domain server version: 4.0.4-2-019a14f800, server license: Community server time: 2020-01-22T19:33:24.566, client token: bd4dafec9dff90867aa61c73a55b6b32d4013868 Servers: [nuoadmin1] nuoadmin1:48005 (LEADER, Leader=nuoadmin1) ACTIVE:Connected * [nuoadmin2] nuoadmin2:48005 (FOLLOWER, Leader=nuoadmin1) ACTIVE:Connected [nuoadmin3] nuoadmin3:48005 (FOLLOWER, Leader=nuoadmin1) ACTIVE:Connected Databases:
Step 5: Start the database Storage Manager (SM)
To start a NuoDB database, start a Storage Manager (SM) process by running the following command. This command will create a database named test with database login credentials of user dba and password goalie.
docker run -d --name test-sm-1
--hostname test-sm-1
--network nuodb-net
--volume test-arch-vol-1:/var/opt/nuodb
nuodb/nuodb-ce:latest nuodocker
--api-server nuoadmin1:8888
start sm --db-name test
--server-id nuoadmin1
--dba-user dba
--dba-password goalie
Note: We have created our Admin services and SM processes with persistent storage volumes by using the –volume argument on our Docker run commands. We highly recommend using persistent storage volumes to ensure that if a container is deleted or removed (for any reason), then the next time a replacement container starts it will find the already existing storage volume and reuse it — picking up where it left off last. In the case of NuoDB, the newly started Admin service or Storage Manager container will sync with an already active process of its same type; if available, before the storage is actively used again by NuoDB.
Step 6: Start a database Transaction Engine (TE)
Start a Transaction Engine (TE) process by running the following command. The use of the –labels argument is optional. The use of database process labels allows a NuoDB Admin process to assign a client user application a TE that matches a desired label name.
docker run -d --name test-te-1
--hostname test-te-1
--network nuodb-net
nuodb/nuodb-ce:latest nuodocker
--api-server nuoadmin1:8888
start te --db-name test
--server-id nuoadmin1
--labels "te te1"
Go ahead and scale-out your database by starting up another TE by rerunning the above command replacing the highlighted 1 values with 2. Following the example, go ahead and create a third TE.
To confirm your NuoDB database has been created properly, type:
$ docker exec -it nuoadmin1 nuocmd show domain
server version: 4.0.4-2-019a14f800, server license: Community
server time: 2020-01-22T19:43:05.116, client token: 131e645a39b2be1535f51d043db0aaa4d5435a47
Servers:
[nuoadmin1] nuoadmin1:48005 (LEADER, Leader=nuoadmin1) ACTIVE:Connected *
[nuoadmin2] nuoadmin2:48005 (FOLLOWER, Leader=nuoadmin1) ACTIVE:Connected
[nuoadmin3] nuoadmin3:48005 (FOLLOWER, Leader=nuoadmin1) ACTIVE:Connected
Databases:
test [RUNNING]
[SM] test-sm-1/192.168.0.5:48006 (Default) [start_id = 0] [server_id = nuoadmin1] [pid = 42] [node_id = 1] MONITORED:RUNNING
[TE] test-te-3/192.168.0.8:48006 (Default) [start_id = 1] [server_id = nuoadmin3] [pid = 42] [node_id = 4] MONITORED:RUNNING
[TE] test-te-1/192.168.0.6:48006 (Default) [start_id = 2] [server_id = nuoadmin1] [pid = 41] [node_id = 2] MONITORED:RUNNING
[TE] test-te-2/192.168.0.7:48006 (Default) [start_id = 3] [server_id = nuoadmin2] [pid = 42] [node_id = 3] MONITORED:RUNNING
Running SQL
Now that you have a running NuoDB database, let’s run some SQL statements! You can do this easily by installing the NuoDB sample “hockey” database.
Open a terminal window to your nuoadmin1 container:
docker exec -it nuoadmin1 bash
You are now connected to the nuoadmin1 container. Your command prompt will change to something like:
bash-4.2$
Then, run the following commands:
nuosql test@nuoadmin1 --schema hockey --user dba --password goalie </opt/nuodb/samples/quickstart/sql/create-db.sql >& /dev/null nuosql test@nuoadmin1 --schema hockey --user dba --password goalie </opt/nuodb/samples/quickstart/sql/Players.sql >& /dev/null nuosql test@nuoadmin1 --schema hockey --user dba --password goalie </opt/nuodb/samples/quickstart/sql/Teams.sql >& /dev/null nuosql test@nuoadmin1 --schema hockey --user dba --password goalie </opt/nuodb/samples/quickstart/sql/Scoring.sql >& /dev/null
Done. You now have a sample hockey player and team database! You can run any SQL you’d like against the hockey schema.
Here are a few examples. First, log into the database using the NuoDB SQL run tool nuosql.
SQL Exercise 1.
bash-4.2$ nuosql test@nuoadmin1 --schema hockey --user dba --password goalie
SQL> use hockey;
SQL> show tables;
Tables in schema HOCKEY
HOCKEY
PLAYERS
SCORING
TEAMS
VW_PLAYER_STATS is a view
SQL> select /* top 10 goal scorers in single season */
firstname, lastname, birthyear, s.year "YEAR PLAYED", t.name "TEAM", s.goals
from scoring s, players p, teams t
where s.playerid = p.playerid
and s.year = t.year
and s.teamid = t.teamid
order by s.goals desc
limit 10;
FIRSTNAME LASTNAME BIRTHYEAR YEAR PLAYED TEAM GOALS
---------- --------- ---------- ------------ ------------------- ------
Wayne Gretzky 1961 1981 Edmonton Oilers 92
Wayne Gretzky 1961 1983 Edmonton Oilers 87
Brett Hull 1964 1990 St. Louis Blues 86
Mario Lemieux 1965 1988 Pittsburgh Penguins 85
Bobby Hull 1939 1974 Winnipeg Jets 77
Phil Esposito 1942 1970 Boston Bruins 76
Alexander Mogilny 1969 1992 Buffalo Sabres 76
Teemu Selanne 1970 1992 Winnipeg Jets 76
Real Cloutier 1956 1978 Quebec Nordiques 75
Wayne Gretzky 1961 1984 Edmonton Oilers 73
SQL> select /* top all-time cummulative goal scorers */
firstname, lastname, birthyear,
count(s.year) "YEARS PLAYED", round(avg(s.goals),2) "AVG GOALS/YR", sum(s.goals) "TOT GOALS"
from scoring s, players p, teams t
where s.playerid = p.playerid
and s.year = t.year
and s.teamid = t.teamid
group by firstname, lastname, birthyear
order by sum(s.goals) desc
limit 10;
FIRSTNAME LASTNAME BIRTHYEAR YEARS PLAYED AVG GOALS/YR TOT GOALS
---------- --------- ---------- ------------- ------------- ----------
Gordie Howe 1928 32 30.47 975
Wayne Gretzky 1961 23 40.87 940
Bobby Hull 1939 24 38.04 913
Brett Hull 1964 21 35.29 741
Mike Gartner 1959 23 31.96 735
Marcel Dionne 1951 19 38.47 731
Phil Esposito 1942 19 37.74 717
Mark Messier 1961 27 25.74 695
Steve Yzerman 1965 22 31.45 692
Mario Lemieux 1965 17 40.59 690
SQL>
SQL Exercise 2.
NuoDB supports ANSI standard SQL and, additionally, many string, date, and arithmetic functions found in other vendors’ SQL RDBMS products. Have fun and try some of your own SQL.
Here is an example of the first SQL statement above rewritten using ANSI SQL 92 syntax. Run this SQL statement to confirm the SQL results are exactly the same as the first SQL statement.
SQL> select /* top goal scorers in single season */ firstname, lastname, birthyear, s.year "YEAR PLAYED", t.name "TEAM", s.goals from scoring s join players using (playerid) join teams t using (year,teamid) order by s.goals desc Limit 10;
SQL Exercise 3.
When you connect to the database using the nuosql command, by default the connection to obtain a Transaction Engine (TE) is made via the default round robin load balancer. Here is how you can override that if you would like to connect to a specific TE based on label value. Recall, when we created our TEs, we assigned them each specific labels!
Try this command:
bash-4.2$ nuosql test@nuoadmin1 --user dba --password goalie --connection-property "LBQuery=round_robin(first(label(te te1) any))" SQL> select nodeid, clientinfo, created from system.connections; NODEID CLIENTINFO CREATED ------- ----------- -------------------------- 2 nuosql 2018-12-07 18:37:02.671364 SQL>
Notice the nuosql command specifies to connect first to a TE labeled te1 and if it doesn’t find one, then just connect to “any” TE.
The SELECT query shows that the nuosql session connected to nodeid 2. If you run the nuocmd show domain command, you will see (in this example) that TE1 is in fact node 2 in the NuoDB domain of database processes as demonstrated below, see the blue highlights.
bash-4.2$ nuocmd show domain
server version: 3.3-1-14df3bb390, server license: Community
server time: 2018-12-07T19:06:58.749, client token: 7b51af9937f32efbdbc1e800e04251276751acd5
Servers:
[nuoadmin1] nuoadmin1:48005 (LEADER, Leader=nuoadmin1) ACTIVE:Connected *
[nuoadmin2] nuoadmin2:48005 (FOLLOWER, Leader=nuoadmin1) ACTIVE:Connected
[nuoadmin3] nuoadmin3:48005 (FOLLOWER, Leader=nuoadmin1) ACTIVE:Connected
Databases:
test [RUNNING]
[SM] test-sm-1/192.168.0.5:48006 (Default) [start_id = 0] [server_id = nuoadmin1] [pid = 42] [node_id = 1] MONITORED:RUNNING
[TE] test-te-3/192.168.0.8:48006 (Default) [start_id = 1] [server_id = nuoadmin3] [pid = 42] [node_id = 4] MONITORED:RUNNING
[TE] test-te-1/192.168.0.6:48006 (Default) [start_id = 2] [server_id = nuoadmin1] [pid = 41] [node_id = 2] MONITORED:RUNNING
[TE] test-te-2/192.168.0.7:48006 (Default) [start_id = 3] [server_id = nuoadmin2] [pid = 42] [node_id = 3] MONITORED:RUNNING
You can try this yourself using the same nuosql command, but instead connect directly to either TE test-te-2 or test-te-3 using the correct label specification, either te2 or te3.
When done, type exit to exit from the nuoadmin1 container.
bash-4.2$ exitHow to Shutdown and Restart NuoDB
To shutdown and restart your database, just issue the NuoDB shutdown database command, remove the database SM and TE containers, and recreate them. This command will remove the database containers.
docker exec -it nuoadmin1 nuocmd shutdown database --db-name test docker rm -f test-sm-1 test-te-1 test-te-2 test-te-3
To prove the database has been shutdown and is now in a non-running state, run:
docker exec -it nuoadmin1 nuocmd show domain
To startup the database again, rerun the same docker run commands listed earlier to create a new SM container and three new TE containers. When the new SM process starts, it will discover the already existing storage volume test-arch-vol-1 and mount it. Thus, your database will start from where it left off last. To demonstrate this, log into the NuoDB SQL tool nuosql as you did earlier. You will notice your sample hockey schema and its data will still be there.
Cleaning Up – Removing Your Database
When working with containers, cleanup is easy. You can simply remove your database by deleting your NuoDB containers, storage volumes, and the Docker network, nuodb-net.
docker rm -f nuoadmin1 nuoadmin2 nuoadmin3 docker rm -f test-sm-1 test-te-1 test-te-2 test-te-3 docker volume rm -f nuoadmin-raft-1 nuoadmin-raft-2 nuoadmin-raft-3 docker volume rm -f test-arch-vol-1 docker network rm nuodb-net
Next Steps
Ready for more from this series? Check out the rest of the NuoDB in Docker blogs:
As Senior Product Manager at NuoDB, Joe drives NuoDB product releases and roadmap to continue NuoDB's cloud-native distributed SQL database leadership position which delivers scale-out and continuous availability for Hybrid Cloud Applications. Joe has over 25 years of experience delivering database products and management tools in the transactional and analytical database market place.