Repository navigation
horrible histogram post-aggregation #6853
Description
Hello. @jon-wei
I am confused with perform a druid histogram query.
The request body of histogram query like this :
"queryType": "timeseries",
"dataSource": {
"type": "table",
"name": "BRS_PAGE_HOUR"
},
"intervals": {
"type": "intervals",
"intervals": [
"2018-11-14T11:42:00/2019-01-14T11:42:00"
]
},
"descending": false,
"virtualColumns": [],
"filter": {
"type": "and",
"fields": [
{
"type": "selector",
"dimension": "browser_application_id",
"value": "2",
"extractionFn": null
},
{
"type": "selector",
"dimension": "host",
"value": "10.111.243.10:7001",
"extractionFn": null
},
{
"type": "selector",
"dimension": "uri",
"value": "/PMS/framework/popWindow.html",
"extractionFn": null
},
{
"type": "bound",
"dimension": "count",
"lower": "0",
"upper": null,
"lowerStrict": true,
"upperStrict": false,
"extractionFn": null,
"ordering": {
"type": "lexicographic"
}
}
]
},
"granularity": {
"type": "all"
},
"aggregations": [
{
"type": "longSum",
"name": "count_total",
"fieldName": "count",
"expression": null
},
{
"type": "approxHistogramFold",
"name": "first_paint_time_histogram_total",
"fieldName": "first_paint_time_histogram",
"resolution": 50,
"numBuckets": 9,
"lowerLimit": 0,
"upperLimit": "Infinity"
}
],
"postAggregations": [
{
"type": "customBuckets",
"name": "performance_histogram",
"fieldName": "first_paint_time_histogram_total",
"breaks": [
0,
200,
400,
600,
800,
1000,
1200,
1400,
"Infinity"
]
},
{ "type" : "min", "name" : "min_value", "fieldName" : "first_paint_time_histogram_total"},
{ "type" : "max", "name" : "max_value", "fieldName" : "first_paint_time_histogram_total"}
],
"context": {
"skipEmptyBuckets": "true"
}
}
The first query result like this :
[ {
"timestamp" : "2018-12-14T02:00:00.000Z",
"result" : {
"performance_histogram" : {
"breaks" : [ 0.0, 200.0, 400.0, 600.0, 800.0, 1000.0, 1200.0, 1400.0, "Infinity" ],
"counts" : [ 339.6748046875, 96.61546325683594, 70.65382385253906, 44.69218063354492, 18.730541229248047, 0.9215729236602783, 0.43618109822273254, 9.275420188903809 ]
},
"min_value" : 0.0,
"count_total" : 581,
"first_paint_time_histogram_total" : {
"breaks" : [ -4443.75, 0.0, 4443.75, 8887.5, 13331.25, 17775.0, 22218.75, 26662.5, 31106.25, 35550.0 ],
"counts" : [ 0.0, 576.6718139648438, 1.3282045125961304, 0.0, 1.0, 1.0, 0.0, 0.0, 1.0 ]
},
"max_value" : 35550.0
}
} ]
The second query result like this :
[ {
"timestamp" : "2018-12-14T02:00:00.000Z",
"result" : {
"performance_histogram" : {
"breaks" : [ 0.0, 200.0, 400.0, 600.0, 800.0, 1000.0, 1200.0, 1400.0, "Infinity" ],
"counts" : [ 550.0, 4.094977378845215, 6.259572505950928, 2.6454498767852783, 1.0, 1.0, 0.0, 9.0 ]
},
"min_value" : 0.0,
"count_total" : 581,
"first_paint_time_histogram_total" : {
"breaks" : [ -4443.75, 0.0, 4443.75, 8887.5, 13331.25, 17775.0, 22218.75, 26662.5, 31106.25, 35550.0 ],
"counts" : [ 7.0, 570.0, 1.0, 0.0, 1.0, 1.0, 0.0, 0.0, 1.0 ]
},
"max_value" : 35550.0
}
} ]
Then i increase the resolution to 500 and the result always like this :
[ {
"timestamp" : "2018-12-14T02:00:00.000Z",
"result" : {
"performance_histogram" : {
"breaks" : [ 0.0, 200.0, 400.0, 600.0, 800.0, 1000.0, 1200.0, 1400.0, "Infinity" ],
"counts" : [ 318.0, 79.0, 14.0, 2.0, 1.0, 1.0, 0.0, 9.0 ]
},
"min_value" : 0.0,
"count_total" : 581,
"first_paint_time_histogram_total" : {
"breaks" : [ -4443.75, 0.0, 4443.75, 8887.5, 13331.25, 17775.0, 22218.75, 26662.5, 31106.25, 35550.0 ],
"counts" : [ 157.0, 420.0, 1.0, 0.0, 1.0, 1.0, 0.0, 0.0, 1.0 ]
},
"max_value" : 35550.0
}
} ]
Even though i have known results can vary for the same query from docs, but i am confused why the first bucket of first_paint_time_histogram_total was [negative number, 0] and its count also>0. The min value of the column was 0. And the [0.0, 200.0] bucket which is the first bucket of custom buckets looks like not include count of the first bucket of first_paint_time_histogram_total.
My ingestion spce file define the histogram column like this:
...
{
"name": "first_paint_time_histogram",
"fieldName": "first_paint_time",
"type": "approxHistogramFold",
"resolution": 50,
"numBuckets": 7,
"lowerLimit": 0
},
...
And the data of the column like this:
mysql> select first_paint_time from BRS_PAGE_HOUR where __time>='2019-01-13 00:00:00' and browser_application_id='9' limit 100;
+------------------+
| first_paint_time |
+------------------+
| 325 |
| 147 |
| 653 |
| 189 |
| 147 |
| 422 |
| 413 |
| 17 |
| 238 |
| 423 |
| 150 |
| 449 |
| 149 |
| 345 |
| 1661 |
| 2600 |
| 427 |
| 209 |
| 1622 |
| 0 |
| 0 |
| 12 |
| 153 |
| 144 |
| 572 |
| 112 |
| 48 |
| 2831 |
| 493 |
| 577 |
| 164 |
| 360 |
| 234 |
| 368 |
| 408 |
| 108 |
| 241 |
| 2903 |
| 2923 |
| 1921 |
| 343 |
| 12363 |
| 616 |
| 356 |
| 564 |
| 266 |
| 309 |
| 418 |
| 284 |
| 247 |
| 1858 |
| 1488 |
| 15 |
| 1710 |
| 185 |
| 128 |
| 267 |
| 379 |
| 526 |
| 121 |
| 490 |
| 540 |
| 132 |
| 251 |
| 119 |
| 505 |
| 200 |
| 3715 |
| 347 |
| 33 |
| 18 |
| 1140 |
| 371 |
| 232 |
| 300 |
| 304 |
| 189 |
| 373 |
| 297 |
| 302 |
| 257 |
| 401 |
| 2058 |
| 389 |
| 432 |
| 359 |
| 822 |
| 409 |
| 2992 |
| 1045 |
| 386 |
| 1469 |
| 6715 |
| 1046 |
| 389 |
| 1052 |
| 196 |
| 1050 |
| 390 |
| 454 |
+------------------+
100 rows in set (0.07 sec)
Do you have any advices ? Thank you so much.
Activity
jon-wei commentedon Jan 15, 2019
@quenlang Can you try using the quantiles sketch aggregator instead?
http://druid.io/docs/latest/development/extensions-core/datasketches-quantiles.html
quenlang commentedon Jan 15, 2019
@jon-wei Can i use this feature in druid 0.12.3? It just looks like a new feature in druid 0.13.0.
jon-wei commentedon Jan 15, 2019
It's in 0.12.3, but the docs were added later in 0.13.0: https://github.com/apache/incubator-druid/tree/0.12.3/extensions-core/datasketches
quenlang commentedon Jan 16, 2019
@jon-wei Thanks for quick reply. I had upgraded my cluster to 0.13.0. But there was another problem when the task goes to hand off segment end of the segment granularity window as below:
The ingestion spec :
Some ideas of this exception?
quenlang commentedon Jan 16, 2019
@jon-wei As the data ingested into druid which was stayed at the memory or local disk of middleManager peon task before handing off to deep storage, so i performed a query which will be sent to peon task. The query like this:
In order to compare the accuracy of approximate histogram and quantiles sketch so i defined two aggregation methods in the same query. And the result as below:
The orignal row data which i wrote to kafka, include 11 rows as below:
Also i calculated the quantiles of [0.50, 0.75, 0.90, 0.95] and the histograms of [ 0.0, 200.0, 400.0, 600.0, 800.0, 1000.0, 1200.0, 1400.0, "Infinity" ] by myself. They were [100, 1150, 1772, 1886] and [ 6.0, 0.0, 0.0, 1.0, 0.0, 0.0, 0.0, 3.0 ].
Compared the actual result with the query result, i found the quantile query of approximate histogram was more accurate than quantiles sketch, but for the histogram query, quantiles sketch was win.
Can you tell me more about why the the quantile query of approximate histogram was more accurate thanquantiles sketch?
quenlang commentedon Jan 16, 2019
@jon-wei I had try with druid 0.12.3 in the same scene and the task commited successfully. So is this a bug in druid 0.13.0 ?
jon-wei commentedon Jan 17, 2019
Thanks for the report, this is a bug in 0.13.0 (#6877).
Regarding the question on sketch accuracy, I'm not too sure myself, but perhaps one of the datasketches folks can comment? @AlexanderSaydakov @leerho
quenlang commentedon Jan 17, 2019
AlexanderSaydakov commentedon Jan 17, 2019
It is hard to follow your example. Let's see if I understand it correctly.
You take the following 11 values:
0.0
99.0
100.0
600.0
2000.0
5.0
20.0
2.0
1772.0
1700.0
300.0
You expect 0.5, 0.75, 0.9 and 0.95 quantiles to be 100, 1150, 1772 and 1886 respectively.
Why? Some values you expect were not in the input. Do you expect some sort of interpolation? Our quantiles sketch does not do anything like that. It does not even assume they are numbers. The only requirement for the algorithm is the "less than" relation between the values. To calculate, say, 0.75 quantile exactly one needs to sort the above array and see what value has the normalized rank of 0.75, that is 75% of all values should be less than that value. The value 1700 has 8 values below it, so its rank is 8/11 or about 0.73. So our sketch returns 1700 as the best approximation to 0.75 quantile.
quenlang commentedon Jan 18, 2019
@AlexanderSaydakov Thanks for quick reply !
Yes, you're right. I had sorted the 11 values asc for interpolation and i got [100, 1150, 1772, 1886] finally.
I see the mechanism of quantiles sketch under your explaining.
Thank you so much.
And best wishes for @jon-wei too !
leerho commentedon Jan 18, 2019
@quenlang
There are very good reasons why it is not a good idea for a quantiles (or histogram) sketch to "interpolate" between actual values in the data. Fundamentally, in order for the sketch to be independent of the input data distribution, it cannot make any assumptions about what that distribution might be. And, in order to "interpolate", one must make assumptions about the input data distribution. For example, interpolating using linear, power-law, Gaussian, or discontinuous step distributions would produce wildly different results. Therefore it is best to not to make any assumptions about the input data distribution and only return values actually sampled from the real data.
quenlang commentedon Jan 19, 2019
@leerho That is great ! Thanks for quick reply.