Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id 4E77C200BA3 for ; Thu, 20 Oct 2016 11:23:01 +0200 (CEST) Received: by cust-asf.ponee.io (Postfix) id 4D40E160ADB; Thu, 20 Oct 2016 09:23:01 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 48BEA160AF5 for ; Thu, 20 Oct 2016 11:23:00 +0200 (CEST) Received: (qmail 2645 invoked by uid 500); 20 Oct 2016 09:22:52 -0000 Mailing-List: contact user-help@cassandra.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@cassandra.apache.org Delivered-To: mailing list user@cassandra.apache.org Received: (qmail 2635 invoked by uid 99); 20 Oct 2016 09:22:52 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd4-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 20 Oct 2016 09:22:52 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd4-us-west.apache.org (ASF Mail Server at spamd4-us-west.apache.org) with ESMTP id 9BA06C0C0A for ; Thu, 20 Oct 2016 09:22:51 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd4-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 3.009 X-Spam-Level: *** X-Spam-Status: No, score=3.009 tagged_above=-999 required=6.31 tests=[HTML_MESSAGE=2, KAM_LAZY_DOMAIN_SECURITY=1, RCVD_IN_DNSWL_NONE=-0.0001, RCVD_IN_MSPIKE_H2=-0.001, T_KAM_HTML_FONT_INVALID=0.01] autolearn=disabled Received: from mx1-lw-us.apache.org ([10.40.0.8]) by localhost (spamd4-us-west.apache.org [10.40.0.11]) (amavisd-new, port 10024) with ESMTP id rNmnuHVT6h2e for ; Thu, 20 Oct 2016 09:22:39 +0000 (UTC) Received: from smtpbgbr2.qq.com (smtpbgbr2.qq.com [54.207.22.56]) by mx1-lw-us.apache.org (ASF Mail Server at mx1-lw-us.apache.org) with ESMTPS id 40AAF5F342 for ; Thu, 20 Oct 2016 09:22:34 +0000 (UTC) X-QQ-mid: bizesmtp14t1476955280trlqdej8 Received: from simon-pc-dell (unknown [115.206.114.140]) by esmtp4.qq.com (ESMTP) with SMTP id 0 for ; Thu, 20 Oct 2016 17:21:20 +0800 (CST) X-QQ-SSF: 01100000002000F0F620B00A0000000 X-QQ-FEAT: HLwgFh8nePoEOCF8Gd/sgEqwVnOGDJfciWymo9AyIM0Y9/0QjNS5HET1gxy7Y K87CbUjJsH89DHjA772ZdAtJoSYU1QVJvaA9xnXSy/gMGhnALiIRgjlEMJ6PFMYjfglnSvs Z05TOirtOrg9xcNM+z0Pl5CXZPfg/NTIvlVlEXcGk2PFVqtO/VndGJPVxGrIDL3qFxyO0DD 7t7fKRE4DsHJwK43xrVLbA2BEJqHsi6T91mmgrijWOzBRmmY+6mtFZ1cbNfxyoz1SaFCC4S d4jS5b6n2UMSWbxHWt2rtrsYwDEdP1hePIOO33ugkB3Ttx X-QQ-GoodBg: 0 Date: Thu, 20 Oct 2016 17:21:19 +0800 From: "wxn002@zjqunshuo.com" To: user Subject: Re: time series data model References: <2016102015291551893711@zjqunshuo.com>, <157e12aac39.d265f444110574.1328628264391040099@winguzone.com>, X-Priority: 3 X-GUID: B9BA51FC-9125-406E-8570-2183D06F2589 X-Has-Attach: no X-Mailer: Foxmail 7, 2, 7, 26[cn] Mime-Version: 1.0 Message-ID: <2016102017211858935735@zjqunshuo.com> Content-Type: multipart/alternative; boundary="----=_001_NextPart460862703216_=----" X-QQ-SENDSIZE: 520 X-QQ-Bgrelay: 1 archived-at: Thu, 20 Oct 2016 09:23:01 -0000 This is a multi-part message in MIME format. ------=_001_NextPart460862703216_=---- Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: base64 VGhhbmsgeW91IEt1cnQsIEkgdGhvdWdodCB0aGUgb25lIGNvbHVtbiB3aGljaCB3YXMgaWRlbnRp ZmllZCBieSB0aGUgY29tcHNpdGUga2V5KGRldmljZUlkK2RhdGUrZXZlbnRfdGltZSkgY2FuIGhv bGQgb25seSBvbmUgdmFsdWUsIHNvIEkgcGFja2FnZWQgYWxsIGluZm8gaW50byBvbmUgSlNPTi4g TWF5YmUgSSdtIHdyb25nLiBJIHJld3JpdGUgdGhlIHRhYmxlIGFzIGJlbG93Lg0KDQpDUkVBVEUg VEFCTEUgY2FyZ3RzLmV2ZW50ZGF0YSAoDQogICAgZGV2aWNlaWQgaW50LA0KICAgIGRhdGUgaW50 LA0KICAgIGV2ZW50X3RpbWUgYmlnaW50LA0KICAgIGhlYWRpbmcgaW50LA0KICAgIGxhdCBkZWNp bWFsLA0KICAgIGxvbiBkZWNpbWFsLA0KICAgIHNwZWVkIGludCwNCiAgICBQUklNQVJZIEtFWSAo KGRldmljZWlkLCBkYXRlKSwgZXZlbnRfdGltZSkNCikNCg0KY3Fsc2g6Y2FyZ3RzPiBzZWxlY3Qg KiBmcm9tIGV2ZW50ZGF0YTsNCg0KIGRldmljZWlkIHwgZGF0ZSAgICAgfCBldmVudF90aW1lICAg IHwgaGVhZGluZyB8IGxhdCAgICAgICB8IGxvbiAgICAgICAgfCBzcGVlZA0KLS0tLS0tLS0tLSst LS0tLS0tLS0tKy0tLS0tLS0tLS0tLS0tLSstLS0tLS0tLS0rLS0tLS0tLS0tLS0rLS0tLS0tLS0t LS0tKy0tLS0tLS0NCiAgIDE4NjYyOCB8IDIwMTYwOTI4IHwgMTQ3NDk5MjAwMjAwNSB8ICAgICAg NDggfCAzMC4zNDM0NDMgfCAxMjAuMDg3NTE0IHwgICAgNDENCg0KLVNpbW9uIFd1DQoNCkZyb206 IGt1cnQgR3JlYXZlcw0KRGF0ZTogMjAxNi0xMC0yMCAxNjoyMw0KVG86IHVzZXINClN1YmplY3Q6 IFJlOiB0aW1lIHNlcmllcyBkYXRhIG1vZGVsDQpBaCBkaWRuJ3QgcGljayB1cCBvbiB0aGF0IGJ1 dCBsb29rcyBsaWtlIGhlJ3Mgc3RvcmluZyBKU09OIHdpdGhpbiBwb3NpdGlvbi4gSXMgdGhlcmUg YW55IHN0cm9uZyByZWFzb24gZm9yIHRoaXMgb3IgYXMgVmxhZGltaXIgbWVudGlvbmVkIGNhbiB5 b3Ugc3RvcmUgdGhlIGZpZWxkcyB1bmRlciAicG9zaXRpb24iIGluIHNlcGFyYXRlIGNvbHVtbnM/ DQoNCkt1cnQgR3JlYXZlcw0Ka3VydEBpbnN0YWNsdXN0ci5jb20NCnd3dy5pbnN0YWNsdXN0ci5j b20NCg0KT24gMjAgT2N0b2JlciAyMDE2IGF0IDA4OjE3LCBWbGFkaW1pciBZdWRvdmluIDx2bGFk eXVAd2luZ3V6b25lLmNvbT4gd3JvdGU6DQpIaSBTaW1vbiwNCg0KV2h5IHBvc2l0aW9uIGlzIHRl eHQgYW5kIG5vdCBmbG9hdD8gVGV4dCB0YWtlcyBtdWNoIG1vcmUgcGxhY2UuDQpBbHNvIHNwZWVk IGFuZCBoZWFkaW5ncyBjYW4gYmUgY2FsY3VsYXRlZCBiYXNpbmcgb24gbGF0ZXN0IHBvc2l0aW9u cywgc28geW91IGNhbiBhbHNvIHNhdmUgdGhlbS4gSWYgeW91IHJlYWxseSBuZWVkIGl0IGluIGRh dGEgYmFzZSB5b3UgY2FuIHNhdmUgdGhlbSBhcyBmbG9hdHMsIG9yIGNvbXBvc2Ugc2luZ2xlIGZs b2F0IHZhbHVlIGxpa2Ugc3BlZWQuaGVhZGluZzogNDEuMTczIChvciBvcHBvc2l0ZSwgaGVhZGlu Zy5zcGVlZCkgYW5kIHNhdmUgY29sdW1uIHN0b3JhZ2Ugb3ZlcmhlYWQuDQoNCg0KQmVzdCByZWdh cmRzLCBWbGFkaW1pciBZdWRvdmluLCANCldpbmd1em9uZSAtIEhvc3RlZCBDbG91ZCBDYXNzYW5k cmENCkxhdW5jaCB5b3VyIGNsdXN0ZXIgaW4gbWludXRlcy4NCg0KDQotLS0tIE9uIFRodSwgMjAg T2N0IDIwMTYgMDM6Mjk6MTYgLTA0MDA8d3huMDAyQHpqcXVuc2h1by5jb20+IHdyb3RlIC0tLS0N Cg0KSGkgQWxsLA0KSSdtIHRyeWluZyB0byBtaWdyYXRlIG15IHRpbWUgc2VyaWVzIGRhdGEgd2hp Y2ggaXMgR1BTIHRyYWNlIGZyb20gbXlzcWwgdG8gQyouIEkgd2FudCBhIHdpZGUgcm93IHRvIGhv bGQgb25lIGRheSBkYXRhLiBJIGRlc2lnbmVkIHRoZSBkYXRhIG1vZGVsIGFzIGJlbG93LiBQbGVh c2UgaGVscCB0byBzZWUgaWYgdGhlcmUgaXMgYW55IHByb2JsZW0uIEFueSBzdWdnZXN0aW9uIGlz IGFwcHJlY2lhdGVkLg0KDQpUYWJsZSBNb2RlbDoNCkNSRUFURSBUQUJMRSBjYXJndHMuZXZlbnRk YXRhICgNCiAgICBkZXZpY2VpZCBpbnQsDQogICAgZGF0ZSBpbnQsDQogICAgZXZlbnRfdGltZSBi aWdpbnQsDQogICAgcG9zaXRpb24gdGV4dCwNCiAgICBQUklNQVJZIEtFWSAoKGRldmljZWlkLCBk YXRlKSwgZXZlbnRfdGltZSkNCikNCg0KQSBzbGljZSBvZiBkYXRhOg0KY3Fsc2g6Y2FyZ3RzPiBT RUxFQ1QgKiBGUk9NIGV2ZW50ZGF0YSBXSEVSRSBkZXZpY2VpZCA9MTg2NjI4IGFuZCBkYXRlID0g MjAxNjA5MjggTElNSVQgMTA7DQoNCiBkZXZpY2VpZCB8IGRhdGUgICAgIHwgZXZlbnRfdGltZSAg ICB8IHBvc2l0aW9uDQotLS0tLS0tLS0tKy0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tKy0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tDQogICAxODY2MjggfCAyMDE2MDkyOCB8IDE0NzQ5 OTIwMDIwMDAgfCAgeyJsYXRpdHVkZSI6MzAuMzQzNDQzOTM2Mzg2MjQ3LCJsb25naXR1ZGUiOjEy MC4wODc1MTM1MTgyODk0Mywic3BlZWQiOjQxLCJoZWFkaW5nIjo0OH0NCiAgIDE4NjYyOCB8IDIw MTYwOTI4IHwgMTQ3NDk5MjAxMjAwMCB8ICAgeyJsYXRpdHVkZSI6MzAuMzQ0MDk1MDg5Nzk2NjIs ImxvbmdpdHVkZSI6MTIwLjA4ODQwMDIyMTgzMzUyLCJzcGVlZCI6NDUsImhlYWRpbmciOjUzfQ0K ICAgMTg2NjI4IHwgMjAxNjA5MjggfCAxNDc0OTkyMDIyMDAwIHwgICB7ImxhdGl0dWRlIjozMC4z NDQ2MTYzOTg1Njg4NywibG9uZ2l0dWRlIjoxMjAuMDg5NDYxMDAzMzY0NDMsInNwZWVkIjoyOCwi aGVhZGluZyI6NjV9DQogICAxODY2MjggfCAyMDE2MDkyOCB8IDE0NzQ5OTIwMzIwMDAgfCAgIHsi bGF0aXR1ZGUiOjMwLjM0NDY5NDc4NzE3MDI4LCJsb25naXR1ZGUiOjEyMC4wODk3MzE1NDAxNTQw OSwic3BlZWQiOjExLCJoZWFkaW5nIjo2N30NCiAgIDE4NjYyOCB8IDIwMTYwOTI4IHwgMTQ3NDk5 MjA0MjAwMCB8ICAgeyJsYXRpdHVkZSI6MzAuMzQ0OTQ5OTg5Mjk0NzQsImxvbmdpdHVkZSI6MTIw LjA5MDI3MjYzODExMTUxLCJzcGVlZCI6MTksImhlYWRpbmciOjQ3fQ0KICAgMTg2NjI4IHwgMjAx NjA5MjggfCAxNDc0OTkyMDUyMDAwIHwgeyJsYXRpdHVkZSI6MzAuMzQ2MDU3MzQ5MTI2NjE3LCJs b25naXR1ZGUiOjEyMC4wODk2NzA5MTgxNzkzMSwic3BlZWQiOjQxLCJoZWFkaW5nIjozMjN9DQog ICAxODY2MjggfCAyMDE2MDkyOCB8IDE0NzQ5OTIwNjIwMDAgfCAgICB7ImxhdGl0dWRlIjozMC4z NDY5OTcxNDU3MDgsImxvbmdpdHVkZSI6MTIwLjA4ODgzNTA4ODUzMjUzLCJzcGVlZCI6NTIsImhl YWRpbmciOjMyM30NCiAgIDE4NjYyOCB8IDIwMTYwOTI4IHwgMTQ3NDk5MjA3MjAwMCB8IHsibGF0 aXR1ZGUiOjMwLjM0ODEzMTA0NDM0MDk4OCwibG9uZ2l0dWRlIjoxMjAuMDg3NzQ3MDIzMTU1ODEs InNwZWVkIjo2NSwiaGVhZGluZyI6MzIxfQ0KICAgMTg2NjI4IHwgMjAxNjA5MjggfCAxNDc0OTky MDgyMDAwIHwgeyJsYXRpdHVkZSI6MzAuMzQ5NDM4MTY0NDEyODM4LCJsb25naXR1ZGUiOjEyMC4w ODY1MjYxMjk1OTMyOCwic3BlZWQiOjY4LCJoZWFkaW5nIjozMjJ9DQoNCi1TaW1vbiBXdQ0KDQoN Cg== ------=_001_NextPart460862703216_=---- Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =0A
Than= k 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 pack= aged all info into one JSON. Maybe I'm wrong. I rewrite the table as below= .

CREATE TABLE cargts.eventd= ata (
    deviceid int,
   &= nbsp;date int,
    event_time bigint,
=     heading int,
    lat&n= bsp;decimal,
    lon decimal,
  &= nbsp; speed int,
    PRIMARY KEY&nbs= p;((deviceid, date), event_time)
)

=
<= span style=3D"font-family: "" microsoft=3D"" yahei=3D"" ui'";=3D"" fo= nt-size:=3D"" 14px;=3D"" color:=3D"" rgb(0,=3D"" 0,=3D"" 0);=3D"" backgrou= nd-color:=3D"" rgba(0,=3D"" font-weight:=3D"" normal;=3D"" font-style:=3D"= " normal;text-decoration:=3D"" none;'=3D"">cqlsh:cargts> select&nb= sp;* from eventdata;

 deviceid | date = ;    | event_time    | h= eading | lat       | lon=         | speed
----------= +----------+---------------+---------+-----------+------------+-------
=    186628 | 20160928 | 1474992002005&nb= sp;|      48 | 30.343443 |&nb= sp;120.087514 |    41

-Simon Wu

Date: 2016-10-20&nb= sp;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 po= sition. Is there any strong reason for this or as Vladimir mentioned can y= ou store the fields under "position" in separate columns?

<= /div>=0A
On 20 October 2016 at 08:17, Vladim= ir Yudovin <vladyu@winguzone.com> wrote:
Hi Simon,

Why position is= text and not float? Text takes much more place.
Also speed and heading= s 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 compos= e single float value like speed.heading: 41.173 (or opposite, heading.spee= d) and save column storage overhead.


Best regards, Vladimir Yudovin,
Winguzone= - Hosted Cloud Cassandra
Launch your cluster in minutes.
=


---- On Thu, 20 Oct 20= 16 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:
<= span>CREATE TABLE cargts.eventdata (
  &n= bsp; deviceid int,
    date int,
=     event_time bigint,
    = ;position text,
    PRIMARY KEY ((de= viceid, date), event_time)
)

A slice of data:
= cqlsh:cargts> SELECT * FROM eventdata WHE= RE deviceid =3D186628 and date =3D 2016= 0928 LIMIT 10;

 deviceid | date =     | event_time    |&nb= sp;position
----------+----------+---------------+----------------= ----------------------------------------------------------------= ---------
   186628 | 20160928 |&nbs= p;1474992002000 |  {"latitude":30.343443936386247= ,"longitude":120.08751351828943,"speed":41,"heading":48}
&nbs= p;  186628 | 20160928 | 1474992012000&n= bsp;|   {"latitude":30.34409508979662,"longitude":120.08840022183352,"speed":45,"heading":53}
   186= 628 | 20160928 | 1474992022000 |  =  {"latitude":30.34461639856887,"longitude":120.089461003364= 43,"speed":28,"heading":65}
   186628 | 2= 0160928 | 1474992032000 |   {"latitude"= :30.34469478717028,"longitude":120.08973154015409,"speed":11,"heading":67}
   186628 | 20160928 |&nb= sp;1474992042000 |   {"latitude":30.34494998= 929474,"longitude":120.09027263811151,"speed":19,"heading":47}   186628 | 20160928 | 147499205= 2000 | {"latitude":30.346057349126617,"longitude":120.= 08967091817931,"speed":41,"heading":323}
   186628&= nbsp;| 20160928 | 1474992062000 |  &nbs= p; {"latitude":30.346997145708,"longitude":120.088835088532= 53,"speed":52,"heading":323}
   186628 | = 20160928 | 1474992072000 | {"latitude":30.34= 8131044340988,"longitude":120.08774702315581,"speed":65,"heading= ":321}
   186628 | 20160928 | 1= 474992082000 | {"latitude":30.349438164412838,"longitude":120.08652612959328,"speed":68,"heading":322}
<= div>
-Simon Wu


=0A
=0A ------=_001_NextPart460862703216_=------