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 additional_write_policy = '99PERCENTILE'
AND 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 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 nodesync = {'enabled': 'true', 'incremental': 'true'}
AND read_repair = 'BLOCKING'
AND speculative_retry = '99PERCENTILE';
CREATE TABLE cycling.popular_count (
id uuid PRIMARY KEY,
popularity counter
) WITH additional_write_policy = '99PERCENTILE'
AND 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 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 nodesync = {'enabled': 'true', 'incremental': 'true'}
AND read_repair = 'BLOCKING'
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)
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.
See also:
