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 運行測驗

本文由傳智教育博學谷教研團隊發布，

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

轉載請注明出處！

標籤：Java

上一篇：Java 將Excel轉為UOS

下一篇：etcd v3版本生產級集群搭建以及實作一鍵啟動腳本