课程2：使用 ANSI SQL 查询

目标

本节课展示在 Apache Drill 中如何去使用标准的 SQL 做分析：例如，使用聚合函数和 JOIN 用法。值得注意的是，Apache Drill 提供 ANSI SQL 支持，而不是一个 SQL 接口。

查询

现在，你知道了数据源的原始形式，使用 select * 去查询，尝试在每个数据源上运行一些简单但更有用的查询。这些查询演示了 Drill 是如何支持 ANSI SQL，并且是如何在一个 SELECT 语句中结合不同数据源的数据的。

展示聚合查询在一个单独的文件或是表中。使用 GROUP BY， WHERE， HAVING， ORDER BY 语法。
在 Hive，MapR-DB，和文件系统的数据源中执行 JOIN。
使用表和列的别名。
创建一个 Drill 视图。

聚合

切换到 hive 的 schema：


1. 0: jdbc:drill:> use hive.`default`;
2. +-------+-------------------------------------------+
3. |  ok   |                  summary                  |
4. +-------+-------------------------------------------+
5. | true  | Default schema changed to [hive.default]  |
6. +-------+-------------------------------------------+
7. 1 row selected

按月返回销售总量：


1. 0: jdbc:drill:> select `month`, sum(order_total)
2. from orders group by `month` order by 2 desc;
3. +------------+---------+
4. |   month    | EXPR$1  |
5. +------------+---------+
6. | June       | 950481  |
7. | May        | 947796  |
8. | March      | 836809  |
9. | April      | 807291  |
10. | July       | 757395  |
11. | October    | 676236  |
12. | August     | 572269  |
13. | February   | 532901  |
14. | September  | 373100  |
15. | January    | 346536  |
16. +------------+---------+
17. 10 rows selected

Drill 是支持 SQL 的聚合函数的，例如 SUM，MAX，AVG 和 MIN。标准的 SQL 语法以同样的方式运行在 Drill 查询中是和关系型数据库一样的。

需要注意的是，标记返回的 “month” 列是因为其在 SQL 中是保留字符，所以，需要加上引号。

按 month 和 state 字段分组，返回前 20 行销售总额：


1. 0: jdbc:drill:> select `month`, state, sum(order_total) as sales from orders group by `month`, state
2. order by 3 desc limit 20;
3. +-----------+--------+---------+
4. |   month   | state  |  sales  |
5. +-----------+--------+---------+
6. | May       | ca     | 119586  |
7. | June      | ca     | 116322  |
8. | April     | ca     | 101363  |
9. | March     | ca     | 99540   |
10. | July      | ca     | 90285   |
11. | October   | ca     | 80090   |
12. | June      | tx     | 78363   |
13. | May       | tx     | 77247   |
14. | March     | tx     | 73815   |
15. | August    | ca     | 71255   |
16. | April     | tx     | 68385   |
17. | July      | tx     | 63858   |
18. | February  | ca     | 63527   |
19. | June      | fl     | 62199   |
20. | June      | ny     | 62052   |
21. | May       | fl     | 61651   |
22. | May       | ny     | 59369   |
23. | October   | tx     | 55076   |
24. | March     | fl     | 54867   |
25. | March     | ny     | 52101   |
26. +-----------+--------+---------+
27. 20 rows selected

值得一提的是，这里将别名用在了 SUM 聚合函数上。Drill 是支持列别名和表别名的。

HAVING 语法

使用 HAVING 语法来约束聚合结果。

切换到 dfs.clicks 工作区间


1. 0: jdbc:drill:> use dfs.clicks;
2. +-------+-----------------------------------------+
3. |  ok   |                 summary                 |
4. +-------+-----------------------------------------+
5. | true  | Default schema changed to [dfs.clicks]  |
6. +-------+-----------------------------------------+
7. 1 row selected

按设备分组，设备点击数高过一定阀值的总数：


1. 0: jdbc:drill:> select t.user_info.device, count(*) from `clicks/clicks.json` t
2. group by t.user_info.device
3. having count(*) > 1000;
4. +---------+---------+
5. | EXPR$0  | EXPR$1  |
6. +---------+---------+
7. | IOS5    | 11814   |
8. | AOS4.2  | 5986    |
9. | IOS6    | 4464    |
10. | IOS7    | 3135    |
11. | AOS4.4  | 1562    |
12. | AOS4.3  | 3039    |
13. +---------+---------+
14. 6 rows selected

聚合函数从点击量的数据中，统计不同手机设备的点击量。在查询是附加激活和注册的设备总量必须大于 1000，方能返回统计结果。

UNION 操作

和之前相同的工作区间（dfs.clicks）。

clicks 和 campaign 的 UNION 用法


1. 0: jdbc:drill:> select t.trans_id transaction, t.user_info.cust_id customer from `clicks/clicks.campaign.json` t
2. union all
3. select u.trans_id, u.user_info.cust_id  from `clicks/clicks.json` u limit 5;
4. +-------------+------------+
5. | transaction |  customer  |
6. +-------------+------------+
7. | 35232       | 18520      |
8. | 31995       | 17182      |
9. | 35760       | 18228      |
10. | 37090       | 17015      |
11. | 37838       | 18737      |
12. +-------------+------------+

UNION all 查询存在于 clicks.campaign.json 和 clicks.json 两个文件中的所有记录。

子查询

切换工作空间：


1. 0: jdbc:drill:> use hive.`default`;
2. +-------+-------------------------------------------+
3. |  ok   |                  summary                  |
4. +-------+-------------------------------------------+
5. | true  | Default schema changed to [hive.default]  |
6. +-------+-------------------------------------------+
7. 1 row selected

通过 state 比较 order 的总量：


1. 0: jdbc:drill:> select ny_sales.cust_id, ny_sales.total_orders, ca_sales.total_orders
2. from
3. (select o.cust_id, sum(o.order_total) as total_orders from hive.orders o where state = 'ny' group by o.cust_id) ny_sales
4. left outer join
5. (select o.cust_id, sum(o.order_total) as total_orders from hive.orders o where state = 'ca' group by o.cust_id) ca_sales
6. on ny_sales.cust_id = ca_sales.cust_id
7. order by ny_sales.cust_id
8. limit 20;
9. +------------+------------+------------+
10. |  cust_id   |  ny_sales  |  ca_sales  |
11. +------------+------------+------------+
12. | 1001       | 72         | 47         |
13. | 1002       | 108        | 198        |
14. | 1003       | 83         | null       |
15. | 1004       | 86         | 210        |
16. | 1005       | 168        | 153        |
17. | 1006       | 29         | 326        |
18. | 1008       | 105        | 168        |
19. | 1009       | 443        | 127        |
20. | 1010       | 75         | 18         |
21. | 1012       | 110        | null       |
22. | 1013       | 19         | null       |
23. | 1014       | 106        | 162        |
24. | 1015       | 220        | 153        |
25. | 1016       | 85         | 159        |
26. | 1017       | 82         | 56         |
27. | 1019       | 37         | 196        |
28. | 1020       | 193        | 165        |
29. | 1022       | 124        | null       |
30. | 1023       | 166        | 149        |
31. | 1024       | 233        | null       |
32. +------------+------------+------------+

本示例演示 Drill 支持子查询。

CAST 函数

切换到 maprdb 工作区间：


1. 0: jdbc:drill:> use maprdb;
2. +-------+-------------------------------------+
3. |  ok   |               summary               |
4. +-------+-------------------------------------+
5. | true  | Default schema changed to [maprdb]  |
6. +-------+-------------------------------------+
7. 1 row selected (0.088 seconds)

返回消费数据的数据类型


1. 0: jdbc:drill:> select cast(row_key as int) as cust_id, cast(t.personal.name as varchar(20)) as name,
2. cast(t.personal.gender as varchar(10)) as gender, cast(t.personal.age as varchar(10)) as age,
3. cast(t.address.state as varchar(4)) as state, cast(t.loyalty.agg_rev as dec(7,2)) as agg_rev,
4. cast(t.loyalty.membership as varchar(20)) as membership
5. from customers t limit 5;
6. +----------+----------------------+-----------+-----------+--------+----------+-------------+
7. | cust_id  |         name         |  gender   |    age    | state  | agg_rev  | membership  |
8. +----------+----------------------+-----------+-----------+--------+----------+-------------+
9. | 10001    | "Corrine Mecham"     | "FEMALE"  | "15-20"   | "va"   | 197.00   | "silver"    |
10. | 10005    | "Brittany Park"      | "MALE"    | "26-35"   | "in"   | 230.00   | "silver"    |
11. | 10006    | "Rose Lokey"         | "MALE"    | "26-35"   | "ca"   | 250.00   | "silver"    |
12. | 10007    | "James Fowler"       | "FEMALE"  | "51-100"  | "me"   | 263.00   | "silver"    |
13. | 10010    | "Guillermo Koehler"  | "OTHER"   | "51-100"  | "mn"   | 202.00   | "silver"    |
14. +----------+----------------------+-----------+-----------+--------+----------+-------------+

注意以下查询要点：

CAST 函数是需要作用于表里的每个列的。函数将 MapR-DB/HBase 的二进制数据返回成可读的整形和字符串。另外，你需要使用 CONVERT_TO/CONVERT_FROM 函数去解析字符串列。CONVERT_TO/CONVERT_FROM 比 CAST 更高效。使用 CONVERT_TO 将二进制类型转换成任意类型。
row_key 列函数作为一个表的主键（本案例中是客户的 ID）。
表别名是必须的；否则列簇名将会被解析为表名，从而返回查询错误。

从字符串中删除引号

你可以使用 regexp_replace 函数，去删除查询结果中的引号。例如，将一个 state 名下的 “va” 替换为 va：


1. 0: jdbc:drill:> select cast(row_key as int), regexp_replace(cast(t.address.state as varchar(10)),'"','')
2. from customers t limit 1;
3. +------------+------------+
4. |   EXPR$0   |   EXPR$1   |
5. +------------+------------+
6. | 10001      | va         |
7. +------------+------------+
8. 1 row selected

创建视图命令

切换到工作区间


1. 0: jdbc:drill:> use dfs.views;
2. +-------+----------------------------------------+
3. |  ok   |                summary                 |
4. +-------+----------------------------------------+
5. | true  | Default schema changed to [dfs.views]  |
6. +-------+----------------------------------------+
7. 1 row selected

使用一个可变的工作区间：

一个可变（或是可写）的工作区间是能够被执行“写”操作的。这个属性是存储插件配置的一部分。你可以创建 Drill 视图和表在可变的工作区间中。

创建一个视图在 MapR-DB 表


1. 0: jdbc:drill:> create or replace view custview as select cast(row_key as int) as cust_id,
2. cast(t.personal.name as varchar(20)) as name,
3. cast(t.personal.gender as varchar(10)) as gender,
4. cast(t.personal.age as varchar(10)) as age,
5. cast(t.address.state as varchar(4)) as state,
6. cast(t.loyalty.agg_rev as dec(7,2)) as agg_rev,
7. cast(t.loyalty.membership as varchar(20)) as membership
8. from maprdb.customers t;
9. +-------+-------------------------------------------------------------+
10. |  ok   |                           summary                           |
11. +-------+-------------------------------------------------------------+
12. | true  | View 'custview' created successfully in 'dfs.views' schema  |
13. +-------+-------------------------------------------------------------+
14. 1 row selected

Drill 提供了和关系型数据库创建视图相似的语法 CREATE OR REPLACE VIEW。使用 OR REPLACE 选项可以很方便的去更新视图到最新，而不需要我们在创建之前删除它。值得注意的是，FROM 语法在本示例中必需指向 maprdb.customers。MapR-DB 表不能直接在 dfs.views 的工作区间中可视化。

不像传统数据库那样，视图还需要 DBA/开发驱动业务，在 Drill 中视图是基于文件系统的，属于轻量级的。视图只是一个特殊的文件，具有特殊的扩展名（.drill）。你可以本地文件系统或指定特定的工作区间去存储视图。你可以创建视图的语句中指定任何查询，对任何数据源进行查询。

Drill 提供了一个分散的元数据模型。Drill 能够去查询一些定义好的元数据，例如 Hive，HBase 和文件系统。Drill 还支持在文件系统中创建元数据。

从视图中查询数据：


1. 0: jdbc:drill:> select * from custview limit 1;
2. +----------+-------------------+-----------+----------+--------+----------+-------------+
3. | cust_id  |       name        |  gender   |   age    | state  | agg_rev  | membership  |
4. +----------+-------------------+-----------+----------+--------+----------+-------------+
5. | 10001    | "Corrine Mecham"  | "FEMALE"  | "15-20"  | "va"   | 197.00   | "silver"    |
6. +----------+-------------------+-----------+----------+--------+----------+-------------+
7. 1 row selected

一旦，用户知道哪些数据是直接可以从文件系统中可用的，视图可以用来读取数据到下游工具，例如：Tableau 和 MicroStrategy 用于分析和可视化。对于这些工具，视图是以一个“表”出现的，在它的可选“列”中。

查询数据源

继续使用 dfs.views 做查询。

连接用户视图和订单表：


1. 0: jdbc:drill:> select membership, sum(order_total) as sales from hive.orders, custview
2. where orders.cust_id=custview.cust_id
3. group by membership order by 2;
4. +------------+------------+
5. | membership |   sales    |
6. +------------+------------+
7. | "basic"    | 380665     |
8. | "silver"   | 708438     |
9. | "gold"     | 2787682    |
10. +------------+------------+
11. 3 rows selected

在该查询中，我们从 MapR-DB 表中读取数据，并且结合 Hive 中的订单信息。当跨数据源查询是，你需要完全限定表/视图名称。例如，订单表的前缀是 “hive”，这个是存储插件注册在 Drill 中的。对于 “custview” 我们不使用任何前缀，因为我们明确的把 dfs.views workspace 存储在工作区间。

值得注意的是，如果你的查询结果显示被截断，是因为你的行太长了，将显示的最大宽度设置为 10000：

在设置命令中不要使用分号。

连接客户，订单和点击量的数据：


1. 0: jdbc:drill:> select custview.membership, sum(orders.order_total) as sales from hive.orders, custview,
2. dfs.`/mapr/demo.mapr.com/data/nested/clicks/clicks.json` c
3. where orders.cust_id=custview.cust_id and orders.cust_id=c.user_info.cust_id
4. group by custview.membership order by 2;
5. +------------+------------+
6. | membership |   sales    |
7. +------------+------------+
8. | "basic"    | 372866     |
9. | "silver"   | 728424     |
10. | "gold"     | 7050198    |
11. +------------+------------+
12. 3 rows selected

三者连接从三个不同的数据源中选择一个查询：

hive.orders 表
custview（HBase 客户表的一个视图）
clicks.json 文件

连接列以连接两者的 cust_id 列为条件。视图工作区间用于该查询，以至于 custview 可以访问。而 hive.orders 表的查询也是可见的。

另外，需要注意的是，JSON 文件是不能直接在视图工作区间被可视化的，需要指定查询文件的全路径：


1. dfs.`/mapr/demo.mapr.com/data/nested/clicks/clicks.json`

5.课程2：使用 ANSI SQL 查询