Python和SQL中的ETL

Python 已成为当今最受欢迎的编程语言之一。其简单的语法以及大量的通用库使其功能极为多样——尤其受到数据工程师的青睐，用于处理数据作业。事实上，许多专业人士都选择使用 Python 来满足他们大部分的数据管理需求。

在这一领域，Python 能力的最佳实例之一是其在 ETL 过程中的应用。ETL，即提取（Extract）、转换（Transform）和加载（Load），是一个将原始、无组织且通常繁琐的数据转化为干净、结构化且易于处理的状态的过程。此过程对于确保数据不仅易于获取，而且对于后续评估或决策制定有用和有价值至关重要。

什么是 ETL？

数据管道就像是您数据的旅程。它描述了数据从起点到终点的传输过程。一种重要的数据管道类型是 ETL，即提取（Extract）、转换（Transform）和加载（Load）。让我们来分解 ETL 的每个部分：

• 提取（Extract）： 这是过程的起点。我们从各种来源检索信息，包括 Excel 电子表格、API、数据库和云服务。这全都是在进入下一阶段之前获取必要的事实。Python、AWS Glue、Azure Data Factory 和 Talend 都是完成这项工作的常用工具。
• 转换（Transform）： 获取数据后，我们必须对其进行清理。这包括修正错误、删除重复项以及确保内容格式正确。目的是改进信息，使其准备好被使用。Python、Spark 和 Azure Data Factory 是此时常用的工具。
• 加载（Load）： 最后，将修改后的数据加载到其最终目的地，这可能是一个数据仓库、数据湖或数据映射。此阶段确保数据存储在一个能保证其可以被正确检索和使用的位置。Python、Spark 和 Azure Data Factory 都在这种情况下被使用。

总而言之，信息管道管理着数据从开始到结束的流动，确保数据被适当地提取、转换并存入其最终位置。

ETL 过程概述

1. 管道架构和工具： 在设置您的数据管道时，您需要仔细选择正确的工具和基础设施。这需要权衡几种管道开发和管理的替代方案，如 ETL 平台、数据编排工具和存储解决方案。请考虑相关成本，如许可费、云存储价格以及任何潜在的维护费用。一个精心规划的结构不仅能保证数据流畅，还有助于正确管理开支。
2. 数据源、转换和目标： 要创建高效的 ETL 操作，首先要识别您所有的数据提供者。这些可以是数据库、文件系统或 API。每个信息源都有其独特的特性和需求。接下来，确定数据必须经历哪些转换，这可能包括清理（移除错误或不一致）、丰富（添加相关信息）或分组。最后，确定修改后的数据将被存储或使用的位置，例如在数据仓库、监控工具或分析平台中。
3. 数据拉取频率： 确定您需要多久从源头提取一次数据。您可以采用增量拉取，即只获取新的或修改过的数据，这样更高效并能减少计算机系统的负担。对于初次安装或发生重大变化时，可能需要全量加载，即一次性获取所有信息。决策取决于您的具体需求，例如数据量、对最新信息的需求以及系统效率因素。
4. 数据量和数据速率： 评估您信息的数量和流动性对于创建成功的 ETL 过程至关重要。数据量是您处理的数据量，而数据速率是新数据生成的速度。高数据量和快速率需要强大的处理能力来高效地处理数据涌入。了解这些因素有助于确定适当的数据拉取频率，以及 ETL 工具所需的容量和速度。
5. 全量加载的考量： 全量数据加载有几个好处。它们通过确保您使用完整的数据集来简化数据集成过程，这对于初始数据设置或重大更新尤其有用。全量加载还有助于数据清理和处理模式变更，因为您可以一次性解决整个数据集的问题。然而，这样做可能会消耗大量资源和时间，因此根据您的具体需求评估这些变量至关重要。
6. 可扩展性和性能： 随着您拥有的信息不断增长并以更快的速度生成，您的 ETL 过程应该准备好随之扩展。为了保持效率和有效性，请预见对更高性能和可扩展性的需求。这意味着构建您的 ETL 系统以适应更大量的数据、更快的处理时间以及更多变量，而不会影响性能。定期监控和调整您的系统，以确保其满足日益增长的需求并保持正常运行。

使用 Pandas (Python) 和 SQL 探索您的数据

1. 创建一个数据框

添加数据

df.info() 函数为您提供数据框内容和结构的完整视图。它显示缺失值的数量、非空记录、每列的数据类型、存储使用情况等信息。执行此函数可为您提供数据框的完整画面，让您一眼就能掌握其结构。

代码

输出

 
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 5 entries, 0 to 4
Data columns (total 7 columns):
 #   Column                      Non-Null Count  Dtype 
---  ------                      --------------  ----- 
 0   id                          5 non-null      int64 
 1   product customer purchased  5 non-null      object
 2   Name of customer            5 non-null      object
 3   address of customer         5 non-null      object
 4   email of customer           5 non-null      object
 5   phone number of customer    5 non-null      object
 6   time of purchase            5 non-null      object
dtypes: int64(1), object(6)
memory usage: 408.0+ bytes   

对于重复的信息

• false 意味着： 没有重复（唯一的行）
• true 意味着： 重复

输出

 
0	False
1	False
2	False
3	False
4	False
dtype: bool   

查看重复的行

2. 使用 Python 提取和转换数据

• 从不同来源加载数据
  
  • 加载 csv 文件
    sample_csv = pd.read_csv("sampleData_csv.csv", header = None)
  • 加载 Excel 文件
    sample_excel = pd.read_excel("sampleData_excel.xlsx")
• 提取您的数据
  
  • 使用 Pandas 将我们的 H+ Sports 订单数据从 Excel 提取到我们的工作区。将我们的订单数据从 Excel 导入到我们的 Jupyter 笔记本中：orders = pd.read_excel("H+ Sport Orders.xlsx")
• 清理、预处理数据和数据格式化
  转换数据是数据管理中的一个重要过程，确保信息准确、一致并为分析做好准备。这个过程包括几个关键任务：
  
  • 移除重复项： 数据去重涉及识别并消除数据集中的重复条目。这对于维护数据完整性至关重要。当存在重复记录时，它们会扭曲分析结果并导致错误的结论。通过移除这些重复项，我们确保每条信息都是唯一的，并能准确地贡献于整个数据集。
  • 标准化和集成数据： 此步骤涉及将来自各种格式的数据转换为一致的格式。例如，不同的数据集可能使用不同的货币或计量单位。标准化意味着将所有值转换为通用单位或货币。此外，集成来自不同来源的数据需要将这些零散的部分合并成一个统一的格式。这有助于创建一个更易于分析和解释的连贯数据集。
  • 汇总数据： 数据聚合是从不同地方收集和汇总数据以提供对整体性能和趋势的深入理解的过程。这可能包括合并来自几个部门的销售信息或合并由多个研究项目收集的信息。聚合有助于识别在单个数据集中可能不明显的进展和模式。
  • 筛选数据： 数据筛选是从数据集中删除不需要或不相关的数据元素的过程。这种方法有助于专注于最重要的信息，同时消除噪音。我们通过移除与调查无关的材料，使可用的信息更易于管理和相关。
  • 清理数据： 数据清理是发现和解决数据集中缺陷和不一致之处的过程。这可能涉及修复错误、纠正不准确的数字或处理缺失的数据。维护数据准确性对于可靠的决策和评估至关重要。
    数据转换是提取、转换和加载（ETL）过程的关键组成部分。它涉及清理、集成、标准化、验证和增强数据。这些过程之一是确保数据质量优良并适合用于报告和分析的必要条件。没有充分的转换，数据可能不准确或缺乏完整性，从而导致错误的见解和结论。
• 标准化、处理重复项和缺失值
  
  • 从 Excel 获取数据： 如果您处理的是 Excel 数据，Python 的 Pandas 程序包可以帮助您进行转换。ETL（提取、转换、加载）过程包括提取 Excel 文件中的信息，然后执行各种调整或转换。
  • 多个条目可能会很麻烦。 要清理您的数据，您必须首先使用唯一的客户标识符（如电话号码和电子邮件ID）来检测重复项。一旦识别出来，您就可以删除这些重复项，只保留每个唯一条目的第一次出现。这可以确保您的数据保持干净和准确。

• 检查缺失值： 要在数据集中查找缺失或空值，您可以使用 Pandas 的 isnull() 函数。这是一个用于识别数据中空白的便捷工具。

• 确保数据完整性： 维护数据质量涉及消除重复项并确认在任何转换后没有缺失值残留。

挑战：提取和转换

使用“员工数据”Excel 文件，请按照以下步骤操作：

• 命名生成的 DataFrame。
• 移除标记为“Job Rating”、“New Salary”、“Tax Rate”和“2.91%”的列。
• 保存转换后的 DataFrame。

解决方案

3. 将数据加载到目标系统

• 数据仓库和数据湖简介： 在信息处理领域，理解存储系统的不同职责至关重要。这里概述了数据库、数据仓库、数据湖和数据湖仓的概览，以及它们如何相互配合。
  
  • 数据库： （日常组织者）将数据库视为处理常见数据作业的首选工具。它们的功能类似于完美排列的文件柜，以系统的方式存储详细信息，从而可以轻松操作和访问。数据库管理系统（DBMS）管理数据库，确保信息可以快速创建、读取、更新和删除（CRUD 操作）。数据库管理所有类型的数据，无论它们是整齐排列在表中（结构化）还是更松散地管理（半结构化，如 JSON）。它们被设计用于快速事务和日常活动。
  • 数据仓库： （分析 powerhouse）信息仓库用于各种目的。将它们想象成巨大的、高科技的存储库，将从多个来源收集的信息整合到一个组织良好的存储库中。它们旨在处理大量的历史数据，使组织能够创造知识并做出明智的决策。与优先处理即时事务的数据库不同，数据仓库关注的是随时间积累和解释数据。它们可以处理复杂的请求，并用于商业智能（BI）操作，如监控和分析模式。
  • 数据湖： 灵活的蓄水池，数据湖对保存信息采取了更随意的方法。把它们想象成巨大的蓄水池，可以容纳任何格式的未处理信息——结构化的、非正式的或不受控制的。没有必要立即组织或处理这些信息。由于其多功能性，数据湖非常适合希望检查和分析大量不同信息的数据研究人员和工程师。这就像把任何东西都扔进一个大池子里，然后在需要的时候潜入其中去理解它。数据池是处理来自各种输入和设计的各种数据的理想选择，因为它们具有可扩展性和适应性。
  • 数据湖仓： （混合解决方案）数据湖仓是数据存储领域的最新进展。它们汇集了信息湖和数据仓库的最佳特性。可以将数据湖仓视为一种混合框架，它保留了数据湖灵活的原始存储能力，同时结合了数据仓库的复杂查询和统计技术。这种安排使您能够处理和分析大量原始数据，同时执行结构化查询和生成详尽的报告。它提供了多功能性、可扩展性和强大的分析能力，使其成为两种方案中的最佳选择。
• 将数据加载到关系数据库
  
  • 设置数据库： 首先，我们需要使用 ElephantSQL 创建一个 PostgreSQL 数据库，这是一个为我们管理 PostgreSQL 表的云服务。这个关系数据库将作为 H Plus Sports 数据仓库内的主要存储库，存储和管理我们拥有的所有信息。
  • 使用 SQLAlchemy： SQLAlchemy 是一种用于处理基于 SQL 的数据库（如 PostgreSQL）的编程语言。通过将 SQLAlchemy 集成到我们的 Python 环境中，我们能够连接到数据库、执行查询并执行各种数据库操作，而无需编写原始 SQL 代码。
  • 创建 SQL 引擎： SQLAlchemy 的 create_engine 函数用于构建数据库引擎。该引擎是管理我们 Python 代码和 PostgreSQL 数据库之间交互的重要元素。它使我们能够以快速且受控的方式执行 SQL 查询和处理操作（如提交和回滚）。
  • 连接到数据库： 为了连接到我们在 ElephantSQL 上的 PostgreSQL 数据库，需要从 ElephantSQL 面板获取连接详细信息。这些信息包括数据库的 URL、用户名和密码。然后，我们使用这些详细信息在 SQLAlchemy 引擎中设置关联 URL，从而在我们的 Python 脚本和数据库本身之间建立安全可靠的链接。
  • 加载数据： 连接建立后，我们使用 Pandas（Python 中一个强大的数据操作库）将我们的数据加载到数据库中。Pandas 中的 to_sql 方法允许我们将转换后的数据（例如清理后的客户记录）传输到 H Plus Sports 数据仓库中。在此过程中，我们指定重要的参数，如数据库表的名称、连接详细信息、是否将 DataFrame 索引作为一列包含，以及如何处理表已存在的情况（例如，替换、追加等）。
  • 查询数据： 加载数据后，我们需要查询它以进行分析或检索特定信息。在接下来的课程中，我们将利用一个 A5 SQL 实例对我们导入的信息执行 SQL 查询。此过程需要创建并运行 SQL 查询，以调查、评估和从我们关系数据库中包含的数据中获取信息。
    了解并遵守这些程序可确保我们能有效地管理、加载和检查关系数据库中的数据，从而方便进行进一步的研究并获得有价值的知识。
• 使用 SQL 进行数据质量检查和验证： 在处理数据时，质量控制至关重要。这包括确保数据在 ETL（提取、转换、加载）过程中准确、全面且无错误。数据中的任何差异或缺陷都可能导致不准确的结果和不充分的决策。因此，保持高质量的信息对于获得可靠的结果至关重要。为确保您的数据达到标准，应实施几项关键检查：
  
  • 行数和列数： 首先确认数据中的行数和列数与您的预期相符。这里的差异可能表明数据加载或转换存在问题。
  • 空值和空行： 识别任何空值（缺失值）或完全空的行。缺失的数据会扭曲结果并影响分析的准确性。
  • 重复项和默认值： 查找任何可能人为增加数据计数的重复条目。同样，识别任何可能无法反映数据集真实性质的默认值。
  • 参考比较： 将您的数据与已知的参考来源或预定义规则进行比较。这有助于确保您的数据符合预期的值和格式。
• 验证数据完整性： 在实践中，您可以这样检查数据完整性：
  
  • SQL 示例： 要计算表中的行数，您可以使用类似这样的 SQL 查询：

此查询将返回“customers”表中的总行数。

• Python 示例： 在 Python 中，特别是在使用 Pandas 库时，您可以使用以下方法检查 DataFrame 的维度：

shape 属性返回一个元组，表示行数和列数。您可以将其与您的 SQL 查询结果进行比较，以确保一致性和准确性。

• 设置时间戳规则： 实施基于时间戳的规则来监控数据上次更新的时间。这有助于跟踪数据的新鲜度，并确保您的分析基于最新的信息。
• 实施： 首先执行基本检查，例如比较数据仓库和原始 DataFrame 之间的行数。使用 SQL 查询和其他工具来验证数据完整性并解决任何问题。
• 自动化： 接下来，自动化 ETL 过程以简化您的工作流程。自动化的检查和流程将提高效率，减少手动错误，并确保一致的数据质量。

4. 自动化 ETL 作业：使用 Python 调度 ETL 作业

• 使用 SQL 查询您的数据： 将数据导入数据仓库后，查询数据是做出高效业务决策的关键阶段。可以将其视为原始数据和可操作见解之间的桥梁。例如，如果零售运营商正在考虑扩展其店铺网络，搜索数据可能会识别出客户密度最高的区域。这些信息对于选择新零售分支的最佳地点至关重要，确保增长计划既明智又有效。
• 导航 SQL 控制台： 您将需要一个 SQL 控制台，例如 ElephantSQL，来查看和修改手头的信息。此终端允许您运行 SQL（结构化查询语言）请求，这对于从数据表中检索相关详细信息是必不可少的。SQL 仪表板是一个用户友好的界面，允许您根据特定需求运行语句以获取、编辑或分析您的数据。

示例查询

让我们分解一个示例查询，以说明您如何分析客户数据：

这个 SQL 查询做了几件事：

1. 选择： 它从“Customers”表中选择“State”和“City”列。
2. 聚合： 它计算每个州和城市的客户数量。
3. 分组： 它按“State”和“City”组织信息，并返回每对的计数。
4. 排序： 它按客户数量降序对数据进行分类，让您能快速发现客户最多的地方。

这类查询有助于分析客户在许多地点的分布情况。结果可以提供关于您的客户群最集中在哪里的重要见解，这可以帮助您做出关于在哪里设立新地点的决策。

1. 商业决策： 分析查询结果可以显著影响商业决策。了解消费者分散情况的组织可以就资金投入地点做出更明智的决策。例如，如果数据显示特定区域客户密度高，这可能是一个开设新店的绝佳地点。另一方面，客户数量较少的地点可能表明需要采取替代的营销方法，甚至重新考虑增长计划。
2. 呈现发现： 在收集和分析数据之后，下一步是向管理层报告您的发现。一个强有力的演示不仅包括展示数据，还包括以一种能够加强战略建议的方式进行分析。例如，您可以根据最高的客户密度推荐新的店铺位置。使用事实来支持这些建议，确保您的策略植根于现实，因此更有可能被批准。
3. ETL 开发人员的角色： ETL（提取、转换和加载）开发人员在这种基于数据的方法中至关重要。专业人员负责确保来自多个来源的信息被可靠地获取，转换为可用的格式，并输入数据仓库。这项工作对于维护数据的保密性以及确保可供查询的信息准确及时至关重要。ETL 开发人员促进了这一过程，使公司能够成功地利用其数据进行业务决策。
4. 使用 Airflow 调度 ETL 作业
   
   1. 为什么自动化很重要： 自动化 ETL（提取、转换、加载）过程至关重要，因为它能确保任务按时完成，无需持续的人工输入。这对于满足最后期限和保持数据最新至关重要。
   2. ETL 自动化做什么： 通过使用技术来调度和管理任务，ETL 自动化有助于保持数据的准确性和可靠性。您不是手动处理所有事情，而是设置系统根据计划或特定事件自动运行。
   3. 选择正确的工具： 诸如 Apache Airflow 之类的工具通常用于自动化 ETL 操作。这些应用程序包括用于安排和跟踪流程的功能，使其易于按计划进行。
   4. 了解 Apache Airflow： Apache Airflow 是一个可免费获得的用于编排流程的平台。它使用有向无环图（DAGs）来组织作业及其之间的连接，从而简化复杂的操作。
   5. 设置 Airflow： 要让 Airflow 正确运行，请使用 pip 安装它，设置数据库，为用户生成凭据，并启动网站和计时器。
   6. 使用 Airflow UI： 一旦 Airflow 安装完毕，您就可以使用网络浏览器访问该程序的用户界面。这个用户界面允许您查看您的 DAGs，同时跟踪您所有的活动。

实施这些步骤将使您能够有效地组织和优化使用 Apache Airflow 的 ETL 过程。

在 Mac 或 Linux 上

在 Windows 上

结论

总之，无论您是数据工程师还是分析师，掌握 ETL 过程对于任何与数据打交道的人来说都至关重要。本课程教会了我提取、转换和加载数据的核心概念、工具和注意事项。我了解了管道的架构，如何处理不同的数据源，以及如何管理转换和目标。这些见解对于构建高效的 ETL 工作流至关重要。凭借我在 Python 和 SQL 进行 ETL 的技能，我现在准备好应对数据集成挑战，并为我们组织中数据驱动的决策做出有意义的贡献。