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 <amila1204@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 <bernarpa@gmail.com> wrote:
On Sun, Jul 29, 2012 at 9:30 AM, Abhijit Chanda
<abhijit.chanda0@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




--
Pushpalanka Jayawardhana