--- title: "ycqlsh" url: "https://docs.yugabyte.com/stable/api/ycqlsh/" --- # ycqlsh Shell for interacting with the YugabyteDB YCQL API. Shell for interacting with the YugabyteDB YCQL API ## Overview The YCQL shell (ycqlsh) is a CLI for interacting with YugabyteDB using [YCQL](/stable/api/ycql/ "YCQL"). ### Requirements Python 3 v3.6 or later must be pre-installed. ### Installation ycqlsh is installed with YugabyteDB and located in the `bin` directory of the YugabyteDB home directory. To download and install a standalone version of ycqlsh, refer to [YugabyteDB clients](/stable/releases/yugabyte-clients/ "YugabyteDB clients"). 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 ```sh ./bin/ycqlsh ``` ```output 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 ```sh ycqlsh [flags] [host [port]] ``` Where - `host` is the IP address of the host on which [YB-TServer](/stable/architecture/yb-tserver "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 ```sh ./bin/ycqlsh --execute "select cluster_name, data_center, rack from system.local" 127.0.0.1 ``` ```output cluster_name | data_center | rack ---------------+-------------+------- local cluster | datacenter1 | rack1 ``` ### Flags | Flag | Short Form | Default | Description | |-------------------------|------------|---------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | `--color` | `-C` | | Force color output. | | `--no-color` | | | Disable color output. | | `--browser` | | | Specify the browser to use for displaying ycqlsh help. This can be one of the [supported browser names](https://docs.python.org/2/library/webbrowser.html "supported browser names") (for example, Firefox) or a browser path followed by `%s` (for example, `/usr/bin/google-chrome-stable %s`). | | `--ssl` | | | Use SSL when connecting to YugabyteDB. | | `--user` | `-u` | | Username to authenticate against YugabyteDB with. | | `--password` | `-p` | | Password to authenticate against YugabyteDB with, should be used in conjunction with `--user`. | | `--keyspace` | `-k` | | Keyspace to authenticate to, should be used in conjunction with `--user`. | | `--file` | `-f` | | Execute commands from the given file, then exit. | | `--debug` | | | Print additional debugging information. | | `--encoding` | | UTF-8 | Specify a non-default encoding for output. | | `--cqlshrc` | | | Specify the location for the `cqlshrc` file. The `cqlshrc` file holds configuration options for ycqlsh. By default this is in the user home directory at `~/.cassandra/cqlsh`. | | `--execute` | `-e` | | Execute the given statement, then exit. | | `--connect-timeout` | | 2 | Specify the connection timeout in seconds. | | `--request-timeout` | | 10 | Specify the request timeout in seconds. | | `--tty` | `-t` | | Force tty mode (command prompt). | | `--refresh_on_describe` | `-r` | | Force a refresh of the schema metadata on [DESCRIBE](#describe "DESCRIBE"). | ### 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: ```sh ./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. ```sh CAPTURE '' 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. ```cql CLEAR CLS ``` ### CONSISTENCY ```cql CONSISTENCY ``` Sets the consistency level for the read operations that follow. Valid arguments include: | Consistency Level | Description | |-------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------| | `QUORUM` | Read the strongly consistent results from the tablet's quorum. The read request will be processed by the tablet leader only. This is the default consistency level. | | `ONE` | Read from a follower with relaxed consistency guarantees. | To view the current consistency level, use `CONSISTENCY` with no arguments. ### SERIAL CONSISTENCY ```cql SERIAL CONSISTENCY ``` Sets the serial consistency level for statements that use it (for example, conditional updates with `IF`). This is separate from the regular consistency set by [CONSISTENCY](#consistency "CONSISTENCY"). Valid levels are `SERIAL` and `LOCAL_SERIAL`. To show the current serial consistency, run `SERIAL CONSISTENCY` with no arguments. Not the SERIAL data type This command does not relate to an auto-increment SQL-style `SERIAL` type. In YCQL, use [SERIAL CONSISTENCY](#serial-consistency "SERIAL CONSISTENCY") only for serial consistency on the read path of conditional (LWT-style) operations. For auto-increment-style keys in YCQL, use other patterns (for example, `uuid` or application-side IDs). ### TRACING ```cql TRACING ON TRACING OFF TRACING ``` Enables or disables request tracing for subsequent statements in this session. With no arguments, `TRACING` prints whether tracing is on or off. When tracing is on, the server can attach trace information to responses. How much detail appears in the shell may differ from Apache Cassandra; you can also inspect TServer logs for deeper diagnostics if needed. ### UNICODE `UNICODE` is documented in the embedded ycqlsh help. It does not toggle behavior: it explains how non-ASCII and control characters are displayed for `text` and `ascii` values (for example, backslash escapes vs literal Unicode when your locale supports it). To read this help in the shell: ```cql HELP UNICODE ``` ### COPY FROM Copies data from a CSV file to table. ```cql COPY [(, ...)] FROM WITH [AND ...] ``` 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. | Flags | Default | Description | |-------------------|---------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------| | `INGESTRATE` | 100000 | The maximum number of rows to process per second. | | `MAXROWS` | -1 | The maximum number of rows to import. -1 means unlimited. | | `SKIPROWS` | 0 | A number of initial rows to skip. | | `SKIPCOLS` | | A comma-separated list of column names to ignore. By default, no columns are skipped. | | `MAXPARSEERRORS` | -1 | The maximum global number of parsing errors to ignore. -1 means unlimited. | | `MAXINSERTERRORS` | 1000 | The maximum global number of insert errors to ignore. -1 means unlimited. | | `ERRFILE=` | | A file to store all rows that could not be imported; by default this is `import__
.err` where `` is the keyspace and `
` is the table name. | | `MAXBATCHSIZE` | 20 | The max number of rows inserted in a single batch. | | `MINBATCHSIZE` | 2 | The min number of rows inserted in a single batch. | | `CHUNKSIZE` | 1000 | The number of rows that are passed to child worker processes from the main process at a time. | 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. ```cql COPY
[(, ...)] TO WITH [AND ...] ``` 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. | Flags | Default | Description | |--------------------------|---------|-------------------------------------------------------------------------------------------------------------------------------------------------------| | `MAXREQUESTS` | 6 | The maximum number token ranges to fetch simultaneously. | | `PAGESIZE` | 1000 | The number of rows to fetch in a single page. | | `PAGETIMEOUT` | 10 | The timeout in seconds per 1000 entries in the page size or smaller. | | `BEGINTOKEN`, `ENDTOKEN` | | Token range to export. Defaults to exporting the full ring. | | `MAXOUTPUTSIZE` | -1 | The maximum size of the output file measured in number of lines; beyond this maximum the output file will be split into segments. -1 means unlimited. | | `ENCODING` | utf8 | The encoding used for characters. | The following flags are common to both `COPY TO` and `COPY FROM`. | Flags | Default | Description | |-------------------|--------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | `NULLVAL` | `null` | The string placeholder for null values. | | `HEADER` | `false` | For `COPY TO`, controls whether the first line in the CSV output file will contain the column names. For `COPY FROM`, specifies whether the first line in the CSV input file contains column names. | | `DELIMITER` | `,` | The character that is used to separate fields (columns). | | `DECIMALSEP` | `.` | The character that is used as the decimal point separator. | | `THOUSANDSSEP` | | The character that is used to separate thousands. Defaults to the empty string. | | `BOOLSTYlE` | `True,False` | The string literal format for boolean values. | | `NUMPROCESSES` | | The number of child worker processes to create for `COPY` tasks. Defaults to a max of 4 for `COPY FROM` and 16 for `COPY TO`. However, at most (num\_cores - 1) processes will be created. | | `MAXATTEMPTS` | 5 | The maximum number of failed attempts to fetch a range of data (when using `COPY TO`) or insert a chunk of data (when using `COPY FROM`) before giving up. | | `REPORTFREQUENCY` | 0.25 | How often status updates are refreshed, in seconds. | | `RATEFILE` | | An optional file to output rate statistics to. By default, statistics are not output to a file. | ### 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. ```cql DESCRIBE CLUSTER DESCRIBE SCHEMA DESCRIBE KEYSPACES DESCRIBE KEYSPACE DESCRIBE TABLES DESCRIBE TABLE
DESCRIBE INDEX DESCRIBE TYPES DESCRIBE TYPE ``` In any of the commands, `DESC` may be used in place of `DESCRIBE`. The `DESCRIBE CLUSTER` command prints the cluster name: ```cql ycqlsh> DESCRIBE CLUSTER ``` ```output 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. ```cql 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. ```cql 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 `. Also see the `--browser` argument for controlling what browser is used to display help. ```cql HELP ``` ### LOGIN Authenticate as a specified YugabyteDB user for the current session. ```cql LOGIN [] ``` ### 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. ```cql PAGING ON PAGING OFF PAGING ``` 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: ```cql ycqlsh> SHOW HOST ``` ```output Connected to local cluster at 127.0.0.1:9042. ``` ### SHOW VERSION Prints the ycqlsh, Cassandra, CQL, and native protocol versions in use. Example: ```cql ycqlsh> SHOW VERSION ``` ```output [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. ```sh SOURCE '' ``` Example: ```cql ycqlsh> SOURCE '/home/yugabyte/commands.cql' ``` For request tracing from the shell, see [TRACING](#tracing "TRACING"). ### TIMING Enables or disables basic request round-trip timing, as measured on a current YCQL shell session. ```cql 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. Feature support TIMING for [SELECTs](/stable/api/ycql/dml_select/ "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. #### Example You can use TIMING and run queries from a YCQL session as follows: ```cql ycqlsh> TIMING ``` ```output Timing is currently disabled. Use TIMING ON to enable. ``` ```cql ycqlsh> TIMING ON ``` ```output Now Timing is enabled ``` ```cql ycqlsh> use example; ``` ```output 26.18 milliseconds elapsed ``` ```cql ycqlsh:example> CREATE TABLE employees(department_id INT, employee_id INT,name TEXT, PRIMARY KEY(department_id, employee_id)); ``` ```output 1042.67 milliseconds elapsed ``` ```cql ycqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 1, 'John'); ``` ```output 16.89 milliseconds elapsed ``` ```cql ycqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 2, 'Jane'); ``` ```output 11.65 milliseconds elapsed ``` ```cql ycqlsh:example> SELECT * FROM employees; ``` ```output department_id | employee_id | name ---------------+-------------+------ 1 | 1 | John 1 | 2 | Jane 5.76 milliseconds elapsed (2 rows) ``` ```cql ycqlsh:example> TIMING OFF ``` ```output Disabled Timing. ```