druid為各種場景提供了豐富的查詢類型。 查詢由各種JSON屬性組合而成,不同類型的查詢,JSON屬性不同,下麵主要介紹常用的查詢類型。 1. Components Datasources 一個數據源等價於druid表。此外,一個查詢也可以作為數據源,提供類似於子查詢的功能。查詢數據源目前只支持G ...
druid為各種場景提供了豐富的查詢類型。 查詢由各種JSON屬性組合而成,不同類型的查詢,JSON屬性不同,下麵主要介紹常用的查詢類型。
1. Components
-
Datasources
一個數據源等價於druid表。此外,一個查詢也可以作為數據源,提供類似於子查詢的功能。查詢數據源目前只支持GroupBy查詢
-
Table Data Source
最常用的類型
{ "queryType": "scan", "dataSource": { "type": "table", "name": "wikipedia3" }, "resultFormat": "list", "columns": [ "page", "countryName", "cityName", "countryIsoCode" ], "intervals": [ "2016-06-27/2016-06-28" ], "limit": 5 }
-
Union Data Source
聯合數據源中的數據源必須擁有相同的schema。聯合查詢只能被髮送給broker/router節點,不支持直接發送到歷史節點
{ "type": "union", "dataSources": ["<string_value1>", "<string_value2>", "<string_value3>", ... ] }
-
-
Filters
-
Selector filter
等價於sql的
where countryIsoCode = 'US'
{ "queryType": "scan", "dataSource": { "type": "table", "name": "wikipedia3" }, "filter": { "type": "selector", "dimension": "countryIsoCode", "value": "US" }, "resultFormat": "list", "columns": [ "page", "countryName", "cityName" ], "intervals": [ "2016-06-27/2016-06-28" ], "limit": 5 }
-
Column Comparison filter
等價於sql的
where countryName = cityName
{ "queryType": "scan", "dataSource": { "type": "table", "name": "wikipedia3" }, "filter": { "type": "columnComparison", "dimensions": [ "countryName", "cityName" ] }, "resultFormat": "list", "columns": [ "page", "countryName", "cityName" ], "intervals": [ "2016-06-27/2016-06-28" ], "limit": 5 }
-
Regular expression filter
正則表達式,支持標準的java正則表達式,下麵的查詢表示countryIsoCode以U開頭
{ "queryType": "scan", "dataSource": { "type": "table", "name": "wikipedia3" }, "filter": { "type": "regex", "dimension": "countryIsoCode", "pattern": "^U" }, "resultFormat": "list", "columns": [ "page", "countryName", "cityName", "countryIsoCode" ], "intervals": [ "2016-06-27/2016-06-28" ], "limit": 5 }
-
Logical expression filters
支持and or not,下麵的等價於
where countryIsoCode = 'US' and cityName = 'New York'
{ "queryType": "scan", "dataSource": { "type": "table", "name": "wikipedia3" }, "filter": { "type": "and", "fields": [ { "type": "selector", "dimension": "countryIsoCode", "value": "US" }, { "type": "selector", "dimension": "cityName", "value": "New York" } ] }, "resultFormat": "list", "columns": [ "page", "countryName", "cityName", "countryIsoCode" ], "intervals": [ "2016-06-27/2016-06-28" ], "limit": 5 }
-
JavaScript filter
{ "queryType": "scan", "dataSource": { "type": "table", "name": "wikipedia3" }, "filter": { "type": "javascript", "dimension" : "countryIsoCode", "function" : "function(value) { return (value == 'US' || value == 'CN') }" }, "resultFormat": "list", "columns": [ "page", "countryName", "cityName", "countryIsoCode" ], "intervals": [ "2016-06-27/2016-06-28" ], "limit": 5 }
-
Search filter
用於部分字元串匹配,如下麵的表示包含foo,並且對大小寫不敏感
{ "filter": { "type": "search", "dimension": "product", "query": { "type": "insensitive_contains", "value": "foo" } } }
-
In filter
等價於
where countryIsoCode in ('US', 'CN')
{ "queryType": "scan", "dataSource": { "type": "table", "name": "wikipedia3" }, "filter": { "type": "in", "dimension" : "countryIsoCode", "values": ["US", "CN"] }, "resultFormat": "list", "columns": [ "page", "countryName", "cityName", "countryIsoCode" ], "intervals": [ "2016-06-27/2016-06-28" ], "limit": 5 }
-
Like filter
等價於
where countryIsoCode like '%U'
{ "queryType": "scan", "dataSource": { "type": "table", "name": "wikipedia3" }, "filter": { "type": "like", "dimension" : "countryIsoCode", "pattern": "%U" }, "resultFormat": "list", "columns": [ "page", "countryName", "cityName", "countryIsoCode" ], "intervals": [ "2016-06-27/2016-06-28" ], "limit": 5 }
-
Bound filter
等價於
"CN" < countryIsoCode < "US"
{ "queryType": "scan", "dataSource": { "type": "table", "name": "wikipedia3" }, "filter": { "type": "bound", "dimension" : "countryIsoCode", "lower": "CN", "lowerStrict": true, "upper": "US" , "ordering": "numeric", "upperStrict": true, "ordering": "lexicographic" }, "resultFormat": "list", "columns": [ "page", "countryName", "cityName", "countryIsoCode" ], "intervals": [ "2016-06-27/2016-06-28" ], "limit": 5 }
-
-
Aggregations
-
Count aggregator
select page, count(*) as num from wikipedia3 where "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-28 00:00:00' group by page order by num desc limit 5
{ "queryType": "topN", "dataSource": "wikipedia3", "dimension": "page", "threshold": 5, "metric": "num", "granularity": "all", "aggregations": [ { "type": "count", "name": "num" } ], "intervals": [ "2016-06-27/2016-06-28" ] }
-
Sum aggregators
longSum、doubleSum、floatSum
select page, sum(delta) as num from wikipedia3 where "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-28 00:00:00' group by page order by page asc limit 5
{ "queryType": "topN", "dataSource": "wikipedia3", "dimension": "page", "threshold": 5, "metric": "num", "granularity": "all", "aggregations": [ { "type": "longSum", "name": "num", "fieldName" : "delta" } ], "intervals": [ "2016-06-27/2016-06-28" ] }
-
Min / Max aggregators
doubleMin、doubleMax、floatMin、floatMax、longMin、longMax
select page, max(delta) as num from wikipedia3 where "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-28 00:00:00' group by page order by page asc limit 5
{ "queryType": "topN", "dataSource": "wikipedia3", "dimension": "page", "threshold": 5, "metric": "num", "granularity": "all", "aggregations": [ { "type": "longMax", "name": "num", "fieldName" : "delta" } ], "intervals": [ "2016-06-27/2016-06-28" ] }
-
First / Last aggregator
不能在數據攝入的時候使用,只能用於查詢
Last:最大時間戳對應的數據,0 if no row exist;First最小時間戳對應的數據,0 if no row exist
-
JavaScript aggregator
-
-
Post Aggregations
對Aggregations的結果進行二次加工並輸出,最終的結果既包含Aggregations的結果也包含Post Aggregations的結果
2. Timeseries
統計一段時間內的彙總數據
SELECT count(*) as num, sum(added) FROM wikipedia WHERE "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-27 23:59:59'
{ "queryType": "timeseries", "dataSource": "wikipedia3", "granularity": "all", "aggregations": [ { "type": "count", "name": "count" }, { "type": "longSum", "name": "added", "fieldName": "added" } ], "intervals": [ "2016-06-27/2016-06-28" ] }
3. TopN
返回前N條數據,並可以按照metric排序,可以支持維度,但只有一個
SELECT page, sum(added) as num FROM wikipedia WHERE "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-27 23:59:59' group by page order by num desc limit 5
{ "queryType": "topN", "dataSource": "wikipedia3", "dimension": "page", "threshold": 5, "metric": "added", "granularity": "all", "aggregations": [ { "type": "doubleSum", "name": "added", "fieldName": "added" } ], "intervals": [ "2016-06-27/2016-06-28" ] }
4. GroupBy
能對指定的多個維度分組,也支持對指定的維度排序,也支持limit,但是性能比TopN和Timeseries要差很多
SELECT page, countryName, sum(added) as num, sum(delta) as num2 FROM wikipedia WHERE "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-27 23:59:59' group by page,countryName order by num desc limit 5
{ "queryType": "groupBy", "dataSource": "wikipedia3", "granularity": "all", "dimensions": [ "page", "countryName" ], "limitSpec": { "type": "default", "limit": 5, "columns": [ { "dimension": "added", "direction": "descending", "dimensionOrder": { "type": "numeric" } } ] }, "aggregations": [ { "type": "longSum", "name": "added", "fieldName": "added" }, { "type": "longSum", "name": "delta", "fieldName": "delta" } ], "intervals": [ "2016-06-27/2016-06-28" ] }
5. Search
類似於like操作,可以查詢多個維度列,不支持聚合
SELECT page, countryName FROM wikipedia WHERE "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-27 23:59:59' and page like '%C' or countryName like '%C' limit 5
{ "queryType": "search", "dataSource": "wikipedia3", "granularity": "all", "dimensions": [ "page", "countryName" ], "query": { "type": "insensitive_contains", "value": "C" }, "sort" : { "type": "lexicographic" }, "limit": 5, "intervals": [ "2016-06-27/2016-06-28" ] }
6. Select
查數據,不支持聚合,但支持分頁,排序
SELECT * FROM wikipedia WHERE "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-27 23:59:59' limit 0,5
{ "queryType": "select", "dataSource": "wikipedia3", "granularity": "all", "dimensions":[], "metrics":[], "pagingSpec":{"pagingIdentifiers": {}, "threshold":5}, "intervals": [ "2016-06-27/2016-06-28" ] }
7. Scan
類似於Select,但不支持分頁,但是如果沒有分頁需求,推薦使用這個,性能比Select好
SELECT page,countryName FROM wikipedia WHERE "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-27 23:59:59' limit 5
{ "queryType": "scan", "dataSource": "wikipedia3", "resultFormat": "list", "columns":["page","countryName"], "intervals": [ "2016-06-27/2016-06-28" ], "batchSize":20480, "limit":5 }