DSE Version: 6.0



In this unit, we will be moving on to the counters data type. A counter is a data type where you can store a 64-bit signed integer that can be incremented or decremented. These could be useful, depending on your data model.


Alright let’s move onto counters.  This is another data type that might come in handy as you are considering your data model design.  As we said before, this could potentially be another tool in your data model design tool box. So what is a counter?  Seems self-explanatory, huh? A counter is a data type where you can have a column that stores a 64 bit signed integer.  What else, what else...oh yes! They can be incremented and decremented and the values are changed using the CQL UPDATE command.   They need to have a specially dedicated table however, as the only thing allowed in a table with counters are the primary key columns, which can’t be counters and the counter column or columns.  Yup, you can have more than one counter column in your table.

Let’s take a look at a little example, shall we?  Let’s say we have own a ranch and we specialize in cows.  For whatever reason, we are particularly interested in how many moos each of our cows make.  We for that, we need some way to count up moos. So! We create a table called “moo_counts” and we have cow name, which is is also our primary key and the moo-count counter column.  So far so good. Now to add to our moo count, we will use UPDATE to update the moo_counts table presumably every time we hear a moo. In this example, we updated the moo-count to increment the value, whatever it happens to be, by 8 where the cow is named Betsy.  Got it?

Okay, some caveats you have to remember.  Cassandra is a distributed system. If you haven’t learned that by now, we’ve got bigger problems than counters here.  Seeing as it is a distributed system, replication can sometimes cause consistency errors in counter columns. Be aware that this can happen.  Also, you cannot insert or assign values. The default starting value of a counter is 0. Another thing, counters must be the ONLY non-primary key column or columns.  Counter are not idempotent. In DataStax Enterprise, you cannot use USING TIMESTAMP or USING TTLs to update counters. You must use the UPDATE command. And finally, counter columns cannot be indexed or deleted.

Okay!  I think that’s enough about counters to make you dangerous.  There is a small exercise you should do to enforce your understanding.

No write up.

Exercise 3.2: Using Counters in CQL

In this exercise, you will:

  • Create a new table that makes use of the counter type
  • Load the newly created table with data
  • Run queries against the table to test counter functionality


You have been on a roll lately designing tables, and that hasn't gone unnoticed. Since no good deed goes unpunished, your team has unanimously agreed to let you tackle one of the trickier problems they encountered learning Cassandra.

The problem is with keeping track of the number of videos in a particular category. Whenever a tag is added to a video, a transaction reads the number of videos for that video category, increments it by one, and updates it in the database. However, this technique is susceptible to race conditions. Cassandra is a distributed database that allows for concurrent, simultaneous operations, and it does not allow transactions. Your team has been stumped on how to get this functionality working correctly in Cassandra. Fortunately, you have just read up on the counter type and know that it is the perfect way to solve this problem.

The requirement itself is to be able to support a query that can retrieve the number of videos for a particular category, defined as a specific tag and year added. The query allows querying on a tag, and optionally, on a range for the added year.


1. Navigate to /home/ubuntu/labwork/counters and open the videos_count_by_tag.cql file and review its contents. Notice this is a CQL script rather than a CSV data file.

2. Launch cqlsh and switch to killrvideo keyspace.

3. Create a new table called videos_count_by_tag with a column video_count which makes use of a counter type to store the video count. Structure your table to work correctly with the CQL in videos_count_by_tag.cql.

4. Load the number of counts from the videos_count_by_tag.cql file into the videos_count_by_tag table using the SOURCE command.

5. Run a query to display each category of tag and added year, along with the count of videos for each.

6. Simulate adding another a tag for another video by incrementing the video count for a category, and then querying the new count from the videos_count_by_tag table.

7. Exit cqlsh.

No FAQs.
No resources.
Comments are closed.