ycqlsh - YCQL shell for YugabyteDB

Overview

The YCQL shell (ycqlsh) is a CLI for interacting with YugabyteDB using YCQL.

Installation

ycqlsh is installed with YugabyteDB and located in the bin directory of the YugabyteDB home directory.

{{<lead link="/stable/releases/yugabyte-clients/">}} To download and install a standalone version of ycqlsh, refer to YugabyteDB clients. {{</lead>}}

ycqlsh was previously named cqlsh. Although the cqlsh binary is available in the bin directory, it is deprecated and will be removed in a future release.

Starting ycqlsh

./bin/ycqlsh

Connected to local cluster at 127.0.0.1:9042.
[ycqlsh 5.0.1 | Cassandra 3.9-SNAPSHOT | CQL spec 3.4.2 | Native protocol v4]
Use HELP for help.
ycqlsh>

Online help

Run ycqlsh --help to display the online help.

Syntax

ycqlsh [flags] [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.

Example

./bin/ycqlsh --execute "select cluster_name, data_center, rack from system.local" 127.0.0.1

 cluster_name  | data_center | rack
---------------+-------------+-------
 local cluster | datacenter1 | rack1

Flags

Save YCQL output to a file

To save output from a YCQL statement to a file, run the statement using the --execute flag, and redirect the output to a file.

For example, to save the output of a SELECT statement:

./bin/ycqlsh -e "SELECT * FROM mytable" > output.txt

Special commands

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

CAPTURE

Captures command output and appends it to the specified file. Output is not shown at the console while it is captured.

CAPTURE '<file>'
CAPTURE OFF
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.
• Captures query result output only. Errors and output from ycqlsh-only commands are still shown in the ycqlsh session.
• To stop capturing output and show it in the ycqlsh session again, use CAPTURE OFF.
• To view the current capture configuration, use CAPTURE with no arguments.

CLEAR

Clears the console.

CLEAR
CLS

CONSISTENCY

CONSISTENCY <consistency level>

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

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

COPY FROM

Copies data from a CSV file to table.

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

By default, COPY FROM copies all columns from the CSV file to the table. To copy a subset of columns, add a comma-separated list of column names enclosed in parentheses after the table name.

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

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

COPY TO

Copies data from a table to a CSV file.

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

By default, COPY TO copies all columns from the table to the CSV file. To copy a subset of columns, add a comma-separated list of column names enclosed in parentheses after the table name.

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

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

DESCRIBE

Prints a description (typically a series of DDL statements) of a schema element or the cluster. Use DESCRIBE to dump all or portions of the schema.

DESCRIBE CLUSTER
DESCRIBE SCHEMA
DESCRIBE KEYSPACES
DESCRIBE KEYSPACE <keyspace name>
DESCRIBE TABLES
DESCRIBE TABLE <table name>
DESCRIBE INDEX <index name>
DESCRIBE TYPES
DESCRIBE TYPE <type name>

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

The DESCRIBE CLUSTER command prints the cluster name:

ycqlsh> DESCRIBE CLUSTER

Cluster: local cluster

The DESCRIBE SCHEMA command prints the DDL statements needed to recreate the entire schema. Use this command to dump the schema; you can then use the resulting file to clone the cluster or restore from a backup.

EXIT

Ends the current session and terminates the ycqlsh process.

EXIT
QUIT

EXPAND

Enables or disables vertical printing of rows. Use EXPAND when fetching many columns, or the contents of a single column are large.

EXPAND ON
EXPAND OFF

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

HELP

Gives information about ycqlsh 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.

HELP <topic>

LOGIN

Authenticate as a specified YugabyteDB user for the current session.

LOGIN <username> [<password>]

PAGING

Enables paging, disables paging, or sets the page size for read queries. When paging is enabled, only one page of data is fetched at a time and a prompt appears 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.

PAGING ON
PAGING OFF
PAGING <page size in rows>

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

SHOW HOST

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

ycqlsh> SHOW HOST

Connected to local cluster at 127.0.0.1:9042.

SHOW VERSION

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

ycqlsh> SHOW VERSION

[ycqlsh 5.0.1 | Cassandra 3.9-SNAPSHOT | CQL spec 3.4.2 | Native protocol v4]

SOURCE

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

SOURCE '<file>'

Example:

ycqlsh> SOURCE '/home/yugabyte/commands.cql'

TIMING

Enables or disables basic request round-trip timing, as measured on a current YCQL shell session.

TIMING ON | OFF

TIMING ON: Enables round-trip timing for all further requests.

TIMING OFF: Disables timing.

TIMING (with no arguments): Outputs the current timing status.

{{< note title = "Feature support" >}} TIMING for SELECTs is available starting from YugabyteDB version 2.18.0.0 and later. TIMING will be available for all DMLs starting from YugabyteDB version 2.19.2.0 and later. {{< /note >}}

Example

You can use TIMING and run queries from a YCQL session as follows:

ycqlsh> TIMING

Timing is currently disabled. Use TIMING ON to enable.

ycqlsh> TIMING ON

Now Timing is enabled

ycqlsh> use example;

26.18 milliseconds elapsed

ycqlsh:example> CREATE TABLE employees(department_id INT, employee_id INT,name TEXT, PRIMARY KEY(department_id, employee_id));

1042.67 milliseconds elapsed

ycqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 1, 'John');

16.89 milliseconds elapsed

ycqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 2, 'Jane');

11.65 milliseconds elapsed

ycqlsh:example> SELECT * FROM employees;

 department_id | employee_id | name
---------------+-------------+------
             1 |           1 | John
             1 |           2 | Jane
5.76 milliseconds elapsed
(2 rows)

ycqlsh:example> TIMING OFF

Disabled Timing.