Counter Data Type in Cassandra - Cassandra / CQL Tutorial

Counter Data Type in Cassandra 

Counter data type in Cassandra can store 64 big signed value. You can not set the initial value of counter. You are only allowed to increment of decrease the value. Once you will use Counter data type in table, you will not be able to insert the value but only update. 
Let's test by using below example, I am creating tbs table with Id int as primary key and Cnt as Counter Data type by using below CQL script.

CQLSH:techbrotherstutorials>CREATE TABLE tbs 
             ( 
                          id INT PRIMARY KEY, 
                          cnt COUNTER 
             );

If we try to insert data into table we will get below error. I tried to insert 1 into primary key column.

CQLSH:techbrotherstutorials>INSERT INTO tbs 
            ( 
                        id 
            ) 
            VALUES 
            ( 
                        1 
            );

 InvalidRequest: Error from server: code=2200 [Invalid query] message="INSERT statements are not allowed on counter tables, use UPDATE instead"
As we don't have any record in table so what to update, I tried to run below update query and got error again. 

CQLSH:techbrotherstutorials>UPDATE tbs 
SET    cnt=cnt+1;

SyntaxException: line 1:11 mismatched input 'where' expecting K_SET (update tbs [where] cnt...)
As there is no record, you can set id to any number and that will be value for your id. I used 1 for id and update the record. In Cassandra if the record is not there and you will update the values, it will insert the record.

CQLSH:techbrotherstutorials>UPDATE tbs 
SET    cnt=cnt+1 
WHERE  id=1;

If you like to decrease the value , you can use below statement. Remember you don't need to always increment or decrease the value by 1. If you like to increment by 5 or decrease by 5, you can simpley add or minue 5. It totally depends for which purpose you are using the Counter Data type.

CQLSH:techbrotherstutorials>UPDATE tbs 
SET    cnt=cnt-5 
WHERE  id=1;

if you would like to save another counter value in same table, you can do by updating the records with new id. 

CQLSH:techbrotherstutorials>UPDATE tbs 
SET    cnt=cnt+1 
WHERE  id=2;

Let's check the values in table, now we have two values and you can update the counter as you like for each value.