HDInsight-Hadoop实战（一）站点日志分析

HDInsight-Hadoop实战（一）站点日志分析

 

简单介绍

在此演示样例中。你将使用分析站点日志文件的 HDInsight 查询来深入了解客户使用站点的方式。借助此分析。你可查看外部站点一天内对该站点的訪问频率以及用户体验的站点错误总结。

在此教程中，你将学习怎样使用 HDInsight：

• 连接到包括站点日志文件的 Azure Storage Blob
• 创建配置单元表以查询这些日志
• 创建配置单元查询以分析数据
• 使用 Microsoft Excel 连接到 HDInsight（使用 ODBC 连接）以检索已分析的数据

先决条件

已使用群集配置你完毕脚本和查询所需的全部内容。要将已分析的数据导出到 Microsoft Excel，你必须满足下面要求：

• 必须安装了 Microsoft Excel 2010 或 Microsoft     Excel 2013。
• 你必须具有 ​​Microsoft 配置单元 ODBC 驱动程序​​才干将数据从配置单元导入 Excel 中。基于 Microsoft     Excel 的版本号选择 32 位或 64 位版本号。
  

已载入到 Windows Azure 存储 Blob 的站点日志数据

下面是此演示样例使用的站点日志数据位置。你可从此页顶部的文件浏览器选项卡訪问此数据。也能够在 [default storage account]/[defaultcontainer]/HdiSamples/WebsiteLogSampleData/SampleLog 路径下訪问此演示样例的数据。

 

站点日志数据

wasb://[e-mail]/HdiSamples/WebsiteLogSampleData/SampleLog/

正在创建配置单元表以查询站点日志数据

下面配置单元语句创建了一个外部表。同意配置单元查询存储在 Azure Blob 存储中的数据。

外部表以初始文件格式保留数据，同一时候同意配置单元针对文件内的数据运行查询。

配置单元语句通过描写叙述文件内的字段、字段间的界定符以及  Azure Blob 存储中文件的位置创建了名为网络日志的新表。在此教程的创建配置单元查询以分析数据章节，你将针对存储在此表中的数据运行查询。

CreateExternal Table weblogs

DROP TABLE IFEXISTS weblogs;

 

--create tableweblogs on space-delimited website log data

CREATE EXTERNALTABLE IF NOT EXISTS weblogs(s_date date, s_time string, s_sitename string,cs_method string, cs_uristem string,

cs_uriquerystring, s_port int, cs_username string, c_ip string, cs_useragent string,

cs_cookiestring, cs_referer string, cs_host string, sc_status int, sc_substatus int,

sc_win32statusint, sc_bytes int, cs_bytes int, s_timetaken int )

ROW FORMATDELIMITED FIELDS TERMINATED BY ' '

STORED ASTEXTFILE LOCATION'wasb://[e-mail]/HdiSamples/WebsiteLogSampleData/SampleLog/'

TBLPROPERTIES('skip.header.line.count'='2');

 

创建配置单元查询以分析数据

下面配置单元查询基于网络日志表上运行的查询创建了两个新表。新表名为 clienterrors 和 refersperday。

clienterrors 的查询从介于 400 到 500 之间的 HTTP 状态代码的网络日志表中提取数据。而且按遭遇这些错误的用户以及错误代码类型对其进行分组。状态代码的范围介于 400 到 500 之间，通过网络日志表中的 sc_status 列表示，相应訪问站点时客户端遭遇的错误。

然后，提取的数据按每一个错误代码的发生次数进行排序并写入 clienterrors 表。

refersperday 的查询从引用此站点的全部外部站点的网络日志表中提取数据。外部站点信息从网络日志表的 cs_referer 列中提取。为了确保引用链接不遭遇错误，表仅显示返回 200 到 300 之间的 HTTP 状态代码的页面数据。然后，提取的数据将写入 refersperday 表。

DROP TABLE IFEXISTS ClientErrors;

 

--create tableClientErrors for storing errors users experienced and their frequencies

CREATE EXTERNALTABLE ClientErrors(sc_status int, cs_referer string, cs_page string, cnt int)

ROW FORMATDELIMITED FIELDS TERMINATED BY ',';

 

--populate tableClientErrors with data from table weblogs

INSERT OVERWRITETABLE ClientErrors

SELECT sc_status,cs_referer,

                                concat(cs_uristem,'?',regexp_replace(cs_uriquery,'X-ARR-LOG-ID=[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}',''))cs_page,

                                count(distinctc_ip) as cnt

FROM weblogs

WHERE sc_status>=400 and sc_status < 500

GROUP BYsc_status, cs_referer, concat(cs_uristem,'?',regexp_replace(cs_uriquery,'X-ARR-LOG-ID=[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}',''))

ORDER BY cnt;

-------------------------------------------------------------------------------------------

DROP TABLE IFEXISTS RefersPerDay;

 

--create tableRefersPerDay for storing references from external websites

CREATE EXTERNALTABLE IF NOT EXISTS RefersPerDay(year int, month int, day int, cs_refererstring, cnt int)

ROW FORMATDELIMITED FIELDS TERMINATED BY ',';

 

--populate tableRefersPerDay with data from the weblogs table

INSERT OVERWRITETABLE RefersPerDay

SELECTyear(s_date), month(s_date), day(s_date), cs_referer, count(distinct c_ip) ascnt

FROM weblogs

WHERE sc_status>=200 and sc_status <300

GROUP BY s_date,cs_referer

ORDER BY cntdesc;

-------------------------------------------------------------------------------------------

 

正在运行查询

单击提交以运行先前章节中显示的查询。查询运行下面任务：

2. 从 HDInsight 群集关联的 Azure     Blob 存储中的原始站点日志数据创建网络日志表。
3. 创建并填充先前章节中描写叙述的 clienterrors 和 refersperday 表。

运行查询时，你可单击查看具体信息来获取有关后台运行任务的很多其它信息。在页底全部作业都处于已完毕状态后。继续运行 将数据载入到 Excel。

DROP TABLE IFEXISTS weblogs;

 

--create tableweblogs on space-delimited website log data

CREATE EXTERNALTABLE IF NOT EXISTS weblogs(s_date date, s_time string, s_sitename string,cs_method string, cs_uristem string,

                                    cs_uriquerystring, s_port int, cs_username string, c_ip string, cs_useragent string,

                                    cs_cookiestring, cs_referer string, cs_host string, sc_status int, sc_substatus int,

                                    sc_win32statusint, sc_bytes int, cs_bytes int, s_timetaken int )

ROW FORMATDELIMITED FIELDS TERMINATED BY ' '

STORED ASTEXTFILE LOCATION 'wasb://[e-mail]/HdiSamples/WebsiteLogSampleData/SampleLog/'

TBLPROPERTIES('skip.header.line.count'='2');

 

DROP TABLE IFEXISTS ClientErrors;

 

--create tableClientErrors for storing errors users experienced and their frequencies

CREATE EXTERNALTABLE ClientErrors(sc_status int, cs_referer string, cs_page string, cnt int)

ROW FORMATDELIMITED FIELDS TERMINATED BY ',';

 

--populatetable ClientErrors with data from table weblogs

INSERTOVERWRITE TABLE ClientErrors

SELECTsc_status, cs_referer,

                        concat(cs_uristem,'?',regexp_replace(cs_uriquery,'X-ARR-LOG-ID=[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}',''))cs_page,

                        count(distinct c_ip) ascnt

FROM weblogs

WHERE sc_status>=400 and sc_status < 500

GROUP BYsc_status, cs_referer, concat(cs_uristem,'?

', regexp_replace(cs_uriquery,'X-ARR-LOG-ID=[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}',''))

ORDER BY cnt;

 

DROP TABLE IFEXISTS RefersPerDay;

 

--create tableRefersPerDay for storing references from external websites

CREATE EXTERNALTABLE IF NOT EXISTS RefersPerDay(year int, month int, day int, cs_refererstring, cnt int)

ROW FORMATDELIMITED FIELDS TERMINATED BY ',';

 

--populatetable RefersPerDay with data from the weblogs table

INSERTOVERWRITE TABLE RefersPerDay

SELECTyear(s_date), month(s_date), day(s_date), cs_referer, count(distinct c_ip) ascnt

FROM weblogs

WHERE sc_status>=200 and sc_status <300

GROUP BYs_date, cs_referer

ORDER BY cntdesc;

 

作业会话

查询名称	日期	ID	操作	状态
表中无可用数据

 

正在将数据载入到 Excel

创建配置单元表之后，你可使用 ​​Microsoft 配置单元 ODBC 驱动器​​将数据从配置单元导入到 Excel。

安装驱动程序后。请使用下面步骤连接到表。

1. 打开 Excel 并创建空白的工作表。

2. 从数据选项卡中，选择来自其它源。然后选择来自 Microsoft 查询。

3. 提示选择数据源时，选择演示样例 Microsoft 配置单元 DSN。

4. 在 Microsoft 配置单元 ODBC 驱动器连接对话框中。输入下面值，然后单击“确定”。

• 主机 - HDInsight 群集的主机名。比如。mycluster.azurehdinsight.net
• 用户名 - HDInsight 群集的管理员名称
• password - 管理员password

全部其它字段均为默认值。

5. 在查询向导中。选择 refersperday 表。然后选择 > button。

6. 单击下一步继续查看向导，直到到达带有完毕button的对话框。

单击完毕。

7. 出现导入数据对话框后，单击确定以接受默认值。

完毕查询后。数据将显示在 Excel 中。

摘要

在本教程中。你了解了怎样使用 Azure HDInsight 分析使用 Apache Hive 的站点日志数据。你浏览了一个流程，了解原始数据怎样先上载到  Azure 存储空间 Blob 再载入到配置单元表以便运行查询。最后，你了解了怎样将配置单元查询的结果导入到 Microsoft Excel。

假设你具有本教程或其它演示样例方面的反馈。请使用上面的帮助 + 反馈链接。

使用下面链接继续了解怎样将配置单元和 Excel 与 HDInsight 一同使用。