CQLSH Expand Mode

CQL Shell or CQLSH is a powerful command-line interface for interacting with your Cassandra cluster using the Cassandra Query Language. CQLSH is readily available in the recent versions of Cassandra.

There is a good chance that you will often interact with your keyspaces, users, roles, and other database resources using the CQL Shell.

Therefore, having a few commands and tricks can be an added advantage to help speed up your workflow in the shell.

The goal of this tutorial is to walk you through the CQLSH expand mode. Learning to use this mode can help you retrieve data from a database in a well-formatted format.

Let us dive in.

Cassandra Expand Mode

The expand mode in the Cassandra Shell allows you to display the result of a query in a formatted manner.

Each row, column, and corresponding value are listed in a vertical manner instead of a horizontal one. This is an incredible feature, especially when reading a wide table.

Cassandra Enable / Disable Expand Mode

To use the expand mode, you need to enable it from your shell session. Start by logging in to your cluster:

$ cqlsh -u cassandra -p cassandra

Next, check the current expand mode status as shown below:

cassandra@cqlsh> expand
Expanded output is currently disabled.

This should return the current status of the expand mode. In this case, the expand mode is disabled.

To enable or disable the expand mode, use the following commands:

EXPAND ON – Enable the expand mode
EXPAND OFF – Disable the expand mode

The following example shows how to use the expand mode. Consider the keyspace and table data shown below:

create keyspace zero_day
with replication = {
'class': 'SimpleStrategy',
'replication_factor': 1
};
use zero_day;
create table records(
id int,
cve_number text,
report_date date,
affected_vendor text,
severity float,
primary key(id)
);
begin batch
insert into records(id, cve_number, report_date, affected_vendor, severity)
values (1, 'CVE-2021-33852', '2022-12-02', 'WordPress', 6.0);

insert into records(id, cve_number, report_date, affected_vendor, severity)
values (2, 'CVE-2020-14723', '2020-01-11', 'Oracle', 8.2);

insert into records(id, cve_number, report_date, affected_vendor, severity)
values (3, 'CVE-2019-19306', '2019-10-14', 'Zoho', 5.4);

insert into records(id, cve_number, report_date, affected_vendor, severity)
values (4, 'CVE-2021-33850', '2021-10-18', 'Microsoft', 5.0);

insert into records(id, cve_number, report_date, affected_vendor, severity)
values (5, 'CVE-2020-24600', '2020-07-01', 'Shilpi', 8.6);
apply batch;

Fetch Table Without Expand Mode

Start by disabling the expand mode:

$ EXPAND OFF

Fetch table records:

select * from records;

Resulting output:

In our case, we do not have a wide table for the expand mode to be very useful. However, we can still use it as shown below:

Fetch Table With Expand Mode

Enable the expand mode and select the data from the table:

cassandra@cqlsh:zero_day> expand on;
Now Expanded output is enabled

Select values:

cassandra@cqlsh:zero_day> select * from records;

With the expanded mode, the command should return the output as:

As we can see, the layout of the data is formatted in a vertical manner, thus making it easy to read.

Conclusion

This post covers the CQL Shell expand mode and how to enable/disable it. In addition, it illustrates the differences between the output with expanded mode on/off.