Configuring a cqlshrc file

A cqlshrc file contains CQL shell session settings that are used when the CQL shell starts. If you place a cqlshrc file in the ~/.cassandra directory on a local computer, that file is used by default. You can also specify the directory that the cqlshrc file is in using the --cqlshrc option. Place only one cqlshrc file in a directory.

Do not confuse the cqlshrc file with the --cqlshrc option. The first is a file containing CQL session settings, the second is an option that specifies the directory where the cqlshrc file is located.

Command line options always override the settings in a cqlshrc file.

You can configure these settings in the cqlshrc file:

Synopsis

cqlsh CQLSHRC="~/<directory_name>"
Syntax legend
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.

Tilde (~) expands to the user’s home directory. You can also specify the absolute path, for example /Users/jdoe/cqlshprofiles/west.

Using a sample cqlshrc file

A sample file is installed with DataStax Enterprise cqlshrc sample files. These files contain all the available settings. Some settings are commented out using a single semicolon.

DataStax Enterprise has these sample cqlshrc files:

  • cqlshrc.sample: Contains all available settings.

  • cqlshrc.sample.kerberos: Contains settings related to Kerberos authentication for the CQL shell connection.

  • cqlshrc.sample.ssl: Contains settings related to using SSL for the CQL shell for the connection.

  • cqlshrc.sample.kerberos_ssl: Contains settings related to using SSL and Kerberos authentication for the CQL shell connection.

The location of these files depend on the type of installation:

  • Package installations:/etc/dse/cassandra

  • Tarball installations:<install_location>/resources/cassandra/conf

Make changes as appropriate for your environment.

To use the sample file:

  1. Copy the cqlshrc.sample file to the ~/.cassandra/ directory.

  2. Rename the file to cqlshrc. The file is typically located in ~/.cassandra/cqlshrc.

    If cqlsh finds the .cqlshrc file located in the home directory, cqlsh moves the file to ~/.cassandra/cqlshrc upon its next invocation and shows a message that the file moved.

  3. Remove the semicolon to uncomment an option (options must be in brackets) and the corresponding settings. This example uncomments [copy] and header = false to import all CSV without a header row:

    ;; Options that are common to both COPY TO and COPY FROM
    [copy]
    
    ;; The string placeholder for null values
    ; nullval = null
    
    ;; 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 file contains column names.
    header = false
  4. Restart the CQL shell.

Automatically logging in and selecting a keyspace

Set up credentials to automatically log in when the CQL shell starts and choose an optional keyspace.

Only set a user name and password for hosts that use internal authentication. See Encrypting with SSL.

[authentication]
username

Account name.

password

Password.

keyspace

Optional. Uses the specified keyspace. Equivalent to issuing a USE <keyspace> command immediately after starting cqlsh. Does not require internal authentication.

Disabling logging

Disables saving history to disk for current execution when CQL shell starts.

[history]
disabled

If set to TRUE, cqlsh logging is disabled. Default: FALSE

Changing the CQL shell display

The following options apply to the cqlsh console display settings and COPY TO date parsing settings.

[ui]
color

Shows query results with color.

on - use color

off - no color

datetimeformat

Configure the format of timestamps using Python strftime syntax.

timezone

Display timestamps in Etc/UTC format.

float_precision, double_precision

Sets the number of digits displayed after the decimal point for single and double precision numbers.

Increasing these options to large numbers might produce unusual results when the values are displayed.

completekey

Set the key for automatic completion of a cqlsh shell entry. Default: Tab key.

encoding

Encoding for characters. Default: UTF8.

Forcing the CQL version

Use the specified version of CQL only.

[cql]
version

Only use the specified version of CQL.

Connecting to a CQL host

Specify the host and connection details for the CQL shell session.

[connection]
hostname

The host for the cqlsh connection.

port

The connection port. Default: 9042 (native protocol).

ssl

Always connect using SSL. Default: false.

timeout

Timeout in seconds when opening new connections.

request_timeout

Request timeout in seconds for executing queries. Sets the number of seconds of inactivity.

factory

For SSL, set to cqlshlib.ssl.ssl_transport_factory.

Limiting the field size

[csv]
field_size_limit

Set to a particular field size, for example field_size_limit = 1000000000.

DSE and Native protocols

[protocol]
version

Specify default native protocol version, which is mutually exclusive from dse_version.

Default: 4

dse_version

Specify default DataStax Enterprise protocol version.

Default: 2

Setting tracing timeout

Specify the wait time for tracing.

[tracing]
max_trace_wait

Maximum number of seconds to wait for a trace to complete.

Configuring SSL

Specify connection SSL settings.

For more information, see Connecting to SSL-enabled nodes using cqlsh.

[ssl]
certfile

Path to the DataStax Enterprise certificate. See Connecting to SSL-enabled nodes using cqlsh.

validate

Optional validation. Default: true.

userkey

User key must be provided when require_client_auth=true in cassandra.yaml.

usercert

User certificate must be provided when require_client_auth=true in cassandra.yaml.

Overriding SSL local settings

Overrides the default certfiles in the [ssl] section. Create an entry for each remote host.

[certfiles]
<remote_host>=<path_to_cert>

Specify the IP address or remote host name and path to the certificate file on your local computer.

Setting common COPY TO and COPY FROM options

Settings common to both the COPY TO and COPY FROM commands.

[copy]
BOOLSTYLE       = True,False
CONFIGFILE      = /home/
DATETIMEFORMAT  = %m/%d/%Y
DECIMALSEP      = ,
DELIMITER       = |
ESCAPE          = `
HEADER          = true
MAXATTEMPTS     = 10
NULL            = NULL
NUMPROCESSES    = -1
QUOTE           = "
REPORTFREQUENCY = 1
RATEFILE        = /home/copyrate.log
SKIPCOLS        = firstname
SKIPROWS        = 6
THOUSANDSSEP    = ,
<table_name>

Table for the copy operation.

<column_list>

List of columns in the table. All fields are included when no column names are specified. To omit columns, specify a column list with only the columns to include.

<file_name>, <file2_name>

CSV file name.

BOOLSTYLE

Boolean indicators for true and false. The values are case-insensitive. For example: yes,no and YES,NO are the same.

Default: True,False

CONFIGFILE

Directory that contains the cqlshrc configuration file.

Command line options always override the cqlshrc file.

DATETIMEFORMAT

Time format for reading or writing CSV time data. The timestamp uses the strftime format. If not set, the default value is set to the datetimeformat value in the cqlshrc file.

Default: %Y-%m-%d %H:%M:%S%z

DECIMALSEP

Decimal value separator.

Default: . (period)

DELIMITER

Field separator.

Default: , (comma)

ESCAPE

Single character that escapes literal uses of the QUOTE character.

Default: \ (backslash)

HEADER
  • true - first row contains headers (column names).

  • false - first row does not have headers. Default: false

MAXATTEMPTS

Maximum number of attempts for errors.

Default: 5

NULL

Value used when no value is in the field.

Default: <empty>

NUMPROCESSES

Number of worker processes. Maximum value is 16.

Default: -1

QUOTE

Encloses field values.

Default: " (double quotation mark)

REPORTFREQUENCY

Frequency with which status is displayed in seconds.

Default: 0.25

RATEFILE

Print output statistics to this file.

SKIPCOLS

Name of column to skip.

SKIPROWS

Number of rows starting from the first row of data to skip.

THOUSANDSSEP

Separator for thousands digit groups.

Default: None

Setting COPY TO specific options

BEGINTOKEN

Minimum token string for exporting data.

DOUBLEPRECISION

Number of digits to display after the decimal point for CQL double precision values.

Default: 12

ENCODING

Output string type.

Default: UTF8

ENDTOKEN

Maximum token string for exporting data.

ERRFILE

File to store all rows that are not imported. If no value is set, the information is stored in import_<ks>_<table>.err where <ks> is the keyspace and <table> is the table name.

FLOATPRECISION

Number of digits to display after the decimal point for CQL float (single precision) values.

Default: 5

MAXOUTPUTSIZE

Maximum size of the output file, measured in number of lines. When set, the output file is split into segment when the value is exceeded. Use -1 for no maximum.

Default: -1

MAXREQUESTS

Maximum number of requests each worker can process in parallel.

Default: 6

PAGESIZE

Page size for fetching results.

Default: 1000

PAGETIMEOUT

Page timeout for fetching results.

Default: 10

TTL

Time to live in seconds. By default, data will not expire.

Default: 3600

Setting COPY FROM specific options

BEGINTOKEN

Minimum token string for exporting data.

DOUBLEPRECISION

Number of digits to display after the decimal point for CQL double precision values.

Default: 12

ENCODING

Output string type.

Default: UTF8

ENDTOKEN

Maximum token string for exporting data.

ERRFILE

File to store all rows that are not imported. If no value is set, the information is stored in import_<ks>_<table>.err where <ks> is the keyspace and <table> is the table name.

FLOATPRECISION

Number of digits to display after the decimal point for CQL float (single precision) values.

Default: 5

MAXOUTPUTSIZE

Maximum size of the output file, measured in number of lines. When set, the output file is split into segment when the value is exceeded. Use -1 for no maximum.

Default: -1

MAXREQUESTS

Maximum number of requests each worker can process in parallel.

Default: 6

PAGESIZE

Page size for fetching results.

Default: 1000

PAGETIMEOUT

Page timeout for fetching results.

Default: 10

TTL

Time to live in seconds. By default, data will not expire.

Default: 3600

Setting table specific COPY TO and COPY FROM options

Use these options to configure table-specific settings. Create a new entry for each table. For example, to set the chunk size for cyclist names and rank:

[copy:cycling.cyclist_names]
chunksize = 1000
[copy:cycling.rank_by_year_and_name]
chunksize = 10000
[copy:<keyspace_name.table_name>]
chunksize

Chunk size passed to worker processes. Default value: 1000:

[copy-from:<keyspace_name.table_name>]
ingestrate

Approximate ingest rate in rows per second. Must be greater than the chunk size.

[copy-to:<keyspace_name.table_name>]
pagetimeout

Page timeout for fetching results.

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

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.

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