Upserts: Insert or Update
In Cassandra, INSERT and UPDATE do almost the same thing. Both mean "write these values for this primary key". Neither one checks first if the row exists. This surprises many people who know SQL, so this lesson shows both cases.
UPDATE a row that doesn't exist
There is no employee with id 50. Update that row anyway:
UPDATE employees
SET name = 'Zara', department = 'HR'
WHERE id = 50;
SELECT * FROM employees WHERE id = 50;
Output:
id | age | department | gender | name
----+------+------------+--------+------
50 | null | HR | null | Zara
(1 rows)
There was no error. Cassandra created the row. The columns you did not set are null.
INSERT a row that already exists
Employee 1 is Alice. Insert a row with id 1 again:
INSERT INTO employees
(id, name, age, department, gender)
VALUES (1, 'Alicia', 29, 'Legal', 'female');
SELECT * FROM employees WHERE id = 1;
Output:
id | age | department | gender | name
----+-----+------------+--------+--------
1 | 29 | Legal | female | Alicia
(1 rows)
Again, no error. There is no "duplicate key" error in Cassandra. The new values replaced the old ones.
This is called an upsert (update + insert). If the row exists, it is updated. If not, it is created. In Cassandra, every write works this way.
Why Cassandra works this way
To check if a row exists, Cassandra would have to read it before every write. In a cluster, that read can mean asking other nodes, and that makes writes slow. So Cassandra skips the check.
Instead, every write gets a timestamp (the time it was written). When two writes change the same value, the one with the later timestamp wins. There are no locks and no waiting.
Be careful
- A typo in the key of an
UPDATEgives no error. It creates a new row you didn't want, like the row for id 50 above. - If two people update the same row at the same time, there is no conflict error. The later write wins.
- Sometimes you really need "insert only if the row does not exist", for example for usernames. Cassandra has a tool for this, called lightweight transactions. You will learn it in the Advanced Features section.
In the exercise, update an employee who doesn't exist, and see Cassandra create the row.