CQL command 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

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:

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

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.

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

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:

  • ALTER TABLE

  • CREATE TABLE

  • DELETE

  • INSERT

  • SELECT

  • TRUNCATE

  • UPDATE

    1. 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:

  • blob

    hexadecimal defined as 0xX+

  • 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. 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

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 */

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. However, the database automatically encloses the keyword in double quotes, so any additional actions must include double quotes to reference the object.

CQL Basic Reserved keywords

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

CREATE TABLE cycling."addThis" (
  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

DSE Non-reserved keywords

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

DSE

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

The upper limits are as follows:

  • Cells in a partition: ~2 billion (2^31); single column value size: 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) (just a few fields, such as 2 to 10, are recommended)

  • Collection (list): collection limit is ~2 billion (2^31); values size is 65535 (2^16 - 1)

  • Collection (set): collection limit is ~2 billion (2^31); values size is 65535 (2^16 - 1)

  • Blob size: 2 GB (less than 1 MB is recommended)

  • Vector: maximum dimension is 8K (2^13)

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

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