The Cassandra Query Language (CQL) Quick Reference

The Cassandra Query Language (CQL) is the primary language for communicating with the Apache Cassandra™ database. The most basic way to interact with Apache Cassandra is using the CQL shell, cqlsh.

This article provides a quick reference of the most popular and useful CQL commands and operations.

Navigate to the bottom and grab a printable copy of this guide.

CREATE KEYSPACE

CREATE INDEX [ IF NOT EXISTS ] index_name
  ON [keyspace_name.]table_name
  ([ ( KEYS | FULL ) ] column_name) 
  (ENTRIES column_name);

CREATE TABLE

CREATE TABLE [ IF NOT EXISTS ] [keyspace_name.]table_name
  ( column_definition [ , ... ] | PRIMARY KEY (column_list) )
  [ WITH [ table_options ]
  [ [ AND ] CLUSTERING ORDER BY [ clustering_column_name order ] ]
  [ [ AND ] ID = 'table_hash_tag' ]
  [ [ AND ] COMPACT STORAGE ] ;

CREATE FUNCTION

CREATE [ OR REPLACE ] FUNCTION [ IF NOT EXISTS ] [keyspace_name.]function_name (argument_list [ , ... ])
  ( CALLED | RETURNS NULL ) ON NULL INPUT RETURNS cql_data_type 
  [ DETERMINISTIC ]
  [ MONOTONIC [ ON argument_name ] ]
  LANGUAGE language_name AS 'code_block' ; 

CREATE INDEX

CREATE INDEX [ IF NOT EXISTS ] index_name
  ON [keyspace_name.]table_name
  ([ ( KEYS | FULL ) ] column_name) 
  (ENTRIES column_name);

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 order_option) ] ] ;

CREATE USER/ROLE

CREATE ROLE [ IF NOT EXISTS ] role_name 
  [ WITH [ SUPERUSER = ( true | false ) ]
  [ [ AND ] LOGIN = ( true | false ) ]
  [ [ AND ] PASSWORD = 'role_password' ] 
  [ [ AND ] OPTIONS = option_map ] ] ;

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 CUSTOM INDEX

CREATE CUSTOM INDEX [ IF NOT EXISTS ] [ index_name ]
  ON [keyspace_name.]table_name (column_name)
  USING 'org.apache.cassandra.index.sasi.SASIIndex' 
  [ WITH OPTIONS = { option_map } ] ;

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 TRIGGER

CREATE TRIGGER trigger_name
  ON [keyspace_name.]table_name
  USING 'org.apache.cassandra.triggers.AuditTrigger' ;

CREATE TYPE

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

USE KEYSPACE

USE keyspace_name ;

SELECT

SELECT selectors 
  FROM [keyspace_name.]table_name 
  [ WHERE [ primary_key_conditions ] [ AND ] [ index_conditions ]
  [ GROUP BY column_name [ , ... ] ]
  [ ORDER BY PK_column_name [ , ... ] ( ASC | DESC ) ] 
  [ ( LIMIT N | PER PARTITION LIMIT N ) ]
  [ ALLOW FILTERING ] ;

INSERT

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

UPDATE

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

DROP TABLE

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

DROP KEYSPACE

DROP KEYSPACE [ IF EXISTS ] keyspace_name ;

DELETE

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

DROP FUNCTION

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

DROP INDEX

DROP INDEX [ IF EXISTS ] [keyspace.]index_name ;

DROP TRIGGER

DROP TRIGGER [ IF EXISTS ] trigger_name 
  ON [keyspace_name.]table_name ;

DROP TYPE

DROP TYPE [ IF EXISTS ] [keyspace_name]type_name ;

DROP ROLE

DROP ROLE [ IF EXISTS ] role_name ;

DROP SEARCH INDEX

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

DROP MATERIALIZED VIEW

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

ALTER KEYSPACE

ALTER  KEYSPACE keyspace_name 
   WITH REPLICATION = {replication_map}
   [AND DURABLE_WRITES =  true|false] ;

ALTER ROLE

ALTER ROLE role_name 
[ WITH [ PASSWORD = 'password'
   | LOGIN = (true | false) 
   | SUPERUSER = (true | false) 
   | OPTIONS = option_map ]

ALTER TABLE

ALTER TABLE [keyspace_name.]table_name 
  [ ADD ( column_definition | column_definition_list ) ]
  [ DROP ( column | column_list | COMPACT STORAGE ) ]
  [ RENAME column_name TO column_name ]
  [ WITH table_properties [ , ... ] ] ;

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 ... ] ) ;

ALTER USER

ALTER USER user_name 
WITH PASSWORD 'password' 
[ ( SUPERUSER | NOSUPERUSER ) ]

ALTER MATERIALIZED VIEW

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

ALTER SEARCH INDEX

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 ) ;

BATCH PROCESSING

BEGIN [ ( UNLOGGED | COUNTER ) ] BATCH 
        [ USING TIMESTAMP [ epoch_microseconds ] ]
        dml_statement [ USING TIMESTAMP [ epoch_microseconds ] ] ;
        [ dml_statement [ USING TIMESTAMP [ epoch_microseconds ] ] [ ; ... ] ] ;
        APPLY BATCH ;

GRANT PERMISSION

GRANT permission
  [ ON object ]
  TO role_name ;

REVOKE PRIVILEGE

REVOKE privilege
  ON resource_name
  FROM role_name ;

LIST PERMISSIONS

LIST permission
  [ ON resource_name ]
  [ OF role_name ] 
  [ NORECURSIVE ] ;

LIST ROLES

LIST ROLES 
  [ OF role_name ] 
  [ NORECURSIVE ] ;

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 ;

Thanks for reading!!