{{announcement.body}}
{{announcement.title}}

Getting Started With SQLPad and Distributed SQL on Google Kubernetes Engine

DZone 's Guide to

Getting Started With SQLPad and Distributed SQL on Google Kubernetes Engine

See how to install a 3 node YugabyteDB cluster on Google Kubernetes Engine, build the sample Northwind database, build and configure SQLPad, and more!

· Database Zone ·
Free Resource

SQLPad is an MIT licensed web app written in React and Node.js for writing and running SQL queries and visualizing the results. SQLPad supports PostgreSQL, MySQL, SQL Server, Crate, Vertica, Presto, SAP HANA, Cassandra, Snowflake, Google BigQuery, SQLite, and many more via ODBC. Because YugabyteDB is PostgreSQL compatible, most third-party tools and apps will work “out of the box.” SQLPad is no exception here.

In this blog post we’ll show you how to:

  • Install a 3 node YugabyteDB cluster on Google Kubernetes Engine
  • Build the sample Northwind database
  • Build and configure SQLPad
  • Start the required SQLPad processes
  • Launch the SQLPad UI and issue a test query to validate the deployment

New to distributed SQL or YugabyteDB? Read on.

What Is Distributed SQL?

Distributed SQL databases are becoming popular with organizations interested in moving data infrastructure to the cloud or to cloud native environments. This is often motivated by the desire to reduce TCO or move away from the scaling limitations of monolithic RDBMS like Oracle, MySQL, and SQL Server. The basic characteristics of Distributed SQL are:

  • A SQL API for querying and modeling data, with support for traditional RDBMS features like primary keys, foreign keys, indexes, stored procedures and triggers.
  • Automatic distributed query execution so that no single node becomes a bottleneck.
  • A distributed SQL database should support automatically distributed data storage. This includes indexes which should be automatically distributed (aka sharded) across multiple nodes of the cluster so that no single node becomes a bottleneck for ensuring high performance and high availability.
  • Distributed SQL systems should also provide for strongly consistent replication and distributed ACID transactions.

For a deeper discussion about what Distributed SQL is, check out, “What is Distributed SQL?”

What Is YugabyteDB?

YugabyteDB is an open source, high-performance distributed SQL database built on a scalable and fault-tolerant design inspired by Google Spanner. YugabyteDB is PostgreSQL wire compatible with support for advanced RDBMS features like stored procedures, triggers, and UDFs.

Step 1: Install YugabyteDB on a GKE Cluster using Helm 3

In this section we are going to install YugabyteDB on the cluster. The complete steps are documented here. We’ll assume you already have a GKE cluster up and running as a starting point.

The first thing to do is to add the charts repository.

Java
 




x


 
1
$ helm repo add yugabytedb https://charts.yugabyte.com



Now, fetch the updates.

Java
 




xxxxxxxxxx
1


 
1
$ helm repo update



Create a namespace. In this case we’ll call it yb-demo.

Java
 




xxxxxxxxxx
1


 
1
$ kubectl create namespace yb-demo



Expected output:

Java
 




xxxxxxxxxx
1


 
1
namespace/yb-demo created



We are now ready to install YugabyteDB. In the command below we’ll be specifying values for a resource constrained environment.

Java
 




xxxxxxxxxx
1


 
1
$ helm install yb-demo yugabytedb/yugabyte \
2
--set resource.master.requests.cpu=1,resource.master.requests.memory=1Gi,\
3
resource.tserver.requests.cpu=1,resource.tserver.requests.memory=1Gi,\
4
enableLoadBalancer=True --namespace yb-demo --wait



To check the status of the cluster, execute the below command:

Java
 




xxxxxxxxxx
1


 
1
 
          
2
$ kubectl get services --namespace yb-demo





Note the external-IP for yb-tserver-service which we are going to use to establish a connection between YugabyteDB and SQLPad. From the screenshot above we can see that the IP is 34.72.XX.XX and the YSQL port is 5433.

The next step is to download a sample schema and data. You can find a variety of sample databases that are compatible with YugabyteDB in our Docs. For the purposes of this tutorial we are going to use the Northwind sample database. The Northwind database contains the sales data for a fictitious company called “Northwind Traders,” which imports and exports specialty foods from around the world. The Northwind database is an excellent tutorial schema for a small-business ERP, with customers, orders, inventory, purchasing, suppliers, shipping, employees, and single-entry accounting.

Connect to the yb-tserver-pod by running the following command:

Java
 




xxxxxxxxxx
1


 
1
$ kubectl exec -n yb-demo -it yb-tserver-0 /bin/bash



To download the schema and data files, run the following commands:

Java
 




xxxxxxxxxx
1


 
1
$ wget https://raw.githubusercontent.com/yugabyte/yugabyte-db/master/sample/northwind_ddl.sql
2
$ wget https://raw.githubusercontent.com/yugabyte/yugabyte-db/master/sample/northwind_data.sql



Note: If the Google Cloud Shell tells you that the wget command does not exist, you can execute:

Java
 




xxxxxxxxxx
1


 
1
$ yum install wget -y



To connect to the YSQL service, exit out of the pod shell and run the following command:

Java
 




xxxxxxxxxx
1


 
1
$ exit 
2
$ kubectl exec -n yb-demo -it yb-tserver-0 -- ysqlsh -h yb-tserver-0.yb-tservers.yb-demo



Create a database and connect to it using the following commands:

Java
 




xxxxxxxxxx
1


 
1
yugabyte=# CREATE DATABASE northwind;
2
northwind=# \c northwind;



Create the database objects and load them up with data using the following commands:

Java
 




xxxxxxxxxx
1


 
1
northwind=# \i 'northwind_ddl.sql';
2
northwind=# \i 'northwind_data.sql';



Verify that the tables are created by running the following command:

Java
 




xxxxxxxxxx
1
20


 
1
northwind-# \d
2
 
          
3
                 List of relations
4
 Schema |          Name          | Type  |  Owner
5
--------+------------------------+-------+----------
6
 public | categories             | table | yugabyte
7
 public | customer_customer_demo | table | yugabyte
8
 public | customer_demographics  | table | yugabyte
9
 public | customers              | table | yugabyte
10
 public | employee_territories   | table | yugabyte
11
 public | employees              | table | yugabyte
12
 public | order_details          | table | yugabyte
13
 public | orders                 | table | yugabyte
14
 public | products               | table | yugabyte
15
 public | region                 | table | yugabyte
16
 public | shippers               | table | yugabyte
17
 public | suppliers              | table | yugabyte
18
 public | territories            | table | yugabyte
19
 public | us_states              | table | yugabyte
20
(14 rows)



Verify we have data by issuing a simple SELECT:

Java
 




xxxxxxxxxx
1


 
1
northwind=# SELECT count(*) FROM products;
2
 count
3
-------
4
    77
5
(1 row)



Step 3: Build and Configure SQLPad

We are now ready to install SQLPad. Exit YSQL and return to the main Google Cloud shell.

Java
 




xxxxxxxxxx
1


 
1
northwind=# exit



Execute the following commands to install the various dependencies and required versions of software. For more information on what’s required for your particular environment, check out the SQLPad developer guide. Below is what worked for me on a default Google Cloud VM environment:

First, update some dependencies required for compilation, upgrade the version of Node to 12.0 or greater, and install SQLite3.

Java
 




xxxxxxxxxx
1


 
1
$ sudo apt-get install build-essential
2
$ sudo apt update
3
$ sudo apt install build-essential checkinstall libssl-dev
4
 
          
5
$ nvm install 14.5.0
6
$ nvm alias default 14.5.0
7
$ npm install sqlite3



Next, clone the SQLPad repo.

Java
 




xxxxxxxxxx
1


 
1
$ git clone https://github.com/rickbergfalk/sqlpad.git



Now, run the SQLPad build script, set it up as a global module, and start the app.

Java
 




xxxxxxxxxx
1


 
1
$ cd sqlpad/
2
$ ./scripts/build.sh
3
$ cd server
4
$ npm install -g
5
$ node server.js --config ./config.dev.env



You can now access the SQLPad UI at: http://localhost:3010

After setting up your administrator credentials, you should see something like this in your browser.

Next, let’s configure the connection to YugabyteDB by selecting “New Connection” in the dropdown above the object explorer. Here’s the basic information we’ll need to set it up, which we discovered in Step 1:

  • Driver: Postgres
  • Host: 34.72.XX.XX
  • Port: 5433
  • Database: northwind
  • User: yugabyte
  • Password: There is no password for the default “yugabyte” user upon initial installation

Now that we are connected to the YugabyteDB cluster, you can see all the Northwind database objects in the explorer tree on the left-hand side. Let’s issue a basic query against the Northwind sample database and examine the result set. In this case we are simply going to select all the rows in the products table.

That’s it! You now have a PostgreSQL-compatible, 3 node YugabyteDB cluster running on GKE connected to the SQLPad UI. To learn more about supported third-party PostgreSQL tools that work with YugabyteDB, check out the YugabyteDB Documentation or join our community Slack and hit us up there.

Topics:
database, distributed sql, distributed sql databases, kubernetes, postgres, postgresql, tutorial, yugabyte

Published at DZone with permission of Jimmy Guerrero . See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}