cassandra, simple query
i am starting with cassandra, but i have some difficult to understand how the queries work.
i have this
[default@Keyspace1] get Users[jsmith];
=> (column=adress, value=London, timestamp=1311087089506000)
=> (column=age, value=*, timestamp=1311086896514000)
=> (column=first, value=John, timestamp=13110868开发者_JAVA百科84652000)
=> (column=last, value=Smith, timestamp=1311086891619000)
Returned 4 results.
In SQL i can do: SELECT last FROM Keyspace1 WHERE first="John"
But in cassandra language?
I am trying something like this, but i confess that i'm totally confused about the sintax.
[default@Keyspace1] keyspace1.get("column_family","John","last")
...
[default@stackoverflow] set Users['jsmith']['address']=London;
Value inserted.
[default@stackoverflow] set Users['jsmith']['age']=42;
Value inserted.
[default@stackoverflow] set Users['jsmith']['first']=john;
Value inserted.
[default@stackoverflow] set Users['jsmith']['last']=smith;
Value inserted.
[default@stackoverflow] get Users['jsmith']['last'];
=> (column=last, value=smith, timestamp=1311090805707000)
But, you have to know row-key for this. It will be hard to do this kind of search; look at this thread, they discuss the same.
Update (thanks @jbellis for the hint)
It seem that there are quite a few things added in Cassandra 0.7 and newer releases. I have been working on 0.6. So, yes, you can do it.
In Cassandra 0.7 you can
[default@unknown] connect localhost/9160;
Connected to: "Test Cluster" on localhost/9160
[default@unknown] use stackoverflow;
[default@stackoverflow] create column family IndexedUsers with
comparator=UTF8Type and column_metadata=[
{column_name:address, validation_class:UTF8Type},
{column_name:age, validation_class:UTF8Type},
{column_name:first, validation_class:UTF8Type, index_type:KEYS},
{column_name:last, validation_class:UTF8Type}];
79d6ebce-b279-11e0-9ddf-e700f669bcfc
Waiting for schema agreement...
... schemas agree across the cluster
[default@stackoverflow] set IndexedUsers['jsmith']['age']=42;
Value inserted.
[default@stackoverflow] set IndexedUsers['jsmith']['address']='London';
Value inserted.
[default@stackoverflow] set IndexedUsers['jsmith']['first']='John';
Value inserted.
[default@stackoverflow] set IndexedUsers['jsmith']['last']='Smith';
Value inserted.
[default@stackoverflow] get IndexedUsers where first='John';
-------------------
RowKey: jsmith
=> (column=address, value=London, timestamp=1311129646984000)
=> (column=age, value=42, timestamp=1311129627578000)
=> (column=first, value=John, timestamp=1311129676977000)
=> (column=last, value=Smith, timestamp=1311129698125000)
1 Row Returned.
But not
[default@stackoverflow] get IndexedUsers[last] where first='John';
Syntax error at position 23: missing EOF at 'where'
because:
[default@stackoverflow] help get;
get <cf>['<key>'];
get <cf>['<key>']['<col>'] (as <type>)*;
get <cf>['<key>']['<super>'];
get <cf>['<key>'][<function>];
get <cf>['<key>'][<function>(<super>)][<function>(<col>)];
get <cf> where <column> = <value> [and <column> > <value> and ...] [limit <integer>];
Default LIMIT is 100. Available operations: =, >, >=, <, <=
CQL is a better option here it seems. It's a part of Cassandra 0.8 release. Refer this. There you can:
CREATE INDEX [index_name] ON <column_family> (column_name);
and then
SELECT [FIRST N] [REVERSED] col_name1, col_name2 FROM <COLUMN FAMILY>
[USING <CONSISTENCY>] [WHERE <CLAUSE>] [LIMIT N];
Refer complete CQL commands here.
This post explains how to create indexes and do queries like the one you want, in Cassandra: http://www.datastax.com/dev/blog/whats-new-cassandra-07-secondary-indexes
精彩评论