Create a counter

A counter column value is a limited special column for storing a number that is updated by increments or decrements. You cannot set the value of a counter, you can only either increment or decrement it.

A table that contains a counter column must only have a primary key and one or more counter columns. A counter column cannot be part of the primary key.

There are some limitations on a counter column. You cannot create an index on a counter column. If you drop a counter column from a table, you cannot re-add it to the same table. Additionally, you cannot set a counter column’s value to expire using the Time-To-Live (TTL) or USING TIMESTAMP properties.

To load data into a counter column, or to increase or decrease the value of the counter, use the UPDATE command.

Prerequisite

Keyspace must exist

Create a table for the counter column:

CREATE TABLE IF NOT EXISTS cycling.popular_count (
  id UUID PRIMARY KEY,
  popularity counter
);

Results

CREATE TABLE cycling.popular_count (
    id uuid PRIMARY KEY,
    popularity counter
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

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

The first example uses a BATCH statement to increment the value of the popularity column by 1, then 125, and then decrements by 64.

BEGIN COUNTER BATCH

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

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

  UPDATE cycling.popular_count
  SET popularity = popularity - 64
  WHERE id = 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47;

APPLY BATCH;

The second example increments the value of the popularity column by 2. Note the use of the WHERE clause to specify the row to update.

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

After all these updates, the popularity counter column has a value of 64.

SELECT * FROM cycling.popular_count;

Results

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

(1 rows)

The operations for a counter column are straightforward; additional increments or decrements change the value of the counter column.

Create a counter

Prerequisite

Was this helpful?

Give Feedback