PostgreSQL How-To: Chinook Sample DB on a Distributed SQL Database
In this post, see how to download and install the PostgreSQL version of the Chinook sample DB on the YugaByte DB distributed SQL database with a replication factor of 3.
Join the DZone community and get the full member experience.
Join For FreeIn this post, we are going to walk you through how to download and install the PostgreSQL version of the Chinook sample DB on the YugaByte DB distributed SQL database with a replication factor of 3.
About the Chinook Sample DB
The Chinook data model represents a digital media store, including tables for artists, albums, media tracks, invoices, and customers. Media-related data was created using real data from an iTunes Library. Customer and employee information was created using fictitious names and addresses that can be located on Google maps and other well-formatted data (phone, fax, email, etc.). Sales information was auto-generated using random data for a four year period. The basic characteristics of Chinook include:
11 tables
A variety of indexes, primary and foreign key constraints
Over 15,000 rows of data
Here’s an ER diagram of the Chinook data model:
Download and Extract YugaByte DB
$ wget https://downloads.yugabyte.com/yugabyte-1.3.1.0-darwin.tar.gz
$ tar xvfz yugabyte-1.3.1.0-darwin.tar.gz && cd yugabyte-1.3.1.0/
Note: The above instructions are for version 1.3.1. To find the latest version of YugaByte DB, visit the quickstart page.
Configure Loopback Addresses
Add a few loopback IP addresses for the various YugaByte DB nodes to use.
sudo ifconfig lo0 alias 127.0.0.2
sudo ifconfig lo0 alias 127.0.0.3
sudo ifconfig lo0 alias 127.0.0.4
sudo ifconfig lo0 alias 127.0.0.5
sudo ifconfig lo0 alias 127.0.0.6
sudo ifconfig lo0 alias 127.0.0.7
Create a 3 Node Cluster
With the command below, you’ll create a 3 node cluster with a replication factor of 3.
$ ./bin/yb-ctl --rf 3 create
Check the Status of the YugaByte DB Cluster
Now let’s take a look at the status of the cluster and all the nodes that comprise it.
$ ./bin/yb-ctl status
As you can see from the output, we have three nodes running locally with a replication factor of 3. This means that every piece of data is being replicated on all three nodes.
Enter the YSQL Shell
Next, run the ysqlsh command to enter the PostgreSQL shell.
$ ./bin/ysqlsh --echo-queries
ysqlsh (11.2)
Type "help" for help.
postgres=#
YSQL is YugaByte DB’s PostgreSQL-compatible, distributed SQL API.
We are now ready to build the Chinook database.
Download and Install the Chinook Database
Download the Chinook Scripts
You can download the Chinook database that is compatible with YugaByte DB from our GitHub repo. The three files are:
chinook_ddl.sql which creates tables and constraints
chinook_genres_artists_albums.sql which loads artist and album information
chinook_songs.sql which loads individual song information
Create the Chinook Database
CREATE DATABASE chinook;
Let’s confirm we have the exercises database by listing out the databases on our cluster.
postgres=# \l
Switch to the chinook database.
postgres=# \c chinook
You are now connected to database "chinook" as user "postgres".
chinook=#
Build the Chinook Tables and Objects
chinook=# \i /Users/yugabyte/chinook_ddl.sql
We can verify that all 11 of our tables have been created by executing:
chinook=# \d
Load Sample Data into Chinook
Next, let’s load our database with sample data.
chinook=# \i /Users/yugabyte/chinook_genres_artists_albums.sql
and
chinook=# \i /Users/yugabyte/chinook_songs.sql
Do a simple SELECT to pull data from the “Track” table to verify we now have some data to play with.
chinook=# SELECT "Name", "Composer" FROM "Track" LIMIT 10;
Explore the Chinook Sample DB
That’s it! You are ready to start exploring the Chinook sample database.
If you are looking for a new database administration and SQL development tool, you can check out DBeaver, which is compatible with YugaByte DB.
Thanks for reading.
Published at DZone with permission of Jimmy Guerrero. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments