Understanding the CQL command syntax

Basic rules and limitations for writing CQL statements; including case, valid characters, escaping characters, keyspace qualifiers, commenting, keywords, and exponential notation.

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.test (
  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:

Table 1. What Works and What Doesn't
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 ... 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

Only alpha-numeric characters and underscores are allowed in keyspace and table names; all other names support any characters.

Only alpha-numeric characters and underscores are allowed in keyspace and table names. Keyspace and table names must begin with an alphabetic charcter. All other element names, such as columns or types support any characters.

To specify a name that contains a special character, like period (.) or hyphen (-), enclose the name in double quotes.

Table 2. What Works and What Doesn't
Creations that Work	Creations that Don't Work
CREATE TABLE foo ...	CREATE TABLE foo!$% ...
CREATE TABLE foo_bar ...	CREATE TABLE foo[]"90 ...
CREATE TABLE foo ("what#*&" text, ...)	CREATE TABLE foo (what#*& text, ...)
ALTER TABLE foo5 ...	ALTER TABLE "foo5$$"...
CREATE FUNCTION "foo5$$$^%" ...	CREATE FUNCTION foo5$$...
CREATE AGGREGATE "foo5!@#" ...	CREATE AGGREGATE foo5$$
CREATE TYPE foo5 ("bar#9" text, ...	CREATE TYPE foo5 (bar#9 text ...

Keyspace qualifier

To simplify tracking multiple keyspaces, use the keyspace qualifier instead of the USE statement.

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:

Procedure

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' }
);

Escaping characters

Escape characters using single and double quotation marks in CQL.

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

Values and definitions of valid literals.

Valid literals consist of the following kinds of values:

blob
hexadecimal defined as 0[xX](hex)+
boolean
true or false, case-insensitive, not enclosed in quotation marks
numeric constant
A numeric constant can consist of integers 0-9 and a minus sign prefix. A numeric constant can also be float. A float can be a series of one or more decimal digits, followed by a period (.) and one or more decimal digits. There is no optional + sign. The forms .42 and 42 are unacceptable. You can use leading or trailing zeros before and after decimal points. For example, 0.42 and 42.0. A float constant, expressed in E notation, consists of the characters in this regular expression:
```
'-'?[0-9]+('.'[0-9]*)?([eE][+-]?[0-9+])?
```
NaN and Infinity are floats.
identifier
Names of tables, columns, types, and other objects are identifiers. 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 and user-defined function names can contain any character. To specify an identifier that contains a special character, enclose the name in quotes.
integer
An optional minus sign (-) followed by one or more digits.
string literal
Characters enclosed in single quotation marks. To use a single quotation mark itself in a string literal, escape it using a single quotation mark. For example, use '' to make dog possessive: dog''s.
uuid
32 hex digits, 0-9 or a-f, which are case-insensitive, separated by dashes (-) after the 8th, 12th, 16th, and 20th digits. For example: 01234567-0123-0123-0123-0123456789ab
timeuuid
Uses the time in 100 nanosecond intervals since 00:00:00.00 UTC (60 bits), a clock sequence number for prevention of duplicates (14 bits), plus 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

DataStax Enterprise supports exponential notation.

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

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

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

SELECT *
FROM cycling.team_race_time;

Output:

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

(1 rows)

Code comments

Add comments to CQL code.

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.
```
/* This is a 
   comment that spans multiple
   lines */
SELECT * FROM cycling.race_winners;
```

Keywords

List of reserved and non-reserved keywords.

Reserved keywords have specific meaning in CQL. To use a reserved keyword as an identifier for a keyspace, table, column, function, or user-defined type name, enclose it in double quotes.

Non-reserved keywords are keywords that behave like a reserved keyword, but only in a specific place in a command. Non-reserved keywords can be used as an identifier outside this context without double quotes.

CQL Basic Reserved keywords

To use reserved keywords as an identifier in CQL, enclose in double quotes. For example:

CREATE TABLE cycling."add" (a int PRIMARY KEY);

Reserved keyword list:

ADD
ALLOW
ALTER
AND
APPLY
ASC
AUTHORIZE
BATCH
BEGIN
BY
COLUMNFAMILY
CREATE
DEFAULT
DELETE
DESC
DESCRIBE
DROP
ENTRIES
EXECUTE
FOR
FROM
FULL
GRANT
IF
IN
INDEX
INFINITY
INSERT
INTO
IS
KEYSPACES
LIMIT
MATERIALIZED
MBEAN
MBEANS
MODIFY
NAN
NORECURSIVE
NOT
NULL
OF
ON
OR
ORDER
PRIMARY
RENAME
REPLACE
REVOKE
SCHEMA
SELECT
SET
TABLE
TO
TOKEN
TRUNCATE
UNLOGGED
UNSET
UPDATE
USE
USING
VIEW
WHERE
WITH

CQL Basic Non-reserved keywords

Non-reserved keywords can be used as an identifier without double quotes outside the specific place that the keyword has meaning in a command.

Non-reserved keyword list:

AGGREGATE
ALL
AS
CALLED
CAST
CLUSTERING
COMPACT
CONTAINS
COUNT
CUSTOM
DISTINCT
EXISTS
FILTERING
FINALFUNC
FROZEN
FUNCTION
FUNCTIONS
GROUP
INITCOND
INPUT
JSON
KEY
KEYS
KEYSPACE
LANGUAGE
LIKE
LIST
LOGIN
MAP
NOLOGIN
NOSUPERUSER
OPTIONS
PARTITION
PASSWORD
PER
PERMISSION
PERMISSIONS
RESTRICT
RETURNS
ROLE
ROLES
SFUNC
STATIC
STORAGE
STYPE
SUPERUSER
TRIGGER
TTL
TYPE
USER
USERS
VALUES
WRITETIME

All CQL data types are also non-reserved keywords:

ASCII
BIGINT
BLOB
BOOLEAN
COUNTER
DATE
DECIMAL
DOUBLE
DURATION
FLOAT
INET
INT
SMALLINT
TEXT
TIME
TIMESTAMP
TIMEUUID
TINYINT
TUPLE
UNRESTRICT
UUID
VARCHAR
VARINT

Astra Non-reserved keywords

There are additional non-reserved keywords can be used as an identifier without double quotes in CQL that are Astra-specific.

Astra Non-reserved keyword list:

ACTIVE
ANY
APPLICATION
APPLICATIONS
AUTHENTICATION
BACKUPS
CALL
CALLS
CANCEL
CLEAN
COLUMNS
COMMIT
CONFIG
CONFIGURATION
CONFIGURATIONS
CLUSTER
DEFAULT
DELEGATION
DESTINATION
DESTINATIONS
DETERMINISTIC
DROPPED
EDGE
EXECUTOR
EXECUTORS
FIELD
HASHED
INDICES
INTERNAL
JAVA
KERBEROS
LDAP
LABEL
LOWERCASESTRING
METHOD
MONOTONIC
NO
NODE
NODES
OBJECT
PLAN
PROFILES
REBUILD
RECORD
REDACT
RELOAD
REMOTE
RENEW
RESOURCE
RESTORE
RESTRICT
ROWS
RUN
SCHEMA
SCHEME
SCHEMES
SEARCH
STDERR
STDOUT
STORE
SUBMISSION
TARGET
TOKEN
UNRESTRICT
UNSET
VERIFY
VERTEX
WITHOUT
WORKPOOL

Limitations

Upper CQL limits.

The upper limits are as follows:

Cells in a partition: ~2 billion (2³¹); single column value size: 2 GB (1 MB is recommended)
Clustering column value length of: 65535 (2¹⁶ - 1)
Key length: 65535 (2¹⁶ - 1)
Table / column family name length: 222 characters
Keyspace name length: 222 characters
Query parameters in a query: 65535 (2¹⁶ - 1)
Statements in a batch: 65535 (2¹⁶ - 1)
Fields in a tuple: 32768 (2¹⁵) (just a few fields, such as 2 to 10, are recommended)
Collection (list): collection limit is ~2 billion (2³¹); values size is 65535 (2¹⁶ - 1)
Collection (set): collection limit is ~2 billion (2³¹); values size is 65535 (2¹⁶ - 1)
Collection (map): collection limit for number of keys is 65535 (2¹⁶ - 1); values size is 65535 (2¹⁶ - 1)
Blob size: 2 GB (less than 1 MB is recommended)

Note: The limits specified for collections are for non-frozen collections.