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:

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;

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com