You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cassandra.apache.org by Stephen Powis <st...@pardot.com> on 2012/06/21 05:11:08 UTC

Creating secondary index CQL3 cassandra 1.1.1

I have the following schema:

describe columnfamily visitor_audit;

CREATE TABLE visitor_audit (
  visitor_id text,
  audit_id uuid,
  account_id int,
  audit_type int,
  created_at text,
  PRIMARY KEY (visitor_id, audit_id)
) WITH
  comment='' AND
  caching='KEYS_ONLY' AND
  read_repair_chance=0.100000 AND
  gc_grace_seconds=864000 AND
  min_compaction_threshold=4 AND
  max_compaction_threshold=32 AND
  replicate_on_write='true' AND
  compaction_strategy_class='SizeTieredCompactionStrategy' AND
  compression_parameters:sstable_compression='SnappyCompressor';

I then created an index by executing the following:
CREATE INDEX ix_accountid ON visitor_audit (account_id);

When I attempt to execute: select * from visitor_audit where account_id=1;
Nothing is returned even tho I have rows with account_id equal to 1.

Is there anything I am missing here?

Re: Creating secondary index CQL3 cassandra 1.1.1

Posted by Stephen Powis <st...@pardot.com>.
Ah yes that's it exactly, including the time outs/errors.  I wonder why
this didn't come up when I googled the error from my system logs.

Thanks!
Stephen

On Thu, Jun 21, 2012 at 2:47 AM, Sylvain Lebresne <sy...@datastax.com>wrote:

> On Thu, Jun 21, 2012 at 5:11 AM, Stephen Powis <st...@pardot.com>
> wrote:
> > I have the following schema:
> >
> > describe columnfamily visitor_audit;
> >
> > CREATE TABLE visitor_audit (
> >   visitor_id text,
> >   audit_id uuid,
> >   account_id int,
> >   audit_type int,
> >   created_at text,
> >   PRIMARY KEY (visitor_id, audit_id)
> > ) WITH
> >   comment='' AND
> >   caching='KEYS_ONLY' AND
> >   read_repair_chance=0.100000 AND
> >   gc_grace_seconds=864000 AND
> >   min_compaction_threshold=4 AND
> >   max_compaction_threshold=32 AND
> >   replicate_on_write='true' AND
> >   compaction_strategy_class='SizeTieredCompactionStrategy' AND
> >   compression_parameters:sstable_compression='SnappyCompressor';
> >
> > I then created an index by executing the following:
> > CREATE INDEX ix_accountid ON visitor_audit (account_id);
> >
> > When I attempt to execute: select * from visitor_audit where
> account_id=1;
> > Nothing is returned even tho I have rows with account_id equal to 1.
> >
> > Is there anything I am missing here?
>
> You are pretty much hitting
> https://issues.apache.org/jira/browse/CASSANDRA-4328.
> In other words, secondary indexes is not yet supported on table with
> composite PRIMARY KEY, but we don't correctly reject the CREATE INDEX
> command.
>
> --
> Sylvain
>

Re: Creating secondary index CQL3 cassandra 1.1.1

Posted by Sylvain Lebresne <sy...@datastax.com>.
On Thu, Jun 21, 2012 at 5:11 AM, Stephen Powis <st...@pardot.com> wrote:
> I have the following schema:
>
> describe columnfamily visitor_audit;
>
> CREATE TABLE visitor_audit (
>   visitor_id text,
>   audit_id uuid,
>   account_id int,
>   audit_type int,
>   created_at text,
>   PRIMARY KEY (visitor_id, audit_id)
> ) WITH
>   comment='' AND
>   caching='KEYS_ONLY' AND
>   read_repair_chance=0.100000 AND
>   gc_grace_seconds=864000 AND
>   min_compaction_threshold=4 AND
>   max_compaction_threshold=32 AND
>   replicate_on_write='true' AND
>   compaction_strategy_class='SizeTieredCompactionStrategy' AND
>   compression_parameters:sstable_compression='SnappyCompressor';
>
> I then created an index by executing the following:
> CREATE INDEX ix_accountid ON visitor_audit (account_id);
>
> When I attempt to execute: select * from visitor_audit where account_id=1;
> Nothing is returned even tho I have rows with account_id equal to 1.
>
> Is there anything I am missing here?

You are pretty much hitting
https://issues.apache.org/jira/browse/CASSANDRA-4328.
In other words, secondary indexes is not yet supported on table with
composite PRIMARY KEY, but we don't correctly reject the CREATE INDEX
command.

--
Sylvain