分析高动态数据集

大数据是动态的并由应用驱动。互联网时代的商业软件的发展由不同的产业端所驱动,如网页端,媒体端,移动端,物联网。他们所生成的数据集,包含了新的数据类型和模型。这些应用都是交互式的,他们所关联的数据模型一般都是半结构化,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 预处理。