Apache Druid的SQL查询

Apache Druid还支持原生的Json格式查询，这里我们只讲解SQL查询。Druid的数据查询底层是通过发送HTTP请求

1. 通过dsql

查询2015年9月12日被编辑最多的10个维基百科页面

[root@bigdata001 apache-druid-0.22.1]# 
[root@bigdata001 apache-druid-0.22.1]# pwd
/opt/apache-druid-0.22.1
[root@bigdata001 apache-druid-0.22.1]# 
[root@bigdata001 apache-druid-0.22.1]# bin/dsql --host http://bigdata003:8082/
Welcome to dsql, the command-line client for Druid SQL.
Connected to [http://bigdata003:8082/].

Type "\h" for help.
dsql> 
dsql> \h
Commands:
  \d             show tables
  \dS            show tables, including system tables
  \d table_name  describe table
  \h             show this help
  \q             exit this program
Or enter a SQL query ending with a semicolon (;).
dsql> 
dsql> select page, count(*) as edits from wikipedia where "__time" between timestamp '2015-09-12 00:00:00' and timestamp '2015-09-13 00:00:00' group by page order by edits desc limit 10;
┌──────────────────────────────────────────────────────────┬───────┐
│ page                                                     │ edits │
├──────────────────────────────────────────────────────────┼───────┤
│ Wikipedia:Vandalismusmeldung                             │    33 │
│ User:Cyde/List of candidates for speedy deletion/Subpage │    28 │
│ Jeremy Corbyn                                            │    27 │
│ Wikipedia:Administrators' noticeboard/Incidents          │    21 │
│ Flavia Pennetta                                          │    20 │
│ Total Drama Presents: The Ridonculous Race               │    18 │
│ User talk:Dudeperson176123                               │    18 │
│ Wikipédia:Le Bistro/12 septembre 2015                    │    18 │
│ Wikipedia:In the news/Candidates                         │    17 │
│ Wikipedia:Requests for page protection                   │    17 │
└──────────────────────────────────────────────────────────┴───────┘
Retrieved 10 rows in 0.09s.

dsql> 
dsql> \q
[root@bigdata001 apache-druid-0.22.1]#

2. 通过HTTP

json文件内容如下：

[root@bigdata001 apache-druid-0.22.1]# 
[root@bigdata001 apache-druid-0.22.1]# pwd
/opt/apache-druid-0.22.1
[root@bigdata001 apache-druid-0.22.1]# 
[root@bigdata001 apache-druid-0.22.1]# cat quickstart/tutorial/wikipedia-top-pages-sql.json 
{
  "query":"SELECT page, COUNT(*) AS Edits FROM wikipedia WHERE \"__time\" BETWEEN TIMESTAMP '2015-09-12 00:00:00' AND TIMESTAMP '2015-09-13 00:00:00' GROUP BY page ORDER BY Edits DESC LIMIT 10"
}
[root@bigdata001 apache-druid-0.22.1]#

通过HTTP将SQL提交给Broker

[root@bigdata002 apache-druid-0.22.1]# 
[root@bigdata002 apache-druid-0.22.1]# curl -X 'POST' -H 'Content-Type:application/json' -d @quickstart/tutorial/wikipedia-top-pages-sql.json http://bigdata003:8888/druid/v2/sql
[{"page":"Wikipedia:Vandalismusmeldung","Edits":33},{"page":"User:Cyde/List of candidates for speedy deletion/Subpage","Edits":28},{"page":"Jeremy Corbyn","Edits":27},{"page":"Wikipedia:Administrators' noticeboard/Incidents","Edits":21},{"page":"Flavia Pennetta","Edits":20},{"page":"Total Drama Presents: The Ridonculous Race","Edits":18},{"page":"User talk:Dudeperson176123","Edits":18},{"page":"Wikipédia:Le Bistro/12 septembre 2015","Edits":18},{"page":"Wikipedia:In the news/Candidates","Edits":17},{"page":"Wikipedia:Requests for page protection","Edits":17}]
[root@bigdata002 apache-druid-0.22.1]#

3. 其它SQL查询示例

时间查询

dsql> select floor(__time to hour) as hourTime, sum(deleted) as linesDeleted from wikipedia group by 1 order by linesDeleted desc limit 3;
┌──────────────────────────┬──────────────┐
│ hourTime                 │ linesDeleted │
├──────────────────────────┼──────────────┤
│ 2015-09-12T13:00:00.000Z │        30520 │
│ 2015-09-12T15:00:00.000Z │        26399 │
│ 2015-09-12T16:00:00.000Z │        24759 │
└──────────────────────────┴──────────────┘
Retrieved 3 rows in 0.36s.

dsql>

获取SQL执行计划

dsql> explain plan for select floor(__time to hour) as hourTime, sum(deleted) as linesDeleted from wikipedia group by 1 order by linesDeleted desc limit 3;
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬────────────────────────────────────────────┐
│ PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       │ RESOURCES                                  │
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼────────────────────────────────────────────┤
│ DruidQueryRel(query=[{"queryType":"topN","dataSource":{"type":"table","name":"wikipedia"},"virtualColumns":[{"type":"expression","name":"v0","expression":"timestamp_floor(\"__time\",'PT1H',null,'UTC')","outputType":"LONG"}],"dimension":{"type":"default","dimension":"v0","outputName":"d0","outputType":"LONG"},"metric":{"type":"numeric","metric":"a0"},"threshold":3,"intervals":{"type":"intervals","intervals":["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},"filter":null,"granularity":{"type":"all"},"aggregations":[{"type":"longSum","name":"a0","fieldName":"deleted","expression":null}],"postAggregations":[],"context":{"sqlQueryId":"31562175-516c-4121-83d0-40a756c413b4"},"descending":false}], signature=[{d0:LONG, a0:LONG}]) │ [{"name":"wikipedia","type":"DATASOURCE"}] │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴────────────────────────────────────────────┘
Retrieved 1 row in 0.05s.

dsql>