Return-Path: X-Original-To: apmail-cassandra-user-archive@www.apache.org Delivered-To: apmail-cassandra-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id BED25DD13 for ; Wed, 28 Nov 2012 18:22:08 +0000 (UTC) Received: (qmail 92689 invoked by uid 500); 28 Nov 2012 18:22:06 -0000 Delivered-To: apmail-cassandra-user-archive@cassandra.apache.org Received: (qmail 92665 invoked by uid 500); 28 Nov 2012 18:22:06 -0000 Mailing-List: contact user-help@cassandra.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@cassandra.apache.org Delivered-To: mailing list user@cassandra.apache.org Received: (qmail 92657 invoked by uid 99); 28 Nov 2012 18:22:06 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 28 Nov 2012 18:22:06 +0000 X-ASF-Spam-Status: No, hits=1.5 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of bdeggleston@gmail.com designates 209.85.216.172 as permitted sender) Received: from [209.85.216.172] (HELO mail-qc0-f172.google.com) (209.85.216.172) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 28 Nov 2012 18:22:01 +0000 Received: by mail-qc0-f172.google.com with SMTP id b25so10796054qca.31 for ; Wed, 28 Nov 2012 10:21:40 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; bh=/zsbWaZ/MKtDm/T4STYl3IZc6ia20/3p+th3UQ/Zc1Q=; b=vIMU0oknHDifZ+EcZ7Mgv4GhlWeoE7QLqV8SmKS2UMpudlXQdoD/cVKAgn42/I2grA EukFrcp/5GTIVgCM6PjDgYN4vJKE0vvgziSmUNCOoPVr40LnzUCIaVGxbEQu5O/fFoRh elXEn3/oZjgiZn2DIwsWfEvbtNE6UVAFUO/aFnX13aDQddLTgpOAbmv8xz3XhZJUvWoN VaKPGH1tVwIINVdqQzjGnY9BzvK8a3P80e6cxLKfKZs+EQAw/vbL80lfYzmQ+KhtPAQo GUUg5B8bzVglh8+l9TykuiAtc9z2ztCQN8zNV5F8jygDuYqdLbbF2sPL7+e4sl2uPyCE x7OA== MIME-Version: 1.0 Received: by 10.224.168.80 with SMTP id t16mr21930824qay.8.1354126900579; Wed, 28 Nov 2012 10:21:40 -0800 (PST) Received: by 10.49.81.211 with HTTP; Wed, 28 Nov 2012 10:21:40 -0800 (PST) In-Reply-To: References: Date: Wed, 28 Nov 2012 10:21:40 -0800 Message-ID: Subject: Re: How to query secondary indexes From: Blake Eggleston To: user@cassandra.apache.org Content-Type: multipart/alternative; boundary=20cf3074b298e3ccf004cf923c62 X-Virus-Checked: Checked by ClamAV on apache.org --20cf3074b298e3ccf004cf923c62 Content-Type: text/plain; charset=ISO-8859-1 You're going to have a problem doing this in a single query because you're asking cassandra to select a non-contiguous set of rows. Also, to my knowledge, you can only use non equal operators on clustering keys. The best solution I could come up with would be to define you table like so: CREATE TABLE room_data ( room_id uuid, in_room int, temp float, time timestamp, PRIMARY KEY (room_id, in_room, temp)); Then run 2 queries: SELECT * FROM room_data WHERE in_room > 7; SELECT * FROM room_data WHERE temp > 50.0; And do an intersection on the results. I should add the disclaimer that I am relatively new to CQL, so there may be a better way to do this. Blake On Wed, Nov 28, 2012 at 10:02 AM, Oren Karmi wrote: > Hi, > > According to the documentation on Indexes ( > http://www.datastax.com/docs/1.1/ddl/indexes ), > in order to use WHERE on a column which is not part of my key, I must > define a secondary index on it. However, I can only use equality comparison > on it but I wish to use other comparisons methods like greater than. > > Let's say I have a room with people and every timestamp, I measure > the temperature of the room and number of people. I use the timestamp as my > key and I want to select all timestamps where temperature was over 50 > degrees but I can't seem to be able to do it with a regular query even if I > define that column as a secondary index. > SELECT * FROM MyTable WHERE temp > 50.4571; > > My lame workaround is to define a secondary index on NumOfPeopleInRoom and > than for a specific value > SELECT * FROM MyTable WHERE NumOfPeopleInRoom = 7 AND temp > 50.4571; > > I'm pretty sure this is not the proper way for me to do this. > > How should I attack this? It feels like I'm missing a very basic concept. > I'd appreciate it if your answers include also the option of not changing > my schema. > > Thanks!!! > --20cf3074b298e3ccf004cf923c62 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable You're going to have a problem doing this in a single query because you= 're asking cassandra to select a non-contiguous set of rows. Also, to m= y knowledge, you can only use non equal operators on clustering keys. The b= est solution I could come up with would be to define you table like so:
CREATE TABLE room_data (
room_id uuid,
i= n_room int,
temp float,
time timestamp,
PRIMA= RY KEY (room_id, in_room, temp));

Then run 2 queri= es:
SELECT * FROM room_data WHERE in_room > 7;
SELECT * FROM = room_data WHERE temp > 50.0;

And do an intersec= tion on the results.

I should add the disclaimer t= hat I am relatively new to CQL, so there may be a better way to do this.

Blake


On Wed, Nov 28, 2012 at 10:02 AM, Oren Karmi <okarmi@gmai= l.com> wrote:
Hi,

Acco= rding to the=A0documentation=A0on Indexes (=A0http://www.datastax.com/docs/1.1/ddl/indexes ),
in order to use WHER= E on a column which is not part of my key, I must define a secondary index = on it. However, I can only use equality comparison on it but I wish to use = other comparisons methods like greater than.

Let's say I hav= e a room with people and every timestamp, I measure the=A0temperature=A0of = the room and number of people. I use the timestamp as my key and I want to = select all timestamps where temperature was over 50 degrees but I can't= seem to be able to do it with a regular query even if I define that column= as a secondary index.
SELECT * FROM My= Table WHERE temp > 50.4571;

My lame workaround is to define a secondary index on NumOfPeopleInRoom = and than for a specific value
SELECT * FROM MyTabl= e WHERE=A0NumOfPeo= pleInRoom=A0=3D 7 = AND=A0temp > 50= .4571;

I= 'm=A0pretty=A0sure this is not the proper way for me to do this.=

How shou= ld I attack this? It feels like I'm missing a very basic concept.
I'd=A0apprec= iate=A0it if your answers include also the option of not changing my schema= .

Thanks!!= !

--20cf3074b298e3ccf004cf923c62--