Join us on
Star us on
Get Started
Slack
GitHub
Get Started
v2.5 (latest) v2.2 (stable) v2.1 (earlier version) v2.0 (earlier version) v1.3 (earlier version)
  • GET STARTED
    • Quick start
      • 1. Install YugabyteDB
      • 2. Create a local cluster
      • 3. Explore YSQL
      • 4. Build an application
        • Java
        • NodeJS
        • Go
        • Python
        • Ruby
        • C#
        • PHP
        • C++
        • C
    • Introduction
    • Explore core
      • 1. Linear scalability
      • 2. Fault tolerance
      • 3. Global distribution
      • 4. Auto sharding
      • 5. Tunable reads
      • 6. Observability
  • USER GUIDES
    • Develop
      • Learn app development
        • 1. SQL vs NoSQL
        • 2. Data modeling
        • 3. Data types
        • 4. ACID transactions
        • 5. Aggregations
        • 6. Batch operations
        • 7. Date and time
        • 8. Strings and text
      • Ecosystem integrations
        • Apache Kafka
        • Apache Spark
        • JanusGraph
        • KairosDB
        • Presto
        • Metabase
      • Real-world examples
        • E-Commerce App
        • IoT Fleet Management
        • Retail Analytics
      • Explore sample applications
    • Deploy
      • Checklist
      • Manual deployment
        • 1. System configuration
        • 2. Install software
        • 3. Start YB-Masters
        • 4. Start YB-TServers
        • 5. Verify deployment
      • Kubernetes
        • Helm Chart
        • Helm configuration
        • Local SSD
      • Docker
      • Public clouds
        • Amazon Web Services
        • Google Cloud Platform
        • Microsoft Azure
      • Pivotal Cloud Foundry
      • Yugabyte Platform
        • 1. Prepare cloud environment
        • 2. Install Admin Console
        • 3. Configure Admin Console
        • 4. Configure Cloud Providers
    • Benchmark
      • Performance
      • YCSB
      • Large datasets
    • Secure
      • Security checklist
      • Authentication
      • Authorization
        • 1. RBAC Model
        • 2. Create Roles
        • 3. Grant permissions
      • TLS encryption
        • 1. Prepare nodes
        • 2. Server-server encryption
        • 3. Client-server encryption
        • 4. Connect to cluster
      • Encryption at Rest
    • Manage
      • Backup and restore
        • Backing up data
        • Restoring data
      • Data migration
        • Bulk import
        • Bulk export
      • Change cluster config
      • Upgrade deployment
      • Diagnostics reporting
      • Yugabyte Platform
        • Create universe - Multi-zone
        • Create universe - Multi-region
        • Edit universe
        • Edit config flags
        • Health checking and alerts
        • Create and edit instance tags
        • Node status and actions
        • Read replicas
        • Back up and restore
        • Upgrade universe
        • Delete universe
    • Troubleshoot
      • Troubleshooting overview
      • Cluster level issues
        • YCQL connection issues
        • YEDIS connection Issues
      • Node level issues
        • Check processes
        • Inspect logs
        • System statistics
      • Yugabyte Platform
        • Troubleshoot universes
  • REFERENCE
    • APIs
      • YSQL
        • Statements
          • ABORT
          • ALTER DATABASE
          • ALTER DOMAIN
          • ALTER TABLE
          • BEGIN
          • COMMENT
          • COMMIT
          • COPY
          • CREATE DATABASE
          • CREATE DOMAIN
          • CREATE INDEX
          • CREATE SCHEMA
          • CREATE SEQUENCE
          • CREATE TABLE
          • CREATE TABLE AS
          • CREATE TYPE
          • CREATE USER
          • CREATE VIEW
          • DEALLOCATE
          • DELETE
          • DROP DATABASE
          • DROP DOMAIN
          • DROP SEQUENCE
          • DROP TABLE
          • DROP TYPE
          • END
          • EXECUTE
          • EXPLAIN
          • GRANT
          • INSERT
          • LOCK
          • PREPARE
          • RESET
          • REVOKE
          • ROLLBACK
          • SELECT
          • SET
          • SET CONSTRAINTS
          • SET TRANSACTION
          • SHOW
          • SHOW TRANSACTION
          • TRUNCATE
          • UPDATE
        • Data types
          • Binary
          • Boolean
          • Character
          • Date-time
          • Json
          • Money
          • Numeric
          • Serial
          • UUID
        • Expressions
          • currval()
          • lastval()
          • nextval()
        • Keywords
        • Reserved Names
      • YCQL
        • Quick Start YCQL
        • ALTER KEYSPACE
        • ALTER ROLE
        • ALTER TABLE
        • CREATE INDEX
        • CREATE KEYSPACE
        • CREATE ROLE
        • CREATE TABLE
        • CREATE TYPE
        • DROP INDEX
        • DROP KEYSPACE
        • DROP ROLE
        • DROP TABLE
        • DROP TYPE
        • GRANT PERMISSION
        • GRANT ROLE
        • REVOKE PERMISSION
        • REVOKE ROLE
        • USE
        • INSERT
        • SELECT
        • UPDATE
        • DELETE
        • TRANSACTION
        • TRUNCATE
        • Simple Value
        • Subscript
        • Function Call
        • Operator Call
        • BLOB
        • BOOLEAN
        • MAP, SET, LIST
        • FROZEN
        • INET
        • Integer & Counter
        • Non-Integer
        • TEXT
        • Date & Time Types
        • UUID & TIMEUUID
        • JSONB
        • Date and time functions
    • CLIs
      • yb-ctl
      • yb-docker-ctl
      • yb-master
      • yb-tserver
      • ysqlsh
      • cqlsh
    • Sample data
      • Chinook
      • Northwind
      • PgExercises
      • SportsDB
    • Tools
      • TablePlus
  • RELEASES
    • Release history
      • v1.3.1
      • v1.3.0
      • v1.2.12
      • v1.2.11
      • v1.2.10
      • v1.2.9
      • v1.2.8
      • v1.2.6
      • v1.2.5
      • v1.2.4
  • CONCEPTS
    • Architecture
      • Design goals
      • Layered architecture
      • Basic concepts
        • Universe
        • YB-TServer
        • YB-Master
        • Acknowledgements
      • Query layer
        • Overview
      • DocDB store
        • Sharding
        • Replication
        • Persistence
        • Performance
      • DocDB transactions
        • Isolation Levels
        • Single row transactions
        • Distributed transactions
        • Transactional IO path
  • FAQ
    • Comparisons
      • CockroachDB
      • Google Cloud Spanner
      • MongoDB
      • FoundationDB
      • Amazon DynamoDB
      • Azure Cosmos DB
      • Apache Cassandra
      • Redis in-memory store
      • Apache HBase
    • Other FAQs
      • Product
      • Architecture
      • Yugabyte Platform
      • API compatibility
  • CONTRIBUTOR GUIDES
    • Get involved
  • Misc
    • YEDIS
      • Quick start
      • Develop
        • Client drivers
          • C
          • C++
          • C#
          • Go
          • Java
          • NodeJS
          • Python
      • API reference
        • APPEND
        • AUTH
        • CONFIG
        • CREATEDB
        • DELETEDB
        • LISTDB
        • SELECT
        • DEL
        • ECHO
        • EXISTS
        • EXPIRE
        • EXPIREAT
        • FLUSHALL
        • FLUSHDB
        • GET
        • GETRANGE
        • GETSET
        • HDEL
        • HEXISTS
        • HGET
        • HGETALL
        • HINCRBY
        • HKEYS
        • HLEN
        • HMGET
        • HMSET
        • HSET
        • HSTRLEN
        • HVALS
        • INCR
        • INCRBY
        • KEYS
        • MONITOR
        • PEXPIRE
        • PEXPIREAT
        • PTTL
        • ROLE
        • SADD
        • SCARD
        • RENAME
        • SET
        • SETEX
        • PSETEX
        • SETRANGE
        • SISMEMBER
        • SMEMBERS
        • SREM
        • STRLEN
        • ZRANGE
        • TSADD
        • TSCARD
        • TSGET
        • TSLASTN
        • TSRANGEBYTIME
        • TSREM
        • TSREVRANGEBYTIME
        • TTL
        • ZADD
        • ZCARD
        • ZRANGEBYSCORE
        • ZREM
        • ZREVRANGE
        • ZSCORE
        • PUBSUB
        • PUBLISH
        • SUBSCRIBE
        • UNSUBSCRIBE
        • PSUBSCRIBE
        • PUNSUBSCRIBE
> CLIs >

cqlsh

Attention

This page documents an earlier version. Go to the latest (v2.3) version.
  • Overview
  • Download
  • Example
  • Command line options
  • Special commands
    • CONSISTENCY
    • SHOW VERSION
    • SHOW HOST
    • SOURCE
    • CAPTURE
    • HELP
    • PAGING
    • EXPAND
    • LOGIN
    • EXIT
    • CLEAR
    • DESCRIBE
    • COPY TO
    • COPY FROM

Overview

cqlsh is a command line interface (CLI), or shell, for interacting with YugabyteDB through YCQL.

Download

cqlsh is installed as part of YugabyteDB and is located in the bin directory of Yugabyte home. It is also available for download and install from YugabyteDB's GitHub repo.

Example

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

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

Command line options

Use the --help option to see all the command line options supported.

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.
Options Short Form Default Description
--color -C Force color output
--no-color Disable color output
--browser Specify the browser to use for displaying cqlsh help. This can be one of the supported browser names (e.g. firefox) or a browser path followed by %s (e.g. /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 cqlsh. By default this is in the user’s 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)

Special commands

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

CONSISTENCY

CONSISTENCY <consistency level>

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 inspect the current consistency level, use CONSISTENCY with no arguments.

SHOW VERSION

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

cqlsh> SHOW VERSION
[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 node that cqlsh is connected to in addition to the cluster name. Example:

cqlsh> SHOW HOST
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.

SOURCE '<file>'

Example usage:

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.

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.
  • 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.

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.

PAGING ON
PAGING OFF
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.

EXPAND ON
EXPAND OFF

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

LOGIN

Authenticate as a specified YugabyteDB user for the current session.

LOGIN <username> [<password>]

EXIT

Ends the current session and terminates the cqlsh process.

EXIT
QUIT

CLEAR

Clears the console.

CLEAR
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.

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 namer:

cqlsh> DESCRIBE CLUSTER

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.

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.

Options 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 options are common to both COPY TO and COPY FROM.

Options 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.
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.

COPY FROM

Copies data from a CSV file to table.

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.

Options 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_<ks>_<table>.err where <ks> is your keyspace and <table> is your 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 options common to both COPY TO and COPY FROM.

  • Overview
  • Download
  • Example
  • Command line options
  • Special commands
    • CONSISTENCY
    • SHOW VERSION
    • SHOW HOST
    • SOURCE
    • CAPTURE
    • HELP
    • PAGING
    • EXPAND
    • LOGIN
    • EXIT
    • CLEAR
    • DESCRIBE
    • COPY TO
    • COPY FROM
Ask our community
  • Slack
  • Github
  • Forum
  • StackOverflow
Yugabyte
Contact Us
Copyright © 2017-2020 Yugabyte, Inc. All rights reserved.