3
votes

i am trying to execute conditional query in cassandra CQL but it is giving me error like

cqlsh:events> select * from standardevents where name=ActivityPg_view;

i am executing above query, it is giving me below error

Bad Request: line 1:55 no viable alternative at input ';' Perhaps you meant to use CQL 2? Try using the -2 option when starting cqlsh.

cqlsh:events> select * from standardevents where name='ActivityPg_view';

i am executing above query, it is giving me below error

Bad Request: No indexed columns present in by-columns clause with Equal operator

when i am trying to execute using CQL 2

cqlsh:events> select * from standardevents where name=ActivityPg_view;

it is giving me below error,

Bad Request: No indexed columns present in by-columns clause with "equals" opera tor

cqlsh:events> select * from standardevents where name='ActivityPg_view';

it is giving me below error,

Bad Request: No indexed columns present in by-columns clause with "equals" opera tor Perhaps you meant to use CQL 3? Try using the -3 option when starting cqlsh.

so can somebody suggest the problem and how to solve this thing and execute the conditional query?

cqlsh> DESCRIBE COLUMNFAMILY events.standardevents;

CREATE TABLE standardevents (
    uuid timeuuid PRIMARY KEY,
    data text,
    name text,
    time text,
    tracker text,
    type text,
    userid text
  ) WITH 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'};
2
Please include output from DESCRIBE COLUMNFAMILY events.standardevents – AndySavage
@AndySavage : I edited my question. – Helping Hand..

2 Answers

5
votes

Your table (CF) has no field called courseid (hence the error "Undefined name courseid in where clause"). You can't query on something that doesn't exist.

Were you expecting that field there? (Your PK for that table is called 'uuid' if that helps) Are you querying the right table? Not much else to suggest.

Edit: After update

Your CQL3 attempt is missing the quotes around the name, but that aside ... In Cassandra you can't randomly query by non key columns. Cassandra is a partitioned row store, and it is not really designed to do query's in the manner you are trying.

You could add a secondary index to fix this, but you should be aware it not like in traditional SQL. Having a 2ndary index will need to hit all nodes in a cluster do to the query. It's also not ideal if your data has high cardinality.

In Cassandra the general premise is that storage is cheap, and you should base your model on your query rather than your data. Denormalise everything. For example, if you need to pull events by name, then you should make a table that is key'd by name, and includes all the event data you need. That way reading them is (essentially) and O(1) operation.

0
votes

To be able to restrict by your name column you would need to add a secondary index to it.

Example:

CREATE INDEX standardevents_index ON standardevents (name);