HDInsight-Hadoop实战（二）传感器数据分析
简介
现在很多个人和商业设备含有从物理世界中收集信息的传感器。例如，大多数电话拥有 GPS，健身设备可跟踪你走的步数，恒温控制器可监控建筑的温度。
在此教程中，你将了解如何将 HDInsight 用于处理暖气、通风和空气调节 (HVAC) 系统产生的历史数据，以识别不能有效维持设定温度的系统。你将了解如何：
- 优化和丰富来自多个国家/地区的建筑的温度数据
- 分析数据以确定哪些建筑在保持适宜温度方面存在问题（实际记录的温度对比恒温控制器设定的温度）
- 推断建筑中使用的 HVAC 系统的可靠性
- 在 Microsoft Excel 中可视化数据
先决条件
已使用群集配置你完成脚本和查询所需的所有内容。要将已分析的数据导出到 Microsoft Excel，你必须满足以下要求：
- 必须安装了 Microsoft Excel 2013。
- 你必须具有 Microsoft 配置单元 ODBC 驱动程序才能将数据从配置单元导入 Excel 中。基于 Microsoft Excel 的版本选择 32 位或 64 位版本。
已加载到 Windows Azure 存储 Blob 的传感器数据
以下
URL 的文件包含
HVAC 系统设定的温度以及实际记录的温度。这些文件也包含建筑元数据，如位置和
HVAC 系统信息。我们已将此数据加载到此群集的
Windows Azure 存储
Blob。
可使用此页顶部的文件浏览器选项卡访问存储在
Windows Azure 存储
Blob 中的数据。此示例的数据可在
[default storage account]/[defaultcontainer]/HdiSamples/SensorSampleData 路径下找到。
来自世界
20栋大型建筑的传感器数据
wasb://yzphadoop01@
yzpcloud.blob.core.chinacloudapi.cn/HdiSamples/SensorSampleData/hvac/
20 栋建筑的元数据
wasb://yzphadoop01@
yzpcloud.blob.core.chinacloudapi.cn/HdiSamples/SensorSampleData/building/
正在创建配置单元表，以查询 WindowsAzure Blob 存储中的传感器数据
以下配置单元语句将创建外部表，允许配置单元查询存储在
Azure Blob 存储的数据。外部表以初始文件格式保留数据，同时允许配置单元针对文件内的数据执行查询。在这种情况下，数据作为逗号分隔值
(CSV) 存储在文件中。
以下配置单元语句通过描述文件内字段、文件间定界符（逗号）和
Azure Blob 存储中文件的位置创建了两个名为
hvac 和
building 的新表。由此，你可以创建针对自身数据的配置单元查询。
Create hvac table:
DROP TABLE IFEXISTS hvac;
–create the hvactable on comma-separated sensor data
CREATE EXTERNALTABLE hvac(date STRING, time STRING, targettemp BIGINT,
actualtempBIGINT, system BIGINT, systemage BIGINT, buildingid BIGINT)
ROW FORMATDELIMITED FIELDS TERMINATED BY ','
STORED ASTEXTFILE LOCATION'wasb://yzphadoop01@
yzpcloud.blob.core.chinacloudapi.cn/HdiSamples/SensorSampleData/hvac/';
Create building table:
DROP TABLE IF EXISTS building;
–create the building table on comma-separated building data
CREATE EXTERNAL TABLE building(buildingid BIGINT,buildingmgr STRING,
buildingageBIGINT, hvacproduct STRING, country STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE LOCATION'wasb://yzphadoop01@
yzpcloud.blob.core.chinacloudapi.cn/HdiSamples/SensorSampleData/building/';
正在创建针对传感器数据的配置单元查询
以下配置单元查询创建了来自
HVAC 数据的一些温度，从而查找温度变化（参见以下查询）。尤其是查找恒温控制器设定的目标温度与记录温度之间的差别。如果差别大于
5，则
temp_diff 列将设为
“热
”或
“冷
”，且
extremetemp 设为
1；否则，
temp_diff 将设为
“正常
”且
extremetemp 设为
0。
查询会将结果写入两个新表：
hvac_temperatures 和
hvac_building（参见以下
“创建表
”语句）。
hvac_building 表将包含管理者、楼龄和建筑的
HVAC 系统等建筑信息，也将用于通过与
hvac_temperatures 表联接来查找建筑的温度数据。
Create hvac_temperatures table:
DROP TABLE IFEXISTS hvac_temperatures;
–create thehvac_temperatures table by selecting from the hvac table
CREATE TABLEhvac_temperatures AS
SELECT *,targettemp – actualtemp AS temp_diff,
IF((targettemp – actualtemp)> 5, 'COLD',
IF((targettemp – actualtemp)< -5, 'HOT', 'NORMAL')) AS temprange,
IF((targettemp – actualtemp)> 5, '1', IF((targettemp – actualtemp) < -5, '1', 0)) AS extremetemp
FROM hvac;
Create hvac_building table:
DROP TABLE IFEXISTS hvac_building;
–create thehvac_building table by joining the building table and the hvac_temperaturestable
CREATE TABLEhvac_building AS
SELECT h.*,b.country, b.hvacproduct, b.buildingage, b.buildingmgr
FROM building bJOIN hvac_temperatures h ON b.buildingid = h.buildingid;
正在执行查询
选择提交以创建
“配置单元
”表，并执行分析存储在
Windows AzureBlob 存储的传感器数据的查询。提交作业后，可通过选择下方查看详细信息来查看详细信息。
页底的作业状态变为已完成后，继续执行将数据加载到
Excel。
DROP TABLE IF EXISTS hvac;
–create the hvac table on comma-separated sensor data
CREATE EXTERNAL TABLE hvac(date STRING, time STRING,targettemp BIGINT,
actualtempBIGINT, system BIGINT, systemage BIGINT, buildingid BIGINT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE LOCATION'wasb://yzphadoop01@
yzpcloud.blob.core.chinacloudapi.cn/HdiSamples/SensorSampleData/hvac/';
DROP TABLE IF EXISTS building;
–create the building table on comma-separatedbuilding data
CREATE EXTERNAL TABLE building(buildingid BIGINT,buildingmgr STRING,
buildingageBIGINT, hvacproduct STRING, country STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE LOCATION'wasb://yzphadoop01@
yzpcloud.blob.core.chinacloudapi.cn/HdiSamples/SensorSampleData/building/';
DROP TABLE IF EXISTS hvac_temperatures;
–create the hvac_temperatures table by selecting fromthe hvac table
CREATE TABLE hvac_temperatures AS
SELECT *, targettemp – actualtemp AS temp_diff,
IF((targettemp- actualtemp) > 5, 'COLD',
IF((targettemp- actualtemp) < -5, 'HOT', 'NORMAL')) AS temprange,
IF((targettemp- actualtemp) > 5, '1', IF((targettemp – actualtemp) < -5, '1', 0)) ASextremetemp
FROM hvac;
DROP TABLE IF EXISTS hvac_building;
–create the hvac_building table by joining thebuilding table and the hvac_temperatures table
CREATE TABLE hvac_building AS
SELECT h.*, b.country, b.hvacproduct, b.buildingage,b.buildingmgr
FROM building b JOIN hvac_temperatures h ONb.buildingid = h.buildingid;
作业会话
| 查询名称 | 日期 | ID | 操作 | 状态 |
|---|---|---|---|---|
| 表中无可用数据 正在将数据加载到 Excel 成功完成此作业后，你可使用 Microsoft 配置单元 ODBC 驱动器将数据从配置单元导入到 Excel 2013。安装驱动程序后，可使用以下步骤连接到表格。 1. 打开 Excel 并创建空白的工作表。 2. 从数据选项卡中，选择来自其他源，然后选择来自 Microsoft 查询。 3. 提示选择数据源时，选择示例 Microsoft 配置单元 DSN。 4. 在 Microsoft 配置单元 ODBC 驱动器连接对话框中，输入以下值，然后单击“确定”。 主机 – HDInsight 群集的主机名。例如，mycluster.azurehdinsight.net 用户名 – HDInsight 群集的管理员名称 密码 – 管理员密码 所有其它字段均为默认值。 5. 在查询向导中，选择 hvac_building 表，然后选择 > 按钮。 6. 单击下一步继续查看向导，直到到达带有完成按钮的对话框。单击完成。 7. 出现导入数据对话框时，单击确定以接受默认设置。完成查询后，数据将显示在 Excel 中。 |
1 | 1 |
可视化数据
因为数据已导入
Excel，你将使用
Power View 以可视方式导出数据。
- 在
Excel 工作表中，选择插入选项卡，然后选择
Power View 以打开新的
Power View 报表。
- 在
Power View ****字段区域，选择国家
/地区和
extremetemp 旁的复选框。清除所有其他复选框。
- 在字段
框，单击
extremetemp 旁的向下箭头，然后选择计数（非空白）。
- 从设计选项卡中，选择地图以在世界地图上按国家
/地区显示温度数据。
地图上显示的每个圆圈表示记录温度高于或低于目标温度
5 度之上的次数。圆圈越大，在此位置记录的极端温度的实例越多。
为了进一步筛选数据，以便你查看记录温度高于或低于目标温度的地点，请执行以下步骤。
- 在
Power View ****字段区域，清除
extremetemp 复选框，然后选择
temprange 复选框。选择
temprange 旁的向下箭头，然后选择作为大小添加。
- 将
temprange 从
Power View ****字段区域拖到筛选器框，然后选择热或冷的复选框，查看高于或低于目标温度的地点。
虽然通过可视化地图上的温度数据，你可以更容易查看哪个地点在维持目标温度方面出现问题，但你无法由此深入了解根本原因。请执行以下步骤，使用生成信息确定
HVAC 是否是问题的源头。
- 打开新的
Excel 工作表，依次选择数据选项卡、来自其他源、来自
Microsoft Query。按照先前相同步骤导入数据，但此次选择
hvac_building 表，且仅选择
hvacproduct 和
extremetemp 列。
- 导入数据后，选择插入选项卡，然后选择
Power View。
- 在
Power View ****字段的
字段
章节中，单击
extremetemp 字段旁的向下箭头，然后选择计数（非空白）。
- 从设计选项卡中，选择柱形图，然后选择堆积柱形图。
- 显示图后，选择左上角**按
hvacproduct 排序**旁的向下箭头，然后选择
extremetemp ****的计数以按
extremetemp 字段对列进行排序。
从此图中，你可以看到
FN39TG 的极端温度实例高于其他单位。
摘要
在本教程中，你已成功执行用于分析生成 HVAC 传感器数据的 HDInsight 配置单元作业。要了解更多信息，请访问我们的其余示例作业。如果你具有本教程或其他示例方面的反馈，请使用上面的帮助 + 反馈链接。
使用以下链接继续了解如何将配置单元和 Excel 与 HDInsight 一同使用。
- 将配置单元和 HDInsight 中 Hadoop 一同使用
- 使用 HDInsight 中 Hadoop 分析 Twitter 数据
- 使用 Microsoft 配置单元 ODBC 驱动程序将 Excel 连接到 Hadoop
- 使用 Power Query 将 Excel 连接到 Hadoop
