使用AWS和Power BI分析美国航班
分析美国航班用AWS和Power BI
使用ETL和BI可以获得哪些见解？
目录
∘ 引言 ∘ 问题陈述 ∘ 数据 ∘ AWS架构 ∘ 使用AWS S3进行数据存储 ∘ 设计模式 ∘ 使用AWS Glue进行ETL ∘ 使用AWS Redshift进行数据仓库 ∘ 使用AWS Redshift提取见解 ∘ 使用Power BI可视化数据 ∘ 未来步骤 ∘ 结论 ∘ 参考
引言
空中旅行已成为我们生活的重要组成部分。对于企业来说，它是进行网络和商业活动的一种手段，对于家庭来说，它是拜访亲人或旅行的方式。
尽管对空中旅行产生了影响，但航空业面临着不断变化的困境。它受到经济低迷和繁荣、气候变化、Covid-19大流行以及对可再生能源的更多依赖的推动等外部因素的影响。
为了对这些变化及其对空中旅行的影响有所了解，跟踪这些航班的时间变化是值得的。这样的努力需要一个强大的数据仓库、数据分析和数据可视化策略。
问题陈述
本项目有两个主要目标。首先，利用亚马逊网络服务（AWS）提供的资源，构建一个数据管道，方便存储、转换和分析美国航班数据。
其次，使用Power BI构建一个可有效展示数据主要发现的可视化工具。
数据
本项目使用的数据集来自美国交通统计局。它主要报告了从2003年到2023年的机场和航空公司的总航班数、延误和取消情况。
以下是数据集的预览：
乍一看，原始数据集存在一些问题。
首先，airport_name字段中的信息包含多个部分。它不仅呈现了机场的名称，还包括城市和州的信息。为了方便访问这些信息，该字段需要拆分为3个单独的字段。
其次，当前数据采用的是扁平模型（即一个表）。然而，这不是最佳设置，因为数据包含多个具有关系的实体。
在进行分析或可视化之前，必须解决这些问题。
AWS架构
让我们讨论构建数据管道所需的AWS架构。
所需的资源最好用以下图示说明：
该云解决方案使用Amazon S3存储原始数据和转换后的数据，使用AWS Glue创建ETL作业来实现数据转换，并使用AWS Redshift创建云数据仓库，以便用户可以使用SQL从数据中提取见解。
最后，使用Power BI将数据的关键指标以仪表盘的形式展示。
使用AWS S3进行数据存储
该项目使用了两个S3存储桶：flights-data-raw和flights-data-processed。
桶flights-data-raw包含原始数据集。
桶flights-data-processed将包含经过转换后的数据（目前为空）。
设计模式
接下来，确定适合这些数据的合适模式非常重要。原始数据存储在一个扁平文件中，其中包含一个表：
不幸的是，该模式只有一个表，其中包含多个实体，如日期、机场和承运人。为了优化数据库以更快地检索数据，可以将这个扁平模式转换为使用维度建模的星型模式：
在这个新模式中，flights表充当事实表，而date、carrier和airport表充当维度表。
使用AWS Glue进行ETL
使用AWS Glue创建的ETL作业可以将原始数据转换为事实和维度表，并将它们加载到flights-data-processed桶中。
ETL作业使用一个导入的Python脚本来执行维度建模。
该脚本使用Python SDK的boto3来提取flights-data-raw桶中的原始数据集，创建星型模式中的4个表，并将它们以csv文件的形式加载到flights-data-processed桶中。
例如，以下代码片段用于创建carrier表。
可以在GitHub存储库中访问用于创建4个表的整个脚本。
ETL作业运行没有任何问题：
数据集已转换为一个事实表和3个维度表，以csv文件的形式存储在flights-data-processed桶中。
使用AWS Redshift进行数据仓库
使用AWS Glue，最初以扁平模型表示的数据现在可以在数据仓库中以更适合的星型模式表示。
这个数据的云数据仓库将使用AWS Redshift Serverless创建。这需要创建一个名为flights-namespace的命名空间，以及一个名为dev的数据库。此外，它还需要一个名为flights-workgroup的工作组，该工作组将用于编写SQL查询。
注意：已配置工作组以允许VPC外的设备访问数据库。这在使用Power BI创建可视化时非常有用。
现在，我们可以在Redshift中打开查询编辑器，并在dev数据库中开始创建事实表和维度表。
首先，需要使用以下命令在数据仓库中创建模式中的4个表：
这四个表现在在数据仓库中，但因为数据仍位于flights-data-processed存储桶中，所以它们都是空的。
可以使用COPY命令将数据复制到这个数据仓库中。
例如，可以使用以下命令语法将flights.csv中的数据复制到flights表中：
注意：将
iam_role变量赋值为创建工作组时选择的IAM角色。
通过对flights-data-processed存储桶中的每个CSV文件执行COPY命令，这4个表应该填充了所需的数据。
以下是机场表的预览：
使用AWS Redshift提取洞察力
现在，所有表都加载了数据，我们可以使用SQL查询进行分析了！
由于数据之前已经转换为具有维度建模的星型模式，因此可以高效地检索数据，并且运行时间很短，因此这个设置非常适用于特定的分析。
以下是一些可以通过SQL查询回答的问题的例子。
- 2022年哪些机场的航班数量最多？
2. 从2019年开始，哪种类型的延误对总延误的贡献最大？
3. 在约翰·肯尼迪机场，每年的延误百分变化是多少？
使用Power BI可视化数据
当前的云数据仓库使用户能够在很短的时间和成本内回答关键问题。
然而，我们可以通过创建一个可供最终用户使用的可视化来进一步推进这一点，以回答类似的问题。
实现这一目标的一种方法是使用Power BI创建一个仪表板，这是一款非常受欢迎的商业智能工具。
虽然通过可视化可以挖掘出许多指标，但仪表板将重点关注以下内容的检查：
- 航班数量、延误和取消的汇总
- 随时间变化的航班数量、延误和取消的追踪
- 识别最常用的机场和航空公司
- 各种延误类型的详细情况
此外，仪表板还将包括允许用户定位特定时间和位置的过滤器。
所有这些功能可以组合在以下仪表板中：
使用这样的工具，即使没有数据访问权限或不了解SQL，用户也可以以很少的努力回答关键问题。
此类问题包括：
- 哪个航空公司在JFK机场的航班最多？
2. 从2019年到2022年，加利福尼亚取消了多少航班？
3. 美国航空公司的哪种延误类型对总延误贡献最大？
未来步骤
当前在AWS和Power BI中的设置促进了快速和廉价的数据分析和可视化。然而，值得考虑将来数据的新应用。
- 整合新数据源
如果要包含新的数据源，模式将需要相应地进行修改。此外，还需要创建额外的ETL作业，将这些数据源的数据无缝地集成到现有的数据仓库中。
2. 进行时间序列分析
BTS提供的数据是一个时间序列。因此，考虑使用时间序列分析和构建预测模型来预测未来航空旅行需求是有价值的。
结论
拥有大量记录的数据集（如BTS提供的数据集）可能难以管理。然而，借助AWS提供的资源，可以构建一个能够以成本效益的方式处理数据并将其结构化的数据管道，使用户能够提取见解。
此外，像创建的Power BI仪表板这样的可视化方法是一种有效的方法，可以为受众提供一些指标的背景，并讲述一个有影响力的故事。
有关在AWS Glue中构建ETL作业所使用的代码或用于创建表并进行分析的SQL查询，请访问GitHub存储库：
GitHub – anair123/Tracking-U.S.-Flights-With-AWS-and-Power-BI
通过在GitHub上创建一个帐户，为anair123/Tracking-U.S.-Flights-With-AWS-and-Power-BI的开发做出贡献。
github.com
感谢阅读！
参考资料
- Airline On-Time Statistics and Delay Causes . BTS. (n.d.). https://www.transtats.bts.gov/ot_delay/OT_DelayCause1.asp?20=E
