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://yzphadoop01@yzpcloud.blob.core.chinacloudapi.cn/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://yzphadoop01@yzpcloud.blob.core.chinacloudapi.cn/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;

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

正在执行查询

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

从 HDInsight 群集关联的 Azure Blob 存储中的原始网站日志数据创建网络日志表。
创建并填充先前章节中描述的 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://yzphadoop01@yzpcloud.blob.core.chinacloudapi.cn/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 群集的管理员名称
密码 - 管理员密码

所有其它字段均为默认值。

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

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

7. 出现导入数据对话框后，单击确定以接受默认值。完成查询后，数据将显示在 Excel 中。

摘要

在本教程中，你了解了如何使用 Azure HDInsight 分析使用 Apache Hive 的网站日志数据。你浏览了一个流程，了解原始数据如何先上载到 Azure 存储空间 Blob 再加载到配置单元表以便执行查询。最后，你了解了如何将配置单元查询的结果导入到 Microsoft Excel。如果你具有本教程或其他示例方面的反馈，请使用上面的帮助 + 反馈链接。

使用以下链接继续了解如何将配置单元和 Excel 与 HDInsight 一同使用。