Cassandra Query Language (CQL) for Astra DB

Cassandra Query Language (CQL) is the native query language of Apache Cassandra®. CQL syntax is similar to SQL, but there are important differences for interacting with distributed, non-relational data. Because Astra DB is powered by Cassandra, you can send Cassandra Query Language (CQL) statements to your Astra DB Serverless databases with the CQL shell (cqlsh), Cassandra drivers, or the Data API.

Data API and clients

You can use the Data API and clients to manage tables in Astra DB Serverless databases, including tables you previously created with cqlsh or a driver.

When using the Data API, you structure your queries using HTTP requests with JSON payloads or your preferred Data API client language. Unlike the Cassandra drivers, the Data API doesn’t accept raw CQL statements; you must use parameters and syntax that are specific to the Data API.

For more information, see Get started with the Data API (HTTP) and About tables with the Data API (HTTP).

CQL shell (cqlsh)

The CQL shell is a command-line utility that you can use to interact with your Astra DB Serverless databases using CQL. For more information, see Connect to the CQL shell for Astra DB.

Drivers

Astra DB Serverless databases are compatible with the following Cassandra drivers:

Supported drivers
Language	Driver source	Documentation
C++	cpp-driver	Get started with the C++ driver
C#	csharp-driver	Get started with the C# driver
Go	gocql-astra (includes cassandra-gocql-driver)	Get started with the Go driver
Java	cassandra-java-driver	Get started with the Java driver
Node.js	cassandra-driver	Get started with the Node.js driver
Python	cassandra-driver	Get started with the Python driver
Scala	cassandra-spark-connector	Get started with the Apache Cassandra Spark Connector

For more information, see Cassandra driver compatibility.

For CQL and cqlsh command examples and specifications, see the CQL for Astra DB reference. The reference includes Cassandra Query Language (CQL) commands and CQL shell (cqlsh) commands. cqlsh commands are only available in the CQL shell. CQL statements are based on the specification 3.4.5 with additional SAI enhancements.

Limitations on CQL for Astra DB

To ensure high availability and optimum performance, Astra DB Serverless databases have guardrails on underlying Cassandra functionality, including commands and functionality available through CQL for Astra DB.

Keyspace, table, and column names are case sensitive

If you create a table in the Astra Portal, the table name, keyspace name, and column names become case-sensitive. Make sure your CQL commands use case sensitivity for these values. For more information, see How do I fix a "table does not exist" error in CQL.

Unsupported values are ignored

If you pass a CQL statement with unsupported data definition language (DDL) properties, the statement is executed without the unsupported values. Astra DB ignores the unsupported values and throws a warning.

For example, the following command creates a table with the given schema, and it attempts to pass an additional nodesync property:

CREATE TABLE IF NOT EXISTS library.books
  (
    id UUID PRIMARY KEY,
    lastname text,
    firstname text)
  WITH nodesync={'enabled': 'true'};

If you execute this statement against an Astra DB database, the table is created but the nodesync property is ignored. The response includes a warning with the ignored unsupported values:

Warnings :
Ignoring provided values [nodesync] as they are not supported for Table
Properties(ignored values are: [additional_write_policy,
bloom_filter_fp_chance, caching, cdc, compaction, compression,
crc_check_chance, dse_edge_label_property, dse_vertex_label_property,
gc_grace_seconds, id, max_index_interval, memtable,
memtable_flush_period_in_ms, min_index_interval, nodesync, read_repair,
speculative_retry])

Limited support for user-defined objects

CQL for Astra DB supports user-defined type (UDTs). It doesn’t support user-defined functions (UDFs) or user-defined aggregate functions (UDAs).

Unsupported CQL commands

For all supported CQL commands, see the CQL for Astra DB quick reference.

CQL for Astra DB doesn’t support the following commands:

Category Unsupported commands Notes

Category	Unsupported commands	Notes
Access control	`CREATE ROLE` `GRANT ROLE` `ALTER ROLE` `REVOKE ROLE` `DROP ROLE` `LIST ROLES` `GRANT PERMISSION` `LIST PERMISSIONS` `REVOKE PERMISSION` `RESTRICT` `RESTRICT ROWS` `UNRESTRICT` `UNRESTRICT ROWS`	For information access control in Astra, see Roles and permissions reference.
Keyspaces	`CREATE KEYSPACE` `ALTER KEYSPACE` `DROP KEYSPACE`	The CQL shell `DESCRIBE KEYSPACE` command is supported. With the exception of the keyspace name, keyspace definitions are managed by DataStax as part of the Astra DB managed service offering. To create and drop keyspaces, see Manage keyspaces.
Materialized views (MV)	`CREATE MATERIALIZED VIEW` `ALTER MATERIALIZED VIEW` `DROP MATERIALIZED VIEW`
Triggers	`CREATE TRIGGER` `DROP TRIGGER`
User-defined aggregates (UDA)	`CREATE AGGREGATE` `DESCRIBE AGGREGATE` `DROP AGGREGATE`	Astra DB supports UDTs only.
User-defined functions (UDF)	`CREATE FUNCTION` `DESCRIBE FUNCTION` `DROP FUNCTION`	Astra DB supports UDTs only.
DSE Search indexes	`CREATE SEARCH INDEX` `COMMIT SEARCH INDEX` `REBUILD SEARCH INDEX` `RELOAD SEARCH INDEX` `ALTER SEARCH INDEX SCHEMA` `ALTER SEARCH INDEX CONFIG` `DROP SEARCH INDEX CONFIG`

Access control

CREATE ROLE
GRANT ROLE
ALTER ROLE
REVOKE ROLE
DROP ROLE
LIST ROLES
GRANT PERMISSION
LIST PERMISSIONS
REVOKE PERMISSION
RESTRICT
RESTRICT ROWS
UNRESTRICT
UNRESTRICT ROWS

For information access control in Astra, see Roles and permissions reference.

Keyspaces

CREATE KEYSPACE
ALTER KEYSPACE
DROP KEYSPACE

The CQL shell DESCRIBE KEYSPACE command is supported.

With the exception of the keyspace name, keyspace definitions are managed by DataStax as part of the Astra DB managed service offering. To create and drop keyspaces, see Manage keyspaces.

Materialized views (MV)

CREATE MATERIALIZED VIEW
ALTER MATERIALIZED VIEW
DROP MATERIALIZED VIEW

Triggers

CREATE TRIGGER
DROP TRIGGER

User-defined aggregates (UDA)

CREATE AGGREGATE
DESCRIBE AGGREGATE
DROP AGGREGATE

Astra DB supports UDTs only.

User-defined functions (UDF)

CREATE FUNCTION
DESCRIBE FUNCTION
DROP FUNCTION

Astra DB supports UDTs only.

DSE Search indexes

CREATE SEARCH INDEX
COMMIT SEARCH INDEX
REBUILD SEARCH INDEX
RELOAD SEARCH INDEX
ALTER SEARCH INDEX SCHEMA
ALTER SEARCH INDEX CONFIG
DROP SEARCH INDEX CONFIG

Cassandra Query Language (CQL) for Astra DB

Limitations on CQL for Astra DB

Keyspace, table, and column names are case sensitive

Unsupported values are ignored

Limited support for user-defined objects

Unsupported CQL commands

See also

Was this helpful?

Give Feedback