You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cassandra.apache.org by Shuge Lee <sh...@gmail.com> on 2010/05/11 06:00:18 UTC
How to write WHERE .. LIKE query ?
Hi all:
How to write WHERE ... LIKE query ?
For examples(described in Python):
Schema:
# columnfamily name
resources = [
# key
'foo': {
# columns and value
'url': 'foo.com',
'pushlier': 'foo',
},
'oof': {
'url': 'oof.com',
'pushlier': 'off',
},
# ... ,
}
# this is very easy,
SELECT * FROM KEY = 'foo'
but following are really hard:
SELECT * FROM resources WHERE key LIKE 'o%' # get all records which key name
contains character 'o'?
SELECT * FROM resources WHERE url == 'oof.com'
Anyone have good ideas ?
--
Lee Li
Re: How to write WHERE .. LIKE query ?
Posted by Mike Malone <mi...@simplegeo.com>.
On Tue, May 11, 2010 at 8:54 AM, Schubert Zhang <zs...@gmail.com> wrote:
> In the future, maybe cassandra can provide some "Filter" or "Coprocessor"
> interfaces. Just like what of Bigtable do.
> But now, cassandra is too young, there are many things to do for a clear
> core.
There's been talk of adding coprocessors. It will probably happen one day.
Unfortunately, that day is probably a ways off.
Mike
>
>
> On Tue, May 11, 2010 at 11:35 PM, Mike Malone <mi...@simplegeo.com> wrote:
>
>> On Mon, May 10, 2010 at 11:36 PM, vd <vi...@gmail.com> wrote:
>>
>>> Hi Mike
>>>
>>> AFAIK cassandra queries only on keys and not on column names, please
>>> verify.
>>>
>>
>> Incorrect. You can slice a row or rows (identified by a key) on a column
>> name range (e.g., "a" through "m") or ask for specific columns in a row or
>> rows (e.g., please give me the "first_name," "last_name" and
>> "hashed_password" fields from my Users column family where the key equals
>> "mmalone").
>>
>> See the get_range_slices() method in the thrift service.
>>
>> Mike
>>
>>
>>>
>>>
>>>
>>> On Tue, May 11, 2010 at 11:06 AM, Mike Malone <mi...@simplegeo.com>
>>> wrote:
>>> >
>>> >
>>> > On Mon, May 10, 2010 at 9:00 PM, Shuge Lee <sh...@gmail.com>
>>> wrote:
>>> >>
>>> >> Hi all:
>>> >> How to write WHERE ... LIKE query ?
>>> >> For examples(described in Python):
>>> >> Schema:
>>> >> # columnfamily name
>>> >> resources = [
>>> >> # key
>>> >> 'foo': {
>>> >> # columns and value
>>> >> 'url': 'foo.com',
>>> >> 'pushlier': 'foo',
>>> >> },
>>> >> 'oof': {
>>> >> 'url': 'oof.com',
>>> >> 'pushlier': 'off',
>>> >> },
>>> >> # ... ,
>>> >> }
>>> >> # this is very easy,
>>> >> SELECT * FROM KEY = 'foo'
>>> >> but following are really hard:
>>> >> SELECT * FROM resources WHERE key LIKE 'o%' # get all records which
>>> key
>>> >> name contains character 'o'?
>>> >
>>> > get_range_slices(<keyspace>, ColumnParent(column_family),
>>> > SlicePredicate(slice_range=SliceRange('',''), KeyRange('o', 'o~'),
>>> > ConsistencyLevel.ONE);
>>> >
>>> >>
>>> >> SELECT * FROM resources WHERE url == 'oof.com'
>>> >
>>> > This is a projection. Cassandra doesn't support this sort of query out
>>> of
>>> > the box. You'll have to structure your data so that data you want to
>>> query
>>> > by is in the key or column name. Or you'll have to manually build
>>> secondary
>>> > indexes.
>>> >
>>> > Mike
>>> >
>>>
>>
>>
>
Re: How to write WHERE .. LIKE query ?
Posted by Schubert Zhang <zs...@gmail.com>.
In the future, maybe cassandra can provide some "Filter" or "Coprocessor"
interfaces. Just like what of Bigtable do.
But now, cassandra is too young, there are many things to do for a clear
core.
On Tue, May 11, 2010 at 11:35 PM, Mike Malone <mi...@simplegeo.com> wrote:
> On Mon, May 10, 2010 at 11:36 PM, vd <vi...@gmail.com> wrote:
>
>> Hi Mike
>>
>> AFAIK cassandra queries only on keys and not on column names, please
>> verify.
>>
>
> Incorrect. You can slice a row or rows (identified by a key) on a column
> name range (e.g., "a" through "m") or ask for specific columns in a row or
> rows (e.g., please give me the "first_name," "last_name" and
> "hashed_password" fields from my Users column family where the key equals
> "mmalone").
>
> See the get_range_slices() method in the thrift service.
>
> Mike
>
>
>>
>>
>>
>> On Tue, May 11, 2010 at 11:06 AM, Mike Malone <mi...@simplegeo.com> wrote:
>> >
>> >
>> > On Mon, May 10, 2010 at 9:00 PM, Shuge Lee <sh...@gmail.com> wrote:
>> >>
>> >> Hi all:
>> >> How to write WHERE ... LIKE query ?
>> >> For examples(described in Python):
>> >> Schema:
>> >> # columnfamily name
>> >> resources = [
>> >> # key
>> >> 'foo': {
>> >> # columns and value
>> >> 'url': 'foo.com',
>> >> 'pushlier': 'foo',
>> >> },
>> >> 'oof': {
>> >> 'url': 'oof.com',
>> >> 'pushlier': 'off',
>> >> },
>> >> # ... ,
>> >> }
>> >> # this is very easy,
>> >> SELECT * FROM KEY = 'foo'
>> >> but following are really hard:
>> >> SELECT * FROM resources WHERE key LIKE 'o%' # get all records which key
>> >> name contains character 'o'?
>> >
>> > get_range_slices(<keyspace>, ColumnParent(column_family),
>> > SlicePredicate(slice_range=SliceRange('',''), KeyRange('o', 'o~'),
>> > ConsistencyLevel.ONE);
>> >
>> >>
>> >> SELECT * FROM resources WHERE url == 'oof.com'
>> >
>> > This is a projection. Cassandra doesn't support this sort of query out
>> of
>> > the box. You'll have to structure your data so that data you want to
>> query
>> > by is in the key or column name. Or you'll have to manually build
>> secondary
>> > indexes.
>> >
>> > Mike
>> >
>>
>
>
Re: How to write WHERE .. LIKE query ?
Posted by Mike Malone <mi...@simplegeo.com>.
On Mon, May 10, 2010 at 11:36 PM, vd <vi...@gmail.com> wrote:
> Hi Mike
>
> AFAIK cassandra queries only on keys and not on column names, please
> verify.
>
Incorrect. You can slice a row or rows (identified by a key) on a column
name range (e.g., "a" through "m") or ask for specific columns in a row or
rows (e.g., please give me the "first_name," "last_name" and
"hashed_password" fields from my Users column family where the key equals
"mmalone").
See the get_range_slices() method in the thrift service.
Mike
>
>
>
> On Tue, May 11, 2010 at 11:06 AM, Mike Malone <mi...@simplegeo.com> wrote:
> >
> >
> > On Mon, May 10, 2010 at 9:00 PM, Shuge Lee <sh...@gmail.com> wrote:
> >>
> >> Hi all:
> >> How to write WHERE ... LIKE query ?
> >> For examples(described in Python):
> >> Schema:
> >> # columnfamily name
> >> resources = [
> >> # key
> >> 'foo': {
> >> # columns and value
> >> 'url': 'foo.com',
> >> 'pushlier': 'foo',
> >> },
> >> 'oof': {
> >> 'url': 'oof.com',
> >> 'pushlier': 'off',
> >> },
> >> # ... ,
> >> }
> >> # this is very easy,
> >> SELECT * FROM KEY = 'foo'
> >> but following are really hard:
> >> SELECT * FROM resources WHERE key LIKE 'o%' # get all records which key
> >> name contains character 'o'?
> >
> > get_range_slices(<keyspace>, ColumnParent(column_family),
> > SlicePredicate(slice_range=SliceRange('',''), KeyRange('o', 'o~'),
> > ConsistencyLevel.ONE);
> >
> >>
> >> SELECT * FROM resources WHERE url == 'oof.com'
> >
> > This is a projection. Cassandra doesn't support this sort of query out of
> > the box. You'll have to structure your data so that data you want to
> query
> > by is in the key or column name. Or you'll have to manually build
> secondary
> > indexes.
> >
> > Mike
> >
>
Re: How to write WHERE .. LIKE query ?
Posted by vd <vi...@gmail.com>.
Hi Mike
AFAIK cassandra queries only on keys and not on column names, please verify.
On Tue, May 11, 2010 at 11:06 AM, Mike Malone <mi...@simplegeo.com> wrote:
>
>
> On Mon, May 10, 2010 at 9:00 PM, Shuge Lee <sh...@gmail.com> wrote:
>>
>> Hi all:
>> How to write WHERE ... LIKE query ?
>> For examples(described in Python):
>> Schema:
>> # columnfamily name
>> resources = [
>> # key
>> 'foo': {
>> # columns and value
>> 'url': 'foo.com',
>> 'pushlier': 'foo',
>> },
>> 'oof': {
>> 'url': 'oof.com',
>> 'pushlier': 'off',
>> },
>> # ... ,
>> }
>> # this is very easy,
>> SELECT * FROM KEY = 'foo'
>> but following are really hard:
>> SELECT * FROM resources WHERE key LIKE 'o%' # get all records which key
>> name contains character 'o'?
>
> get_range_slices(<keyspace>, ColumnParent(column_family),
> SlicePredicate(slice_range=SliceRange('',''), KeyRange('o', 'o~'),
> ConsistencyLevel.ONE);
>
>>
>> SELECT * FROM resources WHERE url == 'oof.com'
>
> This is a projection. Cassandra doesn't support this sort of query out of
> the box. You'll have to structure your data so that data you want to query
> by is in the key or column name. Or you'll have to manually build secondary
> indexes.
>
> Mike
>
Re: How to write WHERE .. LIKE query ?
Posted by Mike Malone <mi...@simplegeo.com>.
On Mon, May 10, 2010 at 9:00 PM, Shuge Lee <sh...@gmail.com> wrote:
> Hi all:
>
> How to write WHERE ... LIKE query ?
> For examples(described in Python):
>
> Schema:
>
> # columnfamily name
> resources = [
> # key
> 'foo': {
> # columns and value
> 'url': 'foo.com',
> 'pushlier': 'foo',
> },
> 'oof': {
> 'url': 'oof.com',
> 'pushlier': 'off',
> },
> # ... ,
> }
>
> # this is very easy,
> SELECT * FROM KEY = 'foo'
>
> but following are really hard:
> SELECT * FROM resources WHERE key LIKE 'o%' # get all records which key
> name contains character 'o'?
>
get_range_slices(<keyspace>, ColumnParent(column_family),
SlicePredicate(slice_range=SliceRange('',''), KeyRange('o', 'o~'),
ConsistencyLevel.ONE);
> SELECT * FROM resources WHERE url == 'oof.com'
>
This is a projection. Cassandra doesn't support this sort of query out of
the box. You'll have to structure your data so that data you want to query
by is in the key or column name. Or you'll have to manually build secondary
indexes.
Mike