Configure a cqlshrc file

When you run cqlsh with the Astra CLI, it retrieves credentials and connection details from the Secure Connect Bundle (SCB) and the .astrarc file created by astra setup. If you need to further customize the cqlsh session, you can create a cqlshrc file. Store the cqlshrc file in the ~/.cassandra directory on the machine where you installed the Astra CLI. When you run astra db cqlsh start, the Astra CLI starts cqlsh and loads the cqlshrc file.

Sample `cqlshrc` files

You can download a sample cqlshrc file from the Apache Foundation’s GitHub repository. Use a sample file as a starting point for creating your own cqlshrc file.

Sample files contain different combinations of the available settings. Some settings are commented out with a single semicolon (;). Other settings may have placeholder values that you need to change.

To use a sample file:

Copy the file to the ~/.cassandra/ directory.
Rename the file to cqlshrc.
Edit the file to customize the settings you want to use.
Start or restart cqlsh.

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.

`cqlshrc` file structure

cqlshrc files are written in INI format. Each file is divided into sections, beginning with a section name enclosed in square brackets ([]). A section contains one or more options in the format OPTION = VALUE.

In the following example:

The section is named connection.
hostname is set to 127.0.0.1
port is set to 9042
ssl is commented out with a semicolon (;), so the default value of false is used.

cqlshrc

[connection]

;; The host to connect to
hostname = 127.0.0.1

;; The port to connect to (9042 is the native protocol default)
port = 9042

;; Always connect using SSL - false by default
; ssl = true

Configuration tasks

While the file structure is section-based, most users configure cqlsh by completing specific tasks, such as setting authentication credentials, defining connection settings, or customizing COPY behavior. The following documentation describes common configuration use cases and their corresponding cqlshrc sections.

Select a keyspace

Because the Astra CLI handles credentials, keyspace is the only option in the [authentication] section.

Section: [authentication]

keyspace: Optional. Uses the specified keyspace. Equivalent to issuing a USE KEYSPACE_NAME command immediately after starting cqlsh.

Disable logging

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

Section: [history]

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

Change the CQL shell display

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

Section: [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 CQL shell entry. Default: Tab key.

encoding

Encoding for characters. Default: UTF8.

Force the CQL version

Use the specified version of CQL only.

Section: [cql]

version: Only use the specified version of CQL.

Connect to a CQL host

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

Section: [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.

Limit the field size

Section: [csv]

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

DSE and Native protocols

Section: [protocol]

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

Default: 4

dse_version: Specify default DSE protocol version.

Default: 2

Set tracing timeout

Specify the wait time for tracing.

Section: [tracing]

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

Configure SSL

Specify connection SSL settings.

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

Section: [ssl]

certfile: Path to the DSE certificate. See Connect 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.

Override SSL local settings

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

Section: [certfiles]

<remote_host>=<path_to_cert>: Specify the IP address or remote host name and path to the certificate file on your local computer.

Set 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 (default): First row does not have headers

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

Set `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

Set `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

Set 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

Section: [copy:<keyspace_name.table_name>]

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

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

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

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

pagetimeout: Page timeout for fetching results.