[jira] [Commented] (CASSANDRA-6383) Secondary indexing of map keys

["Peter (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CASSANDRA-6383?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13913527#comment-13913527 ] 

Peter commented on CASSANDRA-6383:
----------------------------------

I'm able to create index on sets and query them, but map seems to have an issue. Here's the cql I'm using to create the table and indexes.

E:\apache-cassandra-2.1\bin>cqlsh.bat
Connected to Test Cluster at localhost:9160.
[cqlsh 4.1.1 | Cassandra 2.1-SNAPSHOT | CQL spec 3.1.1 | Thrift protocol 19.39.0]
Use HELP for help.
cqlsh> CREATE KEYSPACE testks WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };
cqlsh>
cqlsh> use testks;
cqlsh:testks>
cqlsh:testks> CREATE TABLE test2 (
          ...   user_name varchar PRIMARY KEY,
          ...   password varchar,
          ...   gender varchar,
          ...   session_token varchar,
          ...   state varchar,
          ...   birth_year bigint,
          ...   nicknames set<text>,
          ...   keywords map<text,int> );
cqlsh:testks>
cqlsh:testks> CREATE INDEX gender_index on testks.test2 (gender);
cqlsh:testks> CREATE INDEX nicknames_index on testks.test2 (nicknames);
cqlsh:testks> CREATE INDEX keyword_index on testks.test2 (keywords);
cqlsh:testks> describe table test2;

CREATE TABLE test2 (
  user_name text,
  birth_year bigint,
  gender text,
  keywords map<text, int>,
  nicknames set<text>,
  password text,
  session_token text,
  state text,
  PRIMARY KEY (user_name)
) 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
  index_interval=128 AND
  read_repair_chance=0.100000 AND
  populate_io_cache_on_flush='false' AND
  default_time_to_live=0 AND
  speculative_retry='99.0PERCENTILE' AND
  memtable_flush_period_in_ms=0 AND
  compaction={'class': 'SizeTieredCompactionStrategy'} AND
  compression={'sstable_compression': 'LZ4Compressor'};

CREATE INDEX gender_index ON test2 (gender);

CREATE INDEX keyword_index ON test2 (keywords);

CREATE INDEX nicknames_index ON test2 (nicknames);

cqlsh:testks> insert into testks.test2 (user_name,gender,state,birth_year,nicknames,keywords) values ('user1','m','ca',1950,{'bob','bobby','robbie'},{'high':3,'low':1});
cqlsh:testks> insert into testks.test2 (user_name,gender,state,birth_year,nicknames,keywords) values ('user2','f','ma',1952,{'mike','mikey','big m'},{'tall':3,'short':1});
cqlsh:testks> insert into testks.test2 (user_name,gender,state,birth_year,nicknames,keywords) values ('user3','m','mn',1954,{'henry','hank'},{'high':3,'short':1});
cqlsh:testks> insert into testks.test2 (user_name,gender,state,birth_year,nicknames,keywords) values ('user4','f','az',1956,{'howard','howie'},{'tall':3,'low':1});
cqlsh:testks> insert into testks.test2 (user_name,gender,state,birth_year,nicknames,keywords) values ('user5','m','nv',1958,{'arthur','artie'},{'good':3,'bad':1});
cqlsh:testks> insert into testks.test2 (user_name,gender,state,birth_year,nicknames,keywords) values ('user6','f','tx',1960,{'michelle','missy'},{'bad':3,'good':1});
cqlsh:testks> insert into testks.test2 (user_name,gender,state,birth_year,nicknames,keywords) values ('user7','m','ct',1962,{'thomas','tom','tommy'},{'new':3,'old':1});
cqlsh:testks> insert into testks.test2 (user_name,gender,state,birth_year,nicknames,keywords) values ('user8','f','nh',1964,{'jonathan','jonnie','johnboy'},{'fresh':3,'stale':1});
cqlsh:testks> insert into testks.test2 (user_name,gender,state,birth_year,nicknames,keywords) values ('user9','m','ny',1966,{'kenneth','ken','kenny'},{'go':3,'stop':1});
cqlsh:testks> insert into testks.test2 (user_name,gender,state,birth_year,nicknames,keywords) values ('user10','f','co',1968,{'barbara','barb','barbie'},{'cool':3,'lame':1});
cqlsh:testks> select * from testks.test2 where gender = 'm';

 user_name | birth_year | gender | keywords                | nicknames                   | password | session_token | state
-----------+------------+--------+-------------------------+-----------------------------+----------+---------------+-------
     user9 |       1966 |      m |    {'go': 3, 'stop': 1} | {'ken', 'kenneth', 'kenny'} |     null |          null |    ny
     user1 |       1950 |      m |   {'high': 3, 'low': 1} |  {'bob', 'bobby', 'robbie'} |     null |          null |    ca
     user7 |       1962 |      m |    {'new': 3, 'old': 1} |  {'thomas', 'tom', 'tommy'} |     null |          null |    ct
     user3 |       1954 |      m | {'high': 3, 'short': 1} |           {'hank', 'henry'} |     null |          null |    mn
     user5 |       1958 |      m |   {'bad': 1, 'good': 3} |         {'arthur', 'artie'} |     null |          null |    nv

(5 rows)

cqlsh:testks> select * from testks.test2 where keywords CONTAINS KEY 'cool';

(0 rows)

cqlsh:testks>

> Secondary indexing of map keys
> ------------------------------
>
>                 Key: CASSANDRA-6383
>                 URL: https://issues.apache.org/jira/browse/CASSANDRA-6383
>             Project: Cassandra
>          Issue Type: Improvement
>            Reporter: Sylvain Lebresne
>            Assignee: Sylvain Lebresne
>             Fix For: 2.1 beta1
>
>         Attachments: 6383.txt
>
>
> CASSANDRA-4511 started adding secondary indexing for collections, but didn't wired map key indexing because this requires generalizing a bit the 2ndary index API to support 2 indexes on the same column. And since that's not entirely related to the initial problem of CASSANDRA-4511, let's tackle this last part here.
> I'll note that one other is the syntax. For selection, I propose
> {noformat}
> SELECT * FROM foo WHERE myMap CONTAINS KEY 'bar';
> {noformat}
> (but that assumes we use CONTAINS in CASSANDRA-4511, if we use IN, we'll need something else)
> For declaring indexes I'm less inspired. We could have something like
> {noformat}
> CREATE INDEX ON foo(myMap KEYS)
> {noformat}
> but maybe someone has a better idea?



--
This message was sent by Atlassian JIRA
(v6.1.5#6160)