学习钻MapR Sandbox Lesson3：对复杂数据类型进行查询《译》

目标

这节课重点练习查询自我描述数据和复杂数据类型函数和运算符。钻提供了直观的SQL扩展使用这些数据并提供高性能查询与一组复杂数据架构。

查询在这节课中

现在您已经运行ANSI SQL查询不同的表格和关系数据文件,你可以尝试一些例子包括复杂类型。

在一个单一的SELECT声明语句访问文件的目录和子目录。
演示简单的方法访问JSON文件中复杂的数据。
演示repeated_count函数在一个数组中聚合值。

查询分区目录

您可以在你的工作区路径在drill中使用特殊变量参考子目录:

dir0
dir1
…

注意,这些变量是基于分区的文件系统动态确定的。不需要预先定义分区存在。这是一个视觉的例子展示它是如何工作的:

设置dfs.logs工作区：

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

为特定的一年查询日志数据：

0: jdbc:drill:> select * from logs where dir0='2013' limit 10;
+-------+-------+-----------+-------------+-----------+----------+---------+--------+----------+-----------+----------+-------------+
| dir0  | dir1  | trans_id  |    date     |   time    | cust_id  | device  | state  | camp_id  | keywords  | prod_id  | purch_flag  |
+-------+-------+-----------+-------------+-----------+----------+---------+--------+----------+-----------+----------+-------------+
| 2013  | 8     | 12104     | 08/29/2013  | 09:34:37  | 962      | IOS5    | ma     | 3        | milhouse  | 17       | false       |
| 2013  | 8     | 12132     | 08/23/2013  | 01:11:25  | 4        | IOS7    | mi     | 11       | hi        | 439      | false       |
| 2013  | 8     | 12177     | 08/14/2013  | 13:48:50  | 23       | AOS4.2  | il     | 14       | give      | 382      | false       |
| 2013  | 8     | 12180     | 08/03/2013  | 20:48:45  | 1509     | IOS7    | ca     | 0        | i'm       | 340      | false       |
| 2013  | 8     | 12187     | 08/16/2013  | 10:28:07  | 0        | IOS5    | ny     | 16       | clicking  | 11       | false       |
| 2013  | 8     | 12190     | 08/10/2013  | 14:16:50  | 9        | IOS5    | va     | 3        | a         | 495      | false       |
| 2013  | 8     | 12200     | 08/02/2013  | 20:54:38  | 42219    | IOS5    | ia     | 0        | what's    | 346      | false       |
| 2013  | 8     | 12210     | 08/05/2013  | 20:12:24  | 8073     | IOS5    | sc     | 5        | if        | 33       | false       |
| 2013  | 8     | 12235     | 08/28/2013  | 07:49:45  | 595      | IOS5    | tx     | 2        | that      | 51       | false       |
| 2013  | 8     | 12239     | 08/13/2013  | 03:24:31  | 2        | IOS5    | or     | 6        | haw-haw   | 40       | false       |
+-------+-------+-----------+-------------+-----------+----------+---------+--------+----------+-----------+----------+-------------+
10 rows selected

这个查询约束文件内的子目录命名为2013。变量dir0指日志第一级列,dir1是下一个水平列,等等。这个查询返回2013年8月份的10行。

使用用多谓语进一步限制查询结果:

这个查询返回一个在2013年8月通过IOS5设备购购物的用户id列表。


0: jdbc:drill:> select dir0 as yr, dir1 as mth, cust_id from logs
where dir0='2013' and dir1='8' and device='IOS5' and purch_flag='true'
order by `date`;
+-------+------+----------+
|  yr   | mth  | cust_id  |
+-------+------+----------+
| 2013  | 8    | 4        |
| 2013  | 8    | 521      |
| 2013  | 8    | 1        |
| 2013  | 8    | 2        |

...

对于一个给定年份返回每个月每个客户的数量：

0: jdbc:drill:> select cust_id, dir1 month_no, count(*) month_count from logs
where dir0=2014 group by cust_id, dir1 order by cust_id, month_no limit 10;
+----------+-----------+--------------+
| cust_id  | month_no  | month_count  |
+----------+-----------+--------------+
| 0        | 1         | 143          |
| 0        | 2         | 118          |
| 0        | 3         | 117          |
| 0        | 4         | 115          |
| 0        | 5         | 137          |
| 0        | 6         | 117          |
| 0        | 7         | 142          |
| 0        | 8         | 19           |
| 1        | 1         | 66           |
| 1        | 2         | 59           |
+----------+-----------+--------------+
10 rows selected

这个查询为2014年组聚合客户ID和月函数。

复杂数据查询

钻提供了一些专门的操作符和函数,您可以使用它分析嵌套数据（本身没有转换）。如果您熟悉 JavaScript注释,你就会知道这些扩展是如何工作的。

设置dfs.clicks工作区:


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

探测点击流数据：

注意,user_info和trans_info列包含嵌套数据:数组和在数组内的数组。以下查询展示如何访问这些复杂的数据。


0: jdbc:drill:> select * from `clicks/clicks.json` limit 5;
+-----------+-------------+-----------+---------------------------------------------------+---------------------------------------------------------------------------+
| trans_id  |    date     |   time    |                     user_info                     |                                trans_info                                 |
+-----------+-------------+-----------+---------------------------------------------------+---------------------------------------------------------------------------+
| 31920     | 2014-04-26  | 12:17:12  | {"cust_id":22526,"device":"IOS5","state":"il"}    | {"prod_id":[174,2],"purch_flag":"false"}                                  |
| 31026     | 2014-04-20  | 13:50:29  | {"cust_id":16368,"device":"AOS4.2","state":"nc"}  | {"prod_id":[],"purch_flag":"false"}                                       |
| 33848     | 2014-04-10  | 04:44:42  | {"cust_id":21449,"device":"IOS6","state":"oh"}    | {"prod_id":[582],"purch_flag":"false"}                                    |
| 32383     | 2014-04-18  | 06:27:47  | {"cust_id":20323,"device":"IOS5","state":"oh"}    | {"prod_id":[710,47],"purch_flag":"false"}                                 |
| 32359     | 2014-04-19  | 23:13:25  | {"cust_id":15360,"device":"IOS5","state":"ca"}    | {"prod_id":[0,8,170,173,1,124,46,764,30,711,0,3,25],"purch_flag":"true"}  |
+-----------+-------------+-----------+---------------------------------------------------+---------------------------------------------------------------------------+
5 rows selected

打开user_info列：

0: jdbc:drill:> select t.user_info.cust_id as custid, t.user_info.device as device,
t.user_info.state as state
from `clicks/clicks.json` t limit 5;
+---------+---------+--------+
| custid  | device  | state  |
+---------+---------+--------+
| 22526   | IOS5    | il     |
| 16368   | AOS4.2  | nc     |
| 21449   | IOS6    | oh     |
| 20323   | IOS5    | oh     |
| 15360   | IOS5    | ca     |
+---------+---------+--------+
5 rows selected (0.171 seconds)

这个查询使用一个简单的table.column.column符号提取嵌套列数据。例如:

t.user_info.cust_id

t是表在查询中提供的的别名，user_info是一个顶级列名称而cust_id是一个嵌套列名。

表的别名是必需的;否则列名例如user_info等会被 SQL解析器解析成表名。

打开trans_info列:

0: jdbc:drill:> select t.trans_info.prod_id as prodid, t.trans_info.purch_flag as
purchased
from `clicks/clicks.json` t limit 5;
+-------------------------------------------+------------+
|                  prodid                   | purchased  |
+-------------------------------------------+------------+
| [174,2]                                   | false      |
| []                                        | false      |
| [582]                                     | false      |
| [710,47]                                  | false      |
| [0,8,170,173,1,124,46,764,30,711,0,3,25]  | true       |
+-------------------------------------------+------------+
5 rows selected

♣ 注意,这个结果表明prod_id列包含id的数组(每行一个或多个产品ID值之间用逗号分隔)。下一个步展示如何访问这种数据。

查询数组

现在使用[n]符号,其中n是一个数组中的值的位置, 从位置0(不是1)开始第一个值。你可以使用这个针对嵌套数组数据的符号来编写有趣的查询。

例如:

trans_info.prod_id[0]

是指在嵌套prod_id列中的第一个值

trans_info.prod_id[20]

指第21个值，假设1存在。

在每笔交易中搜索发现第一个产品：

0: jdbc:drill:> select t.trans_id, t.trans_info.prod_id[0] from `clicks/clicks.json` t limit 5;
+------------+------------+
|  trans_id  |   EXPR$1   |
+------------+------------+
| 31920      | 174        |
| 31026      | null       |
| 33848      | 582        |
| 32383      | 710        |
| 32359      | 0          |
+------------+------------+
5 rows selected

顾客交易搜索至少21个产品？

0: jdbc:drill:> select t.trans_id, t.trans_info.prod_id[20]
from `clicks/clicks.json` t
where t.trans_info.prod_id[20] is not null
order by trans_id limit 5;
+------------+------------+
|  trans_id  |   EXPR$1   |
+------------+------------+
| 10328      | 0          |
| 10380      | 23         |
| 10701      | 1          |
| 11100      | 0          |
| 11219      | 46         |
+------------+------------+
5 rows selected

这个查询返回交易ID和产品ID作为记录，记录包括在数组中第二十一个位置的一个非空产品ID

点击返回为一个特定的产品范围：

0: jdbc:drill:> select * from (select t.trans_id, t.trans_info.prod_id[0] as prodid,
t.trans_info.purch_flag as purchased
from `clicks/clicks.json` t) sq
where sq.prodid between 700 and 750 and sq.purchased='true'
order by sq.prodid;
+------------+------------+------------+
|  trans_id  |   prodid   | purchased  |
+------------+------------+------------+
| 21886      | 704        | true       |
| 20674      | 708        | true       |
| 22158      | 709        | true       |
| 34089      | 714        | true       |
| 22545      | 714        | true       |
| 37500      | 717        | true       |
| 36595      | 718        | true       |
...

这个查询假设对数组有意义(它是一个产品购买的有序列表,而不是一个随机的列表)。

对数组进行操作

成功的排名点击转化和为每个会话搜索的产品计数:


0: jdbc:drill:> select t.trans_id, t.`date` as session_date, t.user_info.cust_id as
cust_id, t.user_info.device as device, repeated_count(t.trans_info.prod_id) as
prod_count, t.trans_info.purch_flag as purch_flag
from `clicks/clicks.json` t
where t.trans_info.purch_flag = 'true' order by prod_count desc;
+------------+--------------+------------+------------+------------+------------+
|  trans_id  | session_date |  cust_id   |   device   | prod_count | purch_flag |
+------------+--------------+------------+------------+------------+------------+
| 37426      | 2014-04-06   | 18709      | IOS5       | 34         | true       |
| 31589      | 2014-04-16   | 18576      | IOS6       | 31         | true       |
| 11600      | 2014-04-07   | 4260       | AOS4.2     | 28         | true       |
| 35074      | 2014-04-03   | 16697      | AOS4.3     | 27         | true       |
| 17192      | 2014-04-22   | 2501       | AOS4.2     | 26         | true       |
...

这个查询使用了一个SQL扩展，repeated_count函数得到一个计算数组值的聚合。查询返回产品数量寻找每个转变为购买和排名会话,降序排列。只有购买被计数的点击有结果。

对存储在一个表上的结果集进行重用和分析

促进额外的分析结果集,您可以从查询结果中很容易地和快速创建一个钻表。

继续使用dfs.clicks工作区

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

★为高价值客户返回产品搜索：

0: jdbc:drill:> select o.cust_id, o.order_total, t.trans_info.prod_id[0] as prod_id
from 
hive.orders as o
join `clicks/clicks.json` t
on o.cust_id=t.user_info.cust_id
where o.order_total > (select avg(inord.order_total)
                      from hive.orders inord
                      where inord.state = o.state);
+----------+--------------+----------+
| cust_id  | order_total  | prod_id  |
+----------+--------------+----------+
| 1328     | 73           | 26       |
| 1328     | 146          | 26       |
| 1328     | 56           | 26       |
| 1328     | 91           | 26       |
| 1328     | 74           | 26       |
...
+----------+--------------+----------+
107,482 rows selected (14.863 seconds)

这个查询返回了一列产品搜索结果，交易量大于他们州的平均值的客户。

▲▲▲♣实现前面查询的结果：

0: jdbc:drill:> create table product_search as select o.cust_id, o.order_total, t.trans_info.prod_id[0] as prod_id
from
hive.orders as o
join `clicks/clicks.json` t
on o.cust_id=t.user_info.cust_id
where o.order_total > (select avg(inord.order_total)
                      from hive.orders inord
                      where inord.state = o.state);
+-----------+----------------------------+
| Fragment  | Number of records written  |
+-----------+----------------------------+
| 0_0       | 107482                     |
+-----------+----------------------------+
1 row selected (3.488 seconds)

这个示例使用了CTAS声明创建一个基于相关子查询的表您在前面跑。这个表包含了所有的行查询返回(107482)并通过存储插件将它们存储在指定的格式(在这个例子中Parquet格式)。您可以创建表存储数据在csv、parquet和json格式。

查询新表来验证行数:

这个例子仅仅检查CTAS声明通过验证表中的行数工作。

0: jdbc:drill:> select count(*) from product_search;
+---------+
| EXPR$0  |
+---------+
| 107482  |
+---------+
1 row selected (0.155 seconds)

找到表的存储文件：

[root@maprdemo product_search]# cd /mapr/demo.mapr.com/data/nested/product_search
[root@maprdemo product_search]# ls -la
total 451
drwxr-xr-x. 2 mapr mapr      1 Sep 15 13:41 .
drwxr-xr-x. 4 root root      2 Sep 15 13:41 ..
-rwxr-xr-x. 1 mapr mapr 460715 Sep 15 13:41 0_0_0.parquet

注意：表示被存储在一个名为0_0_.parquet文件。这个文件通过dfs.clicks工作区被存储定义在：

"location": "http://demo.mapr.com/data/nested"

这有一个相同名称的子目录作为你创建的表。

总结→

Lesson123 总结

本教程介绍了Apache Drill和其运行ANSI SQL对不同的数据源运行查询的能力，包括hive表、MapR-DB / HBase表和文件系统目录。本教程还展示了如何在Hadoop / NoSQL系统操作复杂和多结构数据随处可见。

既然你熟悉了drill的不同的方式访问示例数据,你可以尝试编写自己的查询自己的数据源。

分析高动态数据集 →