Cassandra secondary index using collection type

Cassandra secondary index using collection type

Here is a cassandra table:
CREATE TABLE Account(
id uuid,
userRef uuid,
name map,
dataStatus text,
dataVisibility text,

PRIMARY KEY( id, dataStatus, dataVisibility, userRef)
)
CREATE INDEX idx_xxx_account_name ON Account (name);

‘name’ is a cql3 column of (collection) type ‘map’. My question is: is it possible to create secondary index on a map type, i.e., name?
Thanks.

Solutions/Answers:

Solution 1:

As of Cassandra 1.2.6, custom indexes on collections are supported.

https://git-wip-us.apache.org/repos/asf?p=cassandra.git;a=blob_plain;f=CHANGES.txt;hb=refs/tags/cassandra-1.2.6

Solution 2:

Your question is quite old. In cassandra 2.1 valid syntax is

CREATE INDEX on Account(keys(name));

Solution 3:

No response? I have decided to rewrite the table as follows:

CREATE TABLE Account(
id uuid,
userRef uuid,
**main_name text,**
**other_name map<text, text>,**
dataStatus text,
dataVisibility text,
...
PRIMARY KEY( id, dataStatus, dataVisibility, userRef)
)
CREATE INDEX idx_xxx_account_name ON Account (main_name);

*_name could be anything e.g., email, phone etc. For example, a main_name could be the mandatory, whereas other_name could be optional.

Anyway now I can index main_name as a ‘text’ type instead of the map of text values.

Solution 4:

To answer your initial question:

There is no support for secondary indexes on collections yet. Concretely, you could associate a set of tags to a user, but you cannot automatically index users by their tags yet. Adding that support is definitively on the roadmap but remains to be implemented.
Coming in 1.2: Collections support in CQL3

Also, I don’t quite see why you use a map? Why not a simple set or list? Have a look at the reference provided below.

Solution 5:

create index idx_name on Account(ENTRIES(name))

this is for access the rows with particular entry in map.

Related:  Best architecture for a social media app

References