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 ailinykh@gmail.com designates
 209.85.217.172 as permitted sender)
MIME-Version: 1.0
In-Reply-To: 
 <CACXUSP4nrPKxO8NjuuuyjDWVzoZxrL_p=gjMUTj_aJgnxyrmXg@mail.gmail.com>
References: 
 <CACXUSP4nrPKxO8NjuuuyjDWVzoZxrL_p=gjMUTj_aJgnxyrmXg@mail.gmail.com>
Date: Tue, 11 Dec 2012 09:45:38 -0800
Message-ID: 
 <CAK0tFt4A5PdEs9UU6Kzg9we6hGhXxXO4-cFaaL2ESduQOO_Pcw@mail.gmail.com>
Subject: Re: Selecting rows efficiently from a Cassandra CF containing time
 series data
From: Andrey Ilinykh <ailinykh@gmail.com>
To: user@cassandra.apache.org
Content-Type: multipart/alternative; boundary=bcaec554de46f95dd804d0973fbf

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

I would consider to use wide rows. If you add timestamp to your column name
you have naturally sorted data. You can easily select any time range
without any indexes.

Thank you,
  Andrey


On Tue, Dec 11, 2012 at 6:23 AM, Chin Ko <cko2223@gmail.com> wrote:

> I would like to get some opinions on how to select an incremental range of
> rows efficiently from a Cassandra CF containing time series data.
>
> Background:
> We have a web application that uses a Cassandra CF as logging storage. We
> insert a row into the CF for every "event" of each user of the web
> application. The row key is timestamp+userid. The column values are
> unstructured data. We only insert rows but never update or delete any rows
> in the CF.
>
> Data volume:
> The CF grows by about 0.5 million rows per day. We have a 4 node cluster
> and use the RandomPartitioner to spread the rows across the nodes.
>
> Requirements:
> There is a need to transfer the Cassandra data to another relational
> database periodically. Due to the large size of the CF, instead of
> truncating the relational table and reloading all rows into it each time,
> we plan to run a job to select the "delta" rows since the last run and
> insert them into the relational database.
>
> We would like to have some flexibility in how often the data transfer job
> is done. It may be run several times each day, or it may be not run at all
> on a day.
>
> Options considered:
> - We are using RandomPartitioner, so range scan by row key is not feasible.
> - Add a secondary index on the timestamp column, but reading rows via
> secondary index still requires an equality condition and does not support
> range scan.
> - Add a secondary index on a column containing the date and hour of the
> timestamp. Iterate each hour between the time job was last run and now.
> Fetch all rows of each hour.
>
> I would appreciate any ideas of other design options of the Cassandra CF
> to enable extracting the rows efficiently.
>
> Besides Java, has anyone used any ETL tools to do this kind of delta
> extraction from Cassandra?
>
> Thanks,
> Chin

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

I would consider to use wide rows. If you add timestamp to your column name=
 you have naturally sorted data. You can easily select any time range witho=
ut any indexes.<div><br></div><div>Thank you,</div><div>=A0 Andrey</div><di=
v class=3D"gmail_extra">
<br><br><div class=3D"gmail_quote">On Tue, Dec 11, 2012 at 6:23 AM, Chin Ko=
 <span dir=3D"ltr">&lt;<a href=3D"mailto:cko2223@gmail.com" target=3D"_blan=
k">cko2223@gmail.com</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex=
">
I would like to get some opinions on how to select an incremental range=20
of rows efficiently from a Cassandra CF containing time series data.<br><br=
>Background:<br>We
 have a web application that uses a Cassandra CF as logging storage. We=20
insert a row into the CF for every &quot;event&quot; of each user of the we=
b=20
application. The row key is timestamp+userid. The column values are=20
unstructured data. We only insert rows but never update or delete any=20
rows in the CF. <br>
<br>Data volume:<br>The CF grows by about 0.5 million rows per day. We=20
have a 4 node cluster and use the RandomPartitioner to spread the rows=20
across the nodes.<br><br>Requirements:<br>There is a need to transfer=20
the Cassandra data to another relational database periodically. Due to=20
the large size of the CF, instead of truncating the relational table and
 reloading all rows into it each time, we plan to run a job to select=20
the &quot;delta&quot; rows since the last run and insert them into the rela=
tional=20
database.<br>
<br>We would like to have some flexibility in how often the data=20
transfer job is done. It may be run several times each day, or it may be
 not run at all on a day.<br><br>Options considered:<br>- We are using Rand=
omPartitioner, so range scan by row key is not feasible.<br>
- Add a secondary index on the timestamp column, but reading rows via=20
secondary index still requires an equality condition and does not=20
support range scan.<br>- Add a secondary index on a column containing=20
the date and hour of the timestamp. Iterate each hour between the time=20
job was last run and now. Fetch all rows of each hour.<br>
<br>I would appreciate any ideas of other design options of the Cassandra C=
F to enable extracting the rows efficiently.<br><br>Besides Java, has anyon=
e used any ETL tools to do this kind of delta extraction from Cassandra?<br=
>


<br>Thanks,<br>Chin
</blockquote></div><br></div>

--bcaec554de46f95dd804d0973fbf--