Zero filling does not work in timeseries queries.

[KenjiTakahashi]

That's using Druid 0.8.2.

An example query and result:

(virtualenv)2015-12-16 23:38:27
monitowl-dev:root:/vagrant> cat query2.json
{
    "filter": {
        "fields": [
            {
                "type": "selector",
                "dimension": "<cut>",
                "value": "<cut>"
            },
            {
                "type": "selector",
                "dimension": "<cut>",
                "value": "<cut>"
            }
        ],
        "type": "and"
    },
    "intervals": "2015-11-04T03:41:58.042000/2015-11-11T22:20:14.927000",
    "dataSource": "logdb_data",
    "granularity": {
        "origin": "1970-01-01T00:00:00",
        "type": "period",
        "period": "PT50000S"
    },
    "postAggregations": [
        {
            "fields": [
                {
                    "fieldName": "sum",
                    "type": "fieldAccess",
                    "name": "sum"
                },
                {
                    "fieldName": "count",
                    "type": "fieldAccess",
                    "name": "count"
                }
            ],
            "type": "arithmetic",
            "name": "result",
            "fn": "/"
        }
    ],
    "queryType": "timeseries",
    "aggregations": [
        {
            "type": "count",
            "name": "count"
        },
        {
            "fieldName": "data_num",
            "type": "doubleSum",
            "name": "sum"
        }
    ]
}
2015-12-16 23:38:30 cat query2.json
(virtualenv)2015-12-16 23:38:30
monitowl-dev:root:/vagrant> curl -X POST 'http://localhost:10000/druid/v2/?pretty' -H 'content-type: application/json' -d@query2.json
[ {
  "timestamp" : "2015-11-04T01:20:00.000Z",
  "result" : {
    "result" : 0.0,
    "count" : 0,
    "sum" : 0.0
  }
}, {
  "timestamp" : "2015-11-04T15:13:20.000Z",
  "result" : {
    "result" : 0.0,
    "count" : 0,
    "sum" : 0.0
  }
}, {
  "timestamp" : "2015-11-10T10:06:40.000Z",
  "result" : {
    "result" : 0.0,
    "count" : 0,
    "sum" : 0.0
  }
}, {
  "timestamp" : "2015-11-11T00:00:00.000Z",
  "result" : {
    "result" : 0.0,
    "count" : 0,
    "sum" : 0.0
  }
}, {
  "timestamp" : "2015-11-11T13:53:20.000Z",
  "result" : {
    "result" : 0.0,
    "count" : 0,
    "sum" : 0.0
  }
} ]
2015-12-16 23:38:32 curl -X POST 'http://localhost:10000/druid/v2/?pretty' -H 'content-type: application/json' -d@query2.json

This uses period of 50000S, which is almost 14 hours.
See that between 2nd and 3rd result, there's ~5 days gap with no data and it is not filled with zeroes.

Explicitly adding

"context": {
    "skipEmptyBuckets": "false"
}

does not change anything.

And, strangely enough, setting "skipEmptyBuckets": "true" returns... nothing (i.e. empty array). Maybe that's because it discards "our" zeroes as well? I'll need to find (or produce) a gap within data that do not evaluate to 0 and test it further.

BTW: I've seen this also reported on ML (https://groups.google.com/forum/#!topic/druid-user/3SfgJ7t001s) recently, but no conclusion was made there.

[jaehc]

There is a mention about intervals in the document about timeseries queries.(http://druid.io/docs/latest/querying/timeseriesquery.html)
Time buckets that lie completely outside the data interval are not zero-filled.

The time buckets in a query result is not continuous when data is not available between disjoint segments. This is because the resulting time buckets are produced based on existing data intervals, not query itself. Inside a single segment level, zero filling would work well.
In my opinion, this might confuse some people because they expect the time buckets is continuous and based on the given query interval no matter how data is stored.
Does it need to be fixed in the way the returned time buckets are based on query interval?

[KenjiTakahashi]

I hear you, you're correct. I've made a simple experiment to confirm that.
Consider a DB with such three values (config_id and stream_name are irrelevant, I just used our internal tool for filling this). Segment granularity is 1 DAY.

{"data_num":490865.0,"timestamp":"2015-09-24T10:00:00.000Z","config_id":"2222222222222222222222222222222222222222","stream_name":"test"}
{"data_num":136179.0,"timestamp":"2015-09-24T20:00:00.000Z","config_id":"2222222222222222222222222222222222222222","stream_name":"test"}
{"data_num":236171.0,"timestamp":"2015-09-25T12:00:00.000Z","config_id":"2222222222222222222222222222222222222222","stream_name":"test"}

Then issue a query:

2016-01-08 07:14:44
monitowl-dev:root:/vagrant> cat query2.json
{
    "filter": {
        "fields": [
            {
                "type": "selector",
                "dimension": "config_id",
                "value": "2222222222222222222222222222222222222222"
            },
            {
                "type": "selector",
                "dimension": "stream_name",
                "value": "test"
            }
        ],
        "type": "and"
    },
    "intervals": "2015-09-24T10:00:00.000000/2015-09-25T13:00:00.000000",
    "dataSource": "logdb_data",
    "context": {
        "skipEmptyBuckets": "false"
    },
    "granularity": {
        "origin": "1970-01-01T00:00:00",
        "type": "period",
        "period": "PT3600S"
    },
    "queryType": "timeseries",
    "aggregations": [
        {
            "fieldName": "data_num",
            "type": "doubleSum",
            "name": "sum"
        }
    ]
}
2016-01-08 07:14:47 cat query2.json
2016-01-08 07:14:47
monitowl-dev:root:/vagrant> curl -X POST 'http://localhost:10000/druid/v2/?pretty' -H 'content-type: application/json' -d@query2.json
[ {
  "timestamp" : "2015-09-24T10:00:00.000Z",
  "result" : {
    "sum" : 490865.0
  }
}, {
  "timestamp" : "2015-09-24T11:00:00.000Z",
  "result" : {
    "sum" : 0.0
  }
}, {
  "timestamp" : "2015-09-24T12:00:00.000Z",
  "result" : {
    "sum" : 0.0
  }
}, {
  "timestamp" : "2015-09-24T13:00:00.000Z",
  "result" : {
    "sum" : 0.0
  }
}, {
  "timestamp" : "2015-09-24T14:00:00.000Z",
  "result" : {
    "sum" : 0.0
  }
}, {
  "timestamp" : "2015-09-24T15:00:00.000Z",
  "result" : {
    "sum" : 0.0
  }
}, {
  "timestamp" : "2015-09-24T16:00:00.000Z",
  "result" : {
    "sum" : 0.0
  }
}, {
  "timestamp" : "2015-09-24T17:00:00.000Z",
  "result" : {
    "sum" : 0.0
  }
}, {
  "timestamp" : "2015-09-24T18:00:00.000Z",
  "result" : {
    "sum" : 0.0
  }
}, {
  "timestamp" : "2015-09-24T19:00:00.000Z",
  "result" : {
    "sum" : 0.0
  }
}, {
  "timestamp" : "2015-09-24T20:00:00.000Z",
  "result" : {
    "sum" : 136179.0
  }
}, {
  "timestamp" : "2015-09-25T12:00:00.000Z",
  "result" : {
    "sum" : 236171.0
  }
} ]
2016-01-08 07:14:51 curl -X POST 'http://localhost:10000/druid/v2/?pretty' -H 'content-type: application/json' -d@query2.json

See that intervals within 2015-09-24 (that's single segment) are filled properly, but subsequent intervals in between two segments are not.
I'd say that this is indeed confusing. Well, I got confused, too.

With this working as is, we'd have to do the filling client side, as our processing expects that result of a single query will be continuous. I'd rather have that done Druid side.

Anyway, thanks @CHOIJAEHONG1 for clarifying what's going on here.

[jaehc]

You are welcome. I think that after merging PR #2058, a broker has got to know uncovered intervals, which represent absent data segments. So If we want to have a query result zero-filled on absent data segments, we can use that information to put zeros among data segments in a query result.

[stale]

This issue has been marked as stale due to 280 days of inactivity. It will be closed in 2 weeks if no further activity occurs. If this issue is still relevant, please simply write any comment. Even if closed, you can still revive the issue at any time or discuss it on the dev@druid.apache.org list. Thank you for your contributions.

[stale]

This issue has been closed due to lack of activity. If you think that is incorrect, or the issue requires additional review, you can revive the issue at any time.

[H-Hima]

Hello everyone. Isn't there any update on this issue?

[gianm]

No update, but it still seems relevant. The issue is that zero-filling happens on a per segment basis, so zeroes will only be filled in for intervals where the datasource has some data. (Maybe filtered out for the query though, hence the fact that the timeseries needs a zero-fill.) The zero filling would need to happen a level or two higher up in order to cover intervals where there are no segments.

[stale]

This issue has been marked as stale due to 280 days of inactivity. It will be closed in 4 weeks if no further activity occurs. If this issue is still relevant, please simply write any comment. Even if closed, you can still revive the issue at any time or discuss it on the dev@druid.apache.org list. Thank you for your contributions.

[stale]

This issue has been closed due to lack of activity. If you think that is incorrect, or the issue requires additional review, you can revive the issue at any time.