Explore Yugabyte Cloud QL
After creating a local cluster, follow the instructions below to explore YugabyteDB's semi-relational Yugabyte Cloud QL API.
ycqlsh is the command line shell for interacting with the YCQL API. You will use ycqlsh for this tutorial.
Connect with ycqlsh
Run ycqlsh to connect to the service as follows:
$ ./bin/ycqlsh
Connected to local cluster at 127.0.0.1:9042.
[ycqlsh 5.0.1 | Cassandra 3.9-SNAPSHOT | CQL spec 3.4.2 | Native protocol v4]
Use HELP for help.
ycqlsh>
Run a YCQL command to verify it is working.
ycqlsh> describe keyspaces;
system_schema system_auth system
Run ycqlsh to connect to the service as follows:
$ ./bin/ycqlsh
Connected to local cluster at 127.0.0.1:9042.
[ycqlsh 5.0.1 | Cassandra 3.9-SNAPSHOT | CQL spec 3.4.2 | Native protocol v4]
Use HELP for help.
ycqlsh>
Run a YCQL command to verify it is working.
ycqlsh> describe keyspaces;
system_schema system_auth system
Run ycqlsh to connect to the service as follows:
$ docker exec -it yb-tserver-n1 /home/yugabyte/bin/ycqlsh yb-tserver-n1
Connected to local cluster at 127.0.0.1:9042.
[ycqlsh 5.0.1 | Cassandra 3.9-SNAPSHOT | CQL spec 3.4.2 | Native protocol v4]
Use HELP for help.
ycqlsh>
Run a YCQL command to verify it is working.
ycqlsh> describe keyspaces;
system_schema system_auth system
ycqlsh>
To connect to the service, open the YCQL shell (ycqlsh) by running the following command:
$ kubectl exec -it yb-tserver-0 -- ycqlsh yb-tserver-0
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.
ycqlsh>
Run a cql command to verify it is working.
ycqlsh> describe keyspaces;
system_schema system_auth system
ycqlsh>
Create a table
Create a keyspace called 'myapp'.
ycqlsh> CREATE KEYSPACE myapp;
Create a table named stock_market', which can store stock prices at various timestamps for different stock ticker symbols.
ycqlsh> CREATE TABLE myapp.stock_market (
stock_symbol text,
ts text,
current_price float,
PRIMARY KEY (stock_symbol, ts)
);
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 ycqlsh shell.
ycqlsh> 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);
ycqlsh> 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);
ycqlsh> 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);
Query the table
Query all the values you have inserted into the database for the stock symbol 'AAPL' as follows.
ycqlsh> 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.
ycqlsh> 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)