• Glossary
  • Support
  • Downloads
  • DataStax Home
Get Live Help
Expand All
Collapse All

CQL for DSE

  • Overview
    • Cassandra structure
    • About CQL
    • CQL quick reference
    • Known issues
  • Getting started
    • Vector Search Quickstart
    • CQL Quickstart
    • SAI Quickstart
    • DSE Search Indexing Quick Start
  • Planning
    • Data modeling
      • Data modeling concepts
      • Data modeling analysis
  • Connecting
    • cqlsh (startup options)
      • Accessing data using CQL
        • Starting the standalone cqlsh tool
        • Starting cqlsh
    • Developing
      • Introduction to CQL
      • Managing keyspaces
        • Keyspace concepts
        • Create a keyspace
        • Check a keyspace
        • Alter a keyspace
        • Drop a keyspace
      • Managing tables
        • Table concepts
        • Data types
        • Create a table
        • Check a table
        • Alter a table
        • Truncate a table
        • Drop a table
      • Managing columns
        • Managing columns
        • Create a table column
        • Check column existence
        • Alter a table column
        • Drop a column
        • User-defined type (UDT) overview
          • Create a user-defined type (UDT)
          • Check for user-defined type (UDT) existence
          • Alter a user-defined type (UDT)
          • Drop a user-defined type (UDT)
        • User-defined functions (UDFs) and user-defined aggregates (UDAs) overview
          • Create a user-defined function (UDF)
          • Check a user-defined function (UDF)
          • Drop a user-defined function (UDF)
          • Create a user-defined aggregate function (UDA)
          • Checking a user-defined aggregate (UDA)
          • Drop a user-defined aggregate (UDA)
      • Managing Materialized views
        • Materialized views concepts
        • Known limitations of materialized views
        • Create a materialized view (MV)
        • Alter a materialized view (MV)
        • Drop a materialized view
        • Frequently asked questions about materialized views
      • Indexing Tables
        • Indexing concepts
        • Storage-Attached Indexing (SAI) Overview
          • SAI concepts
          • SAI Quickstart
          • SAI FAQ
          • Create SAI index
          • Alter SAI index
          • Drop SAI index
          • Querying with SAI
          • Configure SAI indexes
          • Monitor SAI indexes
          • SAI write path and read path
        • Secondary indexes (2i) overview
          • Secondary index (2i) concepts
          • When to use an index
          • Building and maintaining secondary indexes (2i)
          • Create a secondary index (2i)
            • Create a 2i on a collection column
          • Check secondary index (2i) existence
          • Alter a secondary index (2i)
          • Drop a secondary index (2i)
        • Search indexes overview
          • Search index commands
          • Adjusting timeout for index management
          • Creating a search index
          • Configuring search index joins
          • Joining cores
          • Reloading the search index
          • Removing a search index
          • Managing search index fields
            • Syntax for changing schema settings
            • Search index schema field types
            • Adding a new field type
            • Adding a column to the index
            • Indexing tuples and UDTs fields
              • Tuple configuration example
              • UDT configuration example
              • Nesting tuples and UDTs
              • Tuples and UDTs as CQL map values
            • Indexing map columns
            • Indexing a column for different analysis
            • Dropping columns from the index
          • Updating the index after data expires (TTL)
          • Inserting, updating, and deleting data
          • Search indexing examples
        • Search index examples
          • Search index examples
            • Creating a demo keyspace for tutorials
            • Indexing and querying polygons
      • Inserting and updating data
        • Insert simple data
        • Set column
        • List fields
        • Map fields
        • Tuple
        • User-defined type (UDT) column
        • Insert static data
        • UUID and timeuuid column
        • Date column
        • Time column
        • Timestamp column
        • Duration column
        • Update counter
        • Lightweight transactions (LWTs)
        • Inserting JSON formatted values
        • Batching inserts and updates
          • Batching inserts, updates, and deletes
          • Good use of BATCH statement
          • Misuse of BATCH statement
        • Importing data from a CSV file
        • Setting the time-to-live (TTL) for value
          • Inserting, updating, and deleting data
      • Deleting data
        • Deleting values from a column or entire row
        • Expiring data with time-to-live
        • Expiring data with TTL example
      • Querying and retrieving data
        • Using a SELECT statement
        • Restricting queries using WHERE clauses
          • Partition keys
          • Clustering columns
          • Retrieval using the IN keyword
          • Sorting and limiting results
        • Returning data from collection columns
        • Aggregating results
        • Using user-defined functions (UDF)
        • Applying user-defined aggregate (UDA) functions
          • Testing performance impact using tracing
        • Formatting query results as JSON
        • Retrieving the write timestamp
        • Using tracing
          • Tracing performance
        • DSE Search index querying
        • Search index filtering best practices
          • Search index syntax
          • search-index:si-query-syntax.adoc#siIndexedColumns
          • search-index:si-query-syntax.adoc#basicSolrSyntax
          • search-index:si-query-syntax.adoc#queriesJSON
          • search-index:si-query-syntax.adoc#siEscapeCharacters
        • Native CQL search queries
        • Identifying the partition key
        • Filtering on terms
          • Filtering on words, phrases, or substrings
          • Fuzzy search
        • UDT query examples
        • Querying a CQL collection set
        • Using date ranges in solr_query
        • Geospatial queries for Point and LineString
        • Spatial queries with polygons require JTS
        • Using dynamic fields
        • Limiting results and paging
        • Restricted query routing
    • Securing overview
      • Security quickstart
      • Using cqlsh with authentication
      • System tables for authentication and authorization
  • Cycling Examples
    • Setting up the cycling keyspace
    • User-defined types (UDTs)
      • basic_info
      • basic_info_withTTL
      • fullname
      • race
    • User-defined functions (UDFs) and user-defined aggregates (UDAs)
      • flog
      • left
      • average_state
      • average_final
      • Cycling user-defined aggregate team_average
    • Tables
      • birthday_list
      • calendar
      • comments
      • country_flag
      • cyclist_alt_stats
      • cyclist_base
      • cyclist_career_teams
      • cyclist_category
      • cyclist_emails
      • cyclist_expenses
      • cyclist_id
      • cyclist_name
      • cyclist_points
      • cyclist_races
      • cyclist_sponsors
      • cyclist_stats
      • cyclist_teams
      • events
      • popular_count
      • race_sponsors
      • race_starts
      • race_times
      • race_winners
      • rank_by_year_and_name
      • team_average
      • upcoming_calendar
    • Materialized views (MVs)
      • cyclist_by_age-mv
    • Search indexes
      • Search index examples
      • comments search index
    • SASI indexes
      • Index on cyclist_name
    • Access control
      • Cycling internal
    • Cycling queries
      • Aggregating cycling data
    • Reference
      • CQL quick reference
      • Understanding the CQL command syntax
      • Data types
        • Creating a blob column
        • Date, time, and timestamp format
        • Solr field type reference for DSE Search
      • Operators
      • CQL native functions
      • CQL native aggregates
      • System and virtual tables
        • System tables
          • Keyspace tables
          • Querying system tables
          • Functions, aggregates, and user types
        • Virtual tables
          • Virtual keyspaces and tables
          • Virtual tables for SAI indexes and SSTables
      • cqlsh (startup options)
        • Configuring a cqlshrc file
        • Starting cqlsh on a search node
      • CQL shell (cqlsh) reference
        • CAPTURE
        • CLEAR
        • CONSISTENCY
        • COPY TO
        • COPY FROM
        • DESCRIBE AGGREGATE
        • DESCRIBE CLUSTER
        • DESCRIBE SCHEMA
        • DESCRIBE FUNCTION
        • DESCRIBE INDEX
        • DESCRIBE KEYSPACE
        • DESCRIBE MATERIALIZED VIEW
        • DESCRIBE SEARCH INDEX
        • DESCRIBE TABLE
        • DESCRIBE TYPE
        • EXECUTE AS
        • EXPAND
        • EXIT
        • LOGIN
        • PAGING
        • SERIAL CONSISTENCY
        • SHOW
        • SOURCE
        • TIMING
        • TRACING
      • CQL commands
        • ALTER KEYSPACE
        • ALTER MATERIALIZED VIEW
        • ALTER ROLE
        • ALTER SEARCH INDEX CONFIG
        • ALTER SEARCH INDEX SCHEMA
        • ALTER TABLE
        • ALTER TYPE
        • ALTER USER (Deprecated)
        • BATCH
        • COMMIT SEARCH INDEX
        • CREATE AGGREGATE
        • CREATE CUSTOM INDEX
        • CREATE FUNCTION
        • CREATE INDEX
        • CREATE KEYSPACE
        • CREATE MATERIALIZED VIEW
        • CREATE ROLE
        • CREATE SEARCH INDEX
        • CREATE TABLE
        • CREATE TYPE
        • CREATE USER (Deprecated)
        • DELETE
        • DROP AGGREGATE
        • DROP FUNCTION
        • DROP INDEX
        • DROP KEYSPACE
        • DROP MATERIALIZED VIEW
        • DROP ROLE
        • DROP SEARCH INDEX
        • DROP TABLE
        • DROP TYPE
        • DROP USER (Deprecated)
        • GRANT
        • INSERT
        • LIST PERMISSIONS
        • LIST ROLES
        • LIST USERS (Deprecated)
        • REBUILD SEARCH INDEX
        • RELOAD SEARCH INDEX
        • RESTRICT
        • RESTRICT ROWS
        • REVOKE
        • SELECT
        • TRUNCATE
        • UNRESTRICT
        • UNRESTRICT ROWS
        • UPDATE
        • USE
  • CQL for DSE
  • Connecting
  • cqlsh (startup options)
Edit this Page

cqlsh (startup options)

Execute the cqlsh Python script to start the CQL shell. The CQL shell is a Python-based command line interface for running CQL commands interactively. The CQL shell supports tab completion. The cqlsh command runs in either Python 2 or 3.

Synopsis

cqlsh [ <options> ] [ <host_name>[:<port_number>] ]
Syntax legend
Table 1. Legend
Syntax conventions Description

UPPERCASE

Literal keyword.

Lowercase

Not literal.

< >

Variable value. Replace with a user-defined value.

[]

Optional. Square brackets ([]) surround optional command arguments. Do not type the square brackets.

( )

Group. Parentheses ( ( ) ) identify a group to choose from. Do not type the parentheses.

|

Or. A vertical bar (|) separates alternative elements. Type any one of the elements. Do not type the vertical bar.

...

Repeatable. An ellipsis ( ... ) indicates that you can repeat the syntax element as often as required.

'<Literal string>'

Single quotation (') marks must surround literal strings in CQL statements. Use single quotation marks to preserve upper case.

{ <key> : <value> }

Map collection. Braces ({ }) enclose map collections or key value pairs. A colon separates the key and the value.

<datatype2

Set, list, map, or tuple. Angle brackets ( < > ) enclose data types in a set, list, map, or tuple. Separate the data types with a comma.

<cql_statement>;

End CQL statement. A semicolon (;) terminates all CQL statements.

[--]

Separate the command line options from the command arguments with two hyphens ( -- ). This syntax is useful when arguments might be mistaken for command line options.

' <<schema\> ... </schema\>> '

Search CQL only: Single quotation marks (') surround an entire XML schema declaration.

@<xml_entity>='<xml_entity_type>'

Search CQL only: Identify the entity and literal value to overwrite the XML element in the schema and solrConfig files.

--browser="<launch_browser_cmd> %s"

Browser to display the CQL command help. See Web Browser Control for a list of supported browsers. Replace the URL in the command with %s.

-C, --color

Always use color output.

--connect-timeout="<timeout>"

Connection timeout in seconds. Default: 5.

--cqlshrc="/<folder_name>"

Folder that contains the cqlshrc file. Use tilde (~) for paths relative to the user’s home directory.

--cqlversion="<version_number>"

CQL version to use. The CQL version displays after starting cqlsh.

--debug

Show additional debugging information.

--disable-history

Disables saving history to disk for current execution.

--dse-protocol-version=<DSE_PROTOCOL_VERSION>

Specify a specific DSE protocol version; otherwise the client will default and downgrade as necessary. Mutually exclusive with --protocol-version.

--encoding="<output_encoding>"

Output encoding. Default encoding: utf8.

--execute="<cql_statement>"

Execute the CQL statement, then exit. To direct the command output to a file, see saving CQL output.

-f <file_name>, --file=<file_name>

Execute commands from a CQL file, then exit.

After starting cqlsh, use the SOURCE command and the path to the file using the cqlsh command line.

-h, --help

Show help.

host_name:port

To connect the CQL session to a specified node, specify a hostname or IP address and optional port after the cqlsh command, along with any additional CQL shell options.

By default, the CQL shell launches a session with the local host on address 127.0.0.1. You can connect the CQL shell to remote hosts that have a higher or equal CQL shell version than the local CQL shell version. When no port is specified, the connection uses the default port of 9042.

-k <keyspace_name>, --keyspace=<keyspace_name>

Automatically use the specified keyspace after starting the CQL shell.

--no-color

Do not display color output.

-p <password>, --password="<password>"

Connect with the specified user’s password.

--protocol-version=<PROTOCOL_VERSION>

Specify a specific protocol version. If omitted, the client will use a default, and possibly lower version protocol, as needed.Mutually exclusive with --dse-protocol-version.

--request-timeout="<timeout>"

CQL request timeout in seconds. Default: 10.

--consistency-level <consistency_level>

Specify the initial consistency level. Default: ONE.

--serial-consistency-level <serial_consistency_level>

Specify the initial serial consistency level. Default: SERIAL.

--ssl

Use SSL.

-t, --tty

Force TTY command prompt mode.

-u <user_name>, --username="<user_name>"

Connect with the specified user account.

--version

Show the cqlsh version number.

Environment variables

You can use environment variables to overwrite default values for cqlsh commands. For example, increase the timeout values of a user running cqlsh on a particular computer.

CQLSH_SEARCH_MANAGEMENT_TIMEOUT_SECONDS

Overwrite the default 600 seconds (10 minutes) request timeout for search-specific CQL statements. To prevent timeouts, increase the timeout value. Typical use case is to ensure that no timeouts occur when large indexes are reloaded.

The timeout applies only to these search CQL index management commands:

  • ALTER SEARCH INDEX

  • COMMIT SEARCH INDEX

  • CREATE SEARCH INDEX

  • DESCRIBE SEARCH INDEX

  • DROP SEARCH INDEX

  • RELOAD SEARCH INDEX

  • REBUILD SEARCH INDEX

The timeout is used only if the cqlsh request timeout is equal to the default value of 10 seconds:

+

cqlsh --request-timeout 10

+ To increase the timeout request timeout for search-specific CQL statements to 15 minutes (900 seconds):

+

export CQLSH_SEARCH_MANAGEMENT_TIMEOUT_SECONDS=900;

Examples

Starting the CQL shell

On start up, cqlsh shows the name of the cluster, IP address, and connection port. The cqlsh prompt initially is cqlsh>. If you specify a keyspace, it is added after the prompt.

  1. Start the CQL shell:

    cqlsh

    The cluster and host information appears. For example:

    Connected to Test Cluster at 127.0.0.1:9042.
    [cqlsh 5.0.1 | Cassandra 3.3.0 | CQL spec 3.4.0 | Native protocol v4]
    Use HELP for help.
  2. Use the cycling keyspace:

    USE cycling;

    The prompt now includes the keyspace name:

    cqlsh:cycling>

Querying using CQL commands

At the cqlsh prompt, you can enter CQL commands. Use a semicolon to terminate a command. A new line does not terminate a command, and commands can be spread over several lines. For example:

SELECT *
FROM calendar
WHERE race_id = 201;

The returned results are shown in the standard output:

 race_id | race_start_date                 | race_end_date                   | race_name
---------+---------------------------------+---------------------------------+-----------------------------
     201 | 2015-02-18 08:00:00.000000+0000 | 2015-02-22 08:00:00.000000+0000 | Women's Tour of New Zealand

Understanding the CQL command syntax describes:

  • How upper- and lower-case literals are treated in commands.

  • When to use quotation marks in strings.

  • How to enter exponential notation.

Saving CQL output in a file

To save output from a CQL statement to a file, use the cqlsh -e option, followed by the CQL statement placed inside quotation marks, and redirect the output to a file.

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

cqlsh -e "SELECT * FROM mytable" > myoutput.txt

Connecting to a remote node

Specify a remote node IP address:

cqlsh 10.0.0.30
Connected to West CS Cluster at 10.0.0.30:9042.
[cqlsh 5.0.1 | Cassandra 3.3.0 | CQL spec 3.4.0 | Native protocol v4]
Use HELP for help.
Data modeling analysis Accessing data using CQL

General Inquiries: +1 (650) 389-6000 info@datastax.com

© DataStax | Privacy policy | Terms of use

DataStax, Titan, and TitanDB are registered trademarks of DataStax, Inc. and its subsidiaries in the United States and/or other countries.

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries.

Kubernetes is the registered trademark of the Linux Foundation.

landing_page landingpage