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
>
