CQL quick reference

See this quick reference guide for other CQL versions: 5.1 | 6.0.

Download a printable CQL reference with the ten most frequently use CQL commands and a list of the CQL data types.

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 ROLE

ALTER ROLE <role_name>
  ( WITH PASSWORD = '<role_password>'
  ifdef::dse69,dse68,cass50[]
  | WITH HASHED PASSWORD = '<hashed_role_password>'
  endif::dse69,dse68,cass50[]
  [ [ WITH | AND ] LOGIN = ( true | false ) ]
  [ [ WITH | AND ] SUPERUSER = ( true | false ) ]
    ifdef::cass50[]
  [ ( WITH | AND ) ACCESS TO DATACENTERS { 'dc_name' } | ( WITH | AND ) ACCESS TO ALL DATACENTERS
  | ( WITH | AND ) ACCESS FROM CIDRS { 'region1' } | ( WITH | AND ) ACCESS FROM ALL CIDRS']
  endif::cass50[]
  [ [ WITH | AND ] OPTIONS = { <custom_options_map> } ] ] ;

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

BATCH

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

COMMIT SEARCH INDEX

COMMIT SEARCH INDEX ON [<keyspace_name>.]<table_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 CUSTOM INDEX

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

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 ROLE

CREATE ROLE [ IF NOT EXISTS ] <role_name>
  ( WITH PASSWORD = '<role_password>'
  ifdef::dse69,dse68,cass50[]
  | WITH HASHED PASSWORD = '<hashed_role_password>'
  endif::dse69,dse68,cass50[]
  )
  [ ( WITH | AND ) [ SUPERUSER = ( true | false ) ]
  [ ( WITH | AND ) LOGIN = ( true | false ) ]
  ifdef::cass50[]
  [ ( WITH | AND ) ACCESS TO DATACENTERS { 'dc_name' } | ( WITH | AND ) ACCESS TO ALL DATACENTERS
  | ( WITH | AND ) ACCESS FROM CIDRS { 'region1' } | ( WITH | AND ) ACCESS FROM ALL CIDRS']
  endif::cass50[]
  [ [ WITH | AND ] OPTIONS = { <custom_options_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 TABLE

CREATE TABLE [ IF NOT EXISTS ] [<keyspace_name>.]<table_name>
  ( <column_definition> [ , ... ] | [ PRIMARY KEY (partition_key_column_name, clustering_column_name) ] )
  [ WITH COMPACT STORAGE [ AND <table_options> ] (1)
  [ CLUSTERING ORDER BY [ <clustering_column_name> [ ASC | DESC ] ][ , ... ] ] (2)
  ifdef::dse68[]
  [ AND ( VERTEX LABEL <vl_name> | EDGE LABEL ) <el_name> FROM <vl_name> TO <vl_name>]
  endif::dse68[]
  [ [ AND ] ID = '<table_hash_tag>' ] ] ; (3)
1 <column_definition> is a list of paired column_name and cql_type, separated by commas. Some options apply. Use COMPACT STORAGE to store data in the legacy (Thrift) storage engine format to conserve disk space if using DSE 5.1.
2 The CLUSTERING ORDER BY clause is optional. If not specified, the default is ASC.
3 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> ... ]) ;

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

GRANT ROLE

GRANT <role_name> TO <role_name> ;

GRANT PERMISSION

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

INSERT

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

LIST PERMISSIONS

LIST ( ALL PERMISSIONS | <permission_list> )[ 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> ;

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

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> | PER PARTITION LIMIT <N> ) ]
  [ ALLOW FILTERING ] ;

TRUNCATE

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

UNRESTRICT

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

UNRESTRICT ROWS

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

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

USE

USE <keyspace_name> ;

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