Use CQL shell (`cqlsh`)

CQL shell (cqlsh) is a Python-based command-line interface for interacting with Cassandra-based databases. Use it to execute CQL commands to manage schemas, query and modify data, and perform administrative tasks. You can work interactively at a prompt or run commands from a file or directly from the command line. cqlsh connects to a running database instance using the the Apache Cassandra Python driver and the native protocol.

CQL shell commands

In addition to executing CQL statements, cqlsh provides a set of built-in shell commands. These commands control the cqlsh session, format output, inspect cluster metadata, and perform utility tasks such as importing or exporting data. Shell commands are interpreted by cqlsh and are not sent to the database as CQL.

Because these commands are implemented by the cqlsh client rather than the database itself, they are not available when connecting through Apache Cassandra drivers or other tools such as DataStax Studio, that only support CQL commands.

Prerequisites

cqlsh requires Python 2.7 or a supported Python 3 version (3.6–3.11). Newer versions of Python 3 (3.12 and later) are not supported.

Connect with `cqlsh`

See Connect to DSE with the CQL shell (cqlsh). If you use Mission Control, see Use the CQL console to interact with the databases in your datacenter.

Command line options

cqlsh [ OPTIONS ] [ HOST_NAME[:PORT_NUMBER] ]

Command line options override settings in a cqlshrc file.

Required parameters

host_name:port: To connect the CQL session to a specified node, specify a hostname or IP address and optional port after the cqlsh command, along with any additional CQL shell options.

By default, the CQL shell launches a session with the local host on address 127.0.0.1. You can connect the CQL shell to remote hosts that have a higher or equal CQL shell version than the local CQL shell version. When no port is specified, the connection uses the default port of 9042.

Options

Option

Description

--browser="<launch_browser_cmd> %s"

Browser to display the CQL command help. Replace the URL in the command with %s.

-C, --color

Always use color output.

--connect-timeout="<timeout>"

Connection timeout in seconds.

Default: 5

--cqlshrc="/<folder_name>"

Folder that contains the cqlshrc file. Use tilde (~) for paths relative to the user’s home directory.

--cqlversion="<version_number>"

CQL version to use. The CQL version displays after starting cqlsh.

--debug

Show additional debugging information.

--disable-history

Disables saving history to disk for current execution.

--dse-protocol-version=<DSE_PROTOCOL_VERSION>

Specify a specific DSE protocol version; otherwise the client will default and downgrade as necessary. Mutually exclusive with --protocol-version.

--encoding="<output_encoding>"

Output encoding.

Default encoding: utf8

--execute="<cql_statement>"

Execute the CQL statement, then exit. To direct the command output to a file, see saving CQL output.

-f <file_name>, --file=<file_name>

Execute commands from a CQL file, then exit.

After starting cqlsh, use the SOURCE command and the path to the file using the cqlsh command line.

-h, --help

Show help.

-k <keyspace_name>, --keyspace=<keyspace_name>

Automatically use the specified keyspace after starting the CQL shell.

--no-color

Do not display color output.

-p <password>, --password="<password>"

Connect with the specified user’s password.

This option can expose your password in scripts or logs. Use -u without -p or --password to specify the username, and you will be prompted for the password.

--protocol-version=<PROTOCOL_VERSION>

Specify a specific protocol version. If omitted, the client will use a default, and possibly lower version protocol, as needed.Mutually exclusive with --dse-protocol-version.

--request-timeout="<timeout>"

CQL request timeout in seconds.

Default: 10

--consistency-level <consistency_level>

Specify the initial consistency level.

Default: ONE

--serial-consistency-level <serial_consistency_level>

Specify the initial serial consistency level.

Default: SERIAL

--ssl

Use SSL.

-t, --tty

Force TTY command prompt mode.

-u <user_name>, --username="<user_name>"

Connect with the specified user account.

--version

Show the cqlsh version number.

Environment variables

You can use environment variables to overwrite default values for cqlsh commands. For example, set the hostname and port for a standalone cqlsh session:

export CQLSH_HOST=198.51.100.12
export CQLSH_PORT=9042

You can overwrite the default 600 seconds (10 minutes) request timeout for search-specific CQL statements in DSE. To prevent timeouts, increase the timeout value. Typical use case is to ensure that no timeouts occur when large indexes are reloaded. The timeout applies only to these search CQL index management commands:

ALTER SEARCH INDEX
COMMIT SEARCH INDEX
CREATE SEARCH INDEX
DESCRIBE SEARCH INDEX
DROP SEARCH INDEX
RELOAD SEARCH INDEX
REBUILD SEARCH INDEX

The timeout is used only if the cqlsh request timeout is equal to the default value of 10 seconds:

cqlsh --request-timeout 10

To increase the timeout request timeout for search-specific CQL statements to 15 minutes (900 seconds):

export CQLSH_SEARCH_MANAGEMENT_TIMEOUT_SECONDS=900;

Execute commands in `cqlsh`

You can execute CQL or CQL shell commands in cqlsh interactively, from the command line, or from a file. You can also use the embedded CQL shell in Mission Control or the Astra Portal.

Interactive mode

Use interactive mode to execute commands and view results in a shell environment. When you start cqlsh, the shell prompt is cqlsh>. When you use a keyspace, the prompt changes to include the keyspace name.

This example uses the cycling keyspace and retrieves a specific race

cqlsh> USE cycling;
cqlsh:cycling> SELECT * FROM calendar WHERE race_id = 201;

 race_id | race_name
---------+---------------------------------
     201 | Women's Tour of New Zealand

(1 rows)
cqlsh:cycling>

At the command line

Use cqlsh with the -e option to execute commands at the command line. You can specify multiple -e options to run several commands in sequence or combine commands in a single string delimited by semicolons (;).

By default, results are displayed in the terminal. You can use the redirection operator > to write the output to a file instead.

This example includes two -e options: one to switch to the cycling keyspace and another to select a specific race.

cqlsh/bin> ./cqlsh \
  -e "USE cycling;" \
  -e "SELECT race_id, race_name FROM calendar WHERE race_id = 201;"

 race_id | race_name
---------+---------------------------------
     201 | Women's Tour of New Zealand

(1 rows)
cqlsh/bin>

From a file

You can use the -f option to run commands from a file. This example uses a file named query.cql with a USE command and a SELECT command. By default, results are displayed in the terminal. You can use the redirection operator > to write the output to a file instead.

query.cql

USE cycling;
SELECT * FROM calendar WHERE race_id = 201;

cqlsh/bin> ./cqlsh -f ~/working/query.cql

 race_id | race_name
---------+---------------------------------
     201 | Women's Tour of New Zealand

(1 rows)
cqlsh/bin>

Use CQL shell (`cqlsh`)

CQL shell commands

Prerequisites

Connect with `cqlsh`

Command line options

Required parameters

Options

Environment variables

Execute commands in `cqlsh`

Interactive mode

At the command line

From a file

Was this helpful?

Give Feedback

Use CQL shell (cqlsh)

Was this helpful?

Use CQL shell (`cqlsh`)