CQL 3.0 composite keys and secondary indexes

[Roland Mechler]

It seems as though secondary indexes are not supported in tables (column
families) that have composite keys. Is that true? If so, are there plans to
suport that combination in the future?

-Roland

Select on indexed columns and with IN clause for the PRIMARY KEY

[Roland Mechler]

I am using C* 1.1 and CQL 3.0. Am trying to do a select with an IN clause
for the primary key, and on an indexed column, which appears to not be
supported:

cqlsh:Keyspace1> SELECT * FROM TestTable WHERE id IN ('1', '2') AND
data = 'b';
Bad Request: Select on indexed columns and with IN clause for the
PRIMARY KEY are not supported

Any chance this will be supported in the future?


Full example:

cqlsh:Keyspace1> CREATE TABLE TestTable (id text PRIMARY KEY, data text);
cqlsh:Keyspace1> CREATE INDEX ON TestTable (data);
cqlsh:Keyspace1> INSERT INTO TestTable (id, data) VALUES ('1', 'a');
cqlsh:Keyspace1> INSERT INTO TestTable (id, data) VALUES ('2', 'b');
cqlsh:Keyspace1> INSERT INTO TestTable (id, data) VALUES ('3', 'b');
cqlsh:Keyspace1> SELECT * FROM TestTable WHERE id IN ('1', '2');
 id | data
+--
  1 |a
  2 |b

cqlsh:Keyspace1> SELECT * FROM TestTable WHERE data = 'b';
 id | data
+--
  3 |b
  2 |b

cqlsh:Keyspace1> SELECT * FROM TestTable WHERE id IN ('1', '2') AND data =
'b';
Bad Request: Select on indexed columns and with IN clause for the PRIMARY
KEY are not supported
cqlsh:Keyspace1>

-Roland

DELETE from table with composite keys

[Roland Mechler]

I have a table with a 3 part composite key and I want to delete rows based
on the first 2 parts of the key. SELECT works using 2 parts of the key, but
DELETE fails with the error:

Bad Request: Missing mandatory PRIMARY KEY part part3

(see details below). Is there a reason why deleting based on the first 2
parts should not work? I.e., is it just currently not supported, or is it a
permanent limitation?

Note that deleting based on just the first part of the key will work…
deletes all matching rows.

cqlsh:Keyspace1> CREATE TABLE MyTable (part1 text, part2 text, part3 text,
data text, PRIMARY KEY(part1, part2, part3));
cqlsh:Keyspace1> INSERT INTO MyTable (part1, part2, part3, data) VALUES
(‘a’, ‘b’, ‘c’, ‘d’);
cqlsh:Keyspace1> SELECT * FROM MyTable WHERE part1 = ‘a’ AND part2 = ‘b’;
part1 | part2 | part3 | data
——-+——-+——-+——
a | b | c | d

cqlsh:Keyspace1> DELETE FROM MyTable WHERE part1 = ‘a’ AND part2 = ‘b’;
Bad Request: Missing mandatory PRIMARY KEY part part3
cqlsh:Keyspace1> DELETE data FROM MyTable WHERE part1 = ‘a’ AND part2 = ‘b’;
Bad Request: Missing mandatory PRIMARY KEY part part3
cqlsh:Keyspace1> DELETE FROM MyTable WHERE part1 = ‘a’;
cqlsh:Keyspace1> SELECT * FROM MyTable WHERE part1 = ‘a’ AND part2 = ‘b’;
cqlsh:Keyspace1>

-Roland

Re: CQL 3.0 Features

[Roland Mechler]

http://www.datastax.com/dev/blog/whats-new-in-cql-3-0

It's my understanding that that the actual reference documentation for 3.0
should be ready soon. Anyone know when?

-Roland

On Wed, May 16, 2012 at 12:04 AM, Tamil selvan R.S wrote:

> Hi,
>  Is there a tutorial or reference on CQL 3.0 Features. In cassandra
> download site the reference is still pointing to 2.0
>  Specifically Composite Types
> Regards,
> Tamil.s
>

Composite keys question

[Roland Mechler]

Suppose I have a table in CQL3 with a 2 part composite, and I do a select
that specifies just the second part of the key (not the partition key),
will this result in a full table scan, or is the second part of the key
indexed?

Example:

cqlsh:"Keyspace1"> CREATE TABLE test_table (part1 text, part2 text, data
text, PRIMARY KEY (part1, part2));
cqlsh:"Keyspace1"> INSERT INTO test_table (part1, part2, data) VALUES
('1','1','a');
cqlsh:"Keyspace1"> SELECT * FROM test_table WHERE part2 = '1';
 part1 | part2 | data
---+---+--
 1 | 1 |a

-Roland

Re: Help deleting data using cql v3

[Roland Mechler]

This is a known issue, see
https://issues.apache.org/jira/browse/CASSANDRA-4193.

In the meantime, a workaround is to specify all the column names to be
deleted. I.e.,

  delete my_value from testCol where my_id='1_71548' and
time_id=2fc39fa0-1dd5-11b2-9b6a-395f35722afe;

should work.

(I had the same question answered here
http://www.datastax.com/dev/blog/whats-new-in-cql-3-0)

-Roland

On Thu, May 24, 2012 at 6:24 PM, Stephen Powis wrote:

> I have the following schema setup in cassandra 1.1 with cql 3:
>
> CREATE TABLE testCol (
>   my_id varchar,
>   time_id TimeUUIDType,
>   my_value int,
>   PRIMARY KEY (my_id, time_id)
> );
>
> and the following data already inserted:
>
> my_id| time_id   |
> my_value |
> 1_71548 | 2fc39fa0-1dd5-11b2-9b6a-395f35722afe |  1|
>
> but when I issue the following delete command using cqlsh -3, the data is
> not removed.
>
> delete from testCol where my_id='1_71548' and
> time_id=2fc39fa0-1dd5-11b2-9b6a-395f35722afe;
>
> Is it possible to remove data using the full composite key?  Or am I
> formatting the UUID incorrectly?
>
> Thanks!
> Stephen
>

Re: Composite keys question

[Roland Mechler]

Thanks for your response, Cyril. Yeah, I realized shortly after asking that
indeed the second term is not being indexed, so it must be doing a table
scan. Indexing for composite columns is in the works (
https://issues.apache.org/jira/browse/CASSANDRA-3680), but not sure how
soon that will be available.

The thing is, it did actually let me search on the second term only, which
is perhaps a little surprising.

-Roland

On Fri, May 25, 2012 at 12:33 PM, Cyril Auburtin
wrote:

> I'm not much advanced in cassandra, but seeing the pycassa doc
> http://pycassa.github.com/pycassa/assorted/composite_types.html, for
> composites you can't even search for the second term, you need a first
> term, the second will filter, you just do range slices on the composite
> columns
>
> it's totally different from secondary indexes for the rows
>
> also CQL can't do everything as much as the other clients
>
> 2012/5/24 Roland Mechler 
>
>> Suppose I have a table in CQL3 with a 2 part composite, and I do a select
>> that specifies just the second part of the key (not the partition key),
>> will this result in a full table scan, or is the second part of the key
>> indexed?
>>
>> Example:
>>
>> cqlsh:"Keyspace1"> CREATE TABLE test_table (part1 text, part2 text, data
>> text, PRIMARY KEY (part1, part2));
>> cqlsh:"Keyspace1"> INSERT INTO test_table (part1, part2, data) VALUES
>> ('1','1','a');
>> cqlsh:"Keyspace1"> SELECT * FROM test_table WHERE part2 = '1';
>>  part1 | part2 | data
>> ---+---+--
>>  1 | 1 |a
>>
>> -Roland
>>
>>
>>
>

Cassandra 1.1.1 release?

[Roland Mechler]

Anyone have a rough idea of when Cassandra 1.1.1 is likely to be released?

-Roland