Creating a counter table

A counter is a special column used to store an integer that is changed in increments.

Counters are useful for many data models. Some examples:

• To keep track of the number of web page views received on a company website
• To keep track of the number of games played online or the number of players who have joined an online game

Tracking count in a distributed database presents an interesting challenge. In DataStax Enterprise, at any given moment, the counter value may be stored in the Memtable, commit log, and/or one or more SSTables. Replication between nodes can cause consistency issues in certain edge cases.

Because counters are implemented differently from other columns, counter columns can only be created in dedicated tables. A counter column must have the counter data type. This data type cannot be assigned to a column that serves as the primary key or partition key. To implement a counter column, create a table that only includes:

• The primary key (can be one or more columns)
• The counter column

Many counter-related settings can be set in the cassandra.yaml file.

A counter column cannot be indexed or deleted. The database rejects USING TIMESTAMP or USING TTL when updating a counter column.

To create a table having one or more counter columns

• Use CREATE TABLE to define the counter and non-counter columns. Use all non-counter columns as part of the PRIMARY KEY definition.

Using a counter

USE cycling;
CREATE TABLE popular_count (
  id UUID PRIMARY KEY,
  popularity counter
);

SELECT * FROM popular_count;

 id | popularity
----+------------

(0 rows)

Loading data into a counter column is different than other tables. The data is updated rather than inserted.

UPDATE popular_count SET popularity = popularity + 1 WHERE id = 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47;

SELECT * FROM popular_count;

 id                                   | popularity
--------------------------------------+------------
 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 |          1

(1 rows)

UPDATE popular_count SET popularity = 10 WHERE id = 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47;
InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot set the value of counter column popularity (counters can only be incremented/decremented, not set)"