• cqlsh: the CQL shell
  • Compatibility
  • Optional Dependencies
  • pytz
  • cython
  • cqlshrc
  • credentials
  • cql history
  • Command Line Options
  • Special Commands
    • Options for
    • Options for
  • Shared COPY Options

    cqlsh: the CQL shell

    cqlsh is a command-line interface for interacting with Cassandra using CQL (the Cassandra Query Language). It is shipped with every Cassandra package, and can be found in the bin/ directory alongside the cassandra executable. cqlsh is implemented with the Python native protocol driver, and connects to the single specified node.

    Compatibility

    cqlsh is compatible with Python 2.7. cqlsh is only guaranteed to work with the version of Cassandra that it was released with. In some cases, cqlsh may work with older or newer versions of Cassandra, but this is not officially supported.

    Optional Dependencies

    cqlsh ships with all essential dependencies. However, there are some optional dependencies that can be installed to improve the capabilities of cqlsh.

    pytz

    cqlsh displays all timestamps with a UTC timezone. To support display of timestamps with another timezone, install the pytz library. See the timezone option in cqlshrc for specifying a timezone to use.

    cython

    COPY operations can be improved by installing cython. This will compile the python modules that are central to the performance of COPY.

    cqlshrc

    cqlshrc file holds configuration options for cqlsh. By default, the file is locagted the user’s home directory at ~/.cassandra/cqlsh, but a custom location can be specified with the --cqlshrc option. conf/cqlshrc.sample file of a tarball installation. You can also view the latest version of the cqlshrc file online.

    credentials

    [auth_provider] section of the cqlshrc file. The credentials file must be owned by the user and no one else has permission to read the file. conf/credentials.sample file of a tarball installation.

    cql history

    ~/.cassandra/cql_history. You can change this default by setting the environment variable CQL_HISTORY like ~/some/other/path/to/cqlsh_history where cqlsh_history is a file. All parent directories to history file will be created if they do not exist. If you do not want to persist history, you can do so by setting CQL_HISTORY to /dev/null. This feature is supported from Cassandra 4.1.

    Command Line Options

    cqlsh.py [options] [host [port]] CQL Shell for Apache Cassandra Options: --version show program’s version number and exit -h --help show this help message and exit -C --color Always use color output --no-color Never use color output --browser=BROWSER docs.python.org/3/library/webbrowser.html. browser path followed by %s, example: /usr/bin/google-chrome-stable %s --ssl Use SSL -u USERNAME --username=USERNAME Authenticate as user. -p PASSWORD --password=PASSWORD Authenticate using password. -k KEYSPACE --keyspace=KEYSPACE Authenticate to the given keyspace. -f FILE --file=FILE Execute commands from FILE, then exit --debug Show additional debugging information --coverage Collect coverage data --encoding=ENCODING Specify a non-default encoding for output. (Default: utf-8) --cqlshrc=CQLSHRC Specify an alternative cqlshrc file location. --credentials=CREDENTIALS Specify an alternative credentials file location. --cqlversion=CQLVERSION Specify a particular CQL version, by default the highest version supported by the server will be used. Examples: “3.0.3”, “3.1.0” --protocol-version=PROTOCOL_VERSION Specify a specific protcol version otherwise the client will default and downgrade as necessary -e EXECUTE --execute=EXECUTE Execute the statement and quit. --connect-timeout=CONNECT_TIMEOUT Specify the connection timeout in seconds (default: 5 seconds). --request-timeout=REQUEST_TIMEOUT Specify the default request timeout in seconds (default: 10 seconds). -t, --tty Force tty mode (command prompt). -v --v Print the current version of cqlsh.

    Special Commands

    cqlsh also supports a number of special commands that are not part of CQL. These are detailed below.

    CONSISTENCY

    Usage: CONSISTENCY <consistency level> Sets the consistency level for operations to follow. Valid arguments include:
  • ANY
  • ONE
  • TWO
  • THREE
  • QUORUM
  • ALL
  • LOCAL_QUORUM
  • LOCAL_ONE
  • SERIAL
  • LOCAL_SERIAL

    SERIAL CONSISTENCY

    Usage: SERIAL CONSISTENCY <consistency level> Sets the serial consistency level for operations to follow. Valid arguments include:
  • SERIAL
  • LOCAL_SERIAL INSERT, UPDATE and DELETE with an IF condition). For those, the serial consistency level defines the consistency level of the serial phase (or “paxos” phase) while the normal consistency level defines the consistency for the “learn” phase, i.e. what type of reads will be guaranteed to see the update right away. For example, if a conditional write has a consistency level of QUORUM (and is successful), then a QUORUM read is guaranteed to see that write. But if the regular consistency level of that write is ANY, then only a read with a consistency level of SERIAL is guaranteed to see it (even a read with consistency ALL is not guaranteed to be enough).

    SHOW VERSION

    cqlsh, Cassandra, CQL, and native protocol versions in use. Example:
    1. cqlsh> SHOW VERSION[cqlsh 5.0.1 | Cassandra 3.8 | CQL spec 3.4.2 | Native protocol v4]

    SHOW HOST

    cqlsh is connected to in addition to the cluster name. Example:
    1. cqlsh> SHOW HOSTConnected to Prod_Cluster at 192.0.0.1:9042.

    SHOW SESSION

    Pretty prints a specific tracing session. Usage: SHOW SESSION <session id> Example usage:
    1. cqlsh> SHOW SESSION 95ac6470-327e-11e6-beca-dfb660d92ad8Tracing session: 95ac6470-327e-11e6-beca-dfb660d92ad8 activity                                                  | timestamp                  | source    | source_elapsed | client-----------------------------------------------------------+----------------------------+-----------+----------------+-----------                                        Execute CQL3 query | 2016-06-14 17:23:13.979000 | 127.0.0.1 |              0 | 127.0.0.1 Parsing SELECT * FROM system.local; [SharedPool-Worker-1] | 2016-06-14 17:23:13.982000 | 127.0.0.1 |           3843 | 127.0.0.1...

    SOURCE

    Reads the contents of a file and executes each line as a CQL statement or special cqlsh command. Usage: SOURCE <string filename> Example usage:
    1. cqlsh> SOURCE '/home/calvinhobbs/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. Usage:
    1. CAPTURE '<file>';CAPTURE OFF;CAPTURE;
    '~/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. CAPTURE OFF. CAPTURE with no arguments.

    HELP

    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.

    TRACING

    Enables or disables tracing for queries. When tracing is enabled, once a query completes, a trace of the events during the query will be printed. Usage:
    1. TRACING ONTRACING OFF

    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. Usage:
    1. PAGING ONPAGING OFFPAGING <page size in rows>

    EXPAND

    EXPAND is useful when many columns are fetched, or the contents of a single column are large. Usage:
    1. EXPAND ONEXPAND OFF

    LOGIN

    Authenticate as a specified Cassandra user for the current session. Usage:
    1. LOGIN <username> [<password>]

    EXIT

    Ends the current session and terminates the cqlsh process. Usage:
    1. EXITQUIT

    CLEAR

    Clears the console. Usage:
    1. CLEARCLS

    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. Usage:
    1. DESCRIBE CLUSTERDESCRIBE SCHEMADESCRIBE KEYSPACESDESCRIBE KEYSPACE <keyspace name>DESCRIBE TABLESDESCRIBE TABLE <table name>DESCRIBE INDEX <index name>DESCRIBE MATERIALIZED VIEW <view name>DESCRIBE TYPESDESCRIBE TYPE <type name>DESCRIBE FUNCTIONSDESCRIBE FUNCTION <function name>DESCRIBE AGGREGATESDESCRIBE AGGREGATE <aggregate function name>
    DESC may be used in place of DESCRIBE. DESCRIBE CLUSTER command prints the cluster name and partitioner:
    1. cqlsh> DESCRIBE CLUSTERCluster: Test ClusterPartitioner: Murmur3Partitioner
    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. Usage:
    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. <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. shared-copy-options for options that apply to both COPY TO and COPY FROM.

    COPY TO

    MAXREQUESTS The maximum number token ranges to fetch simultaneously. Defaults to 6. PAGESIZE The number of rows to fetch in a single page. Defaults to 1000. PAGETIMEOUT By default the page timeout is 10 seconds per 1000 entries in the page size or 10 seconds if pagesize is smaller. BEGINTOKEN, ENDTOKEN Token range to export. Defaults to exporting the full ring. MAXOUTPUTSIZE 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, and is the default. ENCODING utf8.

    COPY FROM

    Copies data from a CSV file to table. Usage:
    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. <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. shared-copy-options for options that apply to both COPY TO and COPY FROM.

    COPY TO

    INGESTRATE The maximum number of rows to process per second. Defaults to 100000. MAXROWS The maximum number of rows to import. -1 means unlimited, and is the default. SKIPROWS A number of initial rows to skip. Defaults to 0. SKIPCOLS A comma-separated list of column names to ignore. By default, no columns are skipped. MAXPARSEERRORS The maximum global number of parsing errors to ignore. -1 means unlimited, and is the default. MAXINSERTERRORS The maximum global number of insert errors to ignore. -1 means unlimited. The default is 1000. ERRFILE = import_<ks>_<table>.err where <ks> is your keyspace and <table> is your table name. MAXBATCHSIZE The max number of rows inserted in a single batch. Defaults to 20. MINBATCHSIZE The min number of rows inserted in a single batch. Defaults to 2. CHUNKSIZE The number of rows that are passed to child worker processes from the main process at a time. Defaults to 1000.

    Shared COPY Options

    COPY TO and COPY FROM. NULLVAL null. HEADER 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. Defaults to false. DECIMALSEP .. THOUSANDSSEP The character that is used to separate thousands. Defaults to the empty string. BOOLSTYlE True,False. NUMPROCESSES 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 COPY TO) or insert a chunk of data (when using COPY FROM) before giving up. Defaults to 5. REPORTFREQUENCY How often status updates are refreshed, in seconds. Defaults to 0.25. RATEFILE An optional file to output rate statistics to. By default, statistics are not output to a file.