Get Row Count from Table in Cassandra - Cassandra / CQL Tutorial

How to get Row Count from Table in Cassandra by using CQL

You can use Count(*) for Count(1) to get row numbers from table. 

Syntax:

SELECT Count(* |1 ) 
FROM   tablename;

 

Example:

Let's create employee table by using below CQL Statement.

CQLSH:techbrotherstutorials>CREATE TABLE employee 
             ( 
                          employeeid INT , 
                          fname TEXT, 
                          lname TEXT, 
                          address TEXT, 
                          age TINYINT, 
                          PRIMARY KEY((employeeid,fname),lname) 
             ) 
             WITH clustering 
ORDER BY     ( 
                          lname DESC);

Insert sample data by using CQL insert statement

INSERT INTO employee 
            (employeeid,fname,lname,address,age) 
            VALUES (1,'John','Doe','ABC Address',23);
INSERT INTO employee 
            (employeeid,fname,lname,address,age) 
            VALUES (2,'Robert','Ladson',' Address',40);
INSERT INTO employee 
            (employeeid,fname,lname,address,age) 
            VALUES (3,'M','Raza','New Address',38);
            INSERT INTO employee 
            (employeeid,fname,lname,address,age) 
            VALUES (3,'M','Ali','New Address',38);  

 

Get Total Row Count from Table

If we want to get total row count from employee table, we can use below statement

CQLSH:techbrotherstutorials>SELECT Count(*) 
FROM   employee;

Or 

 CQLSH:techbrotherstutorials>SELECT Count(1) 
FROM   employee;

 

Output

 count
-------
     4

(1 rows)

Warnings :
Aggregation query used without partition key

 

Get  Row Count with Where Clause:

You can use where clause in your Select query when geting the row count from table.If you are using where clause with partition keys , you will be good but if you try to use where clause with non partition key columns you will get a warning and will have to use Allow Filtering in select query to get row count. This can be expensive operation if you have large table.

Let's get row count where employeeid=3 and fname in ('M','J'), Remeber employeeid and fname are the partition key columns.

CQLSH:techbrotherstutorials>SELECT Count(1) 
FROM   employee 
WHERE  employeeid=3 
AND    fname IN('M', 
                'J')
;

output

 count
-------
     2

(1 rows)

Warnings :
Aggregation query used on multiple partition keys (IN restriction) 

 

Let's say now if we want to get the row count where age> 25, Notice that Age is not Partition Key column, we have to use Allow Filtering otherwise we will get below warning.

CQLSH:techbrotherstutorials>SELECT Count(*) 
FROM   employee 
WHERE  age>25;

 

Ouput:

Above query is going to throw a warning.

InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING"
 

Let's write the query with allow filtering to get the desired records.

SELECT Count(*) 
FROM   employee 
WHERE  age>25 allow filtering;

Output

 count
-------
 
   3

(1 rows)

Warnings :
Aggregation query used without partition key

1 comment: