Mailing-List: contact user-help@cassandra.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cassandra.apache.org
Received-SPF: pass (nike.apache.org: domain of doanduyhai@gmail.com designates
 209.85.214.177 as permitted sender)
MIME-Version: 1.0
In-Reply-To: 
 <CADQfObWiJpU=waT5XOW+fo+sLkr0OzSweOQmKp82jbVcf5CMXw@mail.gmail.com>
References: 
 <CADQfObWiJpU=waT5XOW+fo+sLkr0OzSweOQmKp82jbVcf5CMXw@mail.gmail.com>
Date: Mon, 14 Jul 2014 09:45:25 +0200
Message-ID: 
 <CABNXB2DTGssWMC_zLF02GozDTbUq+NDWogOrU4ATtw+SeUhNpg@mail.gmail.com>
Subject: Re: Multi-column range scans
From: DuyHai Doan <doanduyhai@gmail.com>
To: user@cassandra.apache.org
Content-Type: multipart/alternative; boundary=089e0158b09e6647d804fe227975

--089e0158b09e6647d804fe227975
Content-Type: text/plain; charset=UTF-8

Hello Mathew

 Since Cassandra 2.0.6 it is possible to query over composites:
https://issues.apache.org/jira/browse/CASSANDRA-4851

For your example:

select * from skill_count where skill='Complaints' and
(interval_id,skill_level) >= (1402359300000,5) and interval_id <
1402359900000;


On Mon, Jul 14, 2014 at 6:09 AM, Matthew Allen <matthew.j.allen@gmail.com>
wrote:

> Hi,
>
> We have a roll-up table that as follows.
>
> CREATE TABLE SKILL_COUNT (
>   skill text,
>   interval_id bigint,
>   skill_level int,
>   skill_count int,
>   PRIMARY KEY (skill, interval_id, skill_level));
>
> Essentially,
>   skill = a names skill i.e. "Complaints"
>   interval_id = a rounded epoch time (15 minute intervals)
>   skill_level = a number/rating from 1-10
>   skill_count = the number of people with the specified skill, with the
> specified skill level, logged in at the interval_id
>
> We'd like to run the following query against it
>
> select * from skill_count where skill='Complaints' and interval_id >=
> 1402359300000 and interval_id < 1402359900000 and skill_level >= 5;
>
> to get a count of people with the relevant skill and level at the
> appropriate time.  However I am getting the following message.
>
> Bad Request: PRIMARY KEY part skill_level cannot be restricted (preceding
> part interval_id is either not restricted or by a non-EQ relation)
>
> Looking at how the data is stored ...
>
> -------------------
> RowKey: Complaints
> => (name=1402359300000:2:, value=, timestamp=1405308260403000)
> => (name=1402359300000:2:skill_count, value=0000000a,
> timestamp=1405308260403000)
> => (name=1402359300000:5:, value=, timestamp=1405308260403001)
> => (name=1402359300000:5:skill_count, value=00000014,
> timestamp=1405308260403001)
> => (name=1402359300000:8:, value=, timestamp=1405308260419000)
> => (name=1402359300000:8:skill_count, value=0000001e,
> timestamp=1405308260419000)
> => (name=1402359300000:10:, value=, timestamp=1405308260419001)
> => (name=1402359300000:10:skill_count, value=00000001,
> timestamp=1405308260419001)
>
> Should cassandra be able to allow for an extra level of filtering ? or is
> this something that should be performed from within the application.
>
> We have a solution working in Oracle, but would like to store this data in
> Cassandra, as all the other data that this solution relies on already sits
> within Cassandra.
>
> Appreciate any guidance on this matter.
>
> Matt
>

--089e0158b09e6647d804fe227975
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div><div>Hello Mathew<br><br></div>=C2=A0Since Cassandra =
2.0.6 it is possible to query over composites: <a href=3D"https://issues.ap=
ache.org/jira/browse/CASSANDRA-4851">https://issues.apache.org/jira/browse/=
CASSANDRA-4851</a><br>
<br></div>For your example: <br><br>select * from skill_count where skill=
=3D&#39;Complaints&#39; and (interval_id,skill_level) &gt;=3D
 (1402359300000,5) and interval_id &lt; 1402359900000;<br></div><div class=
=3D"gmail_extra"><br><br><div class=3D"gmail_quote">On Mon, Jul 14, 2014 at=
 6:09 AM, Matthew Allen <span dir=3D"ltr">&lt;<a href=3D"mailto:matthew.j.a=
llen@gmail.com" target=3D"_blank">matthew.j.allen@gmail.com</a>&gt;</span> =
wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex"><div dir=3D"ltr"><div>Hi,<br><br></div>We ha=
ve a roll-up table that as follows.<br><div><br>CREATE TABLE SKILL_COUNT (<=
br>
=C2=A0 skill text,<br>=C2=A0 interval_id bigint,<br>=C2=A0 skill_level int,=
<br>=C2=A0 skill_count int,<br>=C2=A0 PRIMARY KEY (skill, interval_id, skil=
l_level));<br>
<br></div><div>Essentially,<br></div><div>=C2=A0 skill =3D a names skill i.=
e. &quot;Complaints&quot;<br></div><div>=C2=A0 interval_id =3D a rounded ep=
och time (15 minute intervals)<br></div><div>=C2=A0 skill_level =3D a numbe=
r/rating from 1-10<br>

</div><div>=C2=A0 skill_count =3D the number of people with the specified s=
kill, with the specified skill level, logged in at the interval_id<br><br><=
/div><div>We&#39;d like to run the following query against it<br><br>select=
 * from skill_count where skill=3D&#39;Complaints&#39; and interval_id &gt;=
=3D 1402359300000 and interval_id &lt; 1402359900000 and skill_level &gt;=
=3D 5;<br>

<br></div><div>to get a count of people with the relevant skill and level a=
t the appropriate time.=C2=A0 However I am getting the following message.<b=
r><br>Bad Request: PRIMARY KEY part skill_level cannot be restricted (prece=
ding part interval_id is either not restricted or by a non-EQ relation)<br>

</div><div><br></div><div>Looking at how the data is stored ...<br><br>----=
---------------<br>RowKey: Complaints<br>=3D&gt; (name=3D1402359300000:2:, =
value=3D, timestamp=3D1405308260403000)<br>=3D&gt; (name=3D1402359300000:2:=
skill_count, value=3D0000000a, timestamp=3D1405308260403000)<br>

=3D&gt; (name=3D1402359300000:5:, value=3D, timestamp=3D1405308260403001)<b=
r>=3D&gt; (name=3D1402359300000:5:skill_count, value=3D00000014, timestamp=
=3D1405308260403001)<br>=3D&gt; (name=3D1402359300000:8:, value=3D, timesta=
mp=3D1405308260419000)<br>

=3D&gt; (name=3D1402359300000:8:skill_count, value=3D0000001e, timestamp=3D=
1405308260419000)<br>=3D&gt; (name=3D1402359300000:10:, value=3D, timestamp=
=3D1405308260419001)<br>=3D&gt; (name=3D1402359300000:10:skill_count, value=
=3D00000001, timestamp=3D1405308260419001)<br>

<br></div><div>Should cassandra be able to allow for an extra level of filt=
ering ? or is this something that should be performed from within the appli=
cation.<br><br>We have a solution working in Oracle, but would like to stor=
e this data in Cassandra, as all the other data that this solution relies o=
n already sits within Cassandra.<br>

<br></div><div>Appreciate any guidance on this matter.<br><br></div><div>Ma=
tt<br></div></div>
</blockquote></div><br></div>

--089e0158b09e6647d804fe227975--