DEV Community: Jimmy Guerrero

Enabling pgAudit, pgcrypto and scram-SHA-256 in Distributed SQL

Jimmy Guerrero — Wed, 25 Nov 2020 22:03:55 +0000

This is a cross-post of my colleague Karthik Ranganathan's blog originally posted on the Yugabyte blog.

The YugabyteDB 2.5 release adds many critical enterprise-grade security features. This blog post outlines these newly added features.

Authentication

Adding scram-sha-256 authentication

The first notable addition is the addition of a much improved, password-based authentication mechanism called Salted Challenge Response Authentication Mechanism (or simply SCRAM) as described in RFC5802. This scram-sha-256 authentication mechanism, identical to the most secure PostgreSQL authentication schema, is a challenge-response scheme that prevents password sniffing on untrusted connections and supports storing passwords on the server in a cryptographically hashed form that is thought to be secure. To enable this feature, simply pass the following flag when starting the yb-tserver.

yb-tserver 
  <other flags> 
  --ysql_pg_conf="password_encryption=scram-sha-256"

Note that this is supported only by the YSQL API. Read more about scram-sha-256 authentication mechanism in YugabyteDB documentation.

LDAP authentication

The Lightweight Directory Access Protocol, more commonly abbreviated to LDAP, is an open industry standard for authentication of users. This allows users to use a single password to connect to multiple services, including connecting to databases and running queries against them. With this release, the YSQL API can be configured to authenticate users using LDAP as the password verification method.

Enabling LDAP authentication in YugabyteDB internally sets up two host-based authentication rules. Configure YugabyteDB to work with an external LDAP directory service for authentication works as follows:

Setting up roles and permissions for LDAP users:

The first rule allows connecting to the database as the admin user (which is yugabyte by default) from the localhost (127.0.0.1) using password authentication. This allows administrators to immediately log in with the admin user credentials and set up the required roles and permissions for the LDAP users.

This can be accomplished by starting the yb-tserver processes with the following flag:

--ysql_hba_conf_csv='host all yugabyte 127.0.0.1/0 password,"host   all all 0.0.0.0/0 ldap ldapserver=<ldap-server-url> ldapprefix=""uid="" ldapsuffix="", dc=example, dc=com"" ldapport=389"'

The above would generate the following ysql_hba.conf internal configuration:

# This is an autogenerated file, do not edit manually!
host all yugabyte 127.0.0.1/0 trust
host   all         all      0.0.0.0/0  ldap ldapserver=<ldap-server-url> ldapprefix="uid=" ldapsuffix=", dc=example, dc=com" ldapport=389

Configure LDAP authentication for all other user/host pairs:

The second rule configures authentication for all other additional user/host pairs by using simple bind with a uid-based username (ldapprefix) and a suffix defining the domain component (dc). These should be provided by the LDAP provider.

Connect to the database using ysqlsh and create one or more of the roles required, as shown in the example below.

CREATE ROLE yb_user WITH LOGIN;
GRANT ALL ON DATABASE yugabyte TO yb_user;

Connect using LDAP authentication:

To test connecting to the database using LDAP, simply connect with an LDAP user, as shown below and enter the password when prompted.

./bin/ysqlsh -U yb_user

You can confirm the current user by running:

SELECT current_user;
 current_user
--------------
 yb_user
(1 row)

The LDAP authentication scheme can operate in a simple bind mode or the search-and-bind mode, as well as using a secure connection using TLS encryption between PostgreSQL and the LDAP server using the ldaptls option. Read more about using YugabyteDB with LDAP.

Audit Logging

Audit logging allows administrators and users to track activity related to data access, such as who accessed which portions of data, helping to understand the extent of a breach and sometimes even identify the attackers. Retaining audit logs is also a compliance requirement not only in regulated industries, but also increasingly in scenarios where data privacy laws like GDPR take effect. Audit logging in YugabyteDB will write the output on each node to the standard logging facility, similar to the design of PostgreSQL. These partial log files can subsequently be merged for a global audit trail. This feature is supported for both the YSQL and YCQL APIs. You can read more about audit logging in YugabyteDB in the documentation.

The YSQL API internally uses the pgAudit extension to provide detailed session-level and object-level audit logging. It is possible to both filter what gets logged, as well as configure the output format. The extension is preinstalled in YugabyteDB, so using this feature is easy.

To configure audit logging:

Pass the following flag to the yb-tserver as shown below.

yb-tserver 
  <other flags> 
  --ysql_pg_conf="pgaudit.log='DDL',pgaudit.log_level=notice"

Alternatively, this feature can also be configured with the SET command to change runtime parameters, as shown below.

SET pgaudit.log='DDL'; 
SET pgaudit.log_client=ON;
SET pgaudit.log_level=notice;

To enable audit logging:

First load the extension by running the following SQL command.

CREATE EXTENSION IF NOT EXISTS pgaudit;

Testing the audit logging feature:

Let us create a table by running the following.

create table test (a int);

The following entry should get written to the standard log.

NOTICE:  AUDIT: SESSION,4,1,DDL,CREATE TABLE,TABLE,public.tmp,"CREATE TABLE test (a int);",<not logged>

Customizing audit logging:

There are a number of configuration options available to customize this feature, for example specifying the classes of statements that should be logged, the output format, etc.

Column Level Permissions

Column level security can be used to specify the exact set of columns a user can access or modify (SELECT, INSERT, UPDATE privileges) using the GRANT command. This would effectively prevent the user from seeing or updating all the other columns. You can learn more about column level security in the YugabyteDB documentation.

An example is shown below.

Create an employee table and insert few sample rows:

yugabyte=# create table employees ( empno int, ename text, address text, salary int, account_number text );
CREATE TABLE

yugabyte=# insert into employees values (1, 'joe', '56 grove st',  20000, 'AC-22001' );
INSERT 0 1
yugabyte=# insert into employees values (2, 'mike', '129 81 st',  80000, 'AC-48901' );
INSERT 0 1
yugabyte=# insert into employees values (3, 'julia', '1 finite loop',  40000, 'AC-77051' );
INSERT 0 1

yugabyte=# select * from employees;
 empno | ename |    address    | salary | account_number
-------+-------+---------------+--------+----------------
     1 | Joe   | 56 grove st   |  20000 | AC-22001
     2 | Mike  | 129 81 st     |  80000 | AC-48901
     3 | Julia | 1 finite loop |  40000 | AC-77051
(3 rows)

Create a user `ybadmin` with column-level privileges on the table above:

Assume we want to prevent this ybadmin user from viewing sensitivity information of employees, such as salary and account_number. This can be done as follows by using the following GRANT statement.

yugabyte=> \c yugabyte yugabyte;
You are now connected to database "yugabyte" as user "yugabyte".

yugabyte=# create user ybadmin;
CREATE ROLE

yugabyte=# grant select (empno, ename, address) on employees to ybadmin;
GRANT

Verify column-level permissions:

The ybadmin user will now be able to access only the columns to which permissions were granted. This can be verified as shown below.

yugabyte=# \c yugabyte ybadmin;
You are now connected to database "yugabyte" as user "ybadmin".

yugabyte=> select empno, ename, address from employees;
 empno | ename |    address
-------+-------+---------------
     1 | joe   | 56 grove st
     3 | julia | 1 finite loop
     2 | mike  | 129 81 st
(3 rows)

The ybadmin will still be denied if user tries to access other columns:

yugabyte=> select empno, ename, address, salary from employees;
ERROR:  permission denied for table employees

Encryption of data

In addition to the natively supporting encryption of data at rest, the 2.5 release adds a number of other data encryption related features. YugabyteDB now interoperates with Vormetric Transparent Encryption (VTE) to secure sensitive data allowing users to protect themselves from a wide range of risks from malicious hackers to database administrators with privileged data access. Encrypted backups are now supported by YugabyteDB, enabling backing up sensitive data securely.

Column-level encryption

Column-level encryption is also now supported by YugabyteDB (YSQL only). It uses the pgcrypto module to allow only the columns containing sensitive data to be encrypted before storing them on disk. The client supplies the decryption key and the data is decrypted on the server and then sent to the client. You can read more about column-level encryption in the YugabyteDB docs.

Let’s run through an example of symmetric column-level encryption.

Enable `pgcrypto` extension:

Open the YSQL shell (ysqlsh), specifying the yugabyte user and prompting for the password.

$ ./ysqlsh -U yugabyte -W
When prompted for the password, enter the yugabyte password. You should be able to login and see a response like below.

ysqlsh (11.2-YB-2.5.0.0-b0)
Type "help" for help.
yugabyte=#

Enable pgcrypto extension on the YugabyteDB cluster yugabyte=> \c yugabyte yugabyte;

You are now connected to database "yugabyte" as user "yugabyte".
yugabyte=# CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION

Insert using PGP_SYM_ENCRYPT

Create employees table and insert data into the table using PGP_SYM_ENCRYPT function for columns that need to be encrypted.

yugabyte=# create table employees ( empno int, ename text, address text, salary int, account_number text );
CREATE TABLE

In this example, account numbers of employees table will be encrypted using PGP_SYM_ENCRYPT function.

yugabyte=# insert into employees values (1, 'joe', '56 grove st',  20000, PGP_SYM_ENCRYPT('AC-22001', 'AES_KEY'));
INSERT 0 1
yugabyte=# insert into employees values (2, 'mike', '129 81 st',  80000, PGP_SYM_ENCRYPT('AC-48901', 'AES_KEY'));
INSERT 0 1
yugabyte=# insert into employees values (3, 'julia', '1 finite loop',  40000, PGP_SYM_ENCRYPT('AC-77051', 'AES_KEY'));
INSERT 0 1

Verify column encryption:

Review the encrypted account_number data, as shown below

yugabyte=# select ename, account_number from employees limit 1;
 ename |               account_number
-------+-------------------------------------------------
 joe   | \xc30d04070302ee4c6d5f6656ace96ed23901f56c717d4e
 162b6639429f516b5103acebc4bc91ec15df06c30e29e6841f4a5386
 e7698bfebb49a8660f9ae4b3f34fede3f28c9c7bb245bd
(1 rows)

Query using `PGP_SYM_DECRYPT`

Decrypt the account numbers using PGP_SYM_DECRYPT function as shown here. In order to retrieve the encrypted column data, use PGP_SYM_DECRYPT function to decrypt the data. The Decryption function needs to be used in both SELECT and WHERE clause depending on the query.

To allow the decryption, the field name is also casted to the binary data type with the syntax: account_number:bytea.

yugabyte=# select PGP_SYM_DECRYPT(account_number::bytea, 'AES_KEY') as AccountNumber
           from employees;
 accountnumber
---------------
 AC-22001
 AC-48901
 AC-77051
(3 rows)

What’s Next

We’re very happy to be able to release all of these latest and greatest security features into YugabyteDB 2.5. We invite you to learn more and try it out:

Register for the upcoming webinar, "Enterprise Security Features in YugabyteDB," taking place on December 8 at 11 am Pacific
Read the YugabyteDB 2.5 release announcement
Install YugabyteDB 2.5 in mere minutes
Join us in Slack

A PostgreSQL-Compatible, Distributed SQL Cheat Sheet: The Basics

Jimmy Guerrero — Thu, 01 Oct 2020 19:54:06 +0000

In this blog post, we’ll highlight how all the basic commands you end up using in the first few minutes after installing PostgreSQL are identical in YugabyteDB. We’ll cover connecting to the database, creating users, databases, schemas, and calling external files from the SQL shell. In the next blog post in this series we’ll tackle querying data to demonstrate that if you know how to query data in PostgreSQL, you already know how to do it in YugabyteDB.

First things first, for those of you who might be new to either distributed SQL or YugabyteDB…

What is Distributed SQL?

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

They must have a SQL API for querying and modeling data, with support for traditional RDBMS features like foreign keys, partial indexes, stored procedures and triggers.
Smart distributed query execution so that query processing is pushed closer to the data as opposed to data being pushed over the network and thus slowing down query response times.
Should support automatic and transparent distributed data storage. This includes indexes which should be sharded across multiple nodes of the cluster so that no single node becomes a bottleneck. Data distribution ensures 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’s 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, cloud native, offers deep integration with GraphQL projects, plus supports advanced RDBMS features like stored procedures, triggers, and UDFs.

Got questions? Make sure to ask them in our YugabyteDB Slack channel. Ok, let’s dive in…

Installing YugabyteDB

YugabyteDB is only slightly more involved than getting PostgreSQL up and running. At the end of the day it should only take a few minutes or less depending on your environment. Let’s look at a few scenarios:

Single Node Installation on Mac

$ wget https://downloads.yugabyte.com/yugabyte-2.3.0.0-darwin.tar.gz
$ tar xvfz yugabyte-2.3.0.0-darwin.tar.gz && cd yugabyte-2.3.0.0/
$ ./bin/yugabyted start

Single Node Installation on Linux

$ wget https://downloads.yugabyte.com/yugabyte-2.3.0.0-linux.tar.gz
$ tar xvfz yugabyte-2.3.0.0-linux.tar.gz && cd yugabyte-2.3.0.0/
$ ./bin/post_install.sh
$ ./bin/yugabyted start

Note: If you want to run 3 local nodes instead of a single node for either the Mac or Linux setups, just tweak the last command so it reads:

./bin/yb-ctl --rf 3 create

3 Node Installation on Google Kubernetes Engine

$ helm repo add yugabytedb https://charts.yugabyte.com
$ helm repo update
$ kubectl create namespace yb-demo
$ helm install yb-demo yugabytedb/yugabyte --namespace yb-demo --wait

For more information on other installation types and prerequisites, check out the Quickstart Docs.

Connecting to a YugabyteDB Cluster

Connect Locally

Assuming you are in the YugabyteDB install directory, simply execute the following to get to a YSQL shell:

$ ./bin/ysqlsh

ysqlsh (11.2-YB-2.3.0.0-b0)
Type "help" for help.

yugabyte=#

Connecting on GKE

Assuming you are connected to the Kubernetes cluster via the Google Cloud Console, execute the following.

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

ysqlsh (11.2-YB-2.3.0.0-b0)
Type "help" for help.

yugabyte=#

Check out the documentation for more information about YugabyteDB’s PostgreSQL-compatible YSQL API.

Connecting via JDBC

Assuming we are using the PostgreSQL JDBC driver to connect to YugabyteDB, the construction of the connect string will be identical to PostgreSQL. For example here’s a snippet for setting up a connection to a database called "northwind" in YugabyteDB using the PostgreSQL driver in Spring.

spring.datasource.url=jdbc:postgresql://11.22.33.44:5433/northwind
spring.datasource.username=yugabyte
spring.datasource.password=password
spring.datasource.driver-class-name=org.postgresql.Driver
spring.jpa.database=POSTGRESQL
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.ddl-auto=none

Note: In the example above we assume YugabyteDB’s YSQL API is being accessed at 11.22.33.44 on the default port 5433, using the default user “yugabyte” with the password “password”. For more information about YugabyteDB connectivity options check out the Drivers section of the documentation.

Setting Up Users in YugabyteDB

Creating roles/users, and assigning them privileges and passwords is going to be the same in YugabyteDB as it is in PostgreSQL.

Create a Role with Privileges

CREATE ROLE felix LOGIN;

Create a Role with a Password

CREATE USER felix2 WITH PASSWORD ‘password’;

Create a Role with a Password That Will Expire in the Future

CREATE ROLE felix3 WITH LOGIN PASSWORD 'password' VALID UNTIL '2020-09-30';

Change a User’s Password

ALTER ROLE felix WITH PASSWORD 'newpassword';
List All the Users
\du

For more information about how YugabyteDB handles users, permissions, security, and encryption check out the Secure section of the documentation.

Creating Databases and Schemas in YugabyteDB

Creating databases and schemas in YugabyteDB is identical to how it is done in PostgreSQL.

Create a Database

CREATE DATABASE northwind;

Switch to a Database

\c northwind;

Describe the Database

\dt

                 List of relations
 Schema |          Name          | Type  |  Owner   
--------+------------------------+-------+----------
 public | categories             | table | yugabyte
 public | customer_customer_demo | table | yugabyte
 public | customer_demographics  | table | yugabyte
 public | customers              | table | yugabyte
 public | employee_territories   | table | yugabyte
 public | employees              | table | yugabyte
 public | order_details          | table | yugabyte
 public | orders                 | table | yugabyte
 public | products               | table | yugabyte
 public | region                 | table | yugabyte
 public | shippers               | table | yugabyte
 public | suppliers              | table | yugabyte
 public | territories            | table | yugabyte
 public | us_states              | table | yugabyte
(14 rows)

Create a Schema

CREATE SCHEMA nonpublic;

Create a Schema for a Specific User

CREATE SCHEMA AUTHORIZATION felix;

Create Objects and Load Data from External Files

If you have DDL or DML scripts that you want to call from within the YSQL shell, the process is the same in YugabyteDB as it is in PostgreSQL. You can find the scripts used in the examples below in the "~/yugabyte-2.3.x.x/share" directory. For information about the sample data sets that ship by default with YugabyteDB, check out the Sample Datasets documentation.

Call an External File to Create Objects

\i 'northwind_ddl.sql';

Call an External File to Load Data into the Objects

\i 'northwind_data.sql';

What’s Next?

Stay tuned for part 2 in this series where we’ll dive into querying data from a YugabyteDB cluster using familiar PostgreSQL syntax.

Version Control for Distributed SQL with Flyway’s Maven Plugin

Jimmy Guerrero — Wed, 30 Sep 2020 18:01:38 +0000

Flyway is an open source database version control and migration tool that stresses simplicity and convention over configuration. Changes to the database can be written in SQL (and in some database-specific dialects like PL/SQL and T-SQL) or Java. You interact with Flyway using a command-line client, however there are a variety of plugins that can be leveraged, including Maven, Gradle, Spring Boot, and more.

Supported databases include Oracle, SQL Server, DB2, MySQL, PostgreSQL, and others. Because YugabyteDB is PostgreSQL compatible, most third-party tools and apps will work “out of the box.” Flyway is no exception here. This allows developers to deploy and rollback schema changes to YugabyteDB using Flyway by making use of the PostgreSQL JDBC driver.

Flyway relies on seven commands to manage database version control.

Migrate: Migrates the schema to the latest version. Flyway will create the schema history table automatically if it doesn’t exist.
Clean: Drops all objects in the configured schemas.
Info: Prints the details and status information about all the migrations.
Validate: Validates the applied migrations against the available ones.
Undo: Undoes the most recently applied versioned migration.
Baseline: Baselines an existing database, excluding all migrations up to and including baselineVersion.
Repair: Repairs the schema history table.
In this blog post we’ll walk you though the following steps:
Install a 3 node YugabyteDB cluster on Google Kubernetes Platform
Install and configure Flyway locally to work with YugabyteDB
How to run migrations using the Maven plugin

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.

Ok, on with the demo…

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.

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

Now, fetch the updates.

$ helm repo update

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

$ kubectl create namespace yb-demo

Expected output:

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.

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

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

$ 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 Flyway. From the screenshot above we can see that the IP is 35.224.XX.XX and the YSQL port is 5433.

Step 2: Creating the Northwind sample database

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:

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

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

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

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

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

$ exit
$ 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:

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

We can now create the database objects and load them with data using the files we downloaded to "yb-tserver-pod" using the following commands:

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

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

northwind-# \d

                 List of relations
 Schema |          Name          | Type  |  Owner
--------+------------------------+-------+----------
 public | categories             | table | yugabyte
 public | customer_customer_demo | table | yugabyte
 public | customer_demographics  | table | yugabyte
 public | customers              | table | yugabyte
 public | employee_territories   | table | yugabyte
 public | employees              | table | yugabyte
 public | order_details          | table | yugabyte
 public | orders                 | table | yugabyte
 public | products               | table | yugabyte
 public | region                 | table | yugabyte
 public | shippers               | table | yugabyte
 public | suppliers              | table | yugabyte
 public | territories            | table | yugabyte
 public | us_states              | table | yugabyte
(14 rows)

Verify we have data by issuing a simple SELECT:

northwind=# SELECT count(*) FROM products;
 count
-------
    77
(1 row)

By default, a YugabyteDB installation doesn’t have a password setup for the default "yugabyte" user. Specifying one is done the same way you’d do it in PostgreSQL.

northwind=# ALTER ROLE yugabyte WITH PASSWORD 'password';

Step 3: Install and configure Flyway

For the purposes of this demo we’ll be installing and configuring Flyway Community Edition. After downloading Flyway, navigate to the flyway-6.5.2 directory. We are going to want to make a few configuration changes to get Flyway connected to the YugabyteDB database we just deployed.

In the conf directory locate the flyway.conf file. Uncomment or modify the following lines:

flyway.url=jdbc:postgresql://35.224.XX.XX:5433/northwind
flyway.user=yugabyte
flyway.password=password

At this point Flyway will be able to connect to YugabyteDB running on GKE.

Step 4: Create a project

Create a basic project using the Maven Archetype Plugin by issuing the following command:

$ mvn archetype:generate -B \
    -DarchetypeGroupId=org.apache.maven.archetypes \
    -DarchetypeArtifactId=maven-archetype-quickstart \
    -DarchetypeVersion=1.1 \
    -DgroupId=foo \
    -DartifactId=bar \
    -Dversion=1.0-SNAPSHOT \
    -Dpackage=foobar

You should now see a project structure similar to the one below in your IDE of choice.

Step 5: Configure the Flyway integration

Edit the pom.xml and add entries for the Flyway plugin, YugabyteDB (PostgreSQL) connectivity, and PostgreSQL Driver.

    <plugins>
      <plugin>
        <groupId>org.flywaydb</groupId>
        <artifactId>flyway-maven-plugin</artifactId>
        <version>6.5.2</version>
        <configuration>
         <url>jdbc:postgresql://XX.XX.XX.XX:5433/northwind</url>
          <user>yugabyte</user>
          <password>password</password>
        </configuration>
        <dependencies>
          <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.2.2</version>
          </dependency>
        </dependencies>
      </plugin>
    </plugins>

Note: There are few other configuration options when it comes to Flyway and Maven. You can find them detailed in the Flyway Documentation.

Step 6: Create table migration

Create the migration directory ~bar/src/main/resources/db/migration to hold our SQL scripts.

Create a SQL script in this directory with the following content named V1__create_promotions_table.sql.

CREATE TABLE promotions (
    promotion_id serial PRIMARY KEY,
    supplier_id int2 NOT NULL,
    promotion_name VARCHAR ( 50 ) NOT NULL,
    company_name VARCHAR ( 40 ) NOT NULL,
    start_date DATE NOT NULL,
    stop_date DATE NOT NULL,
    created_on DATE NOT NULL DEFAULT CURRENT_DATE
);

We can now execute this migration at the command line.

$ cd /bar
$ mvn flyway:migrate

We can verify that the "promotions" table was indeed built.

We should also be able to see this migration in the "flyway_schema_history" table.

Step 7: Insert data migration

Create the SQL script below, place it in the migration directory with the following and name it "V2__insert_promotions.sql".

INSERT INTO promotions (supplier_id, promotion_name, company_name, start_date, stop_date, created_on)
VALUES
('28','Buy One Get One Free', 'Gai pâturage', '1996-08-15', '1996-08-30', CURRENT_DATE),
('19', 'Free Shipping', 'New England Seafood Cannery', '1996-10-15', '1996-10-30', CURRENT_DATE),
('16', 'Free T-Shirt With Every Purchase', 'Bigfoot Breweries', '1996-12-15', '1996-12-30', CURRENT_DATE),
('20','10% Off', 'Gula Malacca', '1996-11-15', '1996-11-30', CURRENT_DATE),
('8', '20% Off', 'Specialty Biscuits, Ltd.', '1996-09-15', '1996-09-30', CURRENT_DATE);

We can now execute this migration at the command line.

$ mvn flyway:migrate

Let’s verify that the data was inserted into the "promotions" table.

We should also be able to see this migration in the "flyway_schema_history" table.

Conclusion

That’s it! You now have a 3 node YugabyteDB cluster on GKE, with versioning control managed by a local installation of Flyway. For more information about how to perform more advanced operations in Flyway using the Java API, Maven, or Gradle, check out their documentation.

Getting Started with IntelliJ IDEA and Distributed SQL

Jimmy Guerrero — Tue, 29 Sep 2020 22:52:43 +0000

IntelliJ IDEA is a popular integrated development environment (IDE) written in Java for developing software. It is developed by JetBrains and is available as an Apache 2 licensed community edition and in a proprietary commercial edition. IntelliJ supports a variety of datasources within their IDE. Because YugabyteDB is PostgreSQL compatible, most third-party tools and apps will work “out of the box.” IntelliJ is no exception here.

In this blog post we’ll walk you though the following steps:

Install a 3 node YugabyteDB cluster on Google Kubernetes Engine
Install the Northwind sample database
Configure IntelliJ to work with YugabyteDB
Test out some basic IntelliJ database features with YugabyteDB

New to distributed SQL or YugabyteDB? Read on.

What is Distributed SQL?

They must have a SQL API for querying and modeling data, with support for traditional RDBMS features like foreign keys, partial indexes, stored procedures and triggers.
Smart distributed query execution so that query processing is pushed closer to the data as opposed to data being pushed over the network and thus slowing down query response times.
Should support automatic and transparent distributed data storage. This includes indexes which should be sharded across multiple nodes of the cluster so that no single node becomes a bottleneck. Data distribution ensures 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’s YugabyteDB?

Got questions? Make sure to ask them in our YugabyteDB Slack channel. Ok, let’s dive in…

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.

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

Now, fetch the updates.

$ helm repo update

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

$ kubectl create namespace yb-demo

Expected output:

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.

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

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

$ 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 IntelliJ. From the screenshot above we can see that the IP is 35.224.XX.XX and the YSQL port is 5433.

Step 2: Create the Northwind sample database

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

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

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

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

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

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

$ exit

$ 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:

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

We can now create the database objects and load them with data using the files we downloaded to yb-tserver-pod using the following commands:

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

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

northwind-# \d

                 List of relations
 Schema |          Name          | Type  |  Owner
--------+------------------------+-------+----------
 public | categories             | table | yugabyte
 public | customer_customer_demo | table | yugabyte
 public | customer_demographics  | table | yugabyte
 public | customers              | table | yugabyte
 public | employee_territories   | table | yugabyte
 public | employees              | table | yugabyte
 public | order_details          | table | yugabyte
 public | orders                 | table | yugabyte
 public | products               | table | yugabyte
 public | region                 | table | yugabyte
 public | shippers               | table | yugabyte
 public | suppliers              | table | yugabyte
 public | territories            | table | yugabyte
 public | us_states              | table | yugabyte
(14 rows)

Verify we have data by issuing a simple SELECT:

northwind=# SELECT count(*) FROM products;
 count
-------
    77
(1 row)

By default, a YugabyteDB installation doesn’t have a password setup for the default yugabyte user. Specifying one is done the same way you’d do it in PostgreSQL.

northwind=# ALTER ROLE yugabyte WITH PASSWORD 'password';

Step 3: Configure IntelliJ to work with YugabyteDB

Launch the Database tool window. (View > Tool Windows > Database)

Add a PostgreSQL data source. (New (+) > Data Source > PostgreSQL)

On the General tab, enter your database’s connection string:

Host: The external IP that GKE assigned to YugabyteDB in the previous step
Port: YugabyteDB uses port 5433
Database: For our purposes we’ll be connecting to northwind sample database we created in the previous step
User: The default YugabyteDB user is yugabyte
Password: We’ll use the password password, which we assigned to the default user in the previous step
Driver: Select or install the latest PostgreSQL driver

For good measure, make sure to test the connect before clicking “Ok.”

Step 4: Run a sample query

Next, let’s test the integration by executing the following query in IntelliJ.

In the output window we should see the following result set.

Step 5: Generate an EXPLAIN PLAN

IntelliJ has the built-in ability to generate EXPLAIN visualizations of your queries. For example, we can get the visualization for our last query by selecting Explain Plan > Show Visualization.

Note: Not all of IntelliJ’s database management features are supported with YugabyteDB. If you run into any issues, make sure to drop by our Slack channel and let us know, or you can always open up a GitHub issue.

Conclusion

That’s it! You now have a 3 node YugabyteDB cluster on GKE, with the sample Northwind database that you can develop against in IntelliJ. For more information about how to work with databases in IntelliJ, check out the IntelliJ documentation. For more 3rd party integrations that work with YugabyteDB, check out the YugabyteDB Documentation.

Use Retool to Quickly Build Distributed SQL and React Apps

Jimmy Guerrero — Thu, 24 Sep 2020 19:40:25 +0000

Retool is a next generation WSIWYG SaaS-based tool that enables you to quickly build React applications for internal consumption from your existing data sources using a variety of pre-built “building blocks.” Developers can choose from over 58 drag and drop components and combine them with custom JavaScript to create applications that can be securely deployed on-premise or hosted environments like Heroku. Retool supports over 30 native integrations including PostgreSQL, GraphQL, AWS S3, and Cassandra. Because YugabyteDB is PostgreSQL compatible, most third-party PostgreSQL tools and apps will work “out of the box.” Retool is no exception here.

In this blog post we’ll walk you though the following steps:

Install a 3 node YugabyteDB cluster on Google Kubernetes Platform
Build the Northwind sample database
Configure Retool to connect to YugabyteDB via the PostgreSQL driver
Build a simple React application on top of the Northwind database to help us manage inventory

New to distributed SQL or YugabyteDB? Read on.

What is Distributed SQL?

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?

Ok, on with the demo…

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.

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

Now, fetch the updates.

$ helm repo update

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

$ kubectl create namespace yb-demo

Expected output:

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.

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

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

$ 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 Flyway. From the screenshot above we can see that the IP is 130.211.XX.XX and the YSQL port is 5433.

Step 2: Creating the Northwind sample database

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

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

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

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

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

$ exit 
$ 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:

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

We can now create the database objects and load them with data using the files we downloaded to yb-tserver-pod using the following commands:

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

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

northwind-# \d

                 List of relations
 Schema |          Name          | Type  |  Owner
--------+------------------------+-------+----------
 public | categories             | table | yugabyte
 public | customer_customer_demo | table | yugabyte
 public | customer_demographics  | table | yugabyte
 public | customers              | table | yugabyte
 public | employee_territories   | table | yugabyte
 public | employees              | table | yugabyte
 public | order_details          | table | yugabyte
 public | orders                 | table | yugabyte
 public | products               | table | yugabyte
 public | region                 | table | yugabyte
 public | shippers               | table | yugabyte
 public | suppliers              | table | yugabyte
 public | territories            | table | yugabyte
 public | us_states              | table | yugabyte
(14 rows)

Verify we have data by issuing a simple SELECT:

northwind=# SELECT count(*) FROM products;
 count
-------
    77
(1 row)

By default, a YugabyteDB installation doesn’t have a password setup for the default yugabyte user. Specifying one is done the same way you’d do it in PostgreSQL.

northwind=# ALTER ROLE yugabyte WITH PASSWORD 'password';

Step 3: Configure Retool with YugabyteDB using the Native PostgreSQL Integration

First, head on over to https://retool.com/ and sign up for the service. Next we’ll work through the wizard to set up our first project, which will be a simple application to help us manage inventory in the Northwind database.

Select the PostgreSQL resource

Recall that YugabyteDB is PostgreSQL-compatible and as far as Retool is concerned, it “thinks” it is connecting to a PostgreSQL database.

Configure Connectivity

Note that we are using the external IP and port number of YugabyteDB detailed in Step 1.

Test the connection. If successful, proceed to creating an app.

Step 4: Create the Inventory Dashboard with Retool

We are now ready to build a simple React application for our “buyer” to easily search and add new products to the Northwind database. Retool ships with a couple of templates that you can use to learn how the product works or to use a starting point for building your app. I went ahead and took their eCommerce Management sample app and tweaked it for my purposes.

Product Search

For this particular feature I wanted to make it easy for a “buyer” to search for products so they could check inventory levels.

To build this feature I used the “Table” component which displays tabular data with pagination. I then assigned this component the required connectivity and the necessary SQL query to retrieve the data in the products table.

select * from products where product_name ilike {{ '%' + product_name_input.value + '%' }} order by product_id desc

Although there are a ton of additional options you can specify, I kept it simple for the purposes of this tutorial.

The next component I configured is the “Text Input” component which controls other components or queries with text. A few things to note here in the configuration of this component are that the input type must be “text”, the placeholder text is “Search by name…” and when you submit the text it should trigger the “products” component that we configured in the previous step.

Add New Product

The next feature I wanted to build is one that made it easy to add new products to the Northwind database. To accomplish this I again made use of the “Text Input” component, specified the resource to connect to and the columns that would make up the changeset.

For example, here are the details concerning the product_name field.

Finally, we’ll want to use the “Form” component to enable a “Add Product” button that will submit all of the text input fields via the “add_product” query we just configured.

Again, there are a ton of additional options you can specify, but for the purposes of this tutorial I chose to keep it simple.

Step 5: Test the Inventory Dashboard

We are now ready to test our application. An easy way to do this is to click on the “Share” button on the upper right-hand corner of the Retool UI and share the app via the “Public Access Link.” I’d recommend enabling password protection. We should now be able to interact with our application in our favorite browser.

Let’s find the inventory levels for products which have “lager” in their names. We input the text and click enter. We get two results back from the Northwind database. Looks like the product search feature is working as intended.

Next let’s add a new product to the Northwind database.

After clicking on the “Add Product” button, let’s verify that our new product is now in inventory by searching for products with “soda” in their name.

Looks like “Super Awesome Soda Pop” is now in our inventory.

Bonus: We can now also access our inventory app from a mobile device using the same URL. Although not perfect, it is “mobile friendly” enough for our purposes.

Conclusion

That’s it! You now have a 3 node YugabyteDB cluster on GKE, with the sample Northwind database and simple inventory application that was built in just a few minutes using Retool.

Distributed SQL Tips and Tricks for PostgreSQL and Oracle DBAs – Sep 9, 2020

Jimmy Guerrero — Wed, 09 Sep 2020 21:48:54 +0000

Welcome to this week’s tips and tricks blog where we explore both beginner and advanced YugabyteDB topics for PostgreSQL and Oracle DBAs. First things first, for those of you who might be new to either 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, foregin 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?

Got questions? Make sure to ask them in our YugabyteDB Slack channel. Please note that most examples in the post make use of the Northwind sample database. Instructions on how to get it installed are here.

Ok, let’s dive in…

Is there a functional equivalent to Oracle’s NVL in PostgreSQL or YugabyteDB?

In a nutshell, Oracle’s NVL lets you replace NULL, which returns as a blank in the results of a query with a string. For example, let’s say we want to return a list of our customers and for those that have NULL in the region column, swap in “Not Applicable.”

SELECT company_name, country, NVL(region, 'Not Applicable')
FROM customers
ORDER BY country;

To achieve similar results in PostgreSQL and YugabyteDB, we can use the COALESCE function. So, to start, here’s the partial output of our customers table. Note that Argentina, Austria and Belgium have NULL in the region column.

With the COALESCE function we can make the swap.

SELECT company_name, country, COALESCE (region, 'Not Applicable')
FROM customers 
ORDER BY country;

Does YugabyteDB support PostgreSQL’s Views, Materialized Views, and Rollups?

VIEW and ROLLUP are supported, with materialized view support currently in development. You can track its development with this GitHub issue.

PostgreSQL’s views can be thought of as “virtual tables” that bring together data from one or more tables that can be queried against, just like a regular table. A materialized view on the other hand is not “virtual,” so we don’t have to recompute the view every time we want to access it. This makes materialized views much more efficient to query, with the trade offs of requiring additional storage and when there are updates to the base tables of the view, the materialized view must also be kept updated.

Finally, PostgreSQL’s ROLLUP is a subclause of GROUP BY that allows us to define multiple grouping sets. This is especially useful in analytic use cases where you might want to “rollup” dates, monetary values or other types of data where some calculation might be useful. Let’s walk through each example.

Creating a VIEW in YugabyteDB is identical to how it is done in PostgreSQL. In this example, let’s create a VIEW on the customers and orders table. In this scenario a VIEW is useful because although the customer_id column exists in both the orders and customers tables, company_name does not exist in the orders table. We want to make it super simple to get the order details and customer_name associated with the order in one shot.

CREATE VIEW orders_by_company_name AS
SELECT orders.order_id, orders.order_date, customers.company_name, orders.ship_country
FROM orders, customers 
WHERE orders.customer_id = customers.customer_id;

Now issue a simple SELECT statement to retrieve just the data that we need.

SELECT * FROM orders_by_company_name;

Creating a ROLLUP in YugabyteDB is identical to how it is done in PostgreSQL. For example, let’s say we want to “rollup” the data in our orders table so that the data is grouped by customer_id (who placed the order), employee_id (who took the order), and the total sum of freight sent.

SELECT
customer_id,    
COALESCE(employee_id, '0') as employee_id,
    SUM (freight) as "Rollup Sum of Freight by Customer and Employee"
FROM
    orders
GROUP BY
    ROLLUP (customer_id, employee_id)
ORDER BY customer_id, employee_id;

From the output above we can see that the result set sums the freight by customer_id and employee_id.

Does YugabyteDB support EXPLAIN ANALYZE?

Yes. PostgreSQL’s EXPLAIN is a useful tool for determining where potential performance tuning opportunities lie with queries being issued against the database. Adding the ANALYZE option causes the statement to be actually executed, not only planned.

Let’s issue a simple EXPLAIN ANALYZE against the orders table in the northwind sample database. In this example we are executing queries against a YugabyteDB cluster with a replication factor of 3.

EXPLAIN ANALYZE SELECT customer_id, order_id, ship_country 
FROM orders 
WHERE order_date BETWEEN '1997-01-01' and '1999-12-31';

QUERY PLAN                                                                                                 
----------
Foreign Scan on orders
(cost=0.00..105.00 rows=1000 width=82) 
(actual time=4.865..27.046 rows=678 loops=1)

Filter: ((order_date >= '1997-01-01'::date) AND (order_date <= '1999-12-31'::date)

Rows Removed by Filter: 152                                                                             
Planning Time: 31.899 ms                                                                                  
Execution Time: 29.078 ms

Next, let’s build an index on the order_date column and see if we can’t shave some time off the query.

CREATE INDEX idx_order_date 
ON orders(order_date ASC);

We execute the same query and get the following results.

QUERY PLAN                                                                                                                 
----------
Index Scan using idx_order_date on orders  
(cost=0.00..5.25 rows=10 width=82) 
(actual time=10.350..11.607 rows=678 loops=1)

Index Cond: ((order_date >= '1997-01-01'::date) AND (order_date <= '1999-12-31'::date))                                  
Planning Time: 13.608 ms                                                                                                   Execution Time: 11.913 ms

As you can see we are now able to confirm that by creating the index on the order_date column we were able to make an improvement on our planning time and execution time. The delta in the example is not dramatic, but you get the idea. With more complex queries and larger datasets, EXPLAIN and ANALYZE become essential performance tuning tools.

Using Envoy Proxy’s PostgreSQL & TCP Filters to Collect Yugabyte SQL Statistics

Jimmy Guerrero — Thu, 27 Aug 2020 23:28:37 +0000

Note: This is a cross post of my colleague Sid Choundhury's blog that was originally published here.

Layer 7 proxies like NGINX and HAProxy have been popular since the mid-2000s. The term “proxy” refers to their role as an intermediary for the traffic between an application client and an application server. The “layer 7” classification comes from the fact that these proxies take routing decisions based on URLs, IPs, TCP/UDP ports, cookies, or any information present in messages sent over a layer 7 (aka application layer) networking protocol like HTTP and gRPC. Modern applications use these proxies for a variety of needs including load balancing, security, and web acceleration.

What is Envoy Proxy?

Envoy is a layer 7 proxy and communication bus designed for large modern service oriented architectures. Its home page has the following definition:

Originally built at Lyft, Envoy is a high performance C++ distributed proxy designed for single services and applications, as well as a communication bus and “universal data plane” designed for large microservice “service mesh” architectures. Built on the learnings of solutions such as NGINX, HAProxy, hardware load balancers, and cloud load balancers, Envoy runs alongside every application and abstracts the network by providing common features in a platform-agnostic manner. When all service traffic in an infrastructure flows via an Envoy mesh, it becomes easy to visualize problem areas via consistent observability, tune overall performance, and add substrate features in a single place.

As the recently published Dropbox’s migration from NGINX to Envoy highlights, Envoy is rapidly becoming the default proxy for cloud native applications that need higher performance, observability, extensibility, security, building and testing, and last but not least, deep features (such as HTTP/2, gRPC, and egress proxying). It was the third CNCF project to reach the graduated status, following Kubernetes and Prometheus, and has gained widespread adoption in a relatively short period of time.

Why use Envoy’s PostgreSQL filter?

Envoy supports configuration of multiple traffic listeners where each listener is composed of one or more filter chains. An individual filter chain is selected to process the incoming data based on the filter’s match criteria (which includes connection parameters such as destination port/IP, transport protocol name, source port/IP, and more). When a new connection is received on a listener, the matching filter chain is selected and instantiated. The filters then begin processing subsequent events. This generic listener architecture is used to perform the vast majority of different proxy tasks that Envoy is used for including rate limiting, TLS client authentication, HTTP connection management, raw TCP proxy, and more. One such task relevant to database deployments is the ability to instrument the wire protocol of popular databases such as MySQL, MongoDB, Kafka, and Amazon DynamoDB. PostgreSQL was missing from this list but the latest v1.15 release from July 2020 solved that problem by adding a PostgreSQL proxy filter. This filter is based on PostgreSQL frontend/backend protocol version 3.0, which was introduced in PostgreSQL 7.4.

The main goal of the PostgreSQL filter is to capture runtime statistics while remaining completely transparent to the database server. There is no additional monitoring software to deploy or manage in order to collect these vital statistics! As listed in the official docs, the filter currently offers the following features:

Decode non SSL traffic, ignore SSL traffic
Decode session information
Capture transaction information, including commits and rollbacks
Expose counters for different types of statements (INSERTs, SELECTs, DELETEs, UPDATEs, etc.)
Count frontend, backend, and unknown messages
Identify errors and backend responses

YugabyteDB is fully compatible with the PostgreSQL wire protocol and SQL syntax given that its SQL query layer is based on a fork of PostgreSQL 11.2’s query layer. As a result, YugabyteDB is able to leverage the PostgreSQL filter from Envoy without any modifications whatsoever. The rest of this post outlines the instructions to run the most basic YugabyteDB with Envoy setup (including the PostgreSQL & TCP filters) using Docker Compose. Official Envoy sandboxes use the same approach to test out different features and highlight sample configurations.

YugabyteDB with Envoy in action

Install Docker

Ensure that you have docker and docker-compose installed on your local machine. Docker Desktop can be the simplest way to achieve this goal.

Create the “YugabyteDB with Envoy” stack using docker-compose

Create a working directory

mkdir yugabyte-envoy

Create the envoy.yaml

Copy the following contents into a file named envoy.yaml.

static_resources:
  listeners:
  - name: yb_listener
    address:
      socket_address:
        address: 0.0.0.0
        port_value: 1999
    filter_chains:
    - filters:
      - name: envoy.filters.network.postgres_proxy
        typed_config:
          "@type": type.googleapis.com/envoy.extensions.filters.network.postgres_proxy.v3alpha.PostgresProxy
          stat_prefix: ysql
      - name: envoy.tcp_proxy
        typed_config:
          "@type": type.googleapis.com/envoy.extensions.filters.network.tcp_proxy.v3.TcpProxy
          stat_prefix: tcp_ysql
          cluster: yb_cluster

  clusters:
  - name: yb_cluster
    connect_timeout: 1s
    type: strict_dns
    load_assignment:
      cluster_name: yb_cluster
      endpoints:
      - lb_endpoints:
        - endpoint:
            address:
              socket_address:
                address: ysql
                port_value: 5433

admin:
  access_log_path: "/dev/null"
  address:
    socket_address:
      address: 0.0.0.0
      port_value: 8001

As we can see above, we have configured an Envoy listener on port 1999 that has a filter chain with two filters, namely PostgreSQL and TCP.

Create the envoy dockerfile

Copy the following contents into a file named Dockerfile-proxy. When built and instantiated, we will have an envoyproxy container that will use the envoy.yaml configuration we created in the previous step.

FROM envoyproxy/envoy-dev:latest

COPY ./envoy.yaml /etc/envoy.yaml
RUN chmod go+r /etc/envoy.yaml
CMD /usr/local/bin/envoy -c /etc/envoy.yaml -l debug

Create the yugabytedb dockerfile

Copy the following contents into a file named Dockerfile-yugabyte. When built and instantiated, we will have a single YugabyteDB container with the PostgreSQL-compatible YSQL API available on port 5433.

FROM yugabytedb/yugabyte:latest

CMD ["/home/yugabyte/bin/yugabyted","start","--daemon=false"]

Create the docker-compose.yaml

Copy the following contents into a file named docker-compose.yaml.

version: "3.7"
services:

  proxy:
    build:
      context: .
      dockerfile: Dockerfile-proxy
    networks:
      envoymesh:
        aliases:
          - envoy
    expose:
      - "1999"
      - "8001"
    ports:
      - "1999:1999"
      - "8001:8001"

  yugabyte:
    build:
      context: .
      dockerfile: Dockerfile-yugabyte
    networks:
      envoymesh:
        aliases:
          - ysql
    expose:
      - "5433"
    ports:
      - "5433:5433"

networks:
  envoymesh:
    name: envoymesh

Start the docker-compose stack

docker-compose pull
docker-compose up --build -d
docker-compose ps

Output from the ps command is shown below.

Name                      Command                     State                Ports                                              
---------------------------------------------------------------------------------
yugabyte_proxy_1      /docker-entrypoint.sh /bin ...   Up      ... 0.0.0.0:1999->1999/tcp, 0.0.0.0:8001->8001/tcp                                                                    
yugabyte_yugabyte_1   /home/yugabyte/bin/yugabyt ...   Up      ... 0.0.0.0:5433->5433/tcp, ...

As we can see, two containers have been spun up on a common envoymesh network.

yugabyte_yugabyte_1 is the YugabyteDB container that is ready to interact with PostgreSQL clients on port 5433.
yugabyte_proxy_1 is the Envoy proxy container that is running the PostgreSQL proxy on the 1999 port. Requests to this port get automatically redirected to the port 5433 on the YugabyteDB container.

Connect using ysqlsh via the envoy listener

We are now ready to connect to the YugabyteDB cluster using ysqlsh. However, instead of directly connecting to the 5433 port of the YugabyteDB container, we will connect to the Envoy proxy at the 1999 port.

docker run --rm -it --network envoymesh yugabytedb/yugabyte /home/yugabyte/bin/ysqlsh "sslmode=disable" -h envoy -p 1999

As highlighted in the Envoy docs, the current PostgreSQL filter decodes only non-SSL (aka unencrypted) traffic and ignores any SSL/encrypted traffic. The sslmode=disable option shown above is mandatory for Envoy to treat the PostgreSQL traffic as unencrypted even though the cluster has been set up without any encryption. Since this behavior is reproducible with both PostgreSQL 12 and 11.2 (Yugabyte SQL is based on a fork of this version), this is most likely a bug in the filter implementation.

Run basic YSQL commands

Let’s create a table.

CREATE TABLE links (
    id SERIAL PRIMARY KEY,
    url VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description VARCHAR (255),
      last_update DATE
);

Now let us insert four rows into the table we created.

INSERT INTO links (url, name)
VALUES('https://www.postgresqltutorial.com','PostgreSQL Tutorial');

INSERT INTO links (url, name)
VALUES('https://www.oreilly.com','O''Reilly Media');

INSERT INTO links (url, name)
VALUES('https://docs.yugabyte.com','YugabyteDB Docs');

INSERT INTO links (url, name)
VALUES('https://blog.yugabyte.com','YugabyteDB Blog') 
RETURNING id;

We can now run a SELECT statement to get all the rows we inserted.

SELECT * FROM links;

We can also run a SELECT statement to get the count of rows we inserted.

SELECT count(*) FROM links;

Review YSQL statistics collected by Envoy’s filters

Each of the two filters configured provide us with statistics relevant to the data they observe. All these statistics are available on the Envoy stats page http://localhost:8001/stats.

Using the PostgreSQL filter

Since we gave the stats prefix as ysql, we see all the statistics with the overall prefix as postgres.ysql. The ones that we can easily verify based on the queries we executed in the ysqlsh session we created are highlighted with "**".

postgres.ysql.errors: 0
postgres.ysql.errors_error: 0
postgres.ysql.errors_fatal: 0
postgres.ysql.errors_panic: 0
postgres.ysql.errors_unknown: 0
postgres.ysql.messages: 45
postgres.ysql.messages_backend: 37
postgres.ysql.messages_frontend: 8
postgres.ysql.messages_unknown: 0
postgres.ysql.notices: 0
postgres.ysql.notices_debug: 0
postgres.ysql.notices_info: 0
postgres.ysql.notices_log: 0
postgres.ysql.notices_notice: 0
postgres.ysql.notices_unknown: 0
postgres.ysql.notices_warning: 0
**postgres.ysql.sessions: 1**
postgres.ysql.sessions_encrypted: 0
**postgres.ysql.sessions_unencrypted: 1**
**postgres.ysql.statements: 7**
postgres.ysql.statements_delete: 0
**postgres.ysql.statements_insert: 4**
**postgres.ysql.statements_other: 1**
postgres.ysql.statements_parse_error: 2
postgres.ysql.statements_parsed: 5
**postgres.ysql.statements_select: 2**
postgres.ysql.statements_update: 0
**postgres.ysql.transactions: 7**
**postgres.ysql.transactions_commit: 7**
postgres.ysql.transactions_rollback: 0

Using the TCP filter

The TCP statistics relevant to YSQL are available with the tcp.tcp_ysql prefix. As we can see, the statistics are at the network level including bytes transmitted and bytes received.

tcp.tcp_ysql.downstream_cx_no_route: 0
tcp.tcp_ysql.downstream_cx_rx_bytes_buffered: 33
tcp.tcp_ysql.downstream_cx_rx_bytes_total: 693
tcp.tcp_ysql.downstream_cx_total: 1
tcp.tcp_ysql.downstream_cx_tx_bytes_buffered: 0
tcp.tcp_ysql.downstream_cx_tx_bytes_total: 991
tcp.tcp_ysql.downstream_flow_control_paused_reading_total: 0
tcp.tcp_ysql.downstream_flow_control_resumed_reading_total: 0
tcp.tcp_ysql.idle_timeout: 0
tcp.tcp_ysql.upstream_flush_active: 0
tcp.tcp_ysql.upstream_flush_total: 0

Remove all the containers in the stack

We can remove the containers we have previously spun up using the command below.

docker rm -f $(docker ps -aq)

Summary

The recently released PostgreSQL filter from Envoy Proxy makes it extremely easy for developers and operations engineers to collect SQL statistics from YSQL, YugabyteDB’s PostgreSQL-compatible fully-relational distributed SQL API. The filter runs inside the Envoy Proxy sidecar container and works by simply sniffing the network traffic in a manner that is completely transparent to the database server. As a result, developers and operations engineers can leverage the integration without deploying and managing any additional software.

We welcome all users to give the integration a try today and provide us feedback via GitHub and Slack.

SQL Tips & Tricks: Counting Rows

Jimmy Guerrero — Thu, 27 Aug 2020 22:43:30 +0000

Note: This is a cross post of my colleague Karthik Ranganathan's blog that was originally published here.

Getting total row counts of data in tables across various dimensions (per-table, per-schema, and in a given database) is a useful technique to have in one’s tool belt of SQL tricks. While there are a number of use cases for this, my scenario was to get the per-table row counts of all tables in PostgreSQL and YugabyteDB as a first sanity check after migrating an application with the pre-existing data from PostgreSQL to YugabyteDB.

This blog post outlines how to get the following row counts of tables in a database:

Row counts broken down per table in the schema
Aggregate row counts per schema of the database
Aggregate row count across all tables in the database We will create an example database, import two popular SQL datasets – Northwind and SportsDB, and run through the above scenarios on these example databases.

The examples in this blog post, which are essentially dynamic SQL queries on the system catalog tables, must be done with superuser privileges. Also, note that the programmatic generation of SQL queries using catalog tables needs to handle exotic names properly. An instance of a table and a column with an exotic name is shown below.

create table "Some Exotically Named Table"(
  k bigserial primary key,
  "Some Exotically Named Column" text
);

While this post does not explicitly discuss the challenges posed by the example above, the SQL functions below handle these cases correctly and do so by incorporating some of the important and well-known techniques necessary to prevent SQL injection attacks.

Create an example database

In order to create a test setup, I simply installed YugabyteDB on my laptop, created a database example, and loaded the Northwind dataset – all of which only took a few minutes to do. For the purpose of simplicity, we’re going to use the default yugabyte user for the operations below. However, creating a dedicated user for each of these datasets with the appropriate privileges is the recommended best practice.

Create an example database and connect to it.

yugabyte=# CREATE DATABASE example;
yugabyte=# \c example

Create the Northwind tables and import the dataset into the `northwind` schema.

example=# CREATE SCHEMA northwind;
example=# SET SCHEMA 'northwind';
example=# \i northwind_ddl.sql
example=# \i northwind_data.sql

You can verify that the tables have been created by running the following command.

example=# \d
                   List of relations
  Schema   |          Name          | Type  |  Owner
-----------+------------------------+-------+----------
 northwind | categories             | table | yugabyte
 northwind | customer_customer_demo | table | yugabyte
 northwind | customer_demographics  | table | yugabyte
 northwind | customers              | table | yugabyte
 northwind | employee_territories   | table | yugabyte
 northwind | employees              | table | yugabyte
 northwind | order_details          | table | yugabyte
 northwind | orders                 | table | yugabyte
 northwind | products               | table | yugabyte
 northwind | region                 | table | yugabyte
 northwind | shippers               | table | yugabyte
 northwind | suppliers              | table | yugabyte
 northwind | territories            | table | yugabyte
 northwind | us_states              | table | yugabyte
(14 rows)

Next, let’s import the SportsDB dataset into a new schema named sportsdb as shown below.

example=# CREATE SCHEMA sportsdb;
example=# SET SCHEMA 'sportsdb';
example=# \i sportsdb_tables.sql
example=# \i sportsdb_indexes.sql
example=# \i sportsdb_inserts.sql
example=# \i sportsdb_constraints.sql
example=# \i sportsdb_fks.sql

Create a function for row count

Recall that YugabyteDB re-uses the native PostgreSQL codebase for its query layer (or the SQL processing layer) of the database. This means that the high-level approach to solving this problem is identical in the case of both PostgreSQL and YugabyteDB.

We’ll solve this problem by first creating a user defined function (UDF), count_rows_of_table which counts the number of rows in a single table. Note that this function must be owned by a suitably privileged user, in our example we will use the yugabyte user. This function can subsequently be used in various types of queries to print the desired row counts in the various scenarios. The function definition is shown below.

create function count_rows_of_table(
  schema    text,
  tablename text
  )
  returns   integer

  security  invoker
  language  plpgsql
as
$body$
declare
  query_template constant text not null :=
    '
      select count(*) from "?schema"."?tablename"
    ';

  query constant text not null :=
    replace(
      replace(
        query_template, '?schema', schema),
     '?tablename', tablename);

  result int not null := -1;
begin
  execute query into result;
  return result;
end;
$body$;

You can test the above function by passing in a table (for example, the orders table loaded from the Northwind dataset) as shown below.

example=# SELECT count_rows_of_table('northwind', 'orders');
 count_rows_of_table
---------------------
                 830
(1 row)

Per-table row counts in a given database

The information_schema.tables table in the system catalog contains the list of all tables and the schemas they belong to. Because we are mainly interested in the user tables, we filter out all tables belonging to pg_catalog and information_schema, which are system schemas. We then call the function we defined in the previous section to get the row count for each table.

select
  table_schema,
  table_name,
  count_rows_of_table(table_schema, table_name)
from
  information_schema.tables
where 
  table_schema not in ('pg_catalog', 'information_schema')
  and table_type = 'BASE TABLE'
order by
  1 asc,
  3 desc;

The query above outputs a table that contains the row counts of all tables across the various schemas, first sorted by the table_schema column and for each table schema, sorted by the tables with the largest number of rows. If we run the above query on our test database, we should see the following output.

 table_schema |       table_name       | count_rows_of_table
--------------+------------------------+---------------------
 northwind    | order_details          |                2155
 northwind    | orders                 |                 830
 northwind    | customers              |                  91
 northwind    | products               |                  77

...
 sportsdb     | affiliations_events    |               13052
 sportsdb     | stats                  |                9398
 sportsdb     | participants_events    |                8700
 sportsdb     | events_documents       |                7915
...
(121 rows)

Aggregate row counts per schema

Next, let us say we want to get the total row count across all tables broken down per schema. This can be achieved by using the following query.

SELECT table_schema, SUM(row_count) AS total_rows FROM (
  SELECT table_schema, 
         count_rows_of_table(table_schema, table_name) AS row_count
    FROM information_schema.tables
    WHERE table_schema NOT IN ('pg_catalog', 'information_schema') 
      AND table_type='BASE TABLE'
) AS per_table_count_subquery
  GROUP BY table_schema
  ORDER BY 2 DESC;

The above uses a subquery to first compute the totals row count per table and performs a GROUP BY operation to get the total number of rows in each schema of the current database. The resulting output is sorted by the schema with the maximum number of rows.

 table_schema | total_rows
--------------+------------
 sportsdb     |      79138
 northwind    |       3362
(2 rows)

Aggregate row count across all tables

The query below simply sums the row counts of the individual tables from the previous step to get a total row count across all the tables. This is done by running the per-table row count as a subquery called per_table_count_subquery and performing a SUM across all the row counts that are the output of that subquery.

with per_table_counts as (
  select count_rows_of_table(table_schema, table_name) as row_count
    from information_schema.tables
    where table_schema not in ('pg_catalog', 'information_schema')
      and table_type='BASE TABLE'
) select sum(row_count) as total_rows
    from per_table_counts;

Running this on the Northwind example dataset produces the following output.


 total_rows
------------
      82500
(1 row)

Conclusion

This post shows some important SQL techniques for computing row counts for PostgreSQL-compatible databases like YugabyteDB. Please remember that writing programs that generate and execute dynamic SQL must be done with caution, rigorous testing, and thorough peer reviews. Errors bring the risk of wrong results. But far worse than this, they expose your code, and therefore your entire database, to the risks of SQL injection attacks.

The code in the examples above works exactly the same way across PostgreSQL and YugabyteDB. This is by design, due to the reuse of the PostgreSQL query layer in YugabyteDB. Try out your favorite PostgreSQL feature on YugabyteDB, and let us know how it goes on our Community Slack. If you run into any issues, just file an issue on GitHub.

Hasura GraphQL Remote JOINs on Distributed SQL Running on AKS & GKE

Jimmy Guerrero — Mon, 24 Aug 2020 23:41:39 +0000

Remote Joins in Hasura GraphQL extend the concept of joining data across tables, to being able to join data across tables and remote data sources. In this blog post we are going to demonstrate this capability by configuring the following set up.

A 3 node YugabyteDB cluster running on GKE with a Hasura GraphQL Engine attached
A 3 node YugabyteDB cluster running on AKS with a Hasura GraphQL Engine attached
A Remote Schema and Remote Relationship configured
The ability to issue GraphQL queries that join data from two different databases, hosted in two different clouds, into a single result set

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, foregin 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?

Ok, on with the demo…

Step 1: Install YugabyteDB on GKE

Here are the complete steps to install a 3 node YugabyteDB cluster on Google Kubernetes Engine. Once you have a YugabyteDB cluster running on GKE, check the status of the cluster by executing the command below:

$ kubectl get services --namespace yb-demo 

NAME                 EXTERNAL-IP      PORT(S) 
yb-tserver-service   35.224.XX.XX     5433:3067/TCP

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

Step 2: Create the Northwind Sample Database

In our documentation, you can find a variety of sample databases that are compatible with YugabyteDB. 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:

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

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

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

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

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

$ exit 
$ 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:

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

We can now create the database objects and load them with data using the files we downloaded to yb-tserver-pod using the following commands:

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

By default, a YugabyteDB installation doesn’t have a password setup for the default yugabyte user. Specifying one is done the same way you'd do it in PostgreSQL.

northwind=# ALTER ROLE yugabyte WITH PASSWORD 'password';

Step 3: Set Up Hasura on GKE to Use YugabyteDB

We are now ready to install the Hasura GraphQL Engine on GKE. Exit the YSQL shell and get the Hasura Kubernetes deployment and service files by executing the commands below.

$ wget https://raw.githubusercontent.com/hasura/graphql-engine/master/install-manifests/kubernetes/deployment.yaml

$ wget https://raw.githubusercontent.com/hasura/graphql-engine/master/install-manifests/kubernetes/svc.yaml

Modify the database URL in deployment.yaml file to include the IP of YugabyteDB. For the purposes of this tutorial, the modification should look like this:

value: postgres://yugabyte:[email protected]:5433/northwind

Note: If you’d like everything to run in the yb-demo namespace, make sure to modify the namespace value in both the deployment.yaml and svc.yaml files. This is the setup I have chosen for this demo.

After saving the file, use kubectl to create a Hasura deployment using the commands below:

$ kubectl create -f deployment.yaml 
deployment.apps/hasura created

$ kubectl create -f svc.yaml 
service/hasura created

To find the external IP and open the Hasura console execute the command below:

$ kubectl get services --namespace yb-demo 

NAME      EXTERNAL-IP      PORT(S) 
hasura    35.192.XX.XX     80:30546/TCP

Now use http://<EXTERNAL-IP>/console to access the Hasura console. In this case it is 35.192.XX.XX. You should now see the Hasura console.

Step 4: Track the Northwind Objects in Hasura

Click on Data in top nav, click on Track All for tables, views, and foreign key relations. Refresh your browser and you should now see the northwind tables in the Explorer tree on the left-hand side when you click on GraphQL in the top nav.

Step 5: Install YugabyteDB on AKS

Here are the complete steps to install a 3 node YugabyteDB cluster on Azure Kubernetes Service. Once you have a YugabyteDB cluster running on AKS, check the status of the cluster by executing the command below:

$ kubectl get services --namespace yb-demo 
NAME                   EXTERNAL-IP     PORT(S) 
yb-tserver-service     20.190.XX.XX    5433:3067/TCP

Step 6: Install the Promotions Database

In this step we are going to create a database called promotions, as well as a table called promotions. We'll then load it with data about what types of promotions there are, what company is sponsoring them, and the duration of the promotion.

Log into the YSQL shell:

$ 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:

yugabyte=# CREATE DATABASE promotions;
yugabyte=# \c promotions;

By default, a YugabyteDB installation doesn’t have a password setup for the default yugabyte user. Specifying one is done the same way you'd do it in PostgreSQL.

promotions=# ALTER ROLE yugabyte WITH PASSWORD 'password';

Create the following table in the promotions database.

CREATE TABLE promotions ( 
      promotion_id serial PRIMARY KEY, 
      supplier_id int2 NOT NULL, 
      promotion_name VARCHAR ( 50 ) NOT NULL, 
      company_name VARCHAR ( 40 ) NOT NULL, 
      start_date DATE NOT NULL, 
      stop_date DATE NOT NULL, 
      created_on DATE NOT NULL DEFAULT CURRENT_DATE );

Populate the promotions table with the following data.

INSERT INTO promotions 
     (supplier_id, promotion_name, company_name, start_date, stop_date, created_on) 
VALUES 
     ('28','Buy One Get One Free', 'Gai pâturage', '1996-08-15', '1996-08-30', CURRENT_DATE), 
     ('19', 'Free Shipping', 'New England Seafood Cannery', '1996-10-15', '1996-10-30', CURRENT_DATE), 
     ('16', 'Free T-Shirt With Every Purchase', 'Bigfoot Breweries', '1996-12-15', '1996-12-30', CURRENT_DATE), 
     ('20','10% Off', 'Gula Malacca', '1996-11-15', '1996-11-30', CURRENT_DATE), 
     ('8', '20% Off', 'Specialty Biscuits, Ltd.', '1996-09-15', '1996-09-30', CURRENT_DATE);

Step 7: Set Up Hasura on AKS to Use YugabyteDB

We are now ready to install the Hasura GraphQL Engine on Azure Kubernetes Service. Fortunately, it is very easy to get Hasura up and running on AKS and pointed to our YugabyteDB cluster with a simple wizard. Use this “one-click deployment” to set up the particulars. After selecting the option to connect to an existing database, you should see the following.

Note that we are specifying the external IP and port of our YugabyteDB cluster. In this case 20.189.XX.XX and 5433. We are also specifying the yugabyte user and the promotions database in the settings.

After Hasura is successfully deployed on AKS, locate the external IP for the console. In my case I looked in the Azure Portal under All Services > Resource groups > /Resource Group/ > hasura-promotions-container-group and saw that 52.137.XX.XX was the public IP assigned to my Hasura GraphQL Engine. With this information you should now be able to open up the Hasura console and track the promotions table. Once that's accomplished, you should now see the promotions table in your Explorer as shown below.

Step 8: Set Up a Remote Schema and Remote Relationship

On the Hasura console running on GKE, navigate to the Remote Schemas tab, click on Add and provide the necessary information for the Hasura server running on AKS.

Next, let’s add a Remote Relationship on the supplier_id table between the suppliers and promotions table that we can leverage in our remote JOIN. You can accomplish this by navigating to Data > suppliers > Relationships > Remote Schema Relationships and specify the relationship.

Step 9: Issue a Remote Join Across Two Databases

We are now finished with setting up a Remote Schema to the promotions database and a Remote Relationship on supplier_id. We can now issue a query that processes data located in two different YugabyteDB databases, hosted on two different clouds. For the purposes of this demo, we want to issue a Remote Join GraphQL query that will allow us to determine if a supplier in the "primary" Northwind database has a promotion scheduled in the "remote" Promotions database.

For example, in the query below we want to know if supplier “16” is running any promotions, and if so, what is the name of the promotion and for what dates is the promotion active.

The GraphQL query should return the following results.

If we break down the GraphQL query, here’s what is happening.

From the suppliers table, which is hosted on a YugabyteDB cluster running on GKE, we are returning:

supplier_id = "16"
company_name = "Bigfoot Breweries"
contact _name = "Cheryl Saylor"
phone = "(503) 555-9931"

We then make use of the Remote Schema promotions_remote that has a relationship on supplier_id to allow us to return the following data from the promotions table, which is hosted on a YugabyteDB cluster running on AKS.

promotion_name = "Free T-Shirt With Every Purchase"
start_date = "1996-12-15"
stop_date = "1996-12-30"

Conclusion

That’s it, if you followed all the instructions in this blog post, you now have the following configured.

3 node YugabyteDB cluster running on GKE with a Hasura GraphQL Engine attached
3 node YugabyteDB cluster running on AKS with a Hasura GraphQL Engine attached
A Remote Schema and Remote Relationship configured The ability to issue GraphQL queries that join data from two different databases into a single result set

Deploying a Real-Time Location App with Hasura GraphQL Engine and Distributed SQL

Jimmy Guerrero — Fri, 21 Aug 2020 21:35:24 +0000

Hasura is one of the leading vendors in the GraphQL ecosystem. They offer an open source engine that connects to your databases and microservices, and then auto-generates a production-ready GraphQL backend. GraphQL is a query language (more specifically a specification) for your API, and a server-side runtime for executing queries by using a type system you define for your data. GraphQL is often used for microservices, mobile apps, and as an alternative to REST. Although GraphQL isn’t tied to any specific database or storage engine and is instead backed by your existing code and data, YugabyteDB is a perfect complement to GraphQL giving you horizontal scalability, fault tolerance, and global data distribution in a single system.

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

Install a 3 node YugabyteDB cluster on Google Kubernetes Engine (GKE)
Deploy the Hasura GraphQL Engine on GKE
Stand up Hasura’s “realtime-location-tracking” app locally and connect to resources on GKE

The “realtime location application” is built using React and is powered by Hasura GraphQL Engine backed by a 3 node YugabyteDB cluster. It has an interface for users to track location of a vehicle using Hasura live queries, in real time. The application makes use of Hasura GraphQL Engine’s real-time capabilities using subscription.

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 cloud native environments. This is often motivated by the desire to reduce TCO or move away from the horizontal scaling limitations of monolithic RDBMS like Oracle, PostgreSQL, MySQL, and SQL Server. The basic characteristics of Distributed SQL are:
They must have a SQL API for querying and modeling data, with support for traditional RDBMS features like foreign keys, partial indexes, stored procedures, and triggers.
Automatic distributed query execution so that no single node becomes a bottleneck.
Should support automatic and transparent distributed data storage. This includes indexes, which should be sharded across multiple nodes of the cluster so that no single node becomes a bottleneck. Data distribution ensures 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, cloud native, offers deep integration with GraphQL projects, plus supports 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.

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

Now, fetch the updates.

$ helm repo update

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

$ kubectl create namespace yb-demo

Expected output:

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.

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

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

$ 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 Hasura. In this demo, the IP is 35.224.XX.XX and the YSQL port is 5433.

Step 2: Setting up Hasura to Use YugabyteDB

Get the Hasura Kubernetes deployment and service files by executing the commands below.

$ wget https://raw.githubusercontent.com/hasura/graphql-engine/master/install-manifests/kubernetes/deployment.yaml

$ wget https://raw.githubusercontent.com/hasura/graphql-engine/master/install-manifests/kubernetes/svc.yaml

Modify the database URL in deployment.yaml file to include the IP of YugabyteDB. This file can be edited using a text editor like vi. Note that by default the yugabyte user in YugabyteDB doesn’t have a password and the default database is yugabyte.

For the purposes of this tutorial, the modification should look like this:

value: postgres://yugabyte:@35.224.XX.XX:5433/yugabyte

Note: If you’d like everything to run in the yb-demo namespace, make sure to modify the namespace value in both the deployment.yaml and svc.yaml files. This is the setup I have chosen for this demo.

After saving the file use kubectl to create a Hasura deployment using the commands below:

$ kubectl create -f deployment.yaml

deployment.apps/hasura created

$ kubectl create -f svc.yaml

service/hasura created

To find the external IP and open the Hasura console execute the command below:

$ kubectl get services --namespace yb-demo

Now use http://<EXTERNAL-IP>/console to access the Hasura console. In this case it is 34.68.XX.XX. You should now see the Hasura console.

Step 3: Install the Realtime Tracking Location App

In step 3 we’ll be working locally. For the purposes of this demo, I am on a Mac.

Download the sample applications

$ git clone https://github.com/hasura/graphql-engine

$ cd graphql-engine/community/sample-apps/realtime-location-tracking

Install the Hasura CLI

$ curl -L https://github.com/hasura/graphql-engine/raw/stable/cli/get.sh | bash

Modify the config.yaml file

Navigate to the hasura/ directory and edit the config.yaml file with the external IP of the Hasura service. For example:

endpoint: http://34.68.XX.XX

Modify the docker-compose.yaml file

Within the same hasura/ directory modify the docker-compose.yaml file with the following changes.

Eliminate the need for a password.

environment:
- “POSTGRES_PASSWORD:“

Modify the port assignment so it can connect to YugabyteDB.

ports:
- “5433:5433“

Specify the connect string with YugabyteDB parameters.

environment:
- HASURA_GRAPHQL_DATABASE_URL: postgres://yugabyte:@35.224.xx.xx:5433/yugabyte

Modify the constants.js file

Navigate to the src/ directory and edit the constants.js file with the following changes.

//Constants file
export const HASURA_GRAPHQL_URL = '34.68.XX.XX/v1/graphql';
const HASURA_GRAPHQL_ENGINE_HOSTNAME = '34.68.XX.XX';

Apply the migrations

Navigate back to the hasura/ directory and run the migration scripts.

$ hasura migrate apply

You should now be able to see the sample apps tables in the Hasura console.

Start the application

We are now ready to run the app at the root of the application located at ~/graphql-engine/community/sample-apps/realtime-location-tracking/

Note: You’ll need to Install Node.js if not already present on your system.

$ npm start

You should see a browser window open up with the landing page for the sample app.

Click on the “track location” button to view a real time location tracking example.

Conclusion

That’s it! You now have the Hasura GraphQL Engine running on top of a 3 node YugabyteDB cluster on GKE. The GraphQL realtime location tracking application is running locally but leveraging Hasura and YugabyteDB in the cloud.

Run the REST Version of Spring PetClinic with Angular and Distributed SQL on GKE

Jimmy Guerrero — Thu, 20 Aug 2020 18:39:24 +0000

Java developers know that Spring Data makes it easy to use data access technologies, relational and non-relational databases, map-reduce frameworks, and cloud-based data services. When YugabyteDB is combined with Spring, Java developers are able to leverage their familiarity with PostgreSQL while gaining the added benefits of Distributed SQL. These “out-of-the-box” benefits include geo-data distribution, high performance, and horizontal scalability, which are impossible or difficult to achieve with monolithic SQL databases. To demonstrate just how easy it is to get started with Spring and YugabyteDB, we’ve put together this simple tutorial using examples which should be very familiar to both the novice and expert Spring developer.

In this blog post we’ll walk you though the following steps:

Install a 3 node YugabyteDB cluster on Google Kubernetes Platform
Deploy a REST version of the Spring PetClinic sample application
Configure an Angular frontend for the Spring PetClinic
Basic testing using Swagger and the Angular UI

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, foregin 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?

Ok, on with the demo…

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.

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

Now, fetch the updates.

$ helm repo update

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

$ kubectl create namespace yb-demo

Expected output:

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.

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

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

$ kubectl get services --namespace yb-demo

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

Step 2: Create the petclinic database and set a password

Let’s create a dedicated database called petclinic for our application. To connect to the YSQL service run the following command:

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

Next, create the petclinic database and connect to it using the following commands:

yugabyte=# CREATE DATABASE petclinic;
yugabyte=# \c petclinic;

By default, a YugabyteDB installation doesn’t have a password setup for the default yugabyte user. Specifying one is done the same way you’d do it in PostgreSQL.

petclinic=# ALTER ROLE yugabyte WITH PASSWORD 'password';

Step 3: Install and configure the REST version of Spring PetClinic

In step 3 we’ll be working locally. For the purposes of this demo, I am on a Mac. First up we’ll need to clone the PetClinic application.

$ git clone https://github.com/spring-petclinic/spring-petclinic-rest.git

Next, navigate to ~/spring-petclinic-rest/src/main/resources/.
—
Recall that YugabyteDB is PostgreSQL wire compatible, so it will leverage all of the application’s existing PostgreSQL connectivity and databases scripts.
—
In the application.properties file you’ll want to specify postgresql as the database and set the database initialization to always.

spring.profiles.active=postgresql,spring-data-jpa
server.port=9966
server.servlet.context-path=/petclinic/
spring.messages.basename=messages/messages
spring.datasource.initialization-mode=always
logging.level.org.springframework=INFO
petclinic.security.enable=false

In the application-postgresql.properties file you’ll want to uncomment the init database block and modify the database connectivity string, plus user credentials. For example:

spring.datasource.initialize=true
spring.datasource.schema=classpath*:db/postgresql/initDB.sql
spring.datasource.data=classpath*:db/postgresql/populateDB.sql

spring.datasource.url=jdbc:postgresql://35.224.XX.XX:5433/petclinic
spring.datasource.username=yugabyte
spring.datasource.password=password
spring.datasource.driver-class-name=org.postgresql.Driver
spring.jpa.database=POSTGRESQL
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.ddl-auto=none

Step 4: Run the PetClinic application

You can now run the application with the following commands:

cd spring-petclinic-rest
./mvnw spring-boot:run

Navigate to http://localhost:9966/petclinic/swagger-ui.html to view the Swagger REST API documentation for the app. You should see something similar to this.

We can also jump back over to the YugabyteDB command line and execute PostgreSQL’s familiar \dt command or view the PetClinic tables in a UI like DBeaver. You should see 9 new tables created.

You can also issue a simple SELECT statement or use a UI to view the data that ends up populating the tables by default.

Step 5: Configure an Angular frontend

To get an Angular frontend for our PetClinic application we’ll make use of the project that can be found here. Again we’ll be working locally on a Mac.

Note: You should be running a current version of the angular-cli.

Clone the project from GitHub.

$ git clone https://github.com/spring-petclinic/spring-petclinic-angular.git

Install the local project package.

$ npm install

Bring up a development server.

$ ng serve

Now, navigate to http://localhost:4200/. Note: The application will automatically reload any changes to source files. You should now see something similar to this:

Step 6: Simple data manipulation in Angular

Next, navigate to http://localhost:4200/petclinic/owners and click on “Add Owner” to add a new owner.

We should now see this new owner in the UI. This means we’ve successfully written to the YugabyteDB cluster.

Similarly, the results you’d see in YugabyteDB.

Step 7: Simple data manipulation in Swagger

Navigate to http://localhost:9966/petclinic/swagger-ui.html and locate the pet-rest-controller.

Let’s add a pet named “Maxine” and assign her to the owner we just created named “Richard.” We can make a POST with the following:

{
  "birthDate": "2020/07/20",
  "id": 0,
  "name": "Maxine",
  "owner": {
    "address": "string",
    "city": "string",
    "firstName": "string",
    "id": 101,
    "lastName": "string",
    "pets": [
      null
    ],
    "telephone": "string"
  },
  "type": {
    "id": 2,
    "name": "string"
  },
  "visits": [
    {
      "date": "yyyy/MM/dd",
      "description": "string",
      "id": 0
    }
  ]
}

After execution, in the Angular UI we should see that the owner “Richard” now has a pet named “Maxine”. This tells us that the POST successfully inputted the data into YugabyteDB.

Conclusion

That’s it! You now have the REST version of the Spring PetClinic running on top of a 3 node YugabyteDB cluster on GKE, with an Angular frontend. For more information about how to build Spring applications with YugabyteDB backends, check the following resources:

Geo-Distributed SQL Databases: 9 Techniques to Reduce Cross-Region Latency

Jimmy Guerrero — Wed, 19 Aug 2020 20:43:03 +0000

Here's a webinar playback on how to stretch database clusters across 3 or more regions while at the same time reducing cross-region latency.

Delivered by my colleague Sid Choudhury, SVP of Product at Yugabyte, you can also check out the companion blog here:

blog.yugabyte.com/9-techniques-to-build-cloud-native-geo-distributed-sql-apps-with-low-latency/

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 cloud native environments. This is often motivated by the desire to reduce TCO or move away from the horizontal scaling limitations of monolithic RDBMS like Oracle, PostgreSQL, MySQL, and SQL Server. The basic characteristics of Distributed SQL are:
They must have a SQL API for querying and modeling data, with support for traditional RDBMS features like foreign keys, partial indexes, stored procedures, and triggers.
Automatic distributed query execution so that no single node becomes a bottleneck.
Should support automatic and transparent distributed data storage. This includes indexes, which should be sharded across multiple nodes of the cluster so that no single node becomes a bottleneck. Data distribution ensures 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, cloud native, offers deep integration with GraphQL projects, plus supports advanced RDBMS features like stored procedures, triggers and UDFs.

DEV Community: Jimmy Guerrero

Enabling pgAudit, pgcrypto and scram-SHA-256 in Distributed SQL

Authentication

Adding scram-sha-256 authentication

LDAP authentication

Setting up roles and permissions for LDAP users:

Configure LDAP authentication for all other user/host pairs:

Connect using LDAP authentication:

Audit Logging

To configure audit logging:

To enable audit logging:

Testing the audit logging feature:

Customizing audit logging:

Column Level Permissions

Create an employee table and insert few sample rows:

Create a user ybadmin with column-level privileges on the table above:

Verify column-level permissions:

Encryption of data

Column-level encryption

Enable pgcrypto extension:

Insert using PGP_SYM_ENCRYPT

Verify column encryption:

Query using PGP_SYM_DECRYPT

What’s Next

A PostgreSQL-Compatible, Distributed SQL Cheat Sheet: The Basics

What is Distributed SQL?

What’s YugabyteDB?

Installing YugabyteDB

Single Node Installation on Mac

Single Node Installation on Linux

3 Node Installation on Google Kubernetes Engine

Connecting to a YugabyteDB Cluster

Connect Locally

Connecting on GKE

Connecting via JDBC

Setting Up Users in YugabyteDB

Create a Role with Privileges

Create a Role with a Password

Create a Role with a Password That Will Expire in the Future

Change a User’s Password

Creating Databases and Schemas in YugabyteDB

Create a Database

Switch to a Database

Describe the Database

Create a Schema

Create a Schema for a Specific User

Create Objects and Load Data from External Files

Call an External File to Create Objects

Call an External File to Load Data into the Objects

What’s Next?

Version Control for Distributed SQL with Flyway’s Maven Plugin

What is Distributed SQL?

What is YugabyteDB?

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

Step 2: Creating the Northwind sample database

Step 3: Install and configure Flyway

Step 4: Create a project

Step 5: Configure the Flyway integration

Step 6: Create table migration

Step 7: Insert data migration

Conclusion

Getting Started with IntelliJ IDEA and Distributed SQL

What is Distributed SQL?

What’s YugabyteDB?

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

Step 2: Create the Northwind sample database

Step 3: Configure IntelliJ to work with YugabyteDB

Step 4: Run a sample query

Step 5: Generate an EXPLAIN PLAN

Conclusion

Use Retool to Quickly Build Distributed SQL and React Apps

What is Distributed SQL?

What is YugabyteDB?

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

Step 2: Creating the Northwind sample database

Step 3: Configure Retool with YugabyteDB using the Native PostgreSQL Integration

Select the PostgreSQL resource

Configure Connectivity

Step 4: Create the Inventory Dashboard with Retool

Product Search

Create a user `ybadmin` with column-level privileges on the table above:

Enable `pgcrypto` extension:

Query using `PGP_SYM_DECRYPT`

Create the Northwind tables and import the dataset into the `northwind` schema.