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

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.

See also:

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com