课程2：使用 ANSI SQL 查询

课程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：

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

按月返回销售总量：

0: jdbc:drill:> select `month`, sum(order_total)
from orders group by `month` order by 2 desc;
+------------+---------+
|   month    | EXPR$1  |
+------------+---------+
| June       | 950481  |
| May        | 947796  |
| March      | 836809  |
| April      | 807291  |
| July       | 757395  |
| October    | 676236  |
| August     | 572269  |
| February   | 532901  |
| September  | 373100  |
| January    | 346536  |
+------------+---------+
10 rows selected

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

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

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

0: jdbc:drill:> select `month`, state, sum(order_total) as sales from orders group by `month`, state
order by 3 desc limit 20;
+-----------+--------+---------+
|   month   | state  |  sales  |
+-----------+--------+---------+
| May       | ca     | 119586  |
| June      | ca     | 116322  |
| April     | ca     | 101363  |
| March     | ca     | 99540   |
| July      | ca     | 90285   |
| October   | ca     | 80090   |
| June      | tx     | 78363   |
| May       | tx     | 77247   |
| March     | tx     | 73815   |
| August    | ca     | 71255   |
| April     | tx     | 68385   |
| July      | tx     | 63858   |
| February  | ca     | 63527   |
| June      | fl     | 62199   |
| June      | ny     | 62052   |
| May       | fl     | 61651   |
| May       | ny     | 59369   |
| October   | tx     | 55076   |
| March     | fl     | 54867   |
| March     | ny     | 52101   |
+-----------+--------+---------+
20 rows selected

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

HAVING 语法

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

切换到 dfs.clicks 工作区间

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

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

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

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

UNION 操作

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

clicks 和 campaign 的 UNION 用法

0: jdbc:drill:> select t.trans_id transaction, t.user_info.cust_id customer from `clicks/clicks.campaign.json` t
union all
select u.trans_id, u.user_info.cust_id  from `clicks/clicks.json` u limit 5;
+-------------+------------+
| transaction |  customer  |
+-------------+------------+
| 35232       | 18520      |
| 31995       | 17182      |
| 35760       | 18228      |
| 37090       | 17015      |
| 37838       | 18737      |
+-------------+------------+

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

子查询

切换工作空间：

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

通过 state 比较 order 的总量：

0: jdbc:drill:> select ny_sales.cust_id, ny_sales.total_orders, ca_sales.total_orders
from
(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
left outer join
(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
on ny_sales.cust_id = ca_sales.cust_id
order by ny_sales.cust_id
limit 20;
+------------+------------+------------+
|  cust_id   |  ny_sales  |  ca_sales  |
+------------+------------+------------+
| 1001       | 72         | 47         |
| 1002       | 108        | 198        |
| 1003       | 83         | null       |
| 1004       | 86         | 210        |
| 1005       | 168        | 153        |
| 1006       | 29         | 326        |
| 1008       | 105        | 168        |
| 1009       | 443        | 127        |
| 1010       | 75         | 18         |
| 1012       | 110        | null       |
| 1013       | 19         | null       |
| 1014       | 106        | 162        |
| 1015       | 220        | 153        |
| 1016       | 85         | 159        |
| 1017       | 82         | 56         |
| 1019       | 37         | 196        |
| 1020       | 193        | 165        |
| 1022       | 124        | null       |
| 1023       | 166        | 149        |
| 1024       | 233        | null       |
+------------+------------+------------+

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

CAST 函数

切换到 maprdb 工作区间：

0: jdbc:drill:> use maprdb;
+-------+-------------------------------------+
|  ok   |               summary               |
+-------+-------------------------------------+
| true  | Default schema changed to [maprdb]  |
+-------+-------------------------------------+
1 row selected (0.088 seconds)

返回消费数据的数据类型

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

注意以下查询要点：

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

从字符串中删除引号

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

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

创建视图命令

切换到工作区间

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

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

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

创建一个视图在 MapR-DB 表

0: jdbc:drill:> create or replace view custview as select cast(row_key as int) as cust_id,
cast(t.personal.name as varchar(20)) as name,
cast(t.personal.gender as varchar(10)) as gender,
cast(t.personal.age as varchar(10)) as age,
cast(t.address.state as varchar(4)) as state,
cast(t.loyalty.agg_rev as dec(7,2)) as agg_rev,
cast(t.loyalty.membership as varchar(20)) as membership
from maprdb.customers t;
+-------+-------------------------------------------------------------+
|  ok   |                           summary                           |
+-------+-------------------------------------------------------------+
| true  | View 'custview' created successfully in 'dfs.views' schema  |
+-------+-------------------------------------------------------------+
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 还支持在文件系统中创建元数据。

从视图中查询数据：

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

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

查询数据源

继续使用 dfs.views 做查询。

连接用户视图和订单表：

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

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

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

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

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

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

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

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

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

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

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

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