Test Yugabyte Cloud QL (YCQL) API

AttentionThis page documents an earlier version. Go to the latest (v2.1)version.

After creating a local cluster, follow the instructions below to test YugabyteDB’s Cassandra-compatible YCQL API.

cqlsh is a command line shell for interacting with Apache Cassandra through CQL (the Cassandra Query Language). It utilizes the Python CQL driver, and connects to the single node specified on the command line. For ease of use, YugabyteDB ships with the 3.10 version of cqlsh in its bin directory.

1. Connect with cqlsh

• macOS
• Linux
• Docker
• Kubernetes

• Run cqlsh to connect to the service.

$ ./bin/cqlsh localhost

Connected to local cluster at 127.0.0.1:9042.
[cqlsh 5.0.1 | Cassandra 3.9-SNAPSHOT | CQL spec 3.4.2 | Native protocol v4]
Use HELP for help.
cqlsh> 

• Run a cql command to verify it is working.

cqlsh> describe keyspaces;

system_schema  system_auth  system
cqlsh> 

• Run cqlsh to connect to the service.

$ ./bin/cqlsh localhost

Connected to local cluster at 127.0.0.1:9042.
[cqlsh 5.0.1 | Cassandra 3.9-SNAPSHOT | CQL spec 3.4.2 | Native protocol v4]
Use HELP for help.
cqlsh> 

• Run a cql command to verify it is working.

cqlsh> describe keyspaces;

system_schema  system_auth  system
cqlsh> 

• Run cqlsh to connect to the service.

$ docker exec -it yb-tserver-n3 /home/yugabyte/bin/cqlsh

Connected to local cluster at 127.0.0.1:9042.
[cqlsh 5.0.1 | Cassandra 3.9-SNAPSHOT | CQL spec 3.4.2 | Native protocol v4]
Use HELP for help.
cqlsh> 

• Run a cql command to verify it is working.

cqlsh> describe keyspaces;

system_schema  system_auth  system
cqlsh> 

• Run cqlsh to connect to the service.

$ kubectl exec -it yb-tserver-0 /home/yugabyte/bin/cqlsh

Connected to local cluster at 127.0.0.1:9042.
[cqlsh 5.0.1 | Cassandra 3.9-SNAPSHOT | CQL spec 3.4.2 | Native protocol v4]
Use HELP for help.
cqlsh> 

• Run a cql command to verify it is working.

cqlsh> describe keyspaces;

system_schema  system_auth  system
cqlsh> 

2. Create a table

Create a keyspace called ‘myapp’.

cqlsh> CREATE KEYSPACE myapp;

Create a table named ‘stock_market’ which can store stock prices at various timestamps for different stock ticker symbols.

cqlsh> CREATE TABLE myapp.stock_market (
  stock_symbol text,
  ts text,
  current_price float,
  PRIMARY KEY (stock_symbol, ts)
);

3. Insert data

Let us insert some data for a few stock symbols into our newly created ‘stock_market’ table. You can copy-paste these values directly into your cqlsh shell.

cqlsh> INSERT INTO myapp.stock_market (stock_symbol,ts,current_price) VALUES ('AAPL','2017-10-26 09:00:00',157.41);
INSERT INTO myapp.stock_market (stock_symbol,ts,current_price) VALUES ('AAPL','2017-10-26 10:00:00',157);

cqlsh> INSERT INTO myapp.stock_market (stock_symbol,ts,current_price) VALUES ('FB','2017-10-26 09:00:00',170.63);
INSERT INTO myapp.stock_market (stock_symbol,ts,current_price) VALUES ('FB','2017-10-26 10:00:00',170.1);

cqlsh> INSERT INTO myapp.stock_market (stock_symbol,ts,current_price) VALUES ('GOOG','2017-10-26 09:00:00',972.56);
INSERT INTO myapp.stock_market (stock_symbol,ts,current_price) VALUES ('GOOG','2017-10-26 10:00:00',971.91);

4. Query the table

Query all the values we have inserted into the database for the stock symbol ‘AAPL’ as follows.

cqlsh> 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)

Query all the values for ‘FB’ and ‘GOOG’ as follows.

cqlsh> SELECT * FROM myapp.stock_market WHERE stock_symbol in ('FB', 'GOOG');

 stock_symbol | ts                  | current_price
--------------+---------------------+---------------
           FB | 2017-10-26 09:00:00 |        170.63
           FB | 2017-10-26 10:00:00 |     170.10001
         GOOG | 2017-10-26 09:00:00 |        972.56
         GOOG | 2017-10-26 10:00:00 |     971.90997
(4 rows)