Understanding the CQL command syntax

Provides the basic rules and limitations for writing CQL statements.

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

For example, the following is valid CQL syntax:

SELECT * FROM MyTable;

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

This is a sequence of two CQL statements. This example shows one statement per line, although a statement can usefully be split across lines as well.

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 by using double quotation marks. For example:

CREATE TABLE 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:

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 internally, as the database stores foo in lowercase. The double-quotation mark character can be used as an escape character for the double quotation mark.

Case sensitivity rules in earlier versions of CQL apply when handling legacy tables.

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

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; all other names support any characters, such as COLUMN, FUNCTION, AGGREGATE, TYPE, etc.

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

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 in these statements:
  • ALTER TABLE
  • CREATE TABLE
  • DELETE
  • INSERT
  • SELECT
  • TRUNCATE
  • UPDATE

Procedure

To specify a table when you are not in the keyspace that contains the table, use the name of the keyspace followed by a period, then the table name. For example, cycling.race_winners.
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

Using single and double quotation marks in CQL.

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

Dates, IP addresses, and strings need to 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_start_date, race_end_date, race_name) VALUES 
  (201, '2015-02-18', '2015-02-22', 'Women''s Tour of New Zealand');

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_start_date, race_end_date, race_name) VALUES 
  (201, '2015-02-18', '2015-02-22', $$Women's Tour of New Zealand$$);

Valid literals

Values and definitions of valid literals.

Valid literal consist of these 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. Since 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 used inside string literals, but otherwise CQL ignores whitespace.

Exponential notation

DataStax Enterprise supports exponential notation.

DataStax Enterprise supports exponential notation. This example shows exponential notation in the output from a cqlsh command.
CREATE TABLE test(
  id varchar PRIMARY KEY,
  value_double double,
  value_float float
);

INSERT INTO test (id, value_float, value_double)
  VALUES ('test1', -2.6034345E+38, -2.6034345E+38);

SELECT * FROM test;

 id    | value_double | value_float
-------+--------------+-------------
 test1 |  -2.6034e+38 | -2.6034e+38

Code comments

Add comments to CQL code.

Use the following notation to include comments in CQL code:
  • For a single line or end of line put a double hyphen before the text, this comments out the rest of the line:
    select * from cycling.route; -- End of line comment
  • For a single line or end of line put a double forward slash before the text, this comments out the rest of the line:
    select * from cycling.route; // 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 the first line of 
       of a comment that spans multiple
      lines */
    select * from cycling.route;

Keywords

Table of keywords and whether or not the words are reserved.

This table lists keywords and whether or not the words are reserved. A reserved keyword cannot be used as an identifier unless you enclose the word in double quotation marks. Non-reserved keywords have a specific meaning in certain context but can be used as an identifier outside this context.

Keywords
Keyword Reserved
ADD yes
AGGREGATE yes
ALL no
ALLOW yes
ALTER yes
AND yes
ANY yes
APPLY yes
AS no
ASC yes
ASCII no
AUTHORIZE yes
BATCH yes
BEGIN yes
BIGINT no
BLOB no
BOOLEAN no
BY yes
CLUSTERING no
COLUMNFAMILY yes
COMPACT no
CONSISTENCY no
COUNT no
COUNTER no
CREATE yes
CUSTOM no
DECIMAL no
DELETE yes
DESC yes
DISTINCT no
DOUBLE no
DROP yes
EACH_QUORUM yes
ENTRIES yes
EXISTS no
FILTERING no
FLOAT no
FROM yes
FROZEN no
FULL yes
GRANT yes
IF yes
IN yes
INDEX yes
INET yes
INFINITY yes
INSERT yes
INT no
INTO yes
KEY no
KEYSPACE yes
KEYSPACES yes
LEVEL no
LIMIT yes
LIST no
LOCAL_ONE yes
LOCAL_QUORUM yes
MAP no
MATERIALIZED yes
MODIFY yes
NAN yes
NORECURSIVE yes
NOSUPERUSER no
NOT yes
OF yes
ON yes
ONE yes
ORDER yes
PARTITION yes
PASSWORD yes
PER yes
PERMISSION no
PERMISSIONS no
PRIMARY yes
QUORUM yes
RENAME yes
REVOKE yes
SCHEMA yes
SELECT yes
SET yes
STATIC no
STORAGE no
SUPERUSER no
TABLE yes
TEXT no
TIME yes
TIMESTAMP no
TIMEUUID no
THREE yes
TO yes
TOKEN yes
TRUNCATE yes
TTL no
TUPLE no
TWO yes
TYPE no
UNLOGGED yes
UPDATE yes
USE yes
USER no
USERS no
USING yes
UUID no
VALUES no
VARCHAR no
VARINT no
VIEW yes
WHERE yes
WITH yes
WRITETIME no

Limitations

Upper CQL limits.

Observe the following upper limits:

  • Cells in a partition: ~2 billion (231); single column value size: 2 GB ( 1 MB is recommended)
  • Clustering column value, length of: 65535 (216-1)
  • Key length: 65535 (216-1)
  • Table / CF name length: 48 characters
  • Keyspace name length: 48 characters
  • Query parameters in a query: 65535 (216-1)
  • Statements in a batch: 65535 (216-1)
  • Fields in a tuple: 32768 (215) (just a few fields, such as 2-10, are recommended)
  • Collection (List): collection limit: ~2 billion (231); values size: 65535 (216-1)
  • Collection (Set): collection limit: ~2 billion (231); values size: 65535 (216-1)
  • Collection (Map): collection limit: ~2 billion (231); number of keys: 65535 (216-1); values size: 65535 (216-1)
  • Blob size: 2 GB ( less than 1 MB is recommended)
Note: The limits specified for collections are for non-frozen collections.