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

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_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 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 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 a line, place a double hyphen (--) before the text to comment out the rest of the line:
    select * from cycling.route; -- 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.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

List of reserved and non-reserved keywords.

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 have a specific meaning in certain context but can be used as an identifier outside this context.

Reserved keywords

To use reserved keywords as an identifier in CQL, enclose in double quotes. For example:
CREATE TABLE test."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
  • KEY
  • LANGUAGE
  • MATERIALIZED
  • MBEAN
  • MBEANS
  • MODIFY
  • NAN
  • NORECURSIVE
  • NOT
  • NULL
  • OF
  • ON
  • OR
  • ORDER
  • PRIMARY
  • RENAME
  • REPLACE
  • RESTRICT
  • REVOKE
  • SCHEMA
  • SELECT
  • SET
  • TABLE
  • TO
  • TOKEN
  • TRUNCATE
  • UNLOGGED
  • UNRESTRICT
  • UNSET
  • UPDATE
  • USE
  • USING
  • VIEW
  • WHERE
  • WITH

Non-reserved keywords

Non-reserved keywords have a specific meaning in certain context but can be used as an identifier outside this context.

Non-reserved keyword list:
  • AGGREGATE
  • ALL
  • ANY
  • AS
  • ASCII
  • BIGINT
  • BLOB
  • BOOLEAN
  • CALLED
  • CAST
  • CLUSTERING
  • COMPACT
  • CONTAINS
  • COUNT
  • COUNTER
  • CUSTOM
  • DATE
  • DECIMAL
  • DETERMINISTIC
  • DISTINCT
  • DOUBLE
  • DURATION
  • EXISTS
  • FILTERING
  • FINALFUNC
  • FLOAT
  • FROZEN
  • FUNCTION
  • FUNCTIONS
  • GROUP
  • INET
  • INITCOND
  • INPUT
  • INT
  • JSON
  • KEYS
  • KEYSPACE
  • KEYSPACES
  • LIKE
  • LIMIT
  • LIST
  • LOGIN
  • MAP
  • MONOTONIC
  • NOLOGIN
  • NOSUPERUSER
  • OPTIONS
  • PARTITION
  • PASSWORD
  • PER
  • PERMISSION
  • PERMISSIONS
  • RESOURCE
  • RETURNS
  • ROLE
  • ROLES
  • SFUNC
  • SMALLINT
  • STATIC
  • STORAGE
  • STYPE
  • SUBMISSION
  • SUPERUSER
  • TEXT
  • TIME
  • TIMESTAMP
  • TIMEUUID
  • TINYINT
  • TRIGGER
  • TTL
  • TUPLE
  • TYPE
  • USER
  • USERS
  • UUID
  • VALUES
  • VARCHAR
  • VARINT
  • WORKPOOL
  • WRITETIME

Limitations

Upper CQL limits.

The upper limits are as follows:
  • 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 / column family name length: 222 characters
  • Keyspace name length: 222 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 to 10, are recommended)
  • Collection (list): collection limit is ~2 billion (231); values size is 65535 (216 - 1)
  • Collection (set): collection limit is ~2 billion (231); values size is 65535 (216 - 1)
  • Collection (map): collection limit for number of keys is 65535 (216 - 1); values size is 65535 (216 - 1)
  • Blob size: 2 GB (less than 1 MB is recommended)
Note: The limits specified for collections are for non-frozen collections.