How to execute multiple queries in a single batch on Cassandra

Question

I have a Cassandra table as below:

create table experience.userstats (stream varchar, user varchar, computer varchar, experience varchar, amount int, primary key (experience, stream, user, computer);

I am using Apache storm bolts to create these records in Cassandra from a queue spout.

I want to generate aggregate statistics and display the counter for different experiences for a given stream as a pie chart. For example, 30% satisfied, 40% mixed and 30% unsatisfied experience.

Since I cannot update counters online, I am using a Clock spout (timer) to read this data and generate counters into a new table. But Cassandra does not support group by clauses and hence I have to read thrice to get the counter for each experience as below.

select count(*) from experience.userstats where experience='satisfied' and stream='xyz';

select count(*) from experience.userstats where experience='unsatisfied' and stream='xyz';

select count(*) from experience.userstats where experience='mixed' and stream='xyz';

The problem with this approach is that I have to execute three queries on database to get the counters for the 3 types of experience and experience could be modified in the mean-time making these counters inconsistent.

Is there a way I can execute above three select statements in a single batch? Or could you please suggest an alternative design approach?

http://docs.datastax.com/en/cql/3.1/cql/cql_reference/batch_r.html — undefined_variable, Jun 17 '16 at 11:04

score 0 · Answer 1 · answered Jun 20 '16 at 08:55

0

you can use

IN

relation operator like this,

SELECT count(*) FROM userstats WHERE experience IN('mixed','unsatisfied','satisfied) AND stream = 'xyz' ;

answered Jun 20 '16 at 08:55

Albin Mathew

604
9
19

That would give the count for all experiences. I need count for each experience. – Jun 20 '16 at 15:27

score 0 · Answer 2 · answered Jun 27 '16 at 19:25

0

Executing count(*) will give you timeouts easy. Why don't you use counter field and just increment it when insert happens? This way you will just query for one value. Distributed counters are improved in 2.1 and later and they work great.

answered Jun 27 '16 at 19:25

Matija Gobec

850
6
12

Thanks for the tip. I will avoid count(*) but I need counters by experience so might have to create new tables to maintain this counter. – Jun 29 '16 at 05:38

How to execute multiple queries in a single batch on Cassandra

2 Answers2