Configuring a cqlshrc file
A cqlshrc file contains CQL shell session settings that are used when the CQL shell starts.
cassandra.yaml
The location of the cassandra.yaml file depends on the type of installation:Package installations | /etc/dse/cassandra/cassandra.yaml |
Tarball installations | installation_location/resources/cassandra/conf/cassandra.yaml |
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 these settings in the cqlshrc file:
- Automatically logging in and selecting a keyspace
- Disabling logging
- Changing the CQL shell display
- Forcing the CQL version
- Connecting to a CQL host
- Limiting the field size
- Setting tracing timeout
- Configuring SSL
- Overriding SSL local settings
- Setting common COPY TO and COPY FROM options
- Setting COPY TO specific options
- Setting COPY FROM specific options
- Setting table specific COPY TO and COPY FROM options
Synopsis
cqlsh CQLSHRC="~/directory_name"
/Users/jdoe/cqlshprofiles/west
.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 Enterprise cqlshrc sample files. These files contain all the available settings. Some settings are commented out using a single semicolon.
- 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.
- Package installations: /etc/dse/cassandra
- Tarball installations: install_location/resources/cassandra/conf
Make changes as appropriate for your environment.
- Copy the cqlshrc.sample file to the ~/.cassandra/ directory.
- Rename the file to cqlshrc. The file is typically 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 shows a message that the file moved.
- Remove the semicolon to uncomment an option (options must be in brackets) and the
corresponding settings. This example uncomments
[copy]
andheader = 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
- 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.
- [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.
- 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.Note: 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.
- [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
COPY TO
and COPY FROM
commands.[copy]
option_name = value
- 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
andYES,NO
are the same.Default:
True,False
- CONFIGFILE
- 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.
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
- 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
-1
for 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 and COPY FROM options
[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.