Cassandra Query Language (CQL): Insert, Update & Delete

Insert Data

The Cassandra insert statement writes data in Cassandra columns in row form. Cassandra insert query will store only those columns that are given by the user. You have to necessarily specify just the primary key column.

It will not take any space for not given values. No results are returned after insertion.

Syntax

INSERT INTO KeyspaceName.TableName (ColumnName1, ColumnName2, ColumnName3)
VALUES (Column1Value, Column2Value, Column3Value);

Example

Here is the snapshot of the executed Cassandra Insert into table query that will insert one record in Cassandra table ‘Student’.

INSERT INTO University.Student (RollNo, Name, dept, Semester)
VALUES (2, 'Michael', 'CS', 2);

After successful execution of the command Insert into Cassandra, one row will be inserted in the Cassandra table Student with RollNo 2, Name Michael, dept CS and Semester 2.

Here is the snapshot of the current database state.

Upsert Data

Cassandra does upsert. Upsert means that Cassandra will insert a row if a primary key does not exist already otherwise if primary key already exists, it will update that row.

This has a practical consequence worth stating plainly: an INSERT never reports a duplicate key error, so an accidental re-insert overwrites the existing row without warning. When that must be prevented, append IF NOT EXISTS to make the statement a lightweight transaction.

INSERT INTO University.Student (RollNo, Name)
VALUES (2, 'Michael') IF NOT EXISTS;

Lightweight transactions use a consensus round across replicas, so they are considerably slower than a normal write and should be reserved for cases that genuinely need the check.

Update Data

The Cassandra Update query is used to update the data in the Cassandra table. If no results are returned after updating data, it means data is successfully updated otherwise an error will be returned. Column values are changed in ‘Set’ clause while data is filtered with ‘Where’ clause.

Syntax

UPDATE KeyspaceName.TableName
SET ColumnName1 = NewValue1,
    ColumnName2 = NewValue2
WHERE ColumnName = ColumnValue;

Example

Here is the screenshot that shows the database state before updating data.

Here is the snapshot of the executed Cassandra Update command that updates the record in the Student table.

UPDATE University.Student
SET name = 'Hayden'
WHERE rollno = 1;

After successful execution of the update query in Cassandra ‘Update Student’, student name will be changed from ‘Clark’ to ‘Hayden’ that has rollno 1.

Here is the screenshot that shows the database state after updating data.

Because of upsert behaviour, an UPDATE against a primary key that does not exist creates the row rather than failing.

Cassandra Delete Data

Command ‘Delete’ removes an entire row or some columns from the table Student. When data is deleted, it is not deleted from the table immediately. Instead deleted data is marked with a tombstone and are removed after compaction.

Syntax

DELETE FROM KeyspaceName.TableName
WHERE ColumnName1 = ColumnValue;

The above Cassandra delete row syntax will delete one or more rows depend upon data filtration in where clause.

DELETE ColumnName1, ColumnName2 FROM KeyspaceName.TableName
WHERE ColumnName1 = ColumnValue;

The above syntax will delete some columns from the table.

Example

Here is the snapshot that shows the current database state before deleting data.

Here is the snapshot of the command that will remove one row from the table Student.

DELETE FROM University.Student WHERE rollno = 1;

After successful execution of the CQL Delete command, one row will be deleted from the table Student where rollno value is 1.

Here is the snapshot that shows the database state after deleting data.

Tombstones survive for gc_grace_seconds, ten days by default, so that a node offline during the delete cannot resurrect the row when it returns. Deleting large volumes therefore leaves markers that every subsequent read must scan past.

What Cassandra does not support

CQL borrows SQL syntax but not the relational execution model, so several familiar constructs behave differently or are absent.

1. CQL does not support joins between tables. Related data must be denormalised into one table at write time.
2. CQL does not support OR conditions in a WHERE clause. Use IN on a single column, or run separate queries.
3. CQL does not support UNION or INTERSECT.
4. Non-primary-key columns cannot be filtered until an index exists on them.
5. Greater than and less than comparisons apply only to clustering columns, because only those are sorted on disk.
6. Pattern matching with LIKE requires a SASI index and is not available on ordinary columns.

One long-standing claim needs correcting. Aggregate functions are supported: COUNT, MIN, MAX, SUM and AVG arrived in Cassandra 2.2, and GROUP BY arrived in 3.10. The caveat is scope rather than availability.

SELECT dept, COUNT(*) FROM University.Student
WHERE RollNo = 1 GROUP BY dept;

Restricted to a single partition as above, an aggregate is efficient. Run across the whole table it becomes a cluster-wide scan, which is why Cassandra remains unsuitable for ad hoc analytics and why heavy reporting is normally pushed to Spark or an external warehouse.

Cassandra Where Clause

In Cassandra, data retrieval is a sensitive issue. The column is filtered in Cassandra by creating an index on non-primary key columns.

Syntax

SELECT ColumnNames FROM KeyspaceName.TableName
WHERE ColumnName1 = Column1Value
  AND ColumnName2 = Column2Value;

Example

• Here is the snapshot that shows the data retrieval from Student table without data filtration.

SELECT * FROM University.Student;

Two records are retrieved from Student table.

• Here is the snapshot that shows the data retrieval from Student with data filtration. One record is retrieved.

Data is filtered by name column. All the records are retrieved that has name equal to Guru99.

SELECT * FROM University.Student WHERE name = 'Guru99';

The rules governing which columns a WHERE clause may reference follow directly from the primary key.

• The partition key must be supplied in full for any efficient query, because it identifies the node holding the data.
• Clustering columns may then be restricted, but only in the order they were declared. Skipping one is rejected.
• Range comparisons are allowed on the last clustering column referenced, not on earlier ones.
• Any other column needs a secondary index, covered in the create and drop index tutorial.

When a query is rejected, Cassandra often suggests appending ALLOW FILTERING. Treat that as a warning rather than a fix: it scans every partition on every node, and a schema change is almost always the correct response.

FAQs