Mailing-List: contact user-help@cassandra.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cassandra.apache.org
Received-SPF: pass (athena.apache.org: domain of ajay.garga@gmail.com
 designates 209.85.215.46 as permitted sender)
MIME-Version: 1.0
Date: Sat, 10 Jan 2015 09:42:01 +0530
Message-ID: 
 <CAO_GmeKKaagdLNqpQWKk=2VBPaSS-VtGQ3XR3134gkYB+hbmeQ@mail.gmail.com>
Subject: Re: Cassandra primary key design to cater range query
From: Ajay <ajay.garga@gmail.com>
To: user@cassandra.apache.org
Content-Type: multipart/alternative; boundary=001a11c32cf49deddf050c447997

--001a11c32cf49deddf050c447997
Content-Type: text/plain; charset=UTF-8

Hi,

I read somewhere that the order of columns in the cluster key matters.
Please correct me if I am wrong.

For example,

PRIMARY KEY((prodgroup), status, productid).

Then the below query cannot run,

select * from product where prodgroup='xyz' and prodid > 0

But this query can be run:
select * from product where prodgroup='xyz' and prodid > 0 and status = 0

It means all the preceding part of the clustering key has to be provided in
the query. So with that, if you want to query "Get details of a specific
product"(either active or inactive), you might need to reorder the columns
like PRIMARY KEY((prodgroup), productid, status).

Thanks
Ajay


On Sat, Jan 10, 2015 at 6:03 AM, Tyler Hobbs <tyler@datastax.com> wrote:

> Your proposed model for the table to handle the last query looks good, so
> I would stick with that.
>
> On Mon, Jan 5, 2015 at 5:45 AM, Nagesh <nageswara.raov@gmail.com> wrote:
>
>> Hi All,
>>
>> I have designed a column family
>>
>> prodgroup text, prodid int, status int, , PRIMARY KEY ((prodgroup),
>> prodid, status)
>>
>> The data model is to cater
>>
>>    - Get list of products from the product group
>>    - get list of products for a given range of ids
>>    - Get details of a specific product
>>    - Update status of the product acive/inactive
>>    - Get list of products that are active or inactive (select * from
>>    product where prodgroup='xyz' and prodid > 0 and status = 0)
>>
>> The design works fine, except for the last query . Cassandra not allowing
>> to query on status unless I fix the product id. I think defining a super
>> column family which has the key "PRIMARY KEY((prodgroup), staus,
>> productid)" should work. Would like to get expert advice on other
>> alternatives.
>> --
>> Thanks,
>> Nageswara Rao.V
>>
>> *"The LORD reigns"*
>>
>
>
>
> --
> Tyler Hobbs
> DataStax <http://datastax.com/>
>

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

<div dir=3D"ltr"><div><div><div><div><div><div>Hi,<br><br></div>I read some=
where that the order of columns in the cluster key matters. Please correct =
me if I am wrong.<br><br>For example,<br><br>PRIMARY=20
KEY((prodgroup), status, productid).<br><br></div>Then the below query cann=
ot run,<br><br>select * from product where prodgroup=3D&#39;xyz&#39; and pr=
odid &gt; 0 <br><br></div>But this query can be run:<br>select * from produ=
ct where prodgroup=3D&#39;xyz&#39; and prodid &gt; 0 and status =3D 0<br><b=
r></div>It means all the preceding part of the clustering key has to be pro=
vided in the query. So with that, if you want to query &quot;Get details of=
 a specific product&quot;(either active or inactive), you might need to reo=
rder the columns like PRIMARY=20
KEY((prodgroup), productid, status).<br><br></div>Thanks<br></div>Ajay<br><=
br><div class=3D"gmail_extra"><br><div class=3D"gmail_quote">On Sat, Jan 10=
, 2015 at 6:03 AM, Tyler Hobbs <span dir=3D"ltr">&lt;<a href=3D"mailto:tyle=
r@datastax.com" target=3D"_blank">tyler@datastax.com</a>&gt;</span> wrote:<=
br><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left=
:1px #ccc solid;padding-left:1ex"><div dir=3D"ltr">Your proposed model for =
the table to handle the last query looks good, so I would stick with that.<=
br></div><div class=3D"gmail_extra"><div><div class=3D"h5"><br><div class=
=3D"gmail_quote">On Mon, Jan 5, 2015 at 5:45 AM, Nagesh <span dir=3D"ltr">&=
lt;<a href=3D"mailto:nageswara.raov@gmail.com" target=3D"_blank">nageswara.=
raov@gmail.com</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_quote" s=
tyle=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div=
 dir=3D"ltr">Hi All,<br><p>I have designed a column family</p>

<p>prodgroup text, prodid int, status int, ,
PRIMARY KEY ((prodgroup), prodid, status)</p>

<p>The data model is to cater</p>

<ul><li>Get list of products from the product group</li><li>get list of pro=
ducts for a given range of ids</li><li>Get details of a specific product</l=
i><li>Update status of the product acive/inactive </li><li>Get list of prod=
ucts that are active or inactive (select * from product where prodgroup=3D&=
#39;xyz&#39; and prodid &gt; 0 and status =3D 0)</li></ul>

<p>The design works fine, except for the last query . Cassandra not=20
allowing to query on status unless I fix the product id. I think=20
defining a super column family which has the key &quot;PRIMARY=20
KEY((prodgroup), staus, productid)&quot; should work. Would like to get=20
expert advice on other alternatives.</p><span><font color=3D"#888888">-- <b=
r><div><div dir=3D"ltr"><div>Thanks,<br>Nageswara Rao.V<br><br></div><i>&qu=
ot;The LORD reigns&quot;</i><br></div></div>
</font></span></div>
</blockquote></div><br><br clear=3D"all"><br></div></div><span class=3D"HOE=
nZb"><font color=3D"#888888">-- <br><div><font color=3D"#888888">Tyler Hobb=
s<span></span><br>
<a href=3D"http://datastax.com/" target=3D"_blank">DataStax</a><br></font><=
/div>
</font></span></div>
</blockquote></div><br></div></div>

--001a11c32cf49deddf050c447997--