在KairosDB/OpenTSDB中查询

Question

我有300万条记录，其中包括：

~/Abharthan/kairosdb$ head -10 export.txt
    {"name": "meterreadings", "timestamp":"1359695700","tags": {"Building_id":"1","building_type":"ElementarySchool","meter_type":"temperature","unit":"F"},"value":"34.85"}
    {"name": "meterreadings", "timestamp":"1359695700","tags": {"Building_id":"2","building_type":"Park","meter_type":"temperature","unit":"F"},"value":"0"}
    {"name": "meterreadings", "timestamp":"1359695700","tags": {"Building_id":"3","building_type":"Industrial","meter_type":"temperature","unit":"F"},"value":"0.07"}
    {"name": "meterreadings", "timestamp":"1359695700","tags": {"Building_id":"4","building_type":"RecreationCenter","meter_type":"temperature","unit":"F"},"value":"0"}
    {"name": "meterreadings", "timestamp":"1359695700","tags": {"Building_id":"5","building_type":"Park","meter_type":"temperature","unit":"F"},"value":"2.2"}
    {"name": "meterreadings", "timestamp":"1359695700","tags": {"Building_id":"6","building_type":"CommunityCenter","meter_type":"temperature","unit":"F"},"value":"31.41"}
    {"name": "meterreadings", "timestamp":"1359695700","tags": {"Building_id":"7","building_type":"Office","meter_type":"temperature","unit":"F"},"value":"0"}
    {"name": "meterreadings", "timestamp":"1359695700","tags": {"Building_id":"8","building_type":"ElementarySchool","meter_type":"temperature","unit":"F"},"value":"10.88"}
    {"name": "meterreadings", "timestamp":"1359695700","tags": {"Building_id":"9","building_type":"ElementarySchool","meter_type":"temperature","unit":"F"},"value":"42.27"}
    {"name": "meterreadings", "timestamp":"1359695700","tags": {"Building_id":"10","building_type":"ElementarySchool","meter_type":"temperature","unit":"F"},"value":"10.14"}

这是每栋建筑每小时收集一次building_id计量读数的一年读数。

我的起始数据时间戳是: 1359695700，结束时间戳是: 1422853200。我想查询这个DB以获得以下内容：

Query hourly average for one building(say building_id:100) for one year (output expected of 8760 points)
Query monthly sum for one building for one year (output expected of 12 points)

为了获得上述两个查询的结果，我编写了以下两个查询：

Query1：

{ "start_absolute":1359695700，"end_absolute":1422853200，“指标”：[{“标签”：{“Building_id”：“100”}，“名称”：“计量”，“聚合器”：{“名称”：“avg”，“align_sampling”：真，“抽样”：{“值”：“1”，“单位”：“小时”} 答复: 200 {“查询”：[{“sample_size”：70168，“结果”：[{“名称”：“计量”，“group_by”：{“名称”：“类型”，“键入”：“数字”}，“标签”：{“Building_id”：“100”，"building_type":"MiddleSchool"，“meter_type”：“温度”，“单位”：“F”}"values":[1359695700,42.45377343113282,1360800000,36.42662912912908,1364400000,41.12510250000007,1368000000,54.915547499999946,1371600000,65.07990000000015,1375200000,55.8904375,1378800000,47.33335249999986,1382400000,38.952450000000034,1386000000,41.99267000000001,1389600000,41.28209500000009,1393200000,40.31645895895911,1396800000,40.758327499999915,1400400000,54.05608750000002,1404000000,63.410385,1407600000,65.38089749999993,1411200000,45.99822500000001,1414800000,39.669450137465724,1418400000,39.039874999999945,1422000000,41.795917721519]}]}]}

查询2：

{ "start_absolute":1359695700，"end_absolute":1422853200，“指标”：[{“标签”：{“Building_id”：“100”}，“名称”：“计量”，“聚合器”：{“名称”：“和”，“align_sampling”：真，“抽样”：{“值”：“1”，“单位”：“月份”} 答复: 200 {“查询”：[{“sample_size”：70168，“结果”：[{“名称”：“计量”，“group_by”：{“名称”：“类型”，“类型”：“数字”}，“标签”：{“Building_id”：“100”，"building_type":"MiddleSchool"，“meter_type”：“温度”，“单位”：“F”}，“值”：[13596957,3337957.5700005]}]}}

我没有得到我所期望的，我是不是错过了什么。

Answer 1

答案很简单，正如我前面指出的，这是一个可能的问题:-) cf。Kairosdb error metric[0](name=abcd).tag[xyz].value may not be empty

KairosDB具有毫秒精度-- KairosDB中的所有时间戳都是Unix毫秒。

但是您的时间戳以Unix秒为单位，这是您的问题所在。

因此，在数据采集和查询中，您需要将所有时间戳乘以1000。

例如，query2要求在1970年1月16日至1970年1月17日之间的不到24小时内提供所有样本，因为你在一个月内得到的结果只有一个。

例如，数据采集：

{"name": "meterreadings", "timestamp":"1359695700000","tags": {"Building_id":"1","building_type":"ElementarySchool","meter_type":"temperature","unit":"F"},"value":"34.85"}
    {"name": "meterreadings", "timestamp":"1359695700000","tags": {"Building_id":"2","building_type":"Park","meter_type":"temperature","unit":"F"},"value":"0"}

...And查询：

{ "start_absolute":1359695700000, "end_absolute":1422853200000, "metrics":[{"tags":{"Building_id":["100"]},"name":"meterreadings","aggregators":[{"name":"sum","align_sampling":true,"sampling":{"value":"1","unit":"months"}}]}]}