CODEWITHSUNDEEP
cassandrahector
qualebs
qualebs

Reputation: 1381

How to count columns from multiple rows

How can I count number of columns in different rows of a column family?

I am a Cassandra newbie. I do not know a starting point. The only option I have is to make the application fetch data for each row at a time. It does not sound right to me. I am using Hector to connect to Cassandra.

Upvotes: 0

Views: 1458

Answers (2)

Hardik Bhalani
Hardik Bhalani

Reputation: 863

this is how you will get total column count in particular rowkey

sliceQuery.setColumnFamily("**your column family**");
sliceQuery.setKey("**your row key**");
sliceQuery.setRange(null, null, false, Integer.MAX_VALUE);

QueryResult<ColumnSlice<String, String>> result = sliceQuery.execute();
ColumnSlice<String, String> cs = result.get();

long noOfColumnInRowKey=result.get().getColumns().size();

Upvotes: 1

Nishant
Nishant

Reputation: 55856

Assume that you have wide row (Lets create it using CLI)

create column family cf3 
 with column_type = 'Standard' and 
 comparator = 'TimeUUIDType' and 
 key_validation_class = 'UTF8Type' and 
 default_validation_class = 'UTF8Type';

This is what I see in CQL3:

cqlsh:ks> desc table cf3;

CREATE TABLE cf3 (
  key text,
  column1 timeuuid,
  value text,
  PRIMARY KEY (key, column1)
) WITH COMPACT STORAGE AND
  bloom_filter_fp_chance=0.010000 AND
  caching='KEYS_ONLY' AND
  comment='' AND
  dclocal_read_repair_chance=0.000000 AND
  gc_grace_seconds=864000 AND
  read_repair_chance=0.100000 AND
  replicate_on_write='true' AND
  populate_io_cache_on_flush='false' AND
  compaction={'class': 'SizeTieredCompactionStrategy'} AND
  compression={'sstable_compression': 'SnappyCompressor'};

I inserted some values from CQL3, which makes you feel like good ol' MySQL

cqlsh:ks> insert into cf3 (key, column1, value) values ('user1', now(), 'time5');
cqlsh:ks> select * from cf3;

 key   | column1                              | value
-------+--------------------------------------+-------
 user1 | f0c687b0-d114-11e2-8002-2f4261da0d90 | time1
 user1 | fb9fa130-d114-11e2-8002-2f4261da0d90 | time2
 user1 | 09512f10-d115-11e2-8002-2f4261da0d90 | time3
 user1 | 0f5c93e0-d115-11e2-8002-2f4261da0d90 | time4
 user1 | 21155220-d115-11e2-8002-2f4261da0d90 | time5

But it's your wide-row (as seen from CLI)

[default@ks] list cf3;
Using default limit of 100
Using default column limit of 100
-------------------
RowKey: user1
=> (column=f0c687b0-d114-11e2-8002-2f4261da0d90, value=time1, timestamp=1370789864363000)
=> (column=fb9fa130-d114-11e2-8002-2f4261da0d90, value=time2, timestamp=1370789882563000)
=> (column=09512f10-d115-11e2-8002-2f4261da0d90, value=time3, timestamp=1370789905537000)
=> (column=0f5c93e0-d115-11e2-8002-2f4261da0d90, value=time4, timestamp=1370789915678000)
=> (column=21155220-d115-11e2-8002-2f4261da0d90, value=time5, timestamp=1370789945410000)

1 Row Returned.
Elapsed time: 105 msec(s).

Now, you wanted to count number of columns starting from a given time onwards. Right? Here is CQL3 for that.

cqlsh:ks> select count(*) from cf3 where key = 'user1' and column1 >= 09512f10-d115-11e2-8002-2f4261da0d90 ;

 count
-------
     3

Now, I am somewhat doubtful what goes beneath. But, my intuition says that actually all the columns gets fetched at coordinator node and counted in memory. Which is probably somewhat similar to what you were planning to manually on client machine.

Also, I am unaware if cassandra-cli provides such functionality, but you mentioned you are using Hector. So, you can leverage get_count or CountQuery like mentioned here except have null as range finish and large count value. Like this:

CountQuery<String, String> cq = HFactory.createCountQuery(keyspace, StringSerializer.get(), TimeUUIDSerializer.get());
cq.setColumnFamily(cf).setKey("user1");
cq.setRange(timestamp, null, Integer.MAX_VALUE);
QueryResult<Integer> r = cq.execute();

(uncompiled code above)

HTH

Old answer:

See Hector documentation:

CQL:

CqlQuery<String,String,Long> cqlQuery = new CqlQuery<String,String,Long>(keyspace, se, se, le);
cqlQuery.setQuery("SELECT COUNT(*) FROM StandardLong1 WHERE KEY = 'cqlQueryTest_key1'");
QueryResult<CqlRows<String,String,Long>> result = cqlQuery.execute();
assertEquals(2, result.get().getAsCount());

You may just miss the WHERE condition and use LIMIT to get your purpose solved.

Upvotes: 0

Related Questions