Configuring a cqlshrc file

A cqlshrc file contains CQL shell session settings that are used when the CQL shell starts.

cassandra.yaml

  • The cassandra.yaml file is located in the installation_location/conf directory.

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.

You can configure the following options:

Synopsis

cqlsh --cqlshrc="~/directory_name"
Note: Tilde (~) expands to the user's home directory; you can also specify the absolute path, for example /Users/jdoe/cqlshprofiles/west.
Table 1. Legend
Syntax conventions Description
UPPERCASE Literal keyword.
Lowercase Not literal.
Italics 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.
<datatype1,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.

Using a sample cqlshrc file

A sample file is installed with DataStax Distribution of Apache Cassandra™ (DDAC) cqlshrc sample files. These files contain all the available settings. Some settings are commented out using a single semi-colon.

DDAC provides sample cqlshrc files:
  • cqlshrc.sample: Contains all available settings.

This file is located in the installation_location/conf directory.

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 should be located in ~/.cassandra/cqlshrc.
    Note: If cqlsh finds the .cqlshrc file located in the home directory, cqlsh moves the file to ~/.cassandra/cqlshrc upon its next invocation and outputs a message that the file moved.
  3. Remove the semi-colon to uncomment an option (options must be in brackets) and its corresponding settings. The example below 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 CQL shell starts and optionally choose a keyspace.

Note: Only set a user name and password for hosts that use internal authentication, see Encrypting with SSL.
[authentication]
username
Log in account name.
password
Log in password.
keyspace
Optional. Opens the specified keyspace. Equivalent to issuing a USE keyspace command immediately after starting cqlsh. (Does not require internal authentication.)

Changing the CQL shell display

The cqlsh console display and COPY TO date parsing settings.

[ui]
color
Shows query results with color.
on - color
off - no color
datetimeformat
Configure the format of timestamps using Python strftime syntax.
timezone
Display timestamps in Etc/UTC.
float_precision, double_precision
Sets the number of digits displayed after the decimal point for single and double precision numbers.
Note: Increasing this to large numbers can result in unusual values.
completekey
Set the key for automatic completion of a cqlsh shell entry. Default is the tab key.
encoding
The encoding used for characters. The default is 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
Configures timeout in seconds when opening new connections.
request_timeout
Configures the request timeout in seconds for executing queries. Set the number of seconds of inactivity.

Limiting the field size

[csv]
field_size_limit
Set to a particular field size, such as field_size_limit = 1000000000.

Setting tracing timeout

Specify the wait time for tracing.

[tracing]
max_trace_wait
The maximum number of seconds to wait for a trace to complete.

Configuring SSL

Specify connection SSL settings.

Note: For more information, see Encrypting with SSL.
[ssl]
certfile
The path to the Cassandra certificate.
validate
Optional. Default: true.
userkey
Must be provided when require_client_auth=true in cassandra.yaml.
usercert
Must be provided when require_client_auth=true in cassandra.yaml.

Overriding SSL local settings

Overrides 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 system.

Setting common COPY TO and COPY FROM options

Settings common to both COPY TO and COPY FROM.
[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    = ,
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
Specify a directory that contains the cqlshrc configuration file.
Note: 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.

Setting COPY TO specific options

Under the [copy-to] section set any of the export 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

Under the [copy-from] section set any of the import options.

CHUNKSIZE
Chunk size passed to worker processes.

Default: 1000

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

Default: 100000

MAXBATCHSIZE
Maximum size of an import batch.

Default: 20

MAXINSERTERRORS
Maximum global number of insert errors. Use -1for no maximum.

Default: -1

MAXPARSEERRORS
Maximum global number of parsing errors. Use -1 for no maximum.

Default: -1

MAXROWS
Maximum number of rows. Use -1 for no maximum.

Default: -1

MINBATCHSIZE
Minimum size of an import batch.

Default: 2

Setting table specific COPY TO/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.
[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.