Cassandra Query Language (CQL) for Astra DB

Because Astra DB Classic is powered by Apache Cassandra®, you can send Cassandra Query Language (CQL) queries to your Astra DB Classic databases with the CQL shell (cqlsh) or DataStax drivers.

To use CQL for Astra DB, you need an active Astra account and an active Astra DB Classic database.

Familiarity with CQL is helpful but not required. For more information about CQL for Astra DB, including command examples and specifications, see the CQL for Astra DB reference.

CQL for Astra DB limitations

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

User-defined types

CQL for Astra DB does not support user-defined functions (UDFs) or user-defined aggregate functions (UDAs).

Ignored values

If you try to use a CQL command with unsupported data definition language (DDL) properties, the command works, but ignores the unsupported values.

For example, the following command creates a table with the defined attributes, except for the nodesync values:

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

The response includes a warning that lists the unsupported values, which were ignored:

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

Unsupported CQL commands

Operations concerning keyspaces, materialized views, functions, aggregates, and search indexes are generally not supported. As a result, the following CQL commands aren’t supported for Astra DB:

CREATE KEYSPACE
ALTER KEYSPACE
DROP KEYSPACE
CREATE MATERIALIZED VIEW
ALTER MATERIALIZED VIEW
DROP MATERIALIZED VIEW
CREATE AGGREGATE
DESCRIBE AGGREGATE
DROP AGGREGATE
CREATE FUNCTION
DESCRIBE FUNCTION
DROP FUNCTION
CREATE TRIGGER
DROP TRIGGER
CREATE ROLE
ALTER ROLE
DROP ROLE
LIST ROLES
LIST PERMISSIONS
RESTRICT
RESTRICT ROWS
UNRESTRICT
UNRESTRICT ROWS
CREATE SEARCH INDEX
COMMIT SEARCH INDEX
REBUILD SEARCH INDEX
RELOAD SEARCH INDEX
ALTER SEARCH INDEX SCHEMA
ALTER SEARCH INDEX CONFIG
DROP SEARCH INDEX CONFIG

For a list of supported CQL commands, see the CQL for Astra DB quick reference.

Use CQL for Astra DB

You can use the CQL shell or DataStax drivers to issue CQL commands to your Astra DB databases.

Drivers

For information about DataStax drivers, see Connecting to your Astra DB database.

CQL shell

The Cassandra Query Language Shell (cqlsh) is a utility that you can use to issue CQL commands to your Astra DB Classic database. You can use the embedded CQL shell in the Astra Portal or the standalone CQL shell.

Embedded CQL shell
Standalone CQL shell

The Astra Portal provides an embedded CQL shell instance, known as the CQL Console, for each Astra DB Classic database. You can use the CQL Console to run CQL commands on your databases directly from your browser.

In the Astra Portal navigation menu, select your database.
Click CQL Console to open the CQL Console in a new tab.
Wait for the token@cqlsh> prompt to appear.

This prompt indicates that the CQL Console is connected to your database, and you can begin issuing CQL commands to your database.

To open the CQL Console in a full window, use the following URL:

DATABASE_ID-REGION_NAME.apps.astra.datastax.com/cqlsh

Replace DATABASE_ID and REGION_NAME with your database’s ID and the name of the region where your database is deployed.

The standalone CQL shell is a separate, lightweight utility that you can use to interact with your database.

To install and use the standalone CQL shell, you need Python version 2.7.12 or later with TLS support and the SCB for your database.

You can use the Astra CLI to download and configure the standalone CQL shell, including the Secure Connect Bundle (SCB) and the token for your Astra account. For more information, see Use cqlsh in the Astra CLI documentation.

Download the CQL shell for your Astra DB Classic database:
- DataStax Astra
- DataStax Astra with support for Vector Type.
Change to the directory where you downloaded the CQL shell package:
```
cd /DOWNLOAD_DIRECTORY
```
Extract the files:
```
tar -xvf cqlsh-astra-DATE-bin.tar.gz
```
Replace DATE with the package release date, such as 20210304.
Run the cqlsh script:
```
cd /cqlsh-astra
./bin/cqlsh -u CLIENT_ID -p CLIENT_SECRET -b PATH_TO_SCB
```
Replace the following:
- PATH_TO_SCB: The path to your database’s SCB zip file
- CLIENT_ID and CLIENT_SECRET: Either of the following sets of values from your application token:
  - The clientId value and the secret value
  - The literal string token and the token secret, prefixed by AstraCS:
(Optional) Store credentials in the cqlshrc file
If you don’t want to pass the credentials on the command line every time, you can configure the location in your cqlshrc file:
1. In the ~/.cassandra directory, open the cqlshrc file.
2. In the [authentication] section, add the CLIENT_ID and CLIENT_SECRET values from your application token.
3. In the [connection] section, add the path to the SCB.
[authentication] username = CLIENT_ID # or the literal string token password = CLIENT_SECRET # or the token secret prefixed by 'AstraCS:' [connection] secure_connect_bundle = PATH_TO_SCB

Make sure the command returns output indicating that you connected to your database. For example:

[cqlsh 6.8.0 | DSE 6.8.0.77 | CQL spec 3.4.5 | DSE protocol v2]
Use HELP for help.
username@cqlsh>

Cassandra Query Language (CQL) for Astra DB

CQL for Astra DB limitations

User-defined types

Ignored values

Unsupported CQL commands

Use CQL for Astra DB

Drivers

CQL shell

See also

Was this helpful?

Give Feedback