Counters: Count Up and Down

Apps count things all the time: page views, likes, downloads. The simple way is to read the number, add 1, and write it back. But when many users do this at the same moment, two of them can read the same old number, and one of the additions is lost.

Cassandra has a special column type for this, called counter. You don't read the number first. You just tell Cassandra "add 1", and it does the adding for you.

CREATE TABLE page_views (
  page text PRIMARY KEY,
  views counter
);

Syntax

UPDATE table_name
SET counter_column = counter_column + n
WHERE primary_key = value;

Use + n to count up and - n to count down.

Example: count some visits

UPDATE page_views SET views = views + 1
WHERE page = 'home';
UPDATE page_views SET views = views + 10
WHERE page = 'home';
UPDATE page_views SET views = views - 3
WHERE page = 'home';

SELECT * FROM page_views WHERE page = 'home';

Output:

 page | views
------+-------
 home |     8

(1 rows)

The row for 'home' did not exist before. A counter starts at 0, so the first UPDATE created the row, and 0 + 1 + 10 - 3 = 8.

When two users add 1 at the same time, Cassandra keeps both additions. No update is lost.

Counter rules

Counters are special, and they have strict rules. You can't set a counter to a value. You can only add to it or take away from it:

UPDATE page_views SET views = 100
WHERE page = 'home';

Output:

InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot set the value of counter column views (counters can only be incremented/decremented, not set)"

For the same reason, INSERT does not work on a counter table:

INSERT INTO page_views (page, views)
VALUES ('about', 5);

Output:

InvalidRequest: Error from server: code=2200 [Invalid query] message="INSERT statements are not allowed on counter tables, use UPDATE instead"

A table with a counter can't have normal columns too. Only the primary key and counters are allowed:

CREATE TABLE pages (
  page text PRIMARY KEY,
  title text,
  views counter
);

Output:

InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot mix counter and non counter columns in the same table"

This is why page_views is a small table of its own. The page title and other details go in a different table.

In the exercise, someone just visited the home page. Add 1 to its view counter.