Getting started using the Cassandra CLI¶

Creating a keyspace¶

You can use the Cassandra CLI commands described in this section to create a keyspace. This example creates a keyspace called demo, with a replication factor of 1 and using the SimpleStrategy replica placement strategy.

The single quotes around the string value of placement_strategy:

[default@unknown] CREATE KEYSPACE demo
with placement_strategy = 'org.apache.cassandra.locator.SimpleStrategy'
and strategy_options = {replication_factor:1};

You can verify the creation of a keyspace with the SHOW KEYSPACES command. The new keyspace is listed along with the system keyspace and any other existing keyspaces.

Accessing CQL 3 tables¶

In Cassandra 1.2 and later, you can use the CLI GET command to query tables created with or without the COMPACT STORAGE directive in CQL 3. The CLI SET command can also be used with CQL 3 tables. For more examples of querying CQL 3 tables, see Querying a legacy table.

About data types (Comparators and Validators)¶

In a relational database, you must specify a data type for each column when you define a table. The data type constrains the values that can be inserted into that column. For example, if you have a column defined as an integer datatype, you would not be allowed to insert character data into that column. Column names in a relational database are typically fixed labels (strings) that are assigned when you define the table schema.

In Cassandra CLI and Thrift, the data type for a column (or row key) value is called a validator. The data type for a column name is called a comparator. Cassandra validates that data type of the keys of rows. Columns are sorted, and stored in sorted order on disk, so you have to specify a comparator for columns. You can define the validator and comparator when you create your table schema (which is recommended), but Cassandra does not require it. Internally, Cassandra stores column names and values as hex byte arrays (BytesType). This is the default client encoding used if data types are not defined in the table schema (or if not specified by the client request).

Cassandra comes with the following built-in data types, which can be used as both validators (row key and column value data types) or comparators (column name data types). One exception is CounterColumnType, which is only allowed as a column value (not allowed for row keys or column names).

Creating a table¶

First, connect to the keyspace where you want to define the table with the USE command.

[default@unknown] USE demo;

In this example, we create a users table in the demo keyspace. This table defines a few columns: full_name, email, state, gender, and birth_year. This is considered a static table because the column names are specified and most rows are expected to have more-or-less the same columns.

Notice the settings of comparator, key_validation_class and validation_class. These values set the default encoding used for column names, row key values and column values. In the case of column names, the comparator also determines the sort order. To create a table using the CLI, you use the column family keyword.

[default@unknown] USE demo;

[default@demo] CREATE COLUMN FAMILY users
WITH comparator = UTF8Type
AND key_validation_class=UTF8Type
AND column_metadata = [
{column_name: full_name, validation_class: UTF8Type}
{column_name: email, validation_class: UTF8Type}
{column_name: state, validation_class: UTF8Type}
{column_name: gender, validation_class: UTF8Type}
{column_name: birth_year, validation_class: LongType}
];

Next, create a dynamic table called blog_entry. Notice that here we do not specify column definitions as the column names are expected to be supplied later by the client application.

[default@demo] CREATE COLUMN FAMILY blog_entry
WITH comparator = TimeUUIDType
AND key_validation_class=UTF8Type
AND default_validation_class = UTF8Type;

Creating a counter table¶

A counter table contains counter columns. A counter column is a specific kind of column whose user-visible value is a 64-bit signed integer that can be incremented (or decremented) by a client application. The counter column tracks the most recent value (or count) of all updates made to it. A counter column cannot be mixed in with regular columns of a table, you must create a table specifically to hold counters.

To create a table that holds counter columns, set the default_validation_class of the table to CounterColumnType. For example:

[default@demo] CREATE COLUMN FAMILY page_view_counts
WITH default_validation_class=CounterColumnType
AND key_validation_class=UTF8Type AND comparator=UTF8Type;

To insert a row and counter column into the table (with the initial counter value set to 0):

[default@demo] INCR page_view_counts['www.datastax.com'][home] BY 0;

To increment the counter:

[default@demo] INCR page_view_counts['www.datastax.com'][home] BY 1;

Inserting rows and columns¶

The following examples illustrate using the SET command to insert columns for a particular row key into the users table. In this example, the row key is bobbyjo and we are setting each of the columns for this user. Notice that you can only set one column at a time in a SET command.

[default@demo] SET users['bobbyjo']['full_name']='Robert Jones';

[default@demo] SET users['bobbyjo']['email']='bobjones@gmail.com';

[default@demo] SET users['bobbyjo']['state']='TX';

[default@demo] SET users['bobbyjo']['gender']='M';

[default@demo] SET users['bobbyjo']['birth_year']='1975';

In this example, the row key is yomama and we are just setting some of the columns for this user.

[default@demo] SET users['yomama']['full_name']='Cathy Smith';

[default@demo] SET users['yomama']['state']='CA';

[default@demo] SET users['yomama']['gender']='F';

[default@demo] SET users['yomama']['birth_year']='1969';

In this example, we are creating an entry in the blog_entry table for row key yomama:

[default@demo] SET blog_entry['yomama'][timeuuid()] = 'I love my new shoes!';

Reading rows and columns¶

Use the GET command within Cassandra CLI to retrieve a particular row from a table. Use the LIST command to return a batch of rows and their associated columns (default limit of rows returned is 100).

For example, to return the first 100 rows (and all associated columns) from the users table:

[default@demo] LIST users;

Cassandra stores all data internally as hex byte arrays by default. If you do not specify a default row key validation class, column comparator and column validation class when you define the table, Cassandra CLI will expect input data for row keys, column names, and column values to be in hex format (and data will be returned in hex format).

To pass and return data in human-readable format, you can pass a value through an encoding function. Available encodings are:

• ascii
• bytes
• integer (a generic variable-length integer type)
• lexicalUUID
• long
• utf8

For example to return a particular row key and column in UTF8 format:

[default@demo] GET users[utf8('bobbyjo')][utf8('full_name')];

You can also use the ASSUME command to specify the encoding in which table data should be returned for the entire client session. For example, to return row keys, column names, and column values in ASCII-encoded format:

[default@demo] ASSUME users KEYS AS ascii;
[default@demo] ASSUME users COMPARATOR AS ascii;
[default@demo] ASSUME users VALIDATOR AS ascii;

Setting an expiring column¶

When you set a column in Cassandra, you can optionally set an expiration time, or time-to-live (TTL) attribute for it.

For example, suppose we are tracking coupon codes for our users that expire after 10 days. We can define a coupon_code column and set an expiration date on that column. For example:

[default@demo] SET users['bobbyjo']
[utf8('coupon_code')] = utf8('SAVE20') WITH ttl=864000;

After ten days, or 864,000 seconds have elapsed since the setting of this column, its value will be marked as deleted and no longer be returned by read operations. Note, however, that the value is not actually deleted from disk until normal Cassandra compaction processes are completed.

Indexing a column¶

The CLI can be used to create secondary indexes (indexes on column values). You can add a secondary index when you create a table or add it later using the UPDATE COLUMN FAMILY command.

For example, to add a secondary index to the birth_year column of the users column family:

[default@demo] UPDATE COLUMN FAMILY users WITH comparator = UTF8Type
  AND column_metadata =
    [{column_name: birth_year,
      validation_class: LongType,
      index_type: KEYS
     }
    ];

Because of the secondary index created for the column birth_year, its values can be queried directly for users born in a given year as follows:

[default@demo] GET users WHERE birth_year = 1969;

Deleting rows and columns¶

The Cassandra CLI provides the DEL command to delete a row or column (or subcolumn).

For example, to delete the coupon_code column for the yomama row key in the users table:

[default@demo] DEL users ['yomama']['coupon_code'];

[default@demo] GET users ['yomama'];

Or to delete an entire row:

[default@demo] DEL users ['yomama'];

Dropping tables and keyspaces¶

With Cassandra CLI commands you can drop tables and keyspaces in much the same way that tables and databases are dropped in a relational database. This example shows the commands to drop our example users table and then drop the demo keyspace altogether:

[default@demo] DROP COLUMN FAMILY users;

[default@demo] DROP KEYSPACE demo;