DEV Community

loading...
Yugabyte

usql on YugabyteDB

Franck Pachot
20 years in databases from dev to prod - Oracle Certified Master, AWS Data Hero, love to learn and share
Updated on ・4 min read

You know the powerful psql command line to connect to a PostgreSQL database. In the YugabyteDB binaries, you find it under the name ysqlsh to be consistent with the ycsqlsh (YCQL the Cassandra-like API) but it is the same as the PostgreSQL one. The YSQL API is actually based on PostgreSQL code: same protocol, same SQL (and PL/pgSQL), and same open source license. But if you use many database, SQL or not, and want a common client, usql is for you, very similar to psql.

I'll show how to connect to YugabyteDB and that's also the occasion to connect to YB from the Go driver (pq). What I do here with the YSQL connection can be done the same on a PostgreSQL database.

Install

I'll not reproduce the install doc. Here I'm on Windows where I use Chocolatey package manager which I install from an admin PowerShell with:

Set-ExecutionPolicy Bypass -Scope Process -Force; [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor 3072; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))
Enter fullscreen mode Exit fullscreen mode

I install usql from an admin prompt:

PS C:\WINDOWS\system32> choco install usql
Enter fullscreen mode Exit fullscreen mode

and I'm ready to call usql from any command prompt.

Connect

usql parses the connection string with dburl which has many aliases for PostgreSQL (pg, postgresql, pgsql) and also special entries for wire compatible alternatives. But YugabyteDB is more than wire compatible: the query layer is PostgreSQL. So you just use the postgres connection string. Here I am connecting to my public demo database without SSL:

C:\Users\franck> usql postgres://franck:yugabyteDB@yb1.pachot.net:5433/yb_demo_northwind?sslmode=disable

Connected with driver postgres (PostgreSQL 11.2-YB-2.7.2.0-b0)
Type "help" for help.

pg:franck@yb1.pachot.net:5433/yb_demo_northwind=> \l
 List of databases
      Catalog
-------------------
 postgres
 system_platform
 template0
 template1
 yb_demo_northwind
 yugabyte
(6 rows)

pg:franck@yb1.pachot.net:5433/yb_demo_northwind=> \d
              List of relations
 Schema |          Name          |    Type
--------+------------------------+------------
 public | bench0001              | BASE TABLE
 public | benchruns              | BASE TABLE
 public | categories             | BASE TABLE
 public | customer_customer_demo | BASE TABLE
 public | customer_demographics  | BASE TABLE
 public | customers              | BASE TABLE
 public | employee_territories   | BASE TABLE
 public | employees              | BASE TABLE
 public | order_details          | BASE TABLE
 public | orders                 | BASE TABLE
 public | products               | BASE TABLE
 public | region                 | BASE TABLE
 public | rep_check              | BASE TABLE
 public | shippers               | BASE TABLE
 public | suppliers              | BASE TABLE
 public | territories            | BASE TABLE
 public | us_states              | BASE TABLE
 public | benchruns_job_id_seq   | SEQUENCE
(18 rows)

pg:franck@yb1.pachot.net:5433/yb_demo_northwind=>
Enter fullscreen mode Exit fullscreen mode

Mostly all commands from psql are there.

copy between databases (NoSQL to SQL)

With YugabyteDB the same distributed database server is compatible with Cassandra (YCQL) and Postgres (YSQL). For this example, I'm connecting to the two endpoints of the same YB universe, but of course \copy can be used between any database. However, data goes through the client, so this is not a replacement for Foreign Data Wrapper.

I have loaded the example table from the YCQL documentation


connect cassandra://franck:switzerland@yb1.pachot.net:9402

ca:franck@yb1.pachot.net=> CREATE KEYSPACE myapp;
CREATE KEYSPACE
ca:franck@yb1.pachot.net=> CREATE TABLE myapp.stock_market (
ca:franck@yb1.pachot.net(>   stock_symbol text,
ca:franck@yb1.pachot.net(>   ts text,
ca:franck@yb1.pachot.net(>   current_price float,
ca:franck@yb1.pachot.net(>   PRIMARY KEY (stock_symbol, ts)
ca:franck@yb1.pachot.net(> );
CREATE TABLE
ca:franck@yb1.pachot.net=> INSERT INTO myapp.stock_market (stock_symbol,ts,current_price) VALUES ('AAPL','2017-10-26 09:00:00',157.41);
INSERT
ca:franck@yb1.pachot.net=> INSERT INTO myapp.stock_market (stock_symbol,ts,current_price) VALUES ('AAPL','2017-10-26 10:00:00',157);
INSERT
ca:franck@yb1.pachot.net=> INSERT INTO myapp.stock_market (stock_symbol,ts,current_price) VALUES ('FB','2017-10-26 09:00:00',170.63);
debug2: channel 2: window 992086 sent adjust 56490
INSERT
ca:franck@yb1.pachot.net=> INSERT INTO myapp.stock_market (stock_symbol,ts,current_price) VALUES ('FB','2017-10-26 10:00:00',170.1);
INSERT
ca:franck@yb1.pachot.net=> INSERT INTO myapp.stock_market (stock_symbol,ts,current_price) VALUES ('GOOG','2017-10-26 09:00:00',972.56);
INSERT
ca:franck@yb1.pachot.net=> INSERT INTO myapp.stock_market (stock_symbol,ts,current_price) VALUES ('GOOG','2017-10-26 10:00:00',971.91);
INSERT
ca:franck@yb1.pachot.net=> SELECT * FROM myapp.stock_market WHERE stock_symbol = 'AAPL';
 stock_symbol |         ts          | current_price
-------------------+---------------------+---------------
 AAPL         | 2017-10-26 09:00:00 |        157.41
 AAPL         | 2017-10-26 10:00:00 |           157
(2 rows)
Enter fullscreen mode Exit fullscreen mode

I've done all that from usql that I have installed on one YugabyteDB node here as I've setup local access only.

From the same usql interface I connect to YSQL and create the same table but in SQL):

ca:franck@yb1.pachot.net=> \connect postgres://franck:yugabyteDB@yb1.pachot.net:5433/yb_demo_northwind?sslmode=disable
Connected with driver postgres (PostgreSQL 11.2-YB-2.7.2.0-b0)
pg:franck@yb1.pachot.net:5433/yb_demo_northwind=> CREATE TABLE public.stock_market (
pg:franck@yb1.pachot.net:5433/yb_demo_northwind(>   stock_symbol text,
pg:franck@yb1.pachot.net:5433/yb_demo_northwind(>   ts text,
pg:franck@yb1.pachot.net:5433/yb_demo_northwind(>   current_price float,
pg:franck@yb1.pachot.net:5433/yb_demo_northwind(>   PRIMARY KEY (stock_symbol, ts)
pg:franck@yb1.pachot.net:5433/yb_demo_northwind(> );
CREATE TABLE
Enter fullscreen mode Exit fullscreen mode

Now ready to copy from one to the other:

pg:franck@yb1.pachot.net:5433/yb_demo_northwind=> \set source cassandra://franck:switzerland@yb1.pachot.net
pg:franck@yb1.pachot.net:5433/yb_demo_northwind=> \set target postgres://franck:yugabyteDB@yb1.pachot.net:5433/yb_demo_northwind?sslmode=disable
pg:franck@yb1.pachot.net:5433/yb_demo_northwind=> \copy :source :target 'select * from myapp.stock_market' stock_market
\copy :source :target 'select * from myapp.stock_market' stock_market
COPY 6
Enter fullscreen mode Exit fullscreen mode

And query the result:

pg:franck@yb1.pachot.net:5433/yb_demo_northwind=> SELECT * FROM public.stock_market WHERE stock_symbol = 'AAPL';

 stock_symbol |         ts          |   current_price
-------------------+---------------------+--------------------
 AAPL         | 2017-10-26 09:00:00 | 157.41000366210938
 AAPL         | 2017-10-26 10:00:00 |                157
(2 rows)
Enter fullscreen mode Exit fullscreen mode

My data is there. Are you concerned by the little difference in the column value? Never store a price in float! For money, SQL, ACID and DECIMAL is a must.

Discussion (0)