Cassandra Query Language (CQL) quickstart

For more complete documentation, see the CQL documentation.

Because DataStax Enterprise (DSE) 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 DataStax Enterprise (DSE) database with the CQL shell (CQLSH).

You can also send CQL queries to your DataStax Enterprise (DSE) 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