Cassandra Query Language (CQL) defines, queries, and changes data in Cassandra-compatible databases such as ScyllaDB. Its syntax intentionally resembles simpler SQL, so familiar statements such as CREATE, INSERT, SELECT, UPDATE, and DELETE remain recognizable. The important difference is that CQL does not provide JOINs: one-to-many data is modeled for the needed queries, often by denormalizing related data together. section 2.2.2 section 2.5
Use cqlsh, the CQL command-line shell, to send statements to a cluster. Start by creating a keyspace, which holds tables and replication settings; then create tables, define typed columns, and choose a primary key. A row’s primary key establishes uniqueness, while its first portion is the partition key that groups and distributes rows across the cluster. section 2.2.2 section 2.5
Listing 2.1 First table, which stores food reviews
CREATE TABLE initial.food_reviews ( #1
restaurant text, #2
ordered_at timestamp,
food text,
review text,
PRIMARY KEY (restaurant, ordered_at, food) #3
);
This CREATE TABLE statement stores restaurant reviews. Its primary key makes restaurant the partition key and uses ordered_at and food to further identify rows within that restaurant’s partition. section 2.2.2
CQL operations follow a query-aware data model:
- CREATE KEYSPACE establishes a container for tables and sets replication options; CREATE TABLE defines columns, types, and a primary key. section 2.2.2
- INSERT names a table and columns, then supplies values. Different primary-key values create separate rows, even when they share a restaurant. section 2.3.1
- UPDATE acts as an upsert: it changes an existing value or inserts data when no value exists. section 2.5
- DELETE uses a table and WHERE clause. Deleting by the partition key avoids searching all partitions. section 2.3.4
Saving and removing restaurant reviews
Insert a grilled-cheese review for Ernie’s Eats with a restaurant, timestamp, food, and review value. A later fried-chicken visit is a separate row because ordered_at and food differ. To remove every review for Really Good Ramen, delete with restaurant in the WHERE clause; restaurant is the partition key, so the database can target that partition rather than search every partition. section 2.3.1 section 2.3.4
CQL queries have distribution consequences. A query is sent to a coordinator node, which contacts nodes that may hold the requested data. Queries that read one partition are preferred for performance; without the partition key, the coordinator may need to contact every node in the cluster. section 2.5
