Re: Creating counter columns in cassandra

[Pushpalanka Jayawardhana <pu...@gmail.com>]

Hi All,

I faced this same problem when trying to query the counter values. I am
using a phone number as row key and updating the number of calls taken to
that number. So my query is like

SELECT KEY FROM <columnFamily> WHERE No_of_Calls>5

This does not return any data, neither any exception, though I am 100% sure
that entries are there which satisfy that query.
I used same code as Amila mentioned. My doubt is this is due to some
mismatch types with the counter value representation and query value, but
failed to resolve this. :(

Any ideas or guidance is greatly helpful.
Thanks in advance!


On Tue, Jul 31, 2012 at 1:49 PM, Amila Paranawithana <am...@gmail.com>wrote:

> Hi all,
> Thanks all for the valuable feedback. I have a problem with running
> queries with Cqlsh.
> My query is  SELECT * FROM rule1 WHERE sms=3;
>
> java.lang.NumberFormatException: An hex string representing bytes must
> have an even length
>  at org.apache.cassandra.utils.Hex.hexToBytes(Hex.java:52)
> at
> org.apache.cassandra.utils.ByteBufferUtil.hexToBytes(ByteBufferUtil.java:501)
>  at
> org.apache.cassandra.db.marshal.CounterColumnType.fromString(CounterColumnType.java:57)
>  at org.apache.cassandra.cql.Term.getByteBuffer(Term.java:96)
> at
> org.apache.cassandra.cql.QueryProcessor.multiRangeSlice(QueryProcessor.java:185)
>  at
> org.apache.cassandra.cql.QueryProcessor.processStatement(QueryProcessor.java:484)
> at org.apache.cassandra.cql.QueryProcessor.process(QueryProcessor.java:877)
>  at
> org.apache.cassandra.thrift.CassandraServer.execute_cql_query(CassandraServer.java:1235)
>  at
> org.apache.cassandra.thrift.Cassandra$Processor$execute_cql_query.getResult(Cassandra.java:3542)
>  at
> org.apache.cassandra.thrift.Cassandra$Processor$execute_cql_query.getResult(Cassandra.java:3530)
>  at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:32)
> at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:34)
>  at
> org.apache.cassandra.thrift.CustomTThreadPoolServer$WorkerProcess.run(CustomTThreadPoolServer.java:186)
>  at
> java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
>  at java.lang.Thread.run(Thread.java:662)
>
> but when I say SELECT * FROM rule1 WHERE sms=03; no exceptions are shown.
> But though I have entries where sms count =3 that entry is not retrieved.
>
> And for queries like SELECT * FROM rule1 WHERE sms>=03;
> Bad Request: No indexed columns present in by-columns clause with "equals"
> operator
>
> Can anyone recognize the problem here??
>
> Following are the methods I used.
>
> //for indexing columns
> void indexColumn(String idxColumnName,String CountercfName){
>
>         Cluster cluster = HFactory.getOrCreateCluster(
> BasicConf.CASSANDRA_CLUSTER, BasicConf.CLUSTER_PORT);
>         KeyspaceDefinition keyspaceDefinition =
> cluster.describeKeyspace(BasicConf.KEYSPACE);
>
>         List<ColumnFamilyDefinition> cdfs = keyspaceDefinition.getCfDefs();
>         ColumnFamilyDefinition cfd = null;
>         for(ColumnFamilyDefinition c:cdfs){
>          if(c.getName().toString().equals(CountercfName)) {
>          System.out.println(c.getName());
>          cfd=c;
>          break;
>          }
>         }
>
>         BasicColumnFamilyDefinition columnFamilyDefinition = new
> BasicColumnFamilyDefinition(cfd);
>
>         BasicColumnDefinition bcdf = new BasicColumnDefinition();
>         bcdf.setName(StringSerializer.get().toByteBuffer(idxColumnName));
>         bcdf.setIndexName(idxColumnName+"index");
>         bcdf.setIndexType(ColumnIndexType.KEYS);
>         bcdf.setValidationClass(ComparatorType.COUNTERTYPE.getClassName());
>
>         columnFamilyDefinition.addColumnDefinition(bcdf);
>         cluster.updateColumnFamily(new
> ThriftCfDef(columnFamilyDefinition));
>
>  }
>
> // for adding a new counter column
> void insertCounterColumn(String cfName, String counterColumnName,
>  String phoneNumberKey) {
>
>  Mutator<String> mutator = HFactory.createMutator(keyspace,
>  StringSerializer.get());
>  mutator.insertCounter(phoneNumberKey, cfName, HFactory
> .createCounterColumn(counterColumnName, 1L,
>  StringSerializer.get()));
>  mutator.execute();
> CounterQuery<String, String> counter = new
> ThriftCounterColumnQuery<String, String>(
>  keyspace, StringSerializer.get(), StringSerializer.get());
> counter.setColumnFamily(cfName).setKey(phoneNumberKey)
>  .setName(counterColumnName);
>
>  indexColumn(columnName, cfName);
>
>  }
>
> // incrementing counter values
> void incrementCounter(String ruleName, String columnName,
> HashMap<String, Long> entries) {
>
> Mutator<String> mutator = HFactory.createMutator(keyspace,
> StringSerializer.get());
>
> Set<String> keys = entries.keySet();
> for (String s : keys) {
>  mutator.incrementCounter(s, ruleName, columnName, entries.get(s));
>
> }
>
> mutator.execute();
>
> }
>
>
>
> On Sun, Jul 29, 2012 at 3:29 PM, Paolo Bernardi <be...@gmail.com>wrote:
>
>> On Sun, Jul 29, 2012 at 9:30 AM, Abhijit Chanda
>> <ab...@gmail.com> wrote:
>> > There should be at least one "=" (equals) in the WHERE case on key or
>> > secondary index column, this is the Cassandra limitation.
>>
>> Yep, it's still there (see validateFilterClauses from line 531):
>>
>>
>> https://github.com/apache/cassandra/blob/trunk/src/java/org/apache/cassandra/thrift/ThriftValidation.java
>>
>> It's not probably going to change anytime soon (or later, probably ;-)
>> ), secondary indexes are still CFs underneath:
>>
>>
>> http://grokbase.com/t/cassandra/user/1099pty59r/indexoperator-lt-lte-gt-gte-implementation#20100910dx1fwm8z9ek1cvec10jvmpzfa4
>>
>> From that message you also get an hint on how to proceed in this
>> situation:
>> 1) Add a "fake" equality condition to the query;
>> 2) Go for a range scan, which is more efficient than the hack above.
>>
>> Depending on the speed than you need on the writing side compared to
>> the speed required on the reading side, you might also consider
>> keeping an ad-hoc index of the counter columns with the counter
>> greater than your threshold, but it's surely requiring more
>> housekeeping on your side.
>>
>> Paolo
>>
>
>
>
> --
> Amila Iroshani Paranawithana
> CSE-University of Moratuwa.
> B-http://amilaparanawithana.blogspot.com
> T-https://twitter.com/#!/AmilaPara
>
>
>


-- 
Pushpalanka Jayawardhana