Hi Kurt,
I do need to align the time windows to day bucket to prevent one row become too
big, and event_time is timestamp since unix epoch. If I use bigint as type of
event_time, can I do queries as you mentioned?
-Simon Wu
From: kurt Greaves
Date: 2016-10-20 16:18
To: user
Subject: Re: time series data model
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
k...@instaclustr.com
www.instaclustr.com
On 20 October 2016 at 07:29, wxn...@zjqunshuo.com <wxn...@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
