CQL command syntax

Cassandra Query Language (CQL) is the native query language of Apache Cassandra®. You can use CQL to interact with your DSE databases. CQL syntax is similar to SQL, but there are important differences for interacting with distributed, non-relational data.

This reference includes Cassandra Query Language (CQL) commands and CQL shell (cqlsh) commands. CQL statements are based on the specification 3.4.5 with additional DSE Search, SAI, and security-related enhancements.

CQL commands can be executed with the CQL shell or a Apache Cassandra driver. cqlsh commands are only available in the CQL shell.

Basic syntax

CQL input consists of statements. Like SQL, CQL statements change data, look up data, store data, or change the way data is stored. Statements end in a semicolon (;).

For example, these are valid CQL statements:

SELECT * FROM MyTable;

UPDATE MyTable
SET SomeColumn = 'SomeValue'
WHERE columnName = B70DE1D0-9908-4AE3-BE34-5573E5B09F14;

The example contains SELECT and UPDATE statements. The SELECT is on one line. A statement can be split across multiple lines, as shown in the UPDATE statement.

In cqlsh, type help to list all available topics. Type help <name> to find out more about the name command. For example help CAPTURE or help ALTER_KEYSPACE.

Uppercase and lowercase

Keyspace, column, and table names are case-insensitive unless enclosed in double quotation marks.

Identifiers created using CQL are case-insensitive unless enclosed in double quotation marks. If you enter names for these objects using any uppercase letters, the database stores the names in lowercase. You can force the case using double quotation marks. For example:

CREATE TABLE cycling.uppercase (
  Foo int PRIMARY KEY,
  "Bar" int
);

The following table shows partial queries that work and do not work to return results from the test table:

Queries that Work Queries that Don’t Work

Queries that Work	Queries that Don’t Work
`SELECT foo FROM . . .`	`SELECT "Foo" FROM . . .`
`SELECT Foo FROM . . .`	`SELECT "BAR" FROM . . .`
`SELECT FOO FROM . . .`	`SELECT bar FROM . . .`
`SELECT "Bar" FROM . . .`	`SELECT Bar FROM . . .`
`SELECT "foo" FROM . . .`	`SELECT "bar" FROM . . .`

SELECT foo FROM . . .

SELECT "Foo" FROM . . .

SELECT Foo FROM . . .

SELECT "BAR" FROM . . .

SELECT FOO FROM . . .

SELECT bar FROM . . .

SELECT "Bar" FROM . . .

SELECT Bar FROM . . .

SELECT "foo" FROM . . .

SELECT "bar" FROM . . .

SELECT "foo" FROM ... works because the database stores foo in lowercase.

When using legacy tables, case-sensitivity rules in earlier versions of CQL apply.

CQL keywords are case-insensitive. For example, SELECT and select are equivalent. This document shows keywords in uppercase.

To escape characters, see Escaping characters.

Valid characters in names

Keyspace and table names must begin with a letter, and they can contain letters, numbers, and underscores.

All other element names, such as columns and types, can contain any characters. However, if a name contains a special character, such as a period or hyphen, then you must enclose the name in double quotes.

Here are some examples of valid and invalid statements with special characters. These statements are truncated to focus on the use of special characters and double quotes.

// Valid statements
CREATE TABLE foo ...
CREATE TABLE foo_bar ...
CREATE TABLE foo ("what#*&" text, ...)
ALTER TABLE foo5 ...
CREATE FUNCTION "foo5$$${caret}%" ...
CREATE AGGREGATE "foo5!@#" ...
CREATE TYPE foo5 ("bar#9" text, ...)

// Invalid statements

// Table names can only include letters, numbers, and underscores
CREATE TABLE foo!$% ...
CREATE TABLE foo[]"90 ...
ALTER TABLE "foo5$$"...

// Element names with special characters must be enclosed in double quotes
CREATE TABLE foo (what#*& text, ...)
CREATE FUNCTION foo5$$...
CREATE AGGREGATE foo5$$
CREATE TYPE foo5 (bar#9 text, ...)

Keyspace qualifier

Sometimes issuing a USE statement to select a keyspace is inconvenient. Connection pooling requires managing multiple keyspaces. To simplify tracking multiple keyspaces, use the keyspace qualifier instead of the USE statement. You can specify the keyspace using the keyspace qualifier (dot notation) in these statements:

To specify a table when you are not located in the keyspace that contains the table, specify the name of the keyspace followed by a period, then the table name. For example, cycling.race_winners, where cycling is the keyspace and race_winners is the table name.

INSERT INTO cycling.race_winners (
  race_name, race_position,
  cyclist_name
) VALUES (
  'National Championships South Africa WJ-ITT (CN)', 1,
  { firstname:'Frances', lastname:'DU TOUT' }
);

INSERT INTO cycling.race_winners (
  race_name, race_position,
  cyclist_name
) VALUES (
  'National Championships South Africa WJ-ITT (CN)', 2,
  { firstname:'Lynette', lastname:'BENSON' }
);

INSERT INTO cycling.race_winners (
  race_name, race_position,
  cyclist_name
) VALUES (
  'National Championships South Africa WJ-ITT (CN)', 3,
  { firstname:'Anja', lastname:'GERBER' }
);

INSERT INTO cycling.race_winners (
  race_name, race_position,
  cyclist_name
) VALUES (
  'National Championships South Africa WJ-ITT (CN)', 4,
  { firstname:'Ame', lastname:'VENTER' }
);

INSERT INTO cycling.race_winners (
  race_name, race_position,
  cyclist_name
) VALUES (
  'National Championships South Africa WJ-ITT (CN)', 5,
  { firstname:'Danielle', lastname:'VAN NIEKERK' }
);

Escaping characters

Column names that contain characters that CQL cannot parse must be enclosed in double quotation marks in CQL.

Dates, IP addresses, and strings must be enclosed in single quotation marks. To use a single quotation mark itself in a string literal, escape it using a single quotation mark.

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  201, 'Women''s Tour of New Zealand', '2015-02-18', '2015-02-22'
);

An alternative is to use dollar-quoted strings. Dollar-quoted string constants can be used to create functions, insert data, and select data when complex quoting is needed. Use double dollar signs to enclose the desired string.

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  201, $$Women's Tour of New Zealand$$, '2015-02-18', '2015-02-22'
);

Valid literals

Valid literals consist of the following kinds of values:

Hexadecimal BLOBs
Booleans
Integers and numeric constants (integers or floats).

A numeric constant can consist of integers 0 through 9 with an optional negative sign prefix. Positive sign prefixes aren’t supported or necessary.

A numeric constant can also be a float. A float can be a NaN, Infinity, E notation, or a series of one or more decimal digits followed by a period and one or more decimal digits.

Decimals without leading or trailing zeros are invalid, such as .42 and 42.. The valid forms are 0.42 and 42.0.
Identifiers (names of tables, columns, types, and other objects).

Because keyspace and table names are used in system file names, they must start with a letter or number and can only contain alphanumeric characters and underscores. All other identifiers, such as column names and user-defined function names, can contain any character. To specify an identifier that contains a special character, enclose the name in quotes.
String literals.

These are characters enclosed in single quotation marks. If the string literal includes single quotation marks, use an additional single quotation mark to escape them. For example, the string dog’s is represented as 'dog''s'.
UUIDs.

UUIDs are 32 case-insensitive hex digits ([0-9,a-f,A-F]) separated by dashes (-) after the 8th, 12th, 16th, and 20th digits. For example: 01234567-0123-0123-0123-0123456789ab.
TimeUUIDs.

TimeUUIDs use the time in 100-nanosecond intervals since 00:00:00.00 UTC (60 bits), a clock sequence number to prevent duplicates (14 bits), and the IEEE 801 MAC address (48 bits) to generate a unique identifier. For example: d2177dd0-eaa2-11de-a572-001b779c76e3.
Whitespace.

Separates terms and is used inside string literals, but otherwise CQL ignores whitespace.

Exponential notation

DSE supports exponential notation. This example shows the use of exponential notation in the double and float values.

Create a table with double and float columns:

CREATE TABLE cycling.team_race_time (
  id text PRIMARY KEY,
  value_double double,
  value_float float
);

Insert rows with double and float values:

INSERT INTO cycling.team_race_time (
  id, value_float, value_double
) VALUES (
  'Rabobank-Liv Woman Cycling Team', 2.6034345E+38, 2.6034345E+38
);

A float constant expressed in E notation consists of the characters in the following regular expression:

'-'?[0-9]+('.'[0-9]*)?([eE][+-]?[0-9+])?

Query the table:
```
SELECT * FROM cycling.team_race_time;
```

Notice that the double and float values are returned with rounding:

 id                              | value_double | value_float
---------------------------------+--------------+-------------
 Rabobank-Liv Woman Cycling Team |   2.6034e+38 |  2.6034e+38

(1 rows)

Arithmetic operators

CQL supports these arithmetic operators:

Operator Description

Operator	Description
`-`	Use the minus symbol for either the unary negate operand or subtraction.
`+`	Use the plus symbol for addition.
`*`	Use the asterisk symbol for multiplication.
`/`	Use the forward slash symbol for division.
`%`	Use the percent symbol to return the remainder from division.

-

Use the minus symbol for either the unary negate operand or subtraction.

+

Use the plus symbol for addition.

*

Use the asterisk symbol for multiplication.

/

Use the forward slash symbol for division.

%

Use the percent symbol to return the remainder from division.

The *, /, and % operators have a higher precedence than the + and - operators. Higher precedence operators are evaluated before lower precedence operators. If two operators in an expression have the same precedence, they are evaluated from left to right based on the operator position in the expression.

Multiple columns can be used in an expression, as shown in the following queries:

SELECT (laps_completed + bonus_laps) * lap_distance FROM cycling.race_results;

A time duration can be added (+) or subtracted (-) from a timestamp or a date to create a new timestamp or date. For example, the following query returns cycling races that occur one day before the supplied date:

SELECT * FROM cycling.race_times
  WHERE race_date = '2017-04-15' - 1d;

Return types

The return type of an arithmetic expression is determined by numeric type promotion. When an expression contains mixed data types, the result promotes to the wider or higher-precision type according to the following hierarchies:

Integer types: tinyint → smallint → int → bigint / counter → varint
Floating-point types: float → double → decimal

When mixing integer and floating-point types:

Combining tinyint, smallint, or int with float returns float.
Combining bigint or counter with float promotes to double to prevent precision loss.
Combining any numeric type with double returns double (except decimal and varint).
Combining varint with bigint, counter, or any floating-point type promotes to decimal.
Combining any numeric type with decimal returns decimal.

Other operators

CQL supports the following non-arithmetic operators in certain conditions:

Operator Description

Operator	Description
`CONTAINS`	Use to find a value in a collection .
`CONTAINS`	Use to find a key in a map collection.
`IN`	Use to find a value included in the specified list of values.
`OR`	Use to combine multiple conditions in a query.
`LIKE`	Use to find a value that matches a pattern if a fuzzy search is enabled.

CONTAINS

Use to find a value in a collection .

CONTAINS

Use to find a key in a map collection.

IN

Use to find a value included in the specified list of values.

OR

Use to combine multiple conditions in a query.

LIKE

Use to find a value that matches a pattern if a fuzzy search is enabled.

Code comments

Use the following notation to include comments in CQL code:

For a single line or end of a line, place a double hyphen (--) before the text to comment out the rest of the line:
```
SELECT * FROM cycling.race_winners; -- End of line comment
```
For a single line or end of a line, place a double forward slash (//) before the text to comment out the rest of the line:
```
SELECT * FROM cycling.race_winners; // End of line comment
```
For a block of comments, put a forward slash asterisk at the beginning of the comment and then asterisk forward slash at the end:
```
SELECT * FROM cycling.race_winners;
/* This is a
   comment that spans multiple
   lines */
```

Limitations

The upper limits are as follows:

Cells in a partition: ~2 billion (2^31)
Single column value size in a partition: 2 GB (1 MB is recommended)
Clustering column value length of: 65535 (2^16 - 1)
Key length: 65535 (2^16 - 1)
Table/column family name length: 222 characters
Keyspace name length: 222 characters
Query parameters in a query: 65535 (2^16 - 1)
Statements in a BATCH: 65535 (2^16 - 1)
Fields in a tuple: 32768 (2^15) (No more than 10 fields are recommended)
Non-frozen collection (list):
- Collection limit is ~2 billion (2^31);
- Values size is 65535 (2^16 - 1)
Frozen collections are measured as single values.
Non-frozen collection (set):
- Collection limit is ~2 billion (2^31);
- Values size is 65535 (2^16 - 1)
Frozen collections are measured as single values.
blob size: 2 GB (less than 1 MB is recommended)
vector: maximum dimension is 8K (2^13)

CQL type conversion compatibility

CQL data types have strict requirements for conversion compatibility.

Some conversions are prevented on clustering columns. Clustering columns have stricter requirements because they mandate the order in which data is written to disk.

Original data type	Converted data type	Clustering columns	Non-clustering columns
ascii, bigint, boolean, decimal, double, float, inet, int, timestamp, timeuuid, uuid, varchar, varint	blob	Not allowed on clustering columns	Allowed on non-clustering columns
int	varint	Allowed on clustering columns	Allowed on non-clustering columns
text	varchar	Allowed on clustering columns	Allowed on non-clustering columns
timeuuid	uuid	Not allowed on clustering columns	Allowed on non-clustering columns
varchar	text	Allowed on clustering columns	Allowed on non-clustering columns

Original data type

Converted data type

Clustering columns

Non-clustering columns

ascii, bigint, boolean, decimal, double, float, inet, int, timestamp, timeuuid, uuid, varchar, varint

blob

Not allowed on clustering columns