DS201 Study Notes: Basics of Cassandra Query Language (CQL)

DS201 course study notes covering CQL basics: starting CQLSH, creating keyspaces and tables, inserting, querying, and truncating data.

2 min read

Study notes from DS201: Foundations of Apache Cassandra™ and DataStax Enterprise.

Starting CQLSH (Cassandra Query Language Shell)

cqlsh is a command-line shell for connecting to a Cassandra cluster and performing database operations using CQL (Cassandra Query Language).

cqlsh

Running cqlsh connects to the Cassandra cluster and displays the cqlsh> prompt.

Creating a Keyspace

A Keyspace is the top-level container for data in Cassandra. It provides logical grouping of data and defines the structure, replication strategy, and other settings for tables and data.

Here is an example of creating a keyspace named killrvideo with SimpleStrategy and replication_factor of 1:

CREATE KEYSPACE killrvideo
    WITH replication = {
        'class': 'SimpleStrategy',
        'replication_factor': 1
    };
  • replication: Specifies how redundant copies of data within the keyspace are managed.
    • 'class': Specifies the replication strategy. SimpleStrategy is for single data center deployments, while NetworkTopologyStrategy is for multi-data center deployments.
    • 'replication_factor': Specifies the number of data replicas.

Selecting a Keyspace

Select the keyspace to work with:

USE killrvideo;

After selection, the prompt changes to cqlsh:killrvideo>.

Creating a Table

Create a table within the keyspace. Tables are similar to relational database tables but are designed according to Cassandra’s data model.

CREATE TABLE videos (
    video_id TIMEUUID,
    added_date TIMESTAMP,
    title TEXT,
    PRIMARY KEY (video_id)
);
  • TIMEUUID: A time-based UUID (Universally Unique Identifier) type.
  • TIMESTAMP: A date and time type.
  • TEXT: A string type.
  • PRIMARY KEY (video_id): Sets video_id as the primary key. The primary key in Cassandra determines data partitioning and clustering.

Inserting Data

Use the INSERT INTO statement to insert data into a table.

INSERT INTO videos (video_id, added_date, title)
VALUES (1645ea59-14bd-11e5-a993-8138354b7e31, '2014-01-29', 'Cassandra History');

Querying Data

Use the SELECT statement to retrieve data from a table.

SELECT * FROM videos;

Example output:

  video_id                             | added_date                      | title
--------------------------------------+---------------------------------+-------------------
 1645ea59-14bd-11e5-a993-8138354b7e31 | 2014-01-28 15:00:00.000000+0000 | Cassandra History

Deleting All Data

The TRUNCATE statement deletes all data from a table. The table structure is preserved.

TRUNCATE videos;

After TRUNCATE, running SELECT * FROM videos; confirms that the data is empty.

 video_id | added_date | title
----------+------------+------

関連記事