cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "wxn002@zjqunshuo.com" <wxn...@zjqunshuo.com>
Subject Re: time series data model
Date Thu, 20 Oct 2016 09:21:19 GMT
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
kurt@instaclustr.com
www.instaclustr.com

On 20 October 2016 at 08:17, Vladimir Yudovin <vladyu@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<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