CQL quick reference

A consolidated syntax list of Cassandra Query Language (CQL) commands for quick reference.

Cassandra Query Language (CQL) commands are divided into four categories:

Data Definition Language (DDL): Commands that define or modify database objects such as keyspaces, tables, indexes, and types.
Data Manipulation Language (DML): Commands that manipulate data within the database, such as inserting, updating, deleting, and querying data.
Data Control Language (DCL): Commands that manage access and security such as creating roles, granting permissions, and restricting access.
Session/Utility commands: Commands that manage the session context, such as selecting a keyspace.

This quick reference provides a consolidated list of Cassandra Query Language (CQL) commands along with their syntax for easy reference.

Data Definition Language (DDL)

Don’t execute multiple DDL commands at the same time. Concurrent schema changes can result in nodes having different schemas. These inconsistencies cannot be resolved by normal repair operations.

ALTER KEYSPACE

ALTER KEYSPACE <keyspace_name>
  WITH REPLICATION = { <replication_map> }
  [ AND DURABLE_WRITES = ( true | false ) ]
  [ AND graph_engine =  'Core' ];

ALTER MATERIALIZED VIEW

ALTER MATERIALIZED VIEW [<keyspace_name>.]<view_name>
  WITH <table_options> [ AND <table_options> ... ] ;

ALTER SEARCH INDEX CONFIG

ALTER SEARCH INDEX CONFIG ON [<keyspace_name>]<table_name>
  ( ADD <element_path> [ <attribute_list> ] WITH $$ <json_map> $$
  | SET <element_identifier> = '<value>'
  | SET <shortcut> = <value>
  | DROP <element_identifier>
  | DROP <shortcut> ) ;

ALTER SEARCH INDEX SCHEMA

ALTER SEARCH INDEX SCHEMA ON [<keyspace_name>.]<table_name>
  ( ADD <field column_name>
  | ADD <element_path> [ <attribute_list> ] WITH $$ <json_map> $$
  | SET <element_identifier> = '<value>'
  | DROP <field field_name>
  | DROP <element_identifier> ) ;

ALTER TABLE

ALTER TABLE [<keyspace_name>.]<table_name>
  [ ADD ( <column_definition> | <column_definition_list> ) [ , ... ] ]
  [ DROP <column_name> [ , ... ] ]
  [ [ RENAME <column_name> TO <column_name> ] |
    [ RENAME ( VERTEX LABEL | EDGE LABEL ) TO <new_name> ] ]
  [ WITH <table_properties> [ , ... ] ]
  [ WITH ( VERTEX LABEL | EDGE LABEL ) <current_name> ]
  [ WITHOUT ( VERTEX LABEL | EDGE LABEL ) <current_name> ];

ALTER TYPE

ALTER TYPE <field_name>
  ( ADD <field_name> <cql_datatype>
  | RENAME <field_name> TO <<<new_field_name> [ AND <field_name> TO <new_field_name> ...] ) ;

CREATE AGGREGATE

CREATE [ OR REPLACE ] AGGREGATE [ IF NOT EXISTS ]
  [<keyspace_name>.]<aggregate_name> (<cql_type>)
  SFUNC <udf_name>
  STYPE <cql_type>
  FINALFUNC <udf_name>
  INITCOND <init_value>
  [ DETERMINISTIC ] ;

CREATE FUNCTION

CREATE [ OR REPLACE ] FUNCTION [ IF NOT EXISTS ] [<keyspace_name>]<function_name> (<argument_list> [ , ... ])
  ( CALLED | RETURNS NULL ) ON NULL INPUT RETURNS <type>
  [ DETERMINISTIC ]
  [ MONOTONIC [ ON <argument_name> ] ]
  LANGUAGE ( java | javascript ) AS $$ <code_block> $$ ;

CREATE INDEX

CREATE [CUSTOM] INDEX [ IF NOT EXISTS ] [ <index_name> ]
  ON [<keyspace_name>.]<table_name>
  ([ KEYS | VALUES | ENTRIES | FULL] <column_name>)
    USING <index_type>
  [ WITH OPTIONS = { <option_map> } ] ;

CREATE KEYSPACE

CREATE KEYSPACE [ IF NOT EXISTS ] <keyspace_name>
  WITH REPLICATION = { <replication_map> }
  [ AND DURABLE_WRITES = ( true | false ) ]
  [ AND graph_engine = 'Core' ] ;

CREATE MATERIALIZED VIEW

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] [<keyspace_name>.]<view_name>
  AS SELECT [ (<column_list>) ]
  FROM [<keyspace_name>.]<table_name>
  [ WHERE <column_name> IS NOT NULL
  [ AND <column_name> IS NOT NULL ... ] ]
  [ AND <relation> [ AND ... ] ]
  PRIMARY KEY ( <column_list> )
  [ WITH [ table_properties ]
  [ [ AND ] CLUSTERING ORDER BY (<cluster_column_name> DESC | ASC) ] ] ;

CREATE SEARCH INDEX

CREATE SEARCH INDEX [ IF NOT EXISTS ] ON [<keyspace_name>.]<table_name>
  [ WITH [ COLUMNS <column_list> { <option> : <value> } [ , ... ] ]
  [ [ AND ] PROFILES <profile_name> [ , ... ] ]
  [ [ AND ] CONFIG { <option:value> } [ , ... ] ]
  [ [ AND ] OPTIONS { <option:value> } [ , ... ] ] ] ;

CREATE TABLE

CREATE TABLE [ IF NOT EXISTS ] [<keyspace_name>.]<table_name>
  ( <column_definition> [ , ... ] | [ PRIMARY KEY (partition_key_column_name, clustering_column_name) ] )
  [ AND <table_options> ]
  [ CLUSTERING ORDER BY [ <clustering_column_name> [ ASC | DESC ] ][ , ... ] ]
  [ AND ( VERTEX LABEL <vl_name> | EDGE LABEL ) <el_name> FROM <vl_name> TO <vl_name>]
  [ [ AND ] ID = '<table_hash_tag>' ] ] ;

The CLUSTERING ORDER BY clause is optional. If not specified, the default is ASC.

The ID clause is optional. If specified, the table is created with a hash tag that is used to identify the table in the schema. If a table is accidentally dropped, use this option to recreate the table and run a commit log replay to retrieve the data.

CREATE TYPE

CREATE TYPE [ IF NOT EXISTS ] [<keyspace_name>].<type_name>
  (<field_name> <cql_datatype> [ , <field_name> <cql_datatype> ... ]) ;

DROP AGGREGATE

DROP AGGREGATE [ IF EXISTS ] [<keyspace_name>.]<aggregate_name> [ (<argument_name> [ , ... ]) ] ;

DROP FUNCTION

DROP FUNCTION [ IF EXISTS ] [<keyspace_name>.]<function_name> [ (<argument_name> [ , ... ]) ] ;

DROP INDEX

DROP INDEX [ IF EXISTS ] [<keyspace_name>.]<index_name> ;

DROP KEYSPACE

DROP KEYSPACE [ IF EXISTS ] <keyspace_name> ;

DROP MATERIALIZED VIEW

DROP MATERIALIZED VIEW [ IF EXISTS ] [<keyspace_name>.]<view_name> ;

DROP ROLE

DROP ROLE [ IF EXISTS ] <role_name> ;

DROP SEARCH INDEX CONFIG

DROP SEARCH INDEX ON [<keyspace_name>.]<table_name>
  OPTIONS { <option> : <value> } [ , { <option> : <value> } ... ] ;

DROP TABLE

DROP TABLE [ IF EXISTS ] [<keyspace_name>.]<table_name> ;

DROP TYPE

DROP TYPE [ IF EXISTS ] [<keyspace_name>.]<type_name> ;

REBUILD SEARCH INDEX

REBUILD SEARCH INDEX ON <keyspace_name>.]<table_name>
  [ WITH OPTIONS { deleteAll : ( true | false ) } ] ;

RELOAD SEARCH INDEX

RELOAD SEARCH INDEX ON [<keyspace_name>.]<table_name> ;

TRUNCATE

TRUNCATE [ TABLE ] [<keyspace_name>.]<table_name> ;

Data Manipulation Language (DML)

BATCH

BEGIN [ ( UNLOGGED | LOGGED ) ] BATCH
  [ USING TIMESTAMP [ <epoch_microseconds> ] ]
  <dml_statement> [ USING TIMESTAMP [ <epoch_microseconds> ] ] ;
  [ <dml_statement> [ USING TIMESTAMP [ <epoch_microseconds> ] ] [ ; ... ] ] ;
  APPLY BATCH ;

DELETE

DELETE [ <column_name> [ <term> ] [ , ... ] ]
  FROM [<keyspace_name>.]<table_name>
  [ USING TIMESTAMP <timestamp_value> ]
  WHERE <PK_column_conditions>
  [ ( IF EXISTS | IF <static_column_conditions> ) ] ;

INSERT

INSERT [ JSON ] INTO [<keyspace_name>.]<table_name>
  [ <column_list> VALUES <column_values> ]
  [ IF NOT EXISTS ]
  [ USING [ TTL <seconds> ] [ [ AND ] TIMESTAMP <epoch_in_microseconds> ] ] ;

SELECT

SELECT [ JSON | DISTINCT ] <selectors>
  FROM [<keyspace_name>.]<table_name>
  [ WHERE [ <primary_key_conditions> | <non_primary_key_conditions> IN (<column_name> [, ...]) ALLOW FILTERING ] [ AND ] [ <index_conditions> ]
  [ GROUP BY <column_name> [ , ... ] ]
  [ ORDER BY <column_name> ( ASC | DESC ) [ , ... ] ] |
  [ ORDER BY <vector_column_name> ANN OF [n,n,n,...] [ LIMIT N ] ]
  [ ( LIMIT <N> [ OFFSET <N> ]  | PER PARTITION LIMIT <N> ) ]
  [ ALLOW FILTERING ] ;

UPDATE

UPDATE [<keyspace_name>.]<table_name>
  [ USING TTL <time_value> ]
  [ [ AND ] USING TIMESTAMP <timestamp_value> ]
  SET <assignment> [ , <assignment> ... ]
  WHERE <row_specification>
  [ IF EXISTS | IF <condition> [ AND <condition> ] ] ;

Data Control Language (DCL)

ALTER ROLE

ALTER ROLE <role_name>
  ( WITH PASSWORD = '<role_password>'
  [ [ WITH | AND ] LOGIN = ( true | false ) ]
  [ [ WITH | AND ] SUPERUSER = ( true | false ) ]
  [ [ WITH | AND ] OPTIONS = { <custom_options_map> } ] ] ;

CREATE ROLE

CREATE ROLE [ IF NOT EXISTS ] <role_name>
  ( WITH PASSWORD = '<role_password>'
  )
  [ ( WITH | AND ) [ SUPERUSER = ( true | false ) ]
  [ ( WITH | AND ) LOGIN = ( true | false ) ]
  [ [ WITH | AND ] OPTIONS = { <custom_options_map> } ] ] ;

GRANT ROLE

GRANT <role_name> TO <role_name> ;

GRANT PERMISSION

GRANT <permission> ON <resource> TO <role_name> ;

LIST PERMISSIONS

LIST ( ALL PERMISSIONS | <permission_list> )[ ON <resource_name> ]
  [ OF <role_name> ][ NORECURSIVE ] ;

LIST ROLES

LIST ROLES [ OF <role_name> ] [ NORECURSIVE ] ;

RESTRICT

RESTRICT <permission> ON [<keyspace_name>.]<table_name> TO <role_name> ;

RESTRICT ROWS

RESTRICT ROWS ON [<keyspace_name>.]<table_name>
  USING <pk_column_name> ;

REVOKE ROLE

REVOKE <role_name> FROM <role_name> ;

REVOKE PERMISSION

REVOKE <permission> ON <resource_name> FROM <role_name> ;

UNRESTRICT

UNRESTRICT <permission_name> ON [<keyspace_name>.]<table_name>
  FROM <role_name> ;

UNRESTRICT ROWS

UNRESTRICT ROWS ON [<keyspace_name>.]<table_name> ;

Session/Utility commands

USE

USE <keyspace_name> ;

CQL quick reference

Data Definition Language (DDL)

Data Manipulation Language (DML)

Data Control Language (DCL)

Session/Utility commands

Was this helpful?

Give Feedback