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 aventurella@gmail.com
 designates 74.125.82.169 as permitted sender)
MIME-Version: 1.0
Date: Fri, 1 Mar 2013 08:16:19 -0800
Message-ID: 
 <CAP-hOzmViYbUkxF=jfMzX2cRz8a+5GWWFzapgR8goLkdRjwdKg@mail.gmail.com>
Subject: CQL3 to ORDER BY Verification
From: Adam Venturella <aventurella@gmail.com>
To: "user@cassandra.apache.org" <user@cassandra.apache.org>
Content-Type: multipart/alternative; boundary=089e01177913d4028b04d6df538f

--089e01177913d4028b04d6df538f
Content-Type: text/plain; charset=ISO-8859-1

My ColumnFamily is defined as follows:


CREATE TABLE UserProfileHistory(
    username text,
    timestamp bigint, -- millis since epoch
    data text, -- JSON
    PRIMARY KEY (username, timestamp)
) WITH CLUSTERING ORDER BY (timestamp DESC);


Each insert on the username adds to the wide row. The most recent profile
history being able to be retrieved by

SELECT * FROM UserProfileHistory WHERE username=:username LIMIT 1;

For some reporting needs I need to fetch the entire history, and I need to
do it in ASC order instead of DESC.

One option is to do the sorting in code, collect N results, sort on the
timestamps accordingly. Given the row is of N length, that could start to
put an undo memory burden in my application layer, and I would like to
avoid that if possible opting instead for Cassandra to perform the work.


So I am leaning towards this option:

2) min timestamp seek + ORDER BY

To start the process my initial timestamp would be 01-01-1970T12:00:00+0000
(assume that is in milliseconds, aka 0) I would then issue my query:

SELECT * FROM UserProfileHistory WHERE username=:username AND timestamp >
:milliseconds ORDER BY timestamp ASC LIMIT 100

Once I have those initial results I would just pick my last timestamp from
the result set and + 1 on it  and run the query again until I received 0
results.


The CQL works and returns my results as I expect. This will probably only
be run once every 24 hours, maybe every 12 hours; point being, not often.

Am I setting myself up for a disaster down the line?

--089e01177913d4028b04d6df538f
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">My ColumnFamily is defined as follows:<div><br></div><div>=
<br></div><div><div>CREATE TABLE UserProfileHistory(</div><div>=A0 =A0 user=
name text,</div><div>=A0 =A0 timestamp bigint, -- millis since epoch</div><=
div>=A0 =A0 data text, -- JSON</div>
<div>=A0 =A0 PRIMARY KEY (username, timestamp)</div><div>) WITH CLUSTERING =
ORDER BY (timestamp DESC);</div></div><div><br></div><div><br></div><div st=
yle>Each insert on the username adds to the wide row. The most recent profi=
le history being able to be retrieved by=A0</div>
<div style><br></div><div style>SELECT * FROM UserProfileHistory WHERE user=
name=3D:username LIMIT 1;</div><div style><br></div><div style>For some rep=
orting needs I need to fetch the entire history, and I need to do it in ASC=
 order instead of DESC.</div>
<div style><br></div><div style>One option is to do the sorting in code, co=
llect N results, sort on the timestamps accordingly. Given the row is of N =
length, that could start to put an undo memory burden in my application lay=
er, and I would like to avoid that if possible opting instead for Cassandra=
 to perform the work.</div>
<div style><br></div><div style><br></div><div style>So I am leaning toward=
s this option:</div><div style><br></div><div style>2) min timestamp seek +=
 ORDER BY</div><div style><br></div><div style>To start the process my init=
ial timestamp would be 01-01-1970T12:00:00+0000 (assume that is in millisec=
onds, aka 0) I would then issue my query:</div>
<div style><br></div><div style>SELECT * FROM UserProfileHistory WHERE user=
name=3D:username AND timestamp &gt; :milliseconds ORDER BY timestamp ASC LI=
MIT 100<br></div><div style><br></div><div style>Once I have those initial =
results I would just pick my last timestamp from the result set and + 1 on =
it =A0and run the query again until I received 0 results.</div>
<div style><br></div><div style><br></div><div style>The CQL works and retu=
rns my results as I expect. This will probably only be run once every 24 ho=
urs, maybe every 12 hours; point being, not often.</div><div style><br>
</div><div style>Am I setting myself up for a disaster down the line?=A0</d=
iv></div>

--089e01177913d4028b04d6df538f--