课程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_FROMCAST 更高效。使用 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`
Copyright © smartloli 2016 all right reserved,powered by GitbookModify: 2016-08-26 06:59:28