Cassandra Counter Column And Table

CASSANDRA COUNTER COLUMN AND ITS VARIOUS OPERATIONS

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:

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

So lets start exploring Counters : –

1) Table Creation : –
CREATE TABLE WebLogs (
page_id uuid,
page_name Text,
insertion_time timestamp,
page_count counter,
PRIMARY KEY (page_id, insertion_time)
);

Now if you will go and hit the create command , you will get the below error : –
ERROR : – InvalidRequest: Error from server: code=2200 [Invalid query] message=”Cannot mix counter and non counter columns in the same table”

SOLUTION: –
Counter column do not exists with non counter columns , so every other column in the table should be a primary key/clustering keys .
So lets make out page_name column as composite primary key with page_id .

cqlsh:keyspace_test> CREATE TABLE WebLogs (
…     page_id uuid,
…     page_name Text,
…     insertion_time timestamp,
…     page_count counter,
…     PRIMARY KEY ((page_id,page_name), insertion_time)
… );

2) Lets now insert some data in out table .

cqlsh:keyspace_test> insert into weblogs (page_id , page_name , insertion_time , page_count ) values(uuid(),’test.com’,dateof(now()),0) ;

you’ll be astonished on receiving the below error –

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

SOLUTION : – Cassandra itself have listed out it and ask’s you to use update statements , so lets see how it works : –

cqlsh:keyspace_test> update weblogs set page_count = page_count + 1 where page_id =uuid() and page_name =’test.com’ and insertion_time =dateof(now());

cqlsh:keyspace_test> select * from weblogs ;

page_id                              | page_name | insertion_time           | page_count
————————————–+———–+———————8372cee6-1d04-41f7-a70d-98fdd9036448 |  test.com | 2017-01-05 05:19:31+0000 |          1

wowlaaa…you have successfully created your counter table and have performed the insert operation .

3) Lets do some more CURD operation , like updating and deleting on the counter table .

Say one more visitor visits the test.com webpage , so we need to increment the counter count column .

cqlsh:keyspace_test> update weblogs set page_count = page_count + 1 where page_id =8372cee6-1d04-41f7-a70d-98fdd9036448 and page_name =’test.com’ and insertion_time =’2017-01-05 05:19:31+0000′;

ALL the data in where clause should be same .

cqlsh:keyspace_test> select * from weblogs ;

page_id                              | page_name | insertion_time           | page_count
————————————–+———–+——————-
8372cee6-1d04-41f7-a70d-98fdd9036448 |  test.com | 2017-01-05 05:19:31+0000 |          2

Removing the data : –
The removing clause are same as usual Cassandra delete query : –

cqlsh:keyspace_test> delete from weblogs where  page_id =8372cee6-1d04-41f7-a70d-98fdd9036448 and page_name =’test.com’ and insertion_time =’2017-01-05 05:19:31+0000′;

cqlsh:keyspace_test> select * from weblogs ;

page_id | page_name | insertion_time | page_count
———+———–+—————-+————

Some Important Notes : –
1)A counter column cannot be indexed or deleted..

2)To load data into a counter column, or to increase or decrease the value of the
counter, use the UPDATE command. Cassandra rejects USING TIMESTAMP or USING TTL when updating a counter column.

3) To create a table having one or more counter columns, use this:

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