Cassandra Query Language (CQL) quickstart

For more complete documentation, see the CQL documentation.

Because Hyper-Converged Database (HCD) is powered by Apache Cassandra®, you can use Cassandra Query Language (CQL) queries. You can add and delete schema, as well as input and retrieve data using CQL.

You can send queries to your Hyper-Converged Database (HCD) database with the CQL shell (CQLSH).

You can also send CQL queries to your Hyper-Converged Database (HCD) database with the drivers:

Prerequisites:

Install the CQL shell (CQLSH) on your local machine, so that you can run CQL commands on your database.

Create a keyspace

Create a keyspace named cycling:

CREATE KEYSPACE IF NOT EXISTS cycling
WITH REPLICATION = {
  'class' : 'SimpleStrategy',
  'replication_factor' : 1
};

Create a table

Create a table within your cycling keyspace:

CREATE TABLE IF NOT EXISTS cycling.cyclist_alt_stats (
  id UUID PRIMARY KEY,
  lastname text,
  birthday date,
  nationality text,
  weight float,
  w_units text,
  height float,
  first_race date,
  last_race date
);

Insert data

Add data about a user into your new table:

INSERT INTO cycling.cyclist_alt_stats (id, lastname, birthday, nationality, weight, w_units, height, first_race, last_race) VALUES (ed584e99-80f7-4b13-9a90-9dc5571e6821,'TSATEVICH', '1989-07-05', 'Russia', 64, 'kg', 1.69, '2006-03-15','2017-04-16');
INSERT INTO cycling.cyclist_alt_stats (id, lastname, birthday, nationality, weight, w_units, height,  first_race, last_race) VALUES (a9e96714-2dd0-41f9-8bd0-557196a44ecf,'ISAYCHEV', '1986-04-21', 'Russia', 80, 'kg', 1.88,'2003-04-22','2017-03-05');
INSERT INTO cycling.cyclist_alt_stats (id, lastname, birthday, nationality, weight, w_units, height,  first_race, last_race) VALUES (823ec386-2a46-45c9-be41-2425a4b7658e,'BELKOV', '1985-01-09', 'Russia', 71, 'kg', 1.84,'2002-03-22','2017-04-16');
INSERT INTO cycling.cyclist_alt_stats (id, lastname, birthday, nationality, weight, w_units, height,  first_race, last_race) VALUES (e0953617-07eb-4c82-8f91-3b2757981625,'BRUTT', '1982-01-29', 'Russia', 68, 'kg', 1.78,'1998-02-15','2017-04-16');
INSERT INTO cycling.cyclist_alt_stats (id, lastname, birthday, nationality, weight, w_units, height,  first_race, last_race) VALUES (078654a6-42fa-4142-ae43-cebdc67bd902,'LAGUTIN', '1981-01-14', 'Russia', 63, 'kg', 1.82,'1996-05-21','2010-10-02');
INSERT INTO cycling.cyclist_alt_stats (id, lastname, birthday, nationality, weight, w_units, height,  first_race, last_race) VALUES (d74d6e70-7484-4df5-8551-f5090c37f617,'GRMAY', '1991-08-25', 'Ethiopia', 63, 'kg', 1.75, '2006-05-21','2017-04-16');
INSERT INTO cycling.cyclist_alt_stats (id, lastname, birthday, nationality, weight, w_units, height,  first_race, last_race) VALUES (c09e9451-50da-483d-8108-e6bea2e827b3,'VEIKKANEN', '1981-03-29', 'Finland', 66, 'kg', 1.78,'1996-05-21','2012-10-02');
INSERT INTO cycling.cyclist_alt_stats (id, lastname, birthday, nationality, weight, w_units, height,  first_race, last_race) VALUES (f1deff54-7d96-4981-b14a-b70be4da82d2,'TLEUBAYEV', null, 'Kazakhstan', null, null, null, '2003-04-22','2017-04-16');
INSERT INTO cycling.cyclist_alt_stats (id, lastname, birthday, nationality, weight, w_units, height,  first_race, last_race) VALUES (1ba0417d-62da-4103-b710-de6fb227db6f,'PAULINHO', '1990-05-27', 'Portugal', null, null, null, '2006-03-15','2017-03-05');
INSERT INTO cycling.cyclist_alt_stats (id, lastname, birthday, nationality, weight, w_units, height,  first_race, last_race) VALUES (4ceb495c-55ab-4f71-83b9-81117252bb13,'DUVAL', '1990-05-27','France', null, null, null, '2006-03-15','2017-04-16');
INSERT INTO cycling.cyclist_alt_stats (id, lastname, birthday, nationality, weight, w_units, height, first_race, last_race) VALUES (ed584e99-80f7-4b13-9a90-9dc5571e6821,'TSATEVICH', '1989-07-05', 'Russia', 64, 'kg', 1.69, '2006-03-15','2017-04-16');
INSERT INTO cycling.cyclist_alt_stats (id, lastname, birthday, nationality, weight, w_units, height,  first_race, last_race) VALUES (a9e96714-2dd0-41f9-8bd0-557196a44ecf,'ISAYCHEV', '1986-04-21', 'Russia', 80, 'kg', 1.88,'2003-04-22','2017-03-05');
INSERT INTO cycling.cyclist_alt_stats (id, lastname, birthday, nationality, weight, w_units, height,  first_race, last_race) VALUES (823ec386-2a46-45c9-be41-2425a4b7658e,'BELKOV', '1985-01-09', 'Russia', 71, 'kg', 1.84,'2002-03-22','2017-04-16');
INSERT INTO cycling.cyclist_alt_stats (id, lastname, birthday, nationality, weight, w_units, height,  first_race, last_race) VALUES (e0953617-07eb-4c82-8f91-3b2757981625,'BRUTT', '1982-01-29', 'Russia', 68, 'kg', 1.78,'1998-02-15','2017-04-16');
INSERT INTO cycling.cyclist_alt_stats (id, lastname, birthday, nationality, weight, w_units, height,  first_race, last_race) VALUES (078654a6-42fa-4142-ae43-cebdc67bd902,'LAGUTIN', '1981-01-14', 'Russia', 63, 'kg', 1.82,'1996-05-21','2010-10-02');
INSERT INTO cycling.cyclist_alt_stats (id, lastname, birthday, nationality, weight, w_units, height,  first_race, last_race) VALUES (d74d6e70-7484-4df5-8551-f5090c37f617,'GRMAY', '1991-08-25', 'Ethiopia', 63, 'kg', 1.75, '2006-05-21','2017-04-16');
INSERT INTO cycling.cyclist_alt_stats (id, lastname, birthday, nationality, weight, w_units, height,  first_race, last_race) VALUES (c09e9451-50da-483d-8108-e6bea2e827b3,'VEIKKANEN', '1981-03-29', 'Finland', 66, 'kg', 1.78,'1996-05-21','2012-10-02');
INSERT INTO cycling.cyclist_alt_stats (id, lastname, birthday, nationality, weight, w_units, height,  first_race, last_race) VALUES (f1deff54-7d96-4981-b14a-b70be4da82d2,'TLEUBAYEV', null, 'Kazakhstan', null, null, null, '2003-04-22','2017-04-16');
INSERT INTO cycling.cyclist_alt_stats (id, lastname, birthday, nationality, weight, w_units, height,  first_race, last_race) VALUES (1ba0417d-62da-4103-b710-de6fb227db6f,'PAULINHO', '1990-05-27', 'Portugal', null, null, null, '2006-03-15','2017-03-05');
INSERT INTO cycling.cyclist_alt_stats (id, lastname, birthday, nationality, weight, w_units, height,  first_race, last_race) VALUES (4ceb495c-55ab-4f71-83b9-81117252bb13,'DUVAL', '1990-05-27','France', null, null, null, '2006-03-15','2017-04-16');

Retrieve data

Select all rows in the table:

SELECT * FROM cycling.cyclist_alt_stats;

Result

 id                                   | birthday   | first_race | height | last_race  | lastname  | nationality | w_units | weight
--------------------------------------+------------+------------+--------+------------+-----------+-------------+---------+--------
 e0953617-07eb-4c82-8f91-3b2757981625 | 1982-01-29 | 1998-02-15 |   1.78 | 2017-04-16 |     BRUTT |      Russia |      kg |     68
 a9e96714-2dd0-41f9-8bd0-557196a44ecf | 1986-04-21 | 2003-04-22 |   1.88 | 2017-03-05 |  ISAYCHEV |      Russia |      kg |     80
 ed584e99-80f7-4b13-9a90-9dc5571e6821 | 1989-07-05 | 2006-03-15 |   1.69 | 2024-06-07 | TSATEVICH |      Russia |      kg |     64
 078654a6-42fa-4142-ae43-cebdc67bd902 | 1981-01-14 | 1996-05-21 |   1.82 | 2010-10-02 |   LAGUTIN |      Russia |      kg |     63
 1ba0417d-62da-4103-b710-de6fb227db6f | 1990-05-27 | 2006-03-15 |   null | 2017-03-05 |  PAULINHO |    Portugal |    null |   null
 d74d6e70-7484-4df5-8551-f5090c37f617 | 1991-08-25 | 2006-05-21 |   1.75 | 2017-04-16 |     GRMAY |    Ethiopia |      kg |     63
 c09e9451-50da-483d-8108-e6bea2e827b3 | 1981-03-29 | 1996-05-21 |   1.78 | 2012-10-02 | VEIKKANEN |     Finland |      kg |     66
 823ec386-2a46-45c9-be41-2425a4b7658e | 1985-01-09 | 2002-03-22 |   1.84 | 2017-04-16 |    BELKOV |      Russia |      kg |     71
 f1deff54-7d96-4981-b14a-b70be4da82d2 |       null | 2003-04-22 |   null | 2017-04-16 | TLEUBAYEV |  Kazakhstan |    null |   null
 4ceb495c-55ab-4f71-83b9-81117252bb13 | 1990-05-27 | 2006-03-15 |   null | 2017-04-16 |     DUVAL |      France |    null |   null

(10 rows)

 id                                   | birthday   | first_race | height | last_race  | lastname  | nationality | w_units | weight
--------------------------------------+------------+------------+--------+------------+-----------+-------------+---------+--------
 e0953617-07eb-4c82-8f91-3b2757981625 | 1982-01-29 | 1998-02-15 |   1.78 | 2017-04-16 |     BRUTT |      Russia |      kg |     68
 a9e96714-2dd0-41f9-8bd0-557196a44ecf | 1986-04-21 | 2003-04-22 |   1.88 | 2017-03-05 |  ISAYCHEV |      Russia |      kg |     80
 ed584e99-80f7-4b13-9a90-9dc5571e6821 | 1989-07-05 | 2006-03-15 |   1.69 | 2024-06-10 | TSATEVICH |      Russia |      kg |     64
 078654a6-42fa-4142-ae43-cebdc67bd902 | 1981-01-14 | 1996-05-21 |   1.82 | 2010-10-02 |   LAGUTIN |      Russia |      kg |     63
 1ba0417d-62da-4103-b710-de6fb227db6f | 1990-05-27 | 2006-03-15 |   null | 2017-03-05 |  PAULINHO |    Portugal |    null |   null
 d74d6e70-7484-4df5-8551-f5090c37f617 | 1991-08-25 | 2006-05-21 |   1.75 | 2017-04-16 |     GRMAY |    Ethiopia |      kg |     63
 c09e9451-50da-483d-8108-e6bea2e827b3 | 1981-03-29 | 1996-05-21 |   1.78 | 2012-10-02 | VEIKKANEN |     Finland |      kg |     66
 823ec386-2a46-45c9-be41-2425a4b7658e | 1985-01-09 | 2002-03-22 |   1.84 | 2017-04-16 |    BELKOV |      Russia |      kg |     71
 f1deff54-7d96-4981-b14a-b70be4da82d2 |       null | 2003-04-22 |   null | 2017-04-16 | TLEUBAYEV |  Kazakhstan |    null |   null
 4ceb495c-55ab-4f71-83b9-81117252bb13 | 1990-05-27 | 2006-03-15 |   null | 2017-04-16 |     DUVAL |      France |    null |   null

(10 rows)

Add an SAI index to select rows based on the nationality column:

CREATE CUSTOM INDEX nationality_idx ON cycling.cyclist_alt_stats (nationality)
  USING 'StorageAttachedIndex';

Now select specific rows for cyclists from France:

SELECT first_race, last_race, birthday FROM cycling.cyclist_alt_stats
  WHERE nationality = 'France';

Result

 first_race | last_race  | birthday
------------+------------+------------
 2006-03-15 | 2017-04-16 | 1990-05-27

(1 rows)

 first_race | last_race  | birthday
------------+------------+------------
 2006-03-15 | 2017-04-16 | 1990-05-27

(1 rows)

Update data

Update a cyclist’s nationality using the primary key id:

UPDATE cycling.cyclist_alt_stats SET nationality = 'France'
  WHERE id = ed584e99-80f7-4b13-9a90-9dc5571e6821;

Verify the row has the updated value:

SELECT first_race, last_race, birthday FROM cycling.cyclist_alt_stats
  WHERE nationality = 'France';

Result

 first_race | last_race  | birthday
------------+------------+------------
 2006-03-15 | 2017-04-16 | 1990-05-27
 2006-03-15 | 2024-06-07 | 1989-07-05

(2 rows)

Delete data

Delete a specific row using the primary key id:

DELETE FROM cycling.cyclist_alt_stats WHERE id = ed584e99-80f7-4b13-9a90-9dc5571e6821;

Cassandra Query Language (CQL) quickstart

Prerequisites:

Create a keyspace

Create a table

Insert data

Retrieve data

Update data

Delete data

See also

Was this helpful?

Give Feedback