Apache Druid數據查詢套件詳解計數、排名和分位數計算（送JSON-over-HTTP和SQL兩種查詢詳解）

5. 數據查詢

欲看此文，必看如下兩篇文章：

Druid支持JSON-over-HTTP和SQL兩種查詢方式。除了標準的SQL操作外，Druid還支持大量的唯一性操作，利用Druid提供的演算法套件可以快速的進行計數，排名和分位數計算。

5.1 準備工作

5.1.1 導入大量數據

準備大量數據提供查詢,我們插入1萬條隨機打車數據

http://localhost:8010/taxi/batchTask/100000

5.2.2 查看數據攝取進程

我們發現數據攝取進程正在運行，可以等待數據攝取任務結束

5.3 原生查詢

Druid 最開始的時候是不支持 SQL 查詢的，原生查詢是通過查詢 Broker 提供的 http server 來實現的

5.3.1 查詢語法

curl -L -H'Content-Type:application/json' -XPOST --data-binary  @<query_json_file> <queryable_host>:<port>/druid/v2/?pretty

5.3.2 查詢案例

5.3.2.1 編輯查詢JSON

# 創建查詢目錄
mkdir query
# 編輯查詢的JSON
vi query/filter1.json

json 內容如下

{
    "queryType":"timeseries",
    "dataSource":"message",
    "granularity":"month",
    "aggregations":[
     {
            "type":"count",
            "name":"taxiNum"
     }
    ],
    "filter":{"type":"selector","dimension":"status","value":1},
    "intervals":["2021-06-07/2022-06-07"]
}

5.3.2.2 參數解釋

• queryType：查詢類型，timeseries代表時間序列查詢
• dataSource：數據源，指定需要查詢的數據源是什麼
• granularity：分組粒度，指定需要進行分組的粒度是什麼樣的
• aggregations：聚合查詢：裡面我們聚合了count，對數據進行統計
• filter：數據過濾，需要查詢那些數據
• intervals：查詢時間的範圍，註意時間範圍是前閉後開的，後面的日期是查詢不到的

5.3.2.3 執行查詢命令

在命名行中執行下麵的命令會將查詢json發送到對應的broker中進行查詢--data-binary指定的查詢json的路徑

curl -L -H 'Content-Type:application/json' -XPOST --data-binary @query/filter1.json http://192.168.64.177:8082/druid/v2/?pretty

我們查詢了每個月發起打車的人數有多少

5.4 查詢類型

druid查詢採用的是HTTP RESTFUL方式，REST介面負責接收客戶端的查詢請求，客戶端只需要將查詢條件封裝成JSON格式，通過HTTP方式將JSON查詢條件發送到broker節點，查詢成功會返回JSON格式的結果數據。瞭解一下druid提供的查詢類型

5.4.1 時間序列查詢

timeseries時間序列查詢對於指定時間段按照查詢規則返回聚合後的結果集，查詢規則中可以設置查詢粒度，結果排序方式以及過濾條件，過濾條件可以使用嵌套過濾，並且支持後聚合。

5.4.1.1 查詢屬性

時間序列查詢主要包括7個主要部分

5.4.1.2 案例

{
    "queryType":"topN",
    "dataSource":"taxi_message",
    "dimension":"local",
    "threshold":2,
    "metric":"age",
    "granularity":"month",
    "aggregations":[
     {
        "type":"longMin",
        "name":"age",
        "fieldName":"age"
     }
     ],
    "filter":{"type":"selector","dimension":"sex","value":"女"},
    "intervals":["2021-06-07/2022-06-07"]
}

5.4.2 TopN查詢

topn查詢是通過給定的規則和顯示維度返回一個結果集，topn查詢可以看做是給定排序規則，返回單一維度的group by查詢，但是topn查詢比group by性能更快。metric這個屬性是topn專屬的按照該指標排序。

5.4.2.1 查詢屬性

topn的查詢屬性如下

5.4.2.2 案例

查詢每個季度年齡最小的女性的前兩個的城市

vi query/topN.json

{
    "queryType":"topN",
    "dataSource":"message",
    "dimension":"local",
    "threshold":2,
    "metric":"age",
    "granularity":"quarter",
    "aggregations":[
     {
            "type":"longMin",
            "name":"age",
         	"fieldName":"age"
     }
      ],
    "filter":{"type":"selector","dimension":"sex","value":"女"},
    "intervals":["2021-06-07/2022-06-07"]
}

5.4.2.3 執行查詢

curl -L -H 'Content-Type:application/json' -XPOST --data-binary @query/topN.json http://192.168.64.177:8082/druid/v2/?pretty

5.4.5 分組查詢

在實際應用中經常需要進行分組查詢，等同於sql語句中的Group by查詢，如果對單個維度和指標進行分組聚合計算，推薦使用topN查詢，能夠獲得更高的查詢性能，分組查詢適合多維度，多指標聚合查詢

5.4.5.1 查詢屬性

下表內容為一個GroupBy查詢的主要部分：

5.4.5.2 案例

每一季度統計年齡在21-31的男女打車的數量

vi query/groupBy.json

{
    "queryType":"groupBy",
    "dataSource":"taxi_message",
    "granularity":"Quarter",
    "dimensions":["sex"],
    "aggregations":[
     {
            "type":"count",
            "name":"taxiNum"
     }
    ],
    "filter":{
		"type":"bound",
		"dimension":"age",
		"lower":"21",
		"upper":"31", 
		"alphaNumeric":true 
    },
    "intervals":["2021-06-07/2022-06-07"]
}

5.4.5.3 執行查詢

curl -L -H 'Content-Type:application/json' -XPOST --data-binary @query/groupBy.json http://192.168.64.177:8082/druid/v2/?pretty

5.5 查詢組件

在介紹具體的查詢之前，我們先來瞭解一下各種查詢都會用到的基本組件，如Filter,Aggregator,Post-Aggregator,Query,Interval等，每種組件都包含很多的細節

5.5.1 Filter

Filter就是過濾器，在查詢語句中就是一個JSON對象，用來對維度進行篩選和過濾，表示維度滿足Filter的行是我們需要的數據，類似sql中的where字句。Filter包含的類型如下：

5.5.1.1 選擇過濾器

Selector Filter的功能類似於SQL中的where key=value,它的json示例如下

"Filter":{"type":"selector","dimension":dimension_name,"value":target_value}

使用案例

vi query/filter1.json

{
    "queryType":"timeseries",
    "dataSource":"taxi_message",
    "granularity":"month",
    "aggregations":[
     {
            "type":"count",
            "name":"taxiNum"
     }
    ],
    "filter":{"type":"selector","dimension":"status","value":1},
    "intervals":["2021-06-07/2022-06-07"]
}

5.5.1.2 正則過濾器

Regex Filter 允許用戶使用正則表達式進行維度的過濾篩選，任何java支持的標準正則表達式druid都支持，它的JSON格式如下：

"filter":{"type":"regex","dimension":dimension_name,"pattern":regex}

使用案例，我們搜索姓名包含數字的的用戶進行聚合統計

vi query/filter2.json

{
    "queryType":"timeseries",
    "dataSource":"taxi_message",
    "granularity":"month",
    "aggregations":[
     {
            "type":"count",
            "name":"taxiNum"
     }
    ],
    "filter":{"type":"regex","dimension":"username","pattern":"[0-9]{1,}"},
    "intervals":["2021-06-07/2022-06-07"]
}

執行查詢

curl -L -H 'Content-Type:application/json' -XPOST --data-binary @query/filter2.json http://192.168.64.177:8082/druid/v2/?pretty

5.5.1.3 邏輯過濾器

Logincal Expression Filter包含and,not,or三種過濾器，每一種都支持嵌套，可以構建豐富的邏輯表達式，與sql中的and,not,or類似，JSON表達式如下：

"filter":{"type":"and","fields":[filter1,filter2]}
"filter":{"type":"or","fields":[filter1,filter2]}
"filter":{"type":"not","fields":[filter]}

使用案例，我們查詢每一個月，進行打車並且是女性的數量

vi query/filter3.json

{
    "queryType":"timeseries",
    "dataSource":"taxi_message",
    "granularity":"month",
    "aggregations":[
     {
            "type":"count",
            "name":"taxiNum"
     }
    ],
    "filter":{
        "type":"and",
        "fields":[
            {"type":"selector","dimension":"status","value":1},
            {"type":"selector","dimension":"sex","value":"女"}
        ]
    },
    "intervals":["2021-06-07/2022-06-07"]
}

進行數據查詢

curl -L -H 'Content-Type:application/json' -XPOST --data-binary @query/filter3.json http://192.168.64.177:8082/druid/v2/?pretty

5.5.1.4 包含過濾器

In Filter類似於SQL中的in， 比如 where username in('zhangsan','lisi','zhaoliu')，它的JSON格式如下：

{
	"type":"in",
	"dimension":"local",
	"values":['四川省','江西省','福建省']
}

使用案例，我們查詢每一個月，在四川省、江西省、福建省打車的人數

vi query/filter4.json

{
    "queryType":"timeseries",
    "dataSource":"taxi_message",
    "granularity":"month",
    "aggregations":[
     {
            "type":"count",
            "name":"taxiNum"
     }
    ],
    "filter":{
        "type":"in",
        "dimension":"local",
        "values":["四川省","江西省","福建省"]
    },
    "intervals":["2021-06-07/2022-06-07"]
}

進行數據查詢

curl -L -H 'Content-Type:application/json' -XPOST --data-binary @query/filter4.json http://192.168.64.177:8082/druid/v2/?pretty

5.5.1.5 區間過濾器

Bound Filter是比較過濾器，包含大於，等於，小於三種，它預設支持的就是字元串比較，是基於字典順序，如果使用數字進行比較，需要在查詢中設定alpaNumeric的值為true，需要註意的是Bound Filter預設的大小比較為>=或者<=，因此如果使用<或>,需要指定lowerStrict值為true，或者upperStrict值為true,它的JSON格式如下: 21 <=age<=31

{
"type":"bound",
"dimension":"age",
"lower":"21",  #預設包含等於
"upper":"31",  #預設包含等於
"alphaNumeric":true #數字比較時指定alphaNumeric為true
}

使用案例，我們查詢每一個月，年齡在21-31之間打車人的數量

vi query/filter5.json

{
    "queryType":"timeseries",
    "dataSource":"taxi_message",
    "granularity":"month",
    "aggregations":[
     {
            "type":"count",
            "name":"taxiNum"
     }
    ],
    "filter":{
		"type":"bound",
		"dimension":"age",
		"lower":"21",
		"upper":"31", 
		"alphaNumeric":true 
    },
    "intervals":["2021-06-07/2022-06-07"]
}

進行數據查詢

curl -L -H 'Content-Type:application/json' -XPOST --data-binary @query/filter5.json http://192.168.64.177:8082/druid/v2/?pretty

5.5.2 聚合粒度

聚合粒度通過granularity配置項指定聚合時間跨度，時間跨度範圍要大於等於創建索引時設置的索引粒度，druid提供了三種類型的聚合粒度分別是：Simple,Duration,Period

5.5.2.1 Simple的聚合粒度

Simple的聚合粒度通過druid提供的固定時間粒度進行聚合，以字元串表示，定義查詢規則的時候不需要顯示設置type配置項，druid提供的常用Simple粒度：

all,none,minute,fifteen_minute,thirty_minute,hour,day,month,Quarter（季度）,year；

• all：會將起始和結束時間內所有數據聚合到一起返回一個結果集，

• none：按照創建索引時的最小粒度做聚合計算，最小粒度是毫秒為單位，不推薦使用性能較差；

• minute：以分鐘作為聚合的最小粒度；

• fifteen_minute：15分鐘聚合；

• thirty_minute：30分鐘聚合

• hour：一小時聚合

• day：天聚合

• month：月聚合

• Quarter：季度聚合

• year：年聚合

編寫測試，我們這裡按照季度聚合，並且我們過濾年齡是21-31的數據，並且按照地域以及性別進行分組

vi query/filter6.json

{
    "queryType":"groupBy",
    "dataSource":"taxi_message",
    "granularity":"Quarter",
    "dimensions":["local","sex"],
    "aggregations":[
     {
            "type":"count",
            "name":"taxiNum"
     }
    ],
    "filter":{
		"type":"bound",
		"dimension":"age",
		"lower":"21",
		"upper":"31", 
		"alphaNumeric":true 
    },
    "intervals":["2021-06-07/2022-06-07"]
}

進行查詢

curl -L -H 'Content-Type:application/json' -XPOST --data-binary @query/filter6.json http://192.168.64.177:8082/druid/v2/?pretty

5.5.2.2 Duration聚合粒度

duration聚合粒度提供了更加靈活的聚合粒度，不只局限於Simple聚合粒度提供的固定聚合粒度，而是以毫秒為單位自定義聚合粒度，比如兩小時做一次聚合可以設置duration配置項為7200000毫秒，所以Simple聚合粒度不能夠滿足的聚合粒度可以選擇使用Duration聚合粒度。

註意：使用Duration聚合粒度需要設置配置項type值為duration

編寫測試，我們按照

vi query/filter7.json

{
    "queryType":"groupBy",
    "dataSource":"taxi_message",
    "granularity":{
    	"type":"duration",
    	"duration":7200000
    },
    "dimensions":["local","sex"],
    "aggregations":[
     {
            "type":"count",
            "name":"taxiNum"
     }
    ],
    "filter":{
		"type":"bound",
		"dimension":"age",
		"lower":"21",
		"upper":"31", 
		"alphaNumeric":true 
    },
    "intervals":["2021-06-07/2021-06-10"]
}

數據查詢

curl -L -H 'Content-Type:application/json' -XPOST --data-binary @query/filter7.json http://192.168.64.177:8082/druid/v2/?pretty

5.5.2.3 Period聚合粒度

Period聚合粒度採用了日期格式，常用的幾種時間跨度表示方法，一小時：PT1H,一周：P1W，一天：P1D，一個月：P1M；使用Period聚合粒度需要設置配置項type值為period,

編寫測試，我們按照一天進行聚合

vi query/filter8.json

{
    "queryType":"groupBy",
    "dataSource":"taxi_message",
    "granularity":{
    	"type":"period",
		"period":"P1D"
    },
    "dimensions":["sex"],
    "aggregations":[
     {
            "type":"count",
            "name":"taxiNum"
     }
    ],
    "filter":{
		"type":"bound",
		"dimension":"age",
		"lower":"21",
		"upper":"31", 
		"alphaNumeric":true 
    },
    "intervals":["2021-06-07/2021-06-10"]
}

數據查詢

curl -L -H 'Content-Type:application/json' -XPOST --data-binary @query/filter8.json http://192.168.64.177:8082/druid/v2/?pretty

5.5.3 聚合器

Aggregator是聚合器，聚合器可以在數據攝入階段和查詢階段使用，在數據攝入階段使用聚合器能夠在數據被查詢之前按照維度進行聚合計算，提高查詢階段聚合計算性能，在查詢過程中，使用聚合器能夠實現各種不同指標的組合計算。

5.5.3.1 公共屬性

聚合器的公共屬性介紹

• type：聲明使用的聚合器類型；

• name：定義返回值的欄位名稱，相當於sql語法中的欄位別名；

• fieldName：數據源中已定義的指標名稱，該值不可以自定義，必須與數據源中的指標名一致；

5.5.3.2 計數聚合

計數聚合器，等同於sql語法中的count函數，用於計算druid roll-up合併之後的數據條數，並不是攝入的原始數據條數，在定義數據模式指標規則中必須添加一個count類型的計數指標count;

比如想查詢Roll-up 後有多少條數據，查詢的JSON格式如下

vi query/aggregator1.json

{
    "queryType":"timeseries",
    "dataSource":"taxi_message",
    "granularity":"Quarter",
    "aggregations":[
     	{
            "type":"count",
            "name":"count"
     	}
    ],
    "filter":{
		"type":"bound",
		"dimension":"age",
		"lower":"21",
		"upper":"31", 
		"alphaNumeric":true 
    },
    "intervals":["2021-06-07/2022-06-07"]
}

5.5.3.3 求合聚合

求和聚合器，等同於sql語法中的sum函數，用戶指標求和計算，druid提供兩種類型的聚合器，分別是long類型和double類型的聚合器；

第一類就是longSum Aggregator ,負責整數類型的計算，JSON格式如下：

{"type":"longSum","name":out_name,"fieldName":"metric_name"}

第二類是doubleSum Aggregator,負責浮點數計算，JSON格式如下：

{"type":"doubleSum","name":out_name,"fieldName":"metric_name"}

示例

vi query/aggregator2.json

{
    "queryType":"timeseries",
    "dataSource":"taxi_message",
    "granularity":"Quarter",
    "aggregations":[
     	{
            "type":"longSum",
            "name":"ageSum",
            "fieldName":"age"
     	}
    ],
    "filter":{
		"type":"bound",
		"dimension":"age",
		"lower":"21",
		"upper":"31", 
		"alphaNumeric":true 
    },
    "intervals":["2021-06-07/2022-06-07"]
}

5.6 Druid SQL

Druid SQL是一個內置的SQL層，是Druid基於JSON的本地查詢語言的替代品，它由基於 Apache Calcite的解析器和規劃器提供支持

​ Druid SQL將SQL轉換為查詢Broker(查詢的第一個進程)上的原生Druid查詢，然後作為原生Druid查詢傳遞給數據進程。除了在Broker上 轉換SQL) 的（輕微）開銷之外，與原生查詢相比，沒有額外的性能損失。

5.6.1 基本查詢

5.6.1.1 查詢數據總條數

可以在druid的控制台進行查詢

select count(1) from "taxi_message"

5.6.1.2 查詢當前打車人數

我們可以統計出來當前的打車的人數

5.6.2 聚合功能

聚合函數可以出現在任何查詢的SELECT子句中。可以使用類似語法過濾任何聚合器 AGG(expr) FILTER(WHERE whereExpr)。過濾的聚合器僅聚合與其過濾器匹配的行。同一SQL查詢中的兩個聚合器可能具有不同的篩選器。

只有COUNT聚合可以接受DISTINCT。

5.6.2.1 查詢數據總條數

可以在druid的控制台進行查詢

select count(1) from "taxi_message"

5.7 客戶端API

我們在這裡實現SpringBoot+Mybatis實現SQL查詢ApacheDruid數據

5.7.1 引入Pom依賴

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>


        <dependency>
            <groupId>org.apache.calcite.avatica</groupId>
            <artifactId>avatica</artifactId>
            <version>1.18.0</version>
        </dependency>

        <dependency>
            <groupId>org.apache.calcite.avatica</groupId>
            <artifactId>avatica-server</artifactId>
            <version>1.18.0</version>
        </dependency>
    </dependencies>

5.7.1.1 配置數據源連接

在application.yml中配置資料庫的連接信息

• 連接時需註意Druid時區和JVM時區，不設置時區時預設採用JVM時區
• 文檔參考地址：https://calcite.apache.org/avatica/docs/client_reference.html

spring:
  datasource:
    # 連接池信息
    url: jdbc:avatica:remote:url=http://192.168.64.177:8082/druid/v2/sql/avatica/
    # 驅動信息
    driver-class-name: org.apache.calcite.avatica.remote.Driver

5.7.2 編寫代碼

5.7.2.1 編寫實體類

public class TaxiMessage {

    private String __time;
    private Integer age;
    private Integer createDate;
    private String local;
    private String sex;
    private Integer status;
    private String statusText;
    private String username;
	//setter getter 忽略

    @Override
    public String toString() {
        return "TaxiMessage{" +
                "__time='" + __time + '\'' +
                ", age=" + age +
                ", createDate=" + createDate +
                ", local='" + local + '\'' +
                ", sex='" + sex + '\'' +
                ", status=" + status +
                ", statusText='" + statusText + '\'' +
                ", username='" + username + '\'' +
                '}';
    }
}

5.7.2.2 編寫mapper

所有欄位名、表名必須使用如下方式標識 \"表名\"

@Mapper
public interface TaxiMessageMapper {
    @Select("SELECT * FROM \"taxi_message\" where username=#{username}")
    public TaxiMessage findByUserName(String username);
}

5.7.2.3 編寫Service

@Service
public class TaxiMessageService {
    @Autowired
    private TaxiMessageMapper taxiMessageMapper;

    public TaxiMessage findByUserName(String username) {
        return taxiMessageMapper.findByUserName(username);
    }
}

5.7.2.4 編寫啟動類

@SpringBootApplication
@MapperScan(basePackages = "com.heima.druid.mapper")
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class);
    }
}

5.7.2.5 編寫測試類

@RunWith(SpringRunner.class)
@SpringBootTest(classes = Application.class)
public class DruidTest {
    @Autowired
    private TaxiMessageService taxiMessageService;

    @Test
    public void test() {
        TaxiMessage taxiMessage = taxiMessageService.findByUserName("eFQfLXmXD4");
        System.out.println(taxiMessage);
        Assert.assertNotNull(taxiMessage);
    }
}

5.7.2.6 運行測試

本文由傳智教育博學谷教研團隊發佈。

如果本文對您有幫助，歡迎關註和點贊；如果您有任何建議也可留言評論或私信，您的支持是我堅持創作的動力。

轉載請註明出處！