cassandra - CQL select a column of composite key -

cassandra - CQL select a column of composite key -

i using cassandra database , want utilize cqlsh see specific info of info stored in composite key format. info model this:

rowkey(username) column1(id) column2(city:<city>) value alice 12 city:boston 100 tom 13 city:new york 200 bill 22 state:ca 111

as can see, info stored composite key, , column2 has pattern:city or state(string) + another string(this may vary). in cqlsh see value according column2 pattern: city+:+cityname? example, list 'value' city: pattern in column2?

ps: schema

create table info ( key text, column1 bigint, column2 text, value bigint, primary key (key, column1, column2) ) compact storage , bloom_filter_fp_chance=0.010000 , caching='keys_only' , comment='' , dclocal_read_repair_chance=0.000000 , gc_grace_seconds=864000 , read_repair_chance=0.100000 , replicate_on_write='true' , populate_io_cache_on_flush='false' , compaction={'class': 'sizetieredcompactionstrategy'} , compression={'sstable_compression': 'snappycompressor'};

no, schema not possible accomplish goal. cassandra not back upwards like query. reply of @catpaws 1 of solution accomplish goal. trying is, instead of using single column (for column2) can split 2 columns (locationtype , locationname) , create locationtype 1 of primary key or secondary indexed column. below schema describes as 1 of primary key strategy

create table info ( key text, column1 bigint, locationtype text, locationname text, value bigint, primary key (key, column1, locationtype) )

so possible have query clause. eg,

select * info key = 'tom' , column1 = 13 , locationtype = 'city'

the below schema describes secondary indexed column strategy

create table info ( key text, column1 bigint, locationtype text, locationname text, value bigint, primary key (key, column1) ) create index info_locationtype on info (locationtype);

so possible have query clause. eg,

select * info key = 'tom' , locationtype = 'city'

but how ever if using secondary indexed low cardinality (which means locationtype have 1 of 2 values city or state) impact query performance. , 1 more point remember while using secondary index is, changing column value should not utilize secondary index (but in case locationtype not alter guess), seek have locationtype within primary key.

if want accomplish usage of like go solandra https://github.com/tjake/solandra

cassandra cql