cqlsh shell for YCQL

Overview

The YugabyteDB CQL shell (cqlsh) provides a CLI for interacting with YugabyteDB using YCQL.

Download

The cqlsh shell is installed as part of YugabyteDB and is located in the bin directory of YugabyteDB home. It is also available for download and install from YugabyteDB’s GitHub repository.

Example

1. $ ./bin/cqlsh --execute "select cluster_name, data_center, rack from system.local" 127.0.0.1

3. cluster_name  | data_center | rack
4. ---------------+-------------+-------
5. local cluster | datacenter1 | rack1

Online help

Run cqlsh —help to display the online help.

Syntax

1. cqlsh [options] [host [port]]

Where

• host is the IP address of the host on which YB-TServer is run. The default is local host at 127.0.0.1.
• port is the TCP port at which YB-TServer listens for YCQL connections. The default is 9042.

Special commands

In addition to supporting regular YCQL statements, cqlsh also supports the following special commands.

CONSISTENCY

1. CONSISTENCY <consistency level>

Sets the consistency level for the read operations that follow. Valid arguments include:

To inspect the current consistency level, use CONSISTENCY with no arguments.

SHOW VERSION

Prints the cqlsh, Cassandra, CQL, and native protocol versions in use. Example:

1. cqlsh> SHOW VERSION

1. [cqlsh 5.0.1 | Cassandra 3.8 | CQL spec 3.4.2 | Native protocol v4]

SHOW HOST

Prints the IP address and port of the YB-TServer server that cqlsh is connected to in addition to the cluster name. Example:

1. cqlsh> SHOW HOST

1. Connected to local cluster at 127.0.0.1:9042.

SOURCE

Reads the contents of a file and executes each line as a YCQL statement or special cqlsh command.

1. SOURCE '<file>'

Example usage:

1. cqlsh> SOURCE '/home/yugabyte/commands.cql'

CAPTURE

Begins capturing command output and appending it to a specified file. Output will not be shown at the console while it is captured.

1. CAPTURE '<file>'
2. CAPTURE OFF
3. CAPTURE

• The path to the file to be appended to must be given inside a string literal. The path is interpreted relative to the current working directory. The tilde shorthand notation (~/mydir) is supported for referring to $HOME.
• Only query result output is captured. Errors and output from cqlsh-only commands will still be shown in the cqlsh session.
• To stop capturing output and show it in the cqlsh session again, use CAPTURE OFF.
• To inspect the current capture configuration, use CAPTURE with no arguments.

HELP

Gives information about cqlsh commands. To see available topics, enter HELP without any arguments. To see help on a topic, use HELP <topic>. Also see the —browser argument for controlling what browser is used to display help.

1. HELP <topic>

PAGING

Enables paging, disables paging, or sets the page size for read queries. When paging is enabled, only one page of data will be fetched at a time and a prompt will appear to fetch the next page. Generally, it’s a good idea to leave paging enabled in an interactive session to avoid fetching and printing large amounts of data at once.

1. PAGING ON
2. PAGING OFF
3. PAGING <page size in rows>

To inspect the current paging setting, use PAGING with no arguments.

EXPAND

Enables or disables vertical printing of rows. Enabling EXPAND is useful when many columns are fetched, or the contents of a single column are large.

1. EXPAND ON
2. EXPAND OFF

To inspect the current expand setting, use EXPAND with no arguments.

LOGIN

Authenticate as a specified YugabyteDB user for the current session.

1. LOGIN <username> [<password>]

EXIT

Ends the current session and terminates the cqlsh process.

1. EXIT
2. QUIT

CLEAR

Clears the console.

1. CLEAR
2. CLS

DESCRIBE

Prints a description (typically a series of DDL statements) of a schema element or the cluster. This is useful for dumping all or portions of the schema.

1. DESCRIBE CLUSTER
2. DESCRIBE SCHEMA
3. DESCRIBE KEYSPACES
4. DESCRIBE KEYSPACE <keyspace name>
5. DESCRIBE TABLES
6. DESCRIBE TABLE <table name>
7. DESCRIBE INDEX <index name>
8. DESCRIBE TYPES
9. DESCRIBE TYPE <type name>

In any of the commands, DESC may be used in place of DESCRIBE.

The DESCRIBE CLUSTER command prints the cluster namer:

1. cqlsh> DESCRIBE CLUSTER

1. Cluster: local cluster

The DESCRIBE SCHEMA command prints the DDL statements needed to recreate the entire schema. This is especially useful for dumping the schema in order to clone a cluster or restore from a backup.

COPY TO

Copies data from a table to a CSV file.

1. COPY <table name> [(<column>, ...)] TO <file name> WITH <copy option> [AND <copy option> ...]

If no columns are specified, all columns from the table will be copied to the CSV file. A subset of columns to copy may be specified by adding a comma-separated list of column names surrounded by parenthesis after the table name.

The file name should be a string literal (with single quotes) representing a path to the destination file. This can also the special value STDOUT (without single quotes) to print the CSV to stdout.

The following options are common to both COPY TO and COPY FROM.

COPY FROM

Copies data from a CSV file to table.

1. COPY <table name> [(<column>, ...)] FROM <file name> WITH <copy option> [AND <copy option> ...]

If no columns are specified, all columns from the CSV file will be copied to the table. A subset of columns to copy may be specified by adding a comma-separated list of column names surrounded by parenthesis after the table name.

The file name should be a string literal (with single quotes) representing a path to the source file. This can also the special value STDIN (without single quotes) to read the CSV data from stdin.

See COPY TO for additional options common to both COPY TO and COPY FROM.