cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kurt Greaves <k...@instaclustr.com>
Subject Re: time series data model
Date Thu, 20 Oct 2016 08:18:36 GMT
If event_time is timestamps since unix epoch you 1. may want to use the
in-built timestamps type, and 2. order by event_time DESC. 2 applies if you
want to do queries such as "select * from eventdata where ... and
event_time > x" (i.e; get latest events).

Other than that your model seems workable, I assume you're using DTCS/TWCS,
and aligning the time windows to your day bucket. (If not you should do
that)

Kurt Greaves
kurt@instaclustr.com
www.instaclustr.com

On 20 October 2016 at 07:29, wxn002@zjqunshuo.com <wxn002@zjqunshuo.com>
wrote:

> Hi All,
> I'm trying to migrate my time series data which is GPS trace from mysql to
> C*. I want a wide row to hold one day data. I designed the data model as
> below. Please help to see if there is any problem. Any suggestion is
> appreciated.
>
> Table Model:
> CREATE TABLE cargts.eventdata (
>     deviceid int,
>     date int,
>     event_time bigint,
>     position text,
>     PRIMARY KEY ((deviceid, date), event_time)
> )
>
> A slice of data:
> cqlsh:cargts> SELECT * FROM eventdata WHERE deviceid =
> 186628 and date = 20160928 LIMIT 10;
>
>  deviceid | date     | event_time    | position
> ----------+----------+---------------+----------------------
> -------------------------------------------------------------------
>    186628 | 20160928 | 1474992002000 |  {"latitude":
> 30.343443936386247,"longitude":120.08751351828943,"speed":41,"heading":48}
>    186628 | 20160928 | 1474992012000 |   {"latitude":
> 30.34409508979662,"longitude":120.08840022183352,"speed":45,"heading":53}
>    186628 | 20160928 | 1474992022000 |   {"latitude":
> 30.34461639856887,"longitude":120.08946100336443,"speed":28,"heading":65}
>    186628 | 20160928 | 1474992032000 |   {"latitude":
> 30.34469478717028,"longitude":120.08973154015409,"speed":11,"heading":67}
>    186628 | 20160928 | 1474992042000 |   {"latitude":
> 30.34494998929474,"longitude":120.09027263811151,"speed":19,"heading":47}
>    186628 | 20160928 | 1474992052000 | {"latitude":
> 30.346057349126617,"longitude":120.08967091817931,"speed":
> 41,"heading":323}
>    186628 | 20160928 | 1474992062000 |    {"latitude"
> :30.346997145708,"longitude":120.08883508853253,"speed":52,"heading":323}
>    186628 | 20160928 | 1474992072000 | {"latitude":
> 30.348131044340988,"longitude":120.08774702315581,"speed":
> 65,"heading":321}
>    186628 | 20160928 | 1474992082000 | {"latitude":
> 30.349438164412838,"longitude":120.08652612959328,"speed":
> 68,"heading":322}
>
> -Simon Wu
>

Mime
View raw message