Querying in KairosDB/OpenTSDB

Question

I have 3 million records with entries like:

~/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"}

These are 1 year readings for meterreadings for each building with building_id collected every hour.

My starting data timestamp is: 1359695700 and ending timestamp is: 1422853200. I want to query this DB to get the following:

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)

I have written following two queries to get results for above two queries:

Query1:

{ "start_absolute":1359695700, "end_absolute":1422853200, "metrics":[{"tags":{"Building_id":["100"]},"name":"meterreadings","aggregators":[{"name":"avg","align_sampling":true,"sampling":{"value":"1","unit":"hours"}}]}]}

Response: 200 {"queries":[{"sample_size":70168,"results":[{"name":"meterreadings","group_by":[{"name":"type","type":"number"}],"tags":{"Building_id":["100"],"building_type":["MiddleSchool"],"meter_type":["temperature"],"unit":["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]]}]}]}

Query 2:

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

Response: 200 {"queries":[{"sample_size":70168,"results":[{"name":"meterreadings","group_by":[{"name":"type","type":"number"}],"tags":{"Building_id":["100"],"building_type":["MiddleSchool"],"meter_type":["temperature"],"unit":["F"]},"values":[[1359695700,3337957.570000005]]}]}]}

I am not getting what I expected, am I missing something.

Answer 1

The answer is simple,as I pointed out previously as a possible problem :-) cf. Kairosdb error metric[0](name=abcd).tag[xyz].value may not be empty

KairosDB has millisecond precision - All the timestamps in KairosDB are Unix milliseconds.

But your timestamps are in Unix seconds, and that's your problem.

Therefore you need to multiply by 1000 all your timestamps in the data acquisition and in the queries.

For instance query2 asks for all samples during less than 24h between January 16 1970 to January 17 1970, as you aggregate on one month you get only one result.

E.g. for data acquisition:

{"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 query:

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