Druid 大資料分析之查詢

1、Druid 查詢概述上一節完成資料導入後，接下來講講Druid如何查詢及統計分析導入的資料。

Druid的查詢是使用REST風格的HTTP請求查詢服務節點（Broker、Historical、Realtime），這些服務節點暴露REST查詢接口，用戶端發送Json對象請求查詢接口。一般情況下，查詢服務接口釋出在Broker節點，基于Linux 的POST請求查詢如下所示：

/**
* port: 查詢請求接口對應Broker，預設8082端口
* query_json_file: 查詢Json對象檔案（配置）
*/
curl -X POST '<queryable_host>:<port>/druid/v2/?pretty' -H 'Content-Type:application/json' -d @<query_json_file>

2、Druid 查詢類型 Druid在不同場景下，有很多的查詢類型。查詢是由各種JSON屬性和Druid有不同類型的不同場景下查詢組成。對于各種類型的查詢類型的配置可以json屬性檔案設定。Druid查詢類型，概括一下為3大類：

1. 聚合查詢 - 時間序列查詢（Timeseries）、排名查詢（TopN）、分組查詢（GroupBy）

2. 中繼資料查詢 - 時間範圍(Time Boundary) 、段中繼資料(Segment Metadata)、資料源(Datasource)

3. Search查詢 - Search

本節以聚合查詢為主，其它查詢類型比較簡單，使用上相對比較少，暫不介紹。對聚合查詢類型下的3種查詢如何選擇進行一下概述：

在可能的情況下，我們建議使用的時間序列和TopN查詢代替分組查詢，分組查詢是Druid最靈活的的查詢，但是性能最差。時間序列查詢是明顯快于GROUPBY查詢，因為聚合不需要分組尺寸。對于分組和排序在一個單一的次元，TopN查詢更優于GROUPBY。

2.1 Json查詢屬性在講聚合查詢下的3種查詢類型之前，我們需要對3種查詢類型共有的特别重要的Json屬性了解與熟悉，常用屬性如：queryType、dataSource、granularity、filter、aggregator等。

2.1.1 查詢類型（queryType）對應聚合查詢下的3種類型值：timeseries、topN、groupBy 2.1.2 資料源（dataSource）資料源，類似資料庫中表的概念，對應資料導入時Json配置屬性dataSource值

2.1.3 聚合粒度（granularity）粒度決定如何得到資料塊在跨時間次元，或者如何得到按小時，天，分鐘的彙總等。在配置查詢聚合粒度裡有三種配置方法：

1. 簡單聚合粒度 - 支援字元串值有：all、none、second、minute、fifteen_minute、thirty_minute、hour、day、week、month、quarter、year

(1) all - 将所有塊變成一塊

(2) none - 不使用塊資料（它實際上是使用最小索引的粒度，none意味着為毫秒級的粒度）；按時間序列化查詢時不建議使用none，因為所有的毫秒不存在，系統也将嘗試生成0值，這往往是很多。

2. 時間段聚合粒度 - Druid指定一精确的持續時間（毫秒）和時間綴傳回UTC（世界标準時間）。

3. 常用時間段聚合粒度 - 與時間段聚合粒度差不多，但是常用時間指平時我們常用時間段，如年、月、周、小時等。

下面對3種聚合粒度配置舉例說明：

簡單聚合粒度

查詢粒度比資料采集時配置的粒度小，則不合理，也無意義，因較小粒度（相比）者無索引資料；如

查詢粒度小于采集時配置的查詢粒度時，則Druid的查詢結果與采集資料配置的查詢粒度結果一樣。

假設我們存儲在Druid的資料使用毫秒粒度擷取，資料如下：

{"timestamp": "2013-08-31T01:02:33Z", "page": "AAA", "language" : "en"}
{"timestamp": "2013-09-01T01:02:33Z", "page": "BBB", "language" : "en"}
{"timestamp": "2013-09-02T23:32:45Z", "page": "CCC", "language" : "en"}
{"timestamp": "2013-09-03T03:32:45Z", "page": "DDD", "language" : "en"}

以"小時" 粒度送出一個groupby查詢，查詢配置如下：

{
   "queryType":"groupBy",
   "dataSource":"dataSource",
   "granularity":"hour",
   "dimensions":[
      "language"
   ],
   "aggregations":[
      {
         "type":"count",
         "name":"count"
      }
   ],
   "intervals":[
      "2000-01-01T00:00Z/3000-01-01T00:00Z"
   ]
}

按小時粒度進行的groupby查詢結果中timestamp值精确到小時間，比小時粒度更小粒度值自動補填零，

以此類推按天查詢，則小時及小粒度補零。timestamp值為UTC

[ {
  "version" : "v1",
  "timestamp" : "2013-08-31T01:00:00.000Z",
  "event" : {
    "count" : 1,
    "language" : "en"
  }
}, {
  "version" : "v1",
  "timestamp" : "2013-09-01T01:00:00.000Z",
  "event" : {
    "count" : 1,
    "language" : "en"
  }
}, {
  "version" : "v1",
  "timestamp" : "2013-09-02T23:00:00.000Z",
  "event" : {
    "count" : 1,
    "language" : "en"
  }
}, {
  "version" : "v1",
  "timestamp" : "2013-09-03T03:00:00.000Z",
  "event" : {
    "count" : 1,
    "language" : "en"
  }
} ]

如果指定查詢粒度為 none，則傳回結果與資料導入時設定粒度（queryGranularity屬性值）結果一樣，

此處的導入粒度為毫秒，結果如下：

[ {
  "version" : "v1",
  "timestamp" : "2013-08-31T01:02:33.000Z",
  "event" : {
    "count" : 1,
    "language" : "en"
  }
}, {
  "version" : "v1",
  "timestamp" : "2013-09-01T01:02:33.000Z",
  "event" : {
    "count" : 1,
    "language" : "en"
  }
}, {
  "version" : "v1",
  "timestamp" : "2013-09-02T23:32:45.000Z",
  "event" : {
    "count" : 1,
    "language" : "en"
  }
}, {
  "version" : "v1",
  "timestamp" : "2013-09-03T03:32:45.000Z",
  "event" : {
    "count" : 1,
    "language" : "en"
  }
} ]

如果指定查詢粒度為 all，傳回數組長度結果為1，結果如下：

[ {
  "version" : "v1",
  "timestamp" : "2000-01-01T00:00:00.000Z",
  "event" : {
    "count" : 4,
    "language" : "en"
  }
} ]

時間段聚合粒度

指定一個精确時間持續時長（毫秒表示）及時間綴，傳回UTC時間；支援可選項屬性origin，不指定時

預設開始時間（1970-01-01T00:00:00Z）

/**持續時間段2小時，從1970-01-01T00:00:00Z開始*/
{"type": "duration", "duration": 7200000}

/**持續時間1小時，從origin開始*/
{"type": "duration", "duration": 3600000, "origin": "2012-01-01T00:30:00Z"}

以上簡單聚合粒度的示例資料為例，送出groupby查詢，持續時間段為24小時，查詢配置如下：

{
   "queryType":"groupBy",
   "dataSource":"dataSource",
   "granularity":{"type": "duration", "duration": "86400000"},
   "dimensions":[
      "language"
   ],
   "aggregations":[
      {
         "type":"count",
         "name":"count"
      }
   ],
   "intervals":[
      "2000-01-01T00:00Z/3000-01-01T00:00Z"
   ]
}

查詢結果：

[ {
  "version" : "v1",
  "timestamp" : "2013-08-31T00:00:00.000Z",
  "event" : {
    "count" : 1,
    "language" : "en"
  }
}, {
  "version" : "v1",
  "timestamp" : "2013-09-01T00:00:00.000Z",
  "event" : {
    "count" : 1,
    "language" : "en"
  }
}, {
  "version" : "v1",
  "timestamp" : "2013-09-02T00:00:00.000Z",
  "event" : {
    "count" : 1,
    "language" : "en"
  }
}, {
  "version" : "v1",
  "timestamp" : "2013-09-03T00:00:00.000Z",
  "event" : {
    "count" : 1,
    "language" : "en"
  }
} ]

常用時間段聚合粒度

略...

2.1.4 過濾（Filters）一個Filter就是一個Json對象，用于過濾資料行過濾，類似SQL中的Where子句。過濾器類型有如下：Selector filte、Regular expression filter（正規表達式過濾）、Logical expression filters（AND、OR、NOT）、In filter、Bound filter、Search filter、JavaScript filter、Extraction filter

示例簡單檢視使用方式：

查詢過濾（Selector filte）

等價于：WHERE <dimension_string> = '<dimension_value_string>'

"filter": { "type": "selector", "dimension": <dimension_string>, "value": <dimension_value_string> }

正則表達過濾（Regular expression filter）

與Selector filte差不多，隻是這裡使用正規表達式，表達式為标準的Java正規表達式規範

"filter": { "type": "regex", "dimension": <dimension_string>, "pattern": <pattern_string> }

邏緝表達過濾（Logical expression filters）

AND

"filter": { "type": "and", "fields": [<filter>, <filter>, ...] }

"filter": { "type": "or", "fields": [<filter>, <filter>, ...] }

NOT

"filter": { "type": "not", "field": <filter> }

IN過濾（In filter）

SQL查詢

SELECT COUNT(*) AS 'Count' FROM `table` WHERE `outlaw` IN ('Good', 'Bad', 'Ugly')

Druid IN 過濾表示

{
    "type": "in",
    "dimension": "outlaw",
    "values": ["Good", "Bad", "Ugly"]
}

範圍過濾（Bound filter）

Bound filter 過濾比較值大小或小于某值，預設按字元串比較，使用資料比較需要設定alphaNumeric 屬

性為true；預設 Bound filter為非嚴格性（類閉區間），如 inputString <= upper && inputSting >= lower

{
    "type": "bound",
    "dimension": "age",
    "lower": "21",
    "upper": "31" ,
    "alphaNumeric": true
}

上述表示等價如：21 <= age <= 31

Bound filter 嚴格性，需要設定lowerStrict or/and upperStrict 屬性值為true如下：

{
    "type": "bound",
    "dimension": "age",
    "lower": "21",
    "lowerStrict": true,
    "upper": "31" ,
    "upperStrict": true,
    "alphaNumeric": true
}

等價如：21 < age < 31

2.1.5 聚合（Aggregations）聚合可以在采集時間時規格部分的一種方式，彙總資料進入Druid之前提供。聚合也可以被指定為在查詢時多查詢的部分，聚合類型如下：Count aggregator、Sum aggregators、Min / Max aggregators、Approximate Aggregations、Miscellaneous Aggregations

Count aggregator

查詢傳回比對過濾條件的資料行數，需要注意的是：Druid進行Count查詢的資料量并不一定等于資料采

集時導入的資料量，因為Druid在采集資料并導入時已經對資料進行了聚合。

{ "type" : "count", "name" : <output_name> }

Sum aggregator

longSum aggregator：計算值為有符号位64位整數

{ "type" : "longSum", "name" : <output_name>, "fieldName" : <metric_name> }

doubleSum aggregator：與longSum類似，計算值為64位浮點型

{ "type" : "doubleSum", "name" : <output_name>, "fieldName" : <metric_name> }

Min / Max aggregators

doubleMin aggregator

{ "type" : "doubleMin", "name" : <output_name>, "fieldName" : <metric_name> }

doubleMax aggregator

{ "type" : "doubleMax", "name" : <output_name>, "fieldName" : <metric_name> }

longMin aggregator

{ "type" : "longMin", "name" : <output_name>, "fieldName" : <metric_name> }

longMax aggregator

{ "type" : "longMax", "name" : <output_name>, "fieldName" : <metric_name> }

類似聚合（Approximate Aggregations）

基數聚合（Cardinality aggregator）

計算Druid多種次元基數，Cardinality aggregator使用HyperLogLog評估基數，這種聚合比帶有索引的

hyperUnique聚合慢，運作在一個次元列，意味着不能從資料集中删除字元串次元來提高聚合；一般我們

強力推薦使用hyperUnique aggregator而不是Cardinality aggregator，格式如下：

{
  "type": "cardinality",
  "name": "<output_name>",
  "fieldNames": [ <dimension1>, <dimension2>, ... ],
  "byRow": <false | true> # (optional, defaults to false)
}

. 次元值聚合-當設定屬性byRow為false（預設值）時，通過合并所有給定的次元列來計算值集合。

對于單次元，等價如下：

SELECT COUNT(DISTINCT(dimension)) FROM <datasource>

對于多元度，等價如下:

SELECT COUNT(DISTINCT(value)) FROM (
  SELECT dim_1 as value FROM <datasource>
  UNION
  SELECT dim_2 as value FROM <datasource>
  UNION
  SELECT dim_3 as value FROM <datasource>
)

. 行聚合-當設定屬性byRow為true時，根所不同次元的值合并來計算行值，等價如下：

SELECT COUNT(*) FROM ( SELECT DIM1, DIM2, DIM3 FROM <datasource> GROUP BY DIM1, DIM2, DIM3 )

許多不同國家的人出生地或來自哪裡，用druid配置如下：

{
  "type": "cardinality",
  "name": "distinct_countries",
  "fieldNames": [ "coutry_of_origin", "country_of_residence" ]
}

HyperUnique aggregator

已經被“hyperunique”在建立索引時聚合的次元值使用HyperLogLog計算估計，更多資料請參考官網

{ "type" : "hyperUnique", "name" : <output_name>, "fieldName" : <metric_name> }

後聚合（post-aggregators）後聚合是對Druid進行聚合後的值進行聚全，如果查詢中包括一個後聚合，那麼確定所有聚合滿足後聚合要求；後聚合有以下幾種類型：

1. Arithmetic post-aggregators

2. Field accessor post-aggregator

3. Constant post-aggregator

4. JavaScript post-aggregator

5. HyperUnique Cardinality post-aggregator

Arithmetic post-aggregators

算術後聚合應用已提供的函數從左到右擷取字段，這些字段可聚合或後聚合；支援

, and

quotient。

算術後聚合可以指定ordering屬性，用于聚合結果排序（對topN查詢很有用）：

(1) 如果無ordering屬性（或null），使用預設的浮點排序。

(2) numericFirst 首先傳回有限值，其次是NaN，最後傳回無限值。

算術後聚合文法如下：

postAggregation : {
  "type"  : "arithmetic",
  "name"  : <output_name>,
  "fn"    : <arithmetic_function>,
  "fields": [<post_aggregator>, <post_aggregator>, ...],
  "ordering" : <null (default), or "numericFirst">
}

Field accessor post-aggregator - fieldName引用aggregator定義的名稱

{ "type" : "fieldAccess", "name": <output_name>, "fieldName" : <aggregator_name> }

Constant post-aggregator - 傳回指定值

{ "type"  : "constant", "name"  : <output_name>, "value" : <numerical_value> }

2.2 時間序列查詢（Timeseries）這些類型的查詢以時間序列查詢對象和傳回一個JSON數組對象，每個對象表示時間序列查詢的值，時間序列查詢請求的Json的7個主要屬性如下：

屬性	描述	必填項
queryType	字元串類型，時間序列 "timeseries"	是
dataSource	字元串類型，資料源（類似資料庫表）	是
descending	排序标志，預設為 "false"(升序)	否
intervals	查詢時間範圍跨度，JSON對象，ISO-8601區間	是
granularity	定義查詢結果塊粒度	是
filter	過濾條件	否
aggregations	聚合	是
postAggregations	後聚合	否
context	上下文	否

{
  "queryType": "timeseries",
  "dataSource": "sample_datasource",
  "granularity": "day",
  "descending": "true",
  "filter": {
    "type": "and",
    "fields": [
      { "type": "selector", "dimension": "sample_dimension1", "value": "sample_value1" },
      { "type": "or",
        "fields": [
          { "type": "selector", "dimension": "sample_dimension2", "value": "sample_value2" },
          { "type": "selector", "dimension": "sample_dimension3", "value": "sample_value3" }
        ]
      }
    ]
  },
  "aggregations": [
    { "type": "longSum", "name": "sample_name1", "fieldName": "sample_fieldName1" },
    { "type": "doubleSum", "name": "sample_name2", "fieldName": "sample_fieldName2" }
  ],
  "postAggregations": [
    { "type": "arithmetic",
      "name": "sample_divide",
      "fn": "/",
      "fields": [
        { "type": "fieldAccess", "name": "postAgg__sample_name1", "fieldName": "sample_name1" },
        { "type": "fieldAccess", "name": "postAgg__sample_name2", "fieldName": "sample_name2" }
      ]
    }
  ],
  "intervals": [ "2012-01-01T00:00:00.000/2012-01-03T00:00:00.000" ]
}

上述配置了過濾條件，2個聚合，後聚合器将2個聚合結果進行相除。查詢結果如下，查詢結果存儲在屬性result，以鍵值對方式存儲：

[
  {
    "timestamp": "2012-01-01T00:00:00.000Z",
    "result": { "sample_name1": <some_value>, "sample_name2": <some_value>, "sample_divide": <some_value> } 
  },
  {
    "timestamp": "2012-01-02T00:00:00.000Z",
    "result": { "sample_name1": <some_value>, "sample_name2": <some_value>, "sample_divide": <some_value> }
  }
]

2.3 排名查詢（TopN query） TopN查詢根據規範傳回給定次元的有序的結果集，從概念上來講，TopN查詢被認為單次元、有序的類似分組查詢。在某些情況下，TopN查詢比分組查詢（groupby query）快。TopN查詢結果傳回Json數組對象。

TopN在每個節點将頂上K個結果排名，在Druid預設情況下最大值為1000。在實踐中，如果你要求前1000個項順序排名，那麼從第1-999個項的順序正确性是100%，其後項的結果順序沒有保證。你可以通過增加threshold值來保證順序準确。

屬性	描述	必填項
queryType	字元串類型，時間序列 "topN"	是
dataSource	字元串類型，資料源（類似資料庫表）	是
intervals	查詢時間範圍跨度，JSON對象，ISO-8601區間	是
granularity	定義查詢結果塊粒度	是
filter	過濾條件	否
aggregations	聚合	是
postAggregations	後聚合	否
dimension	查詢的次元（列）	是
threshold	傳回Top N個結果	是
metric	字元串或Json對象指定度量對Top N個結果排序	是
context	上下文	否

Metric

屬性	描述	必填項
type	數字排序	是
metric	排序字段	是

資料排序（Numeric TopNMetricSpec） - 最簡單的規範指定一個字元串值訓示排序TopN結果的度量

"metric": "<metric_name>"

metric屬性通常配置為Json對象，上述等價于：

"metric": {
    "type": "numeric",
    "metric": "<metric_name>"
}

topN query 配置示例如下：

{
  "queryType": "topN",
  "dataSource": "sample_data",
  "dimension": "sample_dim",
  "threshold": 5,
  "metric": "count",
  "granularity": "all",
  "filter": {
    "type": "and",
    "fields": [
      {
        "type": "selector",
        "dimension": "dim1",
        "value": "some_value"
      },
      {
        "type": "selector",
        "dimension": "dim2",
        "value": "some_other_val"
      }
    ]
  },
  "aggregations": [
    {
      "type": "longSum",
      "name": "count",
      "fieldName": "count"
    },
    {
      "type": "doubleSum",
      "name": "some_metric",
      "fieldName": "some_metric"
    }
  ],
  "postAggregations": [
    {
      "type": "arithmetic",
      "name": "sample_divide",
      "fn": "/",
      "fields": [
        {
          "type": "fieldAccess",
          "name": "some_metric",
          "fieldName": "some_metric"
        },
        {
          "type": "fieldAccess",
          "name": "count",
          "fieldName": "count"
        }
      ]
    }
  ],
  "intervals": [
    "2013-08-31T00:00:00.000/2013-09-03T00:00:00.000"
  ]
}

查詢前Top 5個結果，按count排序：

[
  {
    "timestamp": "2013-08-31T00:00:00.000Z",
    "result": [
      {
        "dim1": "dim1_val",
        "count": 111,
        "some_metrics": 10669,
        "average": 96.11711711711712
      },
      {
        "dim1": "another_dim1_val",
        "count": 88,
        "some_metrics": 28344,
        "average": 322.09090909090907
      },
      {
        "dim1": "dim1_val3",
        "count": 70,
        "some_metrics": 871,
        "average": 12.442857142857143
      },
      {
        "dim1": "dim1_val4",
        "count": 62,
        "some_metrics": 815,
        "average": 13.14516129032258
      },
      {
        "dim1": "dim1_val5",
        "count": 60,
        "some_metrics": 2787,
        "average": 46.45
      }
    ]
  }
]

待續。。。

Druid 大資料分析之查詢

繼續閱讀

Spring Boot 配置Druid連接配接池

springboot 整合 druid監控，批量操作報錯問題

springboot整合druid配置pom.xml配置peoperties配置Druid配置類

SpringBoot2.1.2 整合 Druid

durid web監控詳解

搭建通用的SSM架構 (二) 使用druid管理資料源，以及使用Mybatis操作資料庫

Springboot 整合druid+mybatis+jta分布式事務+多資料源aop注解動态切換（一篇到位）

報錯： com.alibaba.druid.pool.DruidDataSource : abandon connection, owner thread

Springboot整合Mybatis增删查改、連接配接MYSQL資料庫及配置druid連接配接池

spring boot 和 mybatis 整合

Spring Boot + Spring Cloud 內建 Druid 資料源Spring Boot + Spring Cloud 內建 Druid 資料源

SpringBoot 之 Spring Boot 內建 Druid

Druid 之 java.sql.SQLException: sql injection violation

Druid報錯：com.alibaba.druid.parser.ParserException:sysntax error,except RPAREN,actual EXCEPT EXCEPT

解決: com.alibaba.druid.filter.FilterManager : load filter error, filter not found : sl4j問題描述問題分析問題解決

SpringBoot內建Mybatis,Redis，druid實作多資料源開發