分析高动态数据集
大数据是动态的并由应用驱动。互联网时代的商业软件的发展由不同的产业端所驱动，如网页端，媒体端，移动端，物联网。他们所生成的数据集，包含了新的数据类型和模型。这些应用都是交互式的，他们所关联的数据模型一般都是半结构化，schema-less，以及持续变化的。半结构化数据模型可以是复杂/嵌套或 schema-less，并且能够在每一行中包含不同的字段，为满足业务需求，字段会频繁修改。
本教程将向你展示如何查询动态的原生数据集，例如 JSON，并在几分钟内从任意类型的数据中获得有效信息。示例中使用的数据集来自 Yelp 签到数据集，其结构如下：
check-in
{
'type': 'checkin',
'business_id': (encrypted business id),
'checkin_info': {
'0-0': (number of checkins from 00:00 to 01:00 on all Sundays),
'1-0': (number of checkins from 01:00 to 02:00 on all Sundays),
...
'14-4': (number of checkins from 14:00 to 15:00 on all Thursdays),
...
'23-6': (number of checkins from 23:00 to 00:00 on all Saturdays)
}, # if there was no checkin for a hour-day block it will not be in the dataset
}
请特别注意此段代码底部的注释：
If there was no checkin for a hour-day block it will not be in the dataset.
你在 checkin_info 中看到的元素名称事先是未知的，并且每行都可能不同。数据虽然简单，但却是高度动态的数据。想要分析数据，无需在 Hadoop 平台上那样，需要先以扁平结构表示数据集，然后才能使用 SQL 类工具。
第 1 步：首先将 Drill（如果你还没有安装 Drill）下载到你的主机
http://drill.apache.org/download/
tar -xvf apache-drill-1.19.0.tar
本地安装 Drill（嵌入式模式）。并不需要安装 Hadoop。
第 2 步：嵌入式启动 Drill 。
bin/drill-embedded
第 3 步：开始使用 SQL 分析数据
首先，让我们查看一下数据集：
0: jdbc:drill:zk=local> SELECT * FROM dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_checkin.json` limit 2;
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|------------|------------------------|
| checkin_info | type | business_id |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|------------|------------------------|
| {"3-4":1,"13-5":1,"6-6":1,"14-5":1,"14-6":1,"14-2":1,"14-3":1,"19-0":1,"11-5":1,"13-2":1,"11-6":2,"11-3":1,"12-6":1,"6-5":1,"5-5":1,"9-2":1,"9-5":1,"9-6":1,"5-2":1,"7-6":1,"7-5":1,"7-4":1,"17-5":1,"8-5":1,"10-2":1,"10-5":1,"10-6":1} | checkin | JwUE5GmEO-sH1FuwJgKBlQ |
| {"6-6":2,"6-5":1,"7-6":1,"7-5":1,"8-5":2,"10-5":1,"9-3":1,"12-5":1,"15-3":1,"15-5":1,"15-6":1,"16-3":1,"10-0":1,"15-4":1,"10-4":1,"8-2":1} | checkin | uGykseHzyS5xAMWoN6YUqA |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|------------|------------------------|
Note
本文档为了展示方便对齐了 Drill 的输出。实际上 Drill 的输出不会这样对齐。
你可以直接查询 JSON 文件中的数据。 不必给 Hive 中的数据定义 schema。checkin_info 列中，第一行和第二行之间的元素名称是不同的。
Drill 提供了一个名为 KVGEN（键值生成器）的函数，该函数在处理由动态和未知元素名称（例如 checkin_info）组成的任意映射的复杂数据时非常有用。 KVGEN 将动态映射转换为键值对数组，其中键表示动态元素名称。
让我们在 checkin_info 元素上利用 KVGEN 来生成键值对。
0: jdbc:drill:zk=local> SELECT KVGEN(checkin_info) checkins FROM dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_checkin.json` LIMIT 2;
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| checkins |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| [{"key":"3-4","value":1},{"key":"13-5","value":1},{"key":"6-6","value":1},{"key":"14-5","value":1},{"key":"14-6","value":1},{"key":"14-2","value":1},{"key":"14-3","value":1},{"key":"19-0","value":1},{"key":"11-5","value":1},{"key":"13-2","value":1},{"key":"11-6","value":2},{"key":"11-3","value":1},{"key":"12-6","value":1},{"key":"6-5","value":1},{"key":"5-5","value":1},{"key":"9-2","value":1},{"key":"9-5","value":1},{"key":"9-6","value":1},{"key":"5-2","value":1},{"key":"7-6","value":1},{"key":"7-5","value":1},{"key":"7-4","value":1},{"key":"17-5","value":1},{"key":"8-5","value":1},{"key":"10-2","value":1},{"key":"10-5","value":1},{"key":"10-6","value":1}] |
| [{"key":"6-6","value":2},{"key":"6-5","value":1},{"key":"7-6","value":1},{"key":"7-5","value":1},{"key":"8-5","value":2},{"key":"10-5","value":1},{"key":"9-3","value":1},{"key":"12-5","value":1},{"key":"15-3","value":1},{"key":"15-5","value":1},{"key":"15-6","value":1},{"key":"16-3","value":1},{"key":"10-0","value":1},{"key":"15-4","value":1},{"key":"10-4","value":1},{"key":"8-2","value":1}] |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
Drill 提供了另一个称为 Flatten 函数来处理复杂数据，可以将 KVGen 函数产生的键值对列表分解为单独的行，以进一步对其使用分析函数。
0: jdbc:drill:zk=local> SELECT FLATTEN(KVGEN(checkin_info)) checkins FROM dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_checkin.json` LIMIT 20;
|--------------------------|
| checkins |
|--------------------------|
| {"key":"3-4","value":1} |
| {"key":"13-5","value":1} |
| {"key":"6-6","value":1} |
| {"key":"14-5","value":1} |
| {"key":"14-6","value":1} |
| {"key":"14-2","value":1} |
| {"key":"14-3","value":1} |
| {"key":"19-0","value":1} |
| {"key":"11-5","value":1} |
| {"key":"13-2","value":1} |
| {"key":"11-6","value":2} |
| {"key":"11-3","value":1} |
| {"key":"12-6","value":1} |
| {"key":"6-5","value":1} |
| {"key":"5-5","value":1} |
| {"key":"9-2","value":1} |
| {"key":"9-5","value":1} |
| {"key":"9-6","value":1} |
| {"key":"5-2","value":1} |
| {"key":"7-6","value":1} |
|--------------------------|
你可以对数据集使用 KVGEN 和 FLATTEN 函数，来快速从数据中获得有价值的信息——并且无需耗时在 schema 定义和数据存储格式。
在扁平化数据的输出中，你可以使用标准的 SQL 功能，例如过滤器、聚合和排序。让我们看几个例子。
获取 Yelp 数据集中签到记录的总数
0: jdbc:drill:zk=local> SELECT SUM(checkintbl.checkins.`value`) AS TotalCheckins FROM (
. . . . . . . . . . . > SELECT FLATTEN(KVGEN(checkin_info)) checkins FROM dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_checkin.json` ) checkintbl
. . . . . . . . . . . > ;
|---------------|
| TotalCheckins |
|---------------|
| 4713811 |
|---------------|
获取周日午夜的签到次数
0: jdbc:drill:zk=local> SELECT SUM(checkintbl.checkins.`value`) AS SundayMidnightCheckins FROM (
. . . . . . . . . . . > SELECT FLATTEN(KVGEN(checkin_info)) checkins FROM dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_checkin.json` ) checkintbl WHERE checkintbl.checkins.key='23-0';
|------------------------|
| SundayMidnightCheckins |
|------------------------|
| 8575 |
|------------------------|
获取一周内每天的签到次数
0: jdbc:drill:zk=local> SELECT `right`(checkintbl.checkins.key,1) WeekDay,sum(checkintbl.checkins.`value`) TotalCheckins from (
. . . . . . . . . . . > select flatten(kvgen(checkin_info)) checkins FROM dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_checkin.json` ) checkintbl GROUP BY `right`(checkintbl.checkins.key,1) ORDER BY TotalCheckins;
|---------|---------------|
| WeekDay | TotalCheckins |
|---------|---------------|
| 1 | 545626 |
| 0 | 555038 |
| 2 | 555747 |
| 3 | 596296 |
| 6 | 735830 |
| 4 | 788073 |
| 5 | 937201 |
|---------|---------------|
获取一天中每小时的签到次数
0: jdbc:drill:zk=local> SELECT SUBSTR(checkintbl.checkins.key,1,strpos(checkintbl.checkins.key,'-')-1) AS HourOfTheDay ,SUM(checkintbl.checkins.`value`) TotalCheckins FROM (
. . . . . . . . . . . > SELECT FLATTEN(KVGEN(checkin_info)) checkins FROM dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_checkin.json` ) checkintbl GROUP BY SUBSTR(checkintbl.checkins.key,1,strpos(checkintbl.checkins.key,'-')-1) ORDER BY TotalCheckins;
|--------------|---------------|
| HourOfTheDay | TotalCheckins |
|--------------|---------------|
| 3 | 20357 |
| 4 | 21076 |
| 2 | 28116 |
| 5 | 33842 |
| 1 | 45467 |
| 6 | 54174 |
| 0 | 74127 |
| 7 | 96329 |
| 23 | 102009 |
| 8 | 130091 |
| 22 | 140338 |
| 9 | 162913 |
| 21 | 211949 |
| 10 | 220687 |
| 15 | 261384 |
| 14 | 276188 |
| 16 | 292547 |
| 20 | 293783 |
| 13 | 328373 |
| 11 | 338675 |
| 17 | 374186 |
| 19 | 385381 |
| 12 | 399797 |
| 18 | 422022 |
|--------------|---------------|
小结
在本教程中，学习了如何处理结构化和半结构化数据，而无需定义 schema 或进行 ETL 预处理。