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:
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.
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.
- ALTER TABLE
- CREATE TABLE
- DELETE
- INSERT
- SELECT
- TRUNCATE
- UPDATE
Procedure
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.
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 Distribution of Apache Cassandra supports exponential notation.
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.
- 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 the words are reserved.
This table lists keywords and whether 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.
Reserved keywords
CREATE TABLE test."add" (a int PRIMARY KEY);
- ADD
- ALLOW
- ALTER
- AND
- APPLY
- ASC
- AUTHORIZE
- BATCH
- BEGIN
- BY
- COLUMNFAMILY
- CREATE
- DEFAULT
- DELETE
- DESC
- DESCRIBE
- DROP
- ENTRIES
- EXECUTE
- 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
- REVOKE
- SCHEMA
- SELECT
- SET
- TABLE
- TO
- TOKEN
- TRUNCATE
- UNLOGGED
- 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.
- 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.
- 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: number of keys: 65535 (216-1); values size: 65535 (216-1)
- Blob size: 2 GB (less than 1 MB is recommended)