usql on YugabyteDB
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'))I install usql from an admin prompt:
PS C:\WINDOWS\system32> choco install usqland 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=disableConnected 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=>
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:9402ca: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)
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 TABLENow 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 6And 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)
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.
