Thank you Kurt, I thought the one column which was identified by the compsite
key(deviceId+date+event_time) can hold only one value, so I packaged all info
into one JSON. Maybe I'm wrong. I rewrite the table as below.
CREATE TABLE cargts.eventdata (
deviceid int,
date int,
event_time bigint,
heading int,
lat decimal,
lon decimal,
speed int,
PRIMARY KEY ((deviceid, date), event_time)
)
cqlsh:cargts> select * from eventdata;
deviceid | date | event_time | heading | lat | lon | speed
----------+----------+---------------+---------+-----------+------------+-------
186628 | 20160928 | 1474992002005 | 48 | 30.343443 | 120.087514 | 41
-Simon Wu
From: kurt Greaves
Date: 2016-10-20 16:23
To: user
Subject: Re: time series data model
Ah didn't pick up on that but looks like he's storing JSON within position. Is
there any strong reason for this or as Vladimir mentioned can you store the
fields under "position" in separate columns?
Kurt Greaves
k...@instaclustr.com
www.instaclustr.com
On 20 October 2016 at 08:17, Vladimir Yudovin <vla...@winguzone.com> wrote:
Hi Simon,
Why position is text and not float? Text takes much more place.
Also speed and headings can be calculated basing on latest positions, so you
can also save them. If you really need it in data base you can save them as
floats, or compose single float value like speed.heading: 41.173 (or opposite,
heading.speed) and save column storage overhead.
Best regards, Vladimir Yudovin,
Winguzone - Hosted Cloud Cassandra
Launch your cluster in minutes.
---- On Thu, 20 Oct 2016 03:29:16 -0400<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
