CQL select a column of composite key

Question

I am using Cassandra database and want to use cqlsh to see some specific information of the data stored in it with the composite key format. The data model is like 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 you can see, the data is stored with a composite key, and the column2 has a pattern:city or state(String) + another String(this may vary). Then in cqlsh what could I do to see the value according to column2 with pattern: city+:+cityname? For example, it list all the 'value' with a city: pattern in column2?

ps: the schema

CREATE TABLE info (
key text,
column1 bigint,
column2 text,
value bigint,
PRIMARY KEY (key, column1, column2)
) 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'};

Why not clear? `rowkey` is the username, with a composite key `id` and `some specific string` — chrisTina, Nov 12 '14 at 03:53
It would be far clearer if you pasted the output from the cqlsh command "describe table table_name". I agree with Jaya...I had to read the wall of text a couple of times, and I'm still not sure what you mean. The schema would make it 100% clear. — ashic, Nov 12 '14 at 09:02
@JayaAnanthram please see the edited question. I list the schema. — chrisTina, Nov 12 '14 at 16:23
Ya now I am looking better :) See my answer below It may help you. — Jaya Ananthram, Nov 13 '14 at 04:57

Jaya Ananthram · Accepted Answer · 2014-11-13T05:25:22.300

No, with your schema it is not possible to achieve your goal. Cassandra does not support for like query. Answer of @catpaws is one of the solution to achieve your goal. What he is trying to say is, instead of using a single column (for column2) you can split into two columns (locationType and locationName) and make the locationType as one of the primary key or secondary indexed column. The below schema describes as one of the primary key strategy

CREATE TABLE info (
key text,
column1 bigint,
locationType text,
locationName text,
value bigint,
PRIMARY KEY (key, column1, locationType)
)

So that it is possible to have a query with where clause. for eg,

select * from info where key = 'Tom' and column1 = 13 and 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 that it is possible to have a query with where clause. for eg,

select * from info where key = 'Tom' and locationType = 'city'

But how ever if you are using secondary indexed with low cardinality (which means locationType will have only any one of two values city or state) which will affect your query performance. And one more point to remember while using secondary index is, frequently changing column value should not use secondary index (But in your case locationType will not change frequently I guess), So however try to have locationType within primary key.

If you want to achieve the usage of like then go with Solandra https://github.com/tjake/Solandra

see[this](http://stackoverflow.com/questions/26901429/select-a-specific-record-in-cassandra-using-cql). It seems that `select * from info where key = 'Tom' and locationType = 'city'` also didn't work properly. What's the issue? — chrisTina, Nov 13 '14 at 05:08
Now query has been edited.. The problem is if PRIMARY KEY (key, column1, locationType) is given as primary key if you want to specify where clause on locationType then value of previous two key 'key' and 'column1' should also need to give in where clause. — Jaya Ananthram, Nov 13 '14 at 14:22

score 1 · Answer 2 · edited May 23 '17 at 12:27

In Cassandra 2.1 and later:

create table users (
username text,
id int,
location map,
value int,
PRIMARY KEY (username, id));

insert into users (username, id, location, value) VALUES ('Alice', 12, {'city': 'Boston'}, 100);
insert into users (username, id, location, value) VALUES ('Tom', 13, {'city': 'New York'}, 200);
insert into users (username, id, location, value) VALUES ('Bill', 22, {'state': 'CA'}, 111);
create index mymapvalues on users (location);
select * from users where location CONTAINS 'New York';

 username | id | location             | value
----------+----+----------------------+-------
      Tom | 13 | {'city': 'New York'} |   200

(1 rows)
drop index mymapvalues;
create index mymapvalueindex ON users (KEYS(location));
select * from users where location contains key 'city';

 username | id | location             | value
----------+----+----------------------+-------
      Tom | 13 | {'city': 'New York'} |   200
    Alice | 12 |   {'city': 'Boston'} |   100

(2 rows)

In earlier releases, use this procedure SELECT Specific Value from map

Please see the edited question, I show the schema I use. The location is not a `map`, it is just a `text`. — chrisTina, Nov 12 '14 at 16:42
I think the answer in this post says how to get detail (no map): http://stackoverflow.com/questions/16024839/select-secific-value-from-map — catpaws, Nov 12 '14 at 23:45

CQL select a column of composite key

2 Answers2