手册   游戏   WEB   字典   单词   在线工具  

druid 查询

druid 为各种场景提供了丰富的查询类型. 查询由各种 JSON 属性组合而成, 不同类型的查询, JSON 属性不同, 下面主要介绍常用的查询类型.

  1. 1. Components
  2. Datasources

一个数据源等价于 druid 表. 此外, 一个查询也可以作为数据源, 提供类似于子查询的功能. 查询数据源目前只支持 GroupBy 查询

Table Data Source

最常用的类型

  1. {
  2.   "queryType": "scan",
  3.   "dataSource": {
  4.     "type": "table",
  5.     "name": "wikipedia3"
  6.   },
  7.   "resultFormat": "list",
  8.   "columns": [
  9.     "page",
  10.     "countryName",
  11.     "cityName",
  12.     "countryIsoCode"
  13.   ],
  14.   "intervals": ["2016-06-27/2016-06-28"],
  15.   "limit": 5
  16. }
  17. Union Data Source

联合数据源中的数据源必须拥有相同的 schema. 联合查询只能被发送给 broker/router 节点, 不支持直接发送到历史节点

  1. {
  2.    "type": "union",
  3.    "dataSources": ["<string_value1>", "<string_value2>", "<string_value3>", ... ]
  4. }
  5. Filters
  6. Selector filter

等价于 sql 的

  1. where countryIsoCode = 'US'
  2. {
  3.       "queryType": "scan",
  4.       "dataSource": {
  5.         "type": "table",
  6.         "name": "wikipedia3"
  7.       },
  8.       "filter": {
  9.         "type": "selector",
  10.         "dimension": "countryIsoCode",
  11.         "value": "US"
  12.       },
  13.       "resultFormat": "list",
  14.       "columns": [
  15.         "page",
  16.         "countryName",
  17.         "cityName"
  18.       ],
  19.       "intervals": [
  20.         "2016-06-27/2016-06-28"
  21.       ],
  22.       "limit": 5
  23.     }
  24. Column Comparison filter

等价于 sql 的

  1. where countryName = cityName
  2. {
  3.   "queryType": "scan",
  4.   "dataSource": {
  5.     "type": "table",
  6.     "name": "wikipedia3"
  7.   },
  8.   "filter": {
  9.     "type": "columnComparison",
  10.     "dimensions": [
  11.       "countryName",
  12.       "cityName"
  13.     ]
  14.   },
  15.   "resultFormat": "list",
  16.   "columns": [
  17.     "page",
  18.     "countryName",
  19.     "cityName"
  20.   ],
  21.   "intervals": [
  22.     "2016-06-27/2016-06-28"
  23.   ],
  24.   "limit": 5
  25. }
  26. Regular expression filter

正则表达式, 支持标准的 java 正则表达式, 下面的查询表示 countryIsoCode 以 U 开头

  1. {
  2.   "queryType": "scan",
  3.   "dataSource": {
  4.     "type": "table",
  5.     "name": "wikipedia3"
  6.   },
  7.   "filter": {
  8.     "type": "regex",
  9.     "dimension": "countryIsoCode",
  10.     "pattern": "^U"
  11.   },
  12.   "resultFormat": "list",
  13.   "columns": [
  14.     "page",
  15.     "countryName",
  16.     "cityName",
  17.     "countryIsoCode"
  18.   ],
  19.   "intervals": [
  20.     "2016-06-27/2016-06-28"
  21.   ],
  22.   "limit": 5
  23. }
  24. Logical expression filters

支持 and or not, 下面的等价于

  1. where countryIsoCode = 'US' and cityName = 'New York'
  2. {
  3.       "queryType": "scan",
  4.       "dataSource": {
  5.         "type": "table",
  6.         "name": "wikipedia3"
  7.       },
  8.       "filter": {
  9.         "type": "and",
  10.         "fields": [
  11.           {
  12.             "type": "selector",
  13.             "dimension": "countryIsoCode",
  14.             "value": "US"
  15.           },
  16.           {
  17.             "type": "selector",
  18.             "dimension": "cityName",
  19.             "value": "New York"
  20.           }
  21.         ]
  22.       },
  23.       "resultFormat": "list",
  24.       "columns": [
  25.         "page",
  26.         "countryName",
  27.         "cityName",
  28.         "countryIsoCode"
  29.       ],
  30.       "intervals": [
  31.         "2016-06-27/2016-06-28"
  32.       ],
  33.       "limit": 5
  34.     }
  35. JavaScript filter
  36. {
  37.   "queryType": "scan",
  38.   "dataSource": {
  39.     "type": "table",
  40.     "name": "wikipedia3"
  41.   },
  42.   "filter": {
  43.     "type": "javascript",
  44.     "dimension" : "countryIsoCode",
  45.     "function" : "function(value) { return (value =='US'|| value =='CN') }"
  46.   },
  47.   "resultFormat": "list",
  48.   "columns": [
  49.     "page",
  50.     "countryName",
  51.     "cityName",
  52.     "countryIsoCode"
  53.   ],
  54.   "intervals": [
  55.     "2016-06-27/2016-06-28"
  56.   ],
  57.   "limit": 5
  58. }
  59. Search filter

用于部分字符串匹配, 如下面的表示包含 foo, 并且对大小写不敏感

  1. {
  2.   "filter": {
  3.     "type": "search",
  4.     "dimension": "product",
  5.     "query": {
  6.       "type": "insensitive_contains",
  7.       "value": "foo"
  8.     }
  9.   }
  10. }
  11. In filter

等价于

  1. where countryIsoCode in ('US', 'CN')
  2. {
  3.   "queryType": "scan",
  4.   "dataSource": {
  5.     "type": "table",
  6.     "name": "wikipedia3"
  7.   },
  8.   "filter": {
  9.     "type": "in",
  10.     "dimension" : "countryIsoCode",
  11.     "values": ["US", "CN"]
  12.   },
  13.   "resultFormat": "list",
  14.   "columns": [
  15.     "page",
  16.     "countryName",
  17.     "cityName",
  18.     "countryIsoCode"
  19.   ],
  20.   "intervals": [
  21.     "2016-06-27/2016-06-28"
  22.   ],
  23.   "limit": 5
  24. }
  25. Like filter

等价于

  1. where countryIsoCode like '%U'
  2. {
  3.   "queryType": "scan",
  4.   "dataSource": {
  5.     "type": "table",
  6.     "name": "wikipedia3"
  7.   },
  8.   "filter": {
  9.     "type": "like",
  10.     "dimension" : "countryIsoCode",
  11.     "pattern": "%U"
  12.   },
  13.   "resultFormat": "list",
  14.   "columns": [
  15.     "page",
  16.     "countryName",
  17.     "cityName",
  18.     "countryIsoCode"
  19.   ],
  20.   "intervals": [
  21.     "2016-06-27/2016-06-28"
  22.   ],
  23.   "limit": 5
  24. }
  25. Bound filter

等价于

  1. "CN" < countryIsoCode < "US"
  2. {
  3.   "queryType": "scan",
  4.   "dataSource": {
  5.     "type": "table",
  6.     "name": "wikipedia3"
  7.   },
  8.   "filter": {
  9.     "type": "bound",
  10.     "dimension" : "countryIsoCode",
  11.     "lower": "CN",
  12.     "lowerStrict": true,
  13.     "upper": "US" ,
  14.     "ordering": "numeric",
  15.     "upperStrict": true,
  16.     "ordering": "lexicographic"
  17.   },
  18.   "resultFormat": "list",
  19.   "columns": [
  20.     "page",
  21.     "countryName",
  22.     "cityName",
  23.     "countryIsoCode"
  24.   ],
  25.   "intervals": [
  26.     "2016-06-27/2016-06-28"
  27.   ],
  28.   "limit": 5
  29. }
  30. Aggregations
  31. Count aggregator
  32. select
  33.     page,
  34.     count(*) as num
  35. from wikipedia3
  36. where "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-28 00:00:00'
  37. group by page
  38. order by num desc
  39. limit 5
  40. {
  41.   "queryType": "topN",
  42.   "dataSource": "wikipedia3",
  43.   "dimension": "page",
  44.   "threshold": 5,
  45.   "metric": "num",
  46.   "granularity": "all",
  47.   "aggregations": [
  48.     {
  49.       "type": "count",
  50.       "name": "num"
  51.     }
  52.   ],
  53.   "intervals": [
  54.     "2016-06-27/2016-06-28"
  55.   ]
  56. }
  57. Sum aggregators
  58. longSum,doubleSum,floatSum
  59. select
  60.     page,
  61.     sum(delta) as num
  62. from wikipedia3
  63. where "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-28 00:00:00'
  64. group by page
  65. order by page asc
  66. limit 5
  67. {
  68.   "queryType": "topN",
  69.   "dataSource": "wikipedia3",
  70.   "dimension": "page",
  71.   "threshold": 5,
  72.   "metric": "num",
  73.   "granularity": "all",
  74.   "aggregations": [
  75.     {
  76.       "type": "longSum",
  77.       "name": "num",
  78.       "fieldName" : "delta"
  79.     }
  80.   ],
  81.   "intervals": [
  82.     "2016-06-27/2016-06-28"
  83.   ]
  84. }
  85. Min / Max aggregators

doubleMin,doubleMax,floatMin,floatMax,longMin,longMax

  1. select
  2.     page,
  3.     max(delta) as num
  4. from wikipedia3
  5. where "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-28 00:00:00'
  6. group by page
  7. order by page asc
  8. limit 5
  9. {
  10.   "queryType": "topN",
  11.   "dataSource": "wikipedia3",
  12.   "dimension": "page",
  13.   "threshold": 5,
  14.   "metric": "num",
  15.   "granularity": "all",
  16.   "aggregations": [
  17.     {
  18.       "type": "longMax",
  19.       "name": "num",
  20.       "fieldName" : "delta"
  21.     }
  22.   ],
  23.   "intervals": [
  24.     "2016-06-27/2016-06-28"
  25.   ]
  26. }
  27. First / Last aggregator

不能在数据摄入的时候使用, 只能用于查询

Last: 最大时间戳对应的数据, 0 if no row exist;First 最小时间戳对应的数据, 0 if no row exist

  1. JavaScript aggregator
  2. Post Aggregations

对 Aggregations 的结果进行二次加工并输出, 最终的结果既包含 Aggregations 的结果也包含 Post Aggregations 的结果

2. Timeseries

统计一段时间内的汇总数据

  1. SELECT count(*) as num,
  2. sum(added)
  3. FROM wikipedia
  4. WHERE "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-27 23:59:59'
  5. {
  6.   "queryType": "timeseries",
  7.   "dataSource": "wikipedia3",
  8.   "granularity": "all",
  9.   "aggregations": [
  10.     { "type": "count", "name": "count" },
  11.     { "type": "longSum", "name": "added", "fieldName": "added" }
  12.   ],
  13.   "intervals": [ "2016-06-27/2016-06-28" ]
  14. }
  15. 3. TopN

返回前 N 条数据, 并可以按照 metric 排序, 可以支持维度, 但只有一个

  1. SELECT
  2.     page,
  3.     sum(added) as num
  4. FROM wikipedia
  5. WHERE "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-27 23:59:59'
  6. group by page
  7. order by num desc
  8. limit 5
  9. {
  10.   "queryType": "topN",
  11.   "dataSource": "wikipedia3",
  12.   "dimension": "page",
  13.   "threshold": 5,
  14.   "metric": "added",
  15.   "granularity": "all",
  16.   "aggregations": [
  17.     {
  18.       "type": "doubleSum",
  19.       "name": "added",
  20.       "fieldName": "added"
  21.     }
  22.   ],
  23.   "intervals": [ "2016-06-27/2016-06-28" ]
  24. }
  25. 4. GroupBy

能对指定的多个维度分组, 也支持对指定的维度排序, 也支持 limit, 但是性能比 TopN 和 Timeseries 要差很多

  1. SELECT
  2.     page,
  3.     countryName,
  4.     sum(added) as num,
  5.     sum(delta) as num2
  6. FROM wikipedia
  7. WHERE "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-27 23:59:59'
  8. group by page,countryName
  9. order by num desc
  10. limit 5
  11. {
  12.   "queryType": "groupBy",
  13.   "dataSource": "wikipedia3",
  14.   "granularity": "all",
  15.   "dimensions": [
  16.     "page",
  17.     "countryName"
  18.   ],
  19.   "limitSpec": {
  20.     "type": "default",
  21.     "limit": 5,
  22.     "columns": [
  23.       {
  24.         "dimension": "added",
  25.         "direction": "descending",
  26.         "dimensionOrder": {
  27.           "type": "numeric"
  28.         }
  29.       }
  30.     ]
  31.   },
  32.   "aggregations": [
  33.     {
  34.       "type": "longSum",
  35.       "name": "added",
  36.       "fieldName": "added"
  37.     },
  38.     {
  39.       "type": "longSum",
  40.       "name": "delta",
  41.       "fieldName": "delta"
  42.     }
  43.   ],
  44.   "intervals": [
  45.     "2016-06-27/2016-06-28"
  46.   ]
  47. }
  48. 5. Search

类似于 like 操作, 可以查询多个维度列, 不支持聚合

  1. SELECT
  2. page,
  3. countryName
  4. FROM wikipedia
  5. WHERE "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-27 23:59:59'
  6. and page like '%C' or countryName like '%C'
  7. limit 5
  8. {
  9.   "queryType": "search",
  10.   "dataSource": "wikipedia3",
  11.   "granularity": "all",
  12.   "dimensions": [
  13.     "page",
  14.     "countryName"
  15.   ],
  16.   "query": {
  17.     "type": "insensitive_contains",
  18.     "value": "C"
  19.   },
  20.   "sort" : {
  21.     "type": "lexicographic"
  22.   },
  23.   "limit": 5,
  24.   "intervals": [
  25.     "2016-06-27/2016-06-28"
  26.   ]
  27. }
  28. 6. Select

查数据, 不支持聚合, 但支持分页, 排序

  1. SELECT
  2. *
  3. FROM wikipedia
  4. WHERE "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-27 23:59:59'
  5. limit 0,5
  6. {
  7.   "queryType": "select",
  8.   "dataSource": "wikipedia3",
  9.   "granularity": "all",
  10.   "dimensions":[],
  11.   "metrics":[],
  12.   "pagingSpec":{"pagingIdentifiers": {}, "threshold":5},
  13.   "intervals": [
  14.     "2016-06-27/2016-06-28"
  15.   ]
  16. }
  17. 7. Scan

类似于 Select, 但不支持分页, 但是如果没有分页需求, 推荐使用这个, 性能比 Select 好

  1. SELECT
  2. page,countryName
  3. FROM wikipedia
  4. WHERE "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-27 23:59:59'
  5. limit 5
  6. {
  7.    "queryType": "scan",
  8.    "dataSource": "wikipedia3",
  9.    "resultFormat": "list",
  10.    "columns":["page","countryName"],
  11.    "intervals": [
  12.      "2016-06-27/2016-06-28"
  13.    ],
  14.    "batchSize":20480,
  15.    "limit":5
  16.  }

来源: https://www.cnblogs.com/xujieyang4j/p/9153443.html


与本文相关文章

  1. Springboot 配置 Druid 多数据源
  2. 第 13 章-数据库连接池(Druid)
  3. spring 配置数据库连接池 druid
  4. presto 和 druid 获取数据
  5. Druid 使用
  6. Springboot 整合 Druid 数据源
  7. druid 的基础架构与应用
  8. 基于 Druid 数据库连接池的 DBUtil 工具类
暂无,快来抢沙发吧！
更多





黑ICP备17005318号-1