使用 SQL 简化 ETL 管道：数据处理的三个技巧

た入场券 2024-02-05 13:41 35阅读 0赞

最近，有人问我如何简化我们的 ETL 管道，以便客户可以在构建复杂的 ML 模型之前快速可视化数据处理步骤。您可以立即将以下三个技巧应用到工作流程中，以使数据更加透明。

使用 SQL 查询生成输入表，无需修改数据库。  
利用SQL函数实现简单的计算步骤。  
设置 ETL 计算步骤的变量。  
好处是显而易见的：

简化工作流程：无需在 SQL、Python 或 JavaScript 之间传输数据以进行数据质量检查。  
缩短周期时间：使用管道中基于 SQL 的数据监控仪表板来识别潜在的数据问题。  
简化管道维护：将用户数据（CSV 或 Excel）无缝集成到您的工作流程中，从而节省时间和成本。  
技巧 1：生成输入表  
事实证明，该方法在各种用户场景中非常有用，包括：

测试查询语法而不从实际数据库检索数据：您可以验证和微调查询，而不会影响实时数据库。  
将 CSV 或 Excel 中的客户数据表与数据库中的表相结合：通过合并来自不同来源的数据，您可以执行全面的分析并获得见解。  
为后续查询建立输入参数表：创建专门的表来存储输入参数，方便后续查询的执行。

-- 1. create meta data table
    WITH devmap AS (
      SELECT *
      FROM (VALUES
    ('John', 'New York', '10001'),
    ('Emma', 'California', '90001'),
    ('Michael', 'Texas', '75001'),
    ('Sophia', 'Florida', '32003'),
    ('James', 'Illinois', '60601'),
    ('Olivia', 'Ohio', '44101'),
    ('William', 'Georgia', '30301'),
    ('Ava', 'Washington', '98101')
    ) AS t(first_name, state, zip_code)
    )
    SELECT first_name, state, zip_code
    FROM devmap;

**提示**：您可以要求chatGPT将csv表重新格式化为查询表，或者生成模拟表来测试您的查询。

技巧 2：在 SQL 中实施 ETL 步骤  
尽管 SQL 主要不是为复杂的科学计算而设计的，但它仍然可以有效地用于许多 ETL（提取、转换、加载）任务。使用 SQL 查询实现 ETL 步骤有几个优点。

为了证明这些想法，我们使用牛顿万有引力定律：两个物体之间的引力定义为

F = (G * m1 * m2) / r²

式中：F为物体间的引力；G是万有引力常数（大约6.67430e-11 N(m/kg)²）；m1和m2是两个物体的质量；r是两个物体中心之间的距离。  
假设输入数据表如下

SELECT m1, m2, distance FROM  objects

执行重力计算并使用 CTE（通用表表达式）包装结果。使用 CTE 包装计算的原因是它允许您将所有计算步骤封装在单个查询中，称为“gravity\_calculation”查询。这种方法使您能够轻松选择并挑选所需的最终列以呈现给最终用户。

WITH gravity_calculation AS (
      SELECT
        m1,
        m2,
        distance,
        (6.67430e-11 * m1 * m2) / POWER(distance, 2) AS gravity
      FROM
        objects
    )
    SELECT
      m1,
      m2,
      distance,
      gravity
    FROM
      gravity_calculation;

技巧 3：使用子查询参数化变量  
为了提高调试或实验过程中的清晰度，为计算步骤设置变量会很有帮助。例如，您可以将 CTE 内的引力常数定义为“g\_coeff”。这种方法允许您根据需要管理更长的变量列表。

WITH vars AS (
      6.67430e-11 AS g_coeff 
    ),

这些变量在后续的主函数中用作子查询

((SELECT g_coeff as from vars) * m1 * m2) / POWER(distance, 2) AS gravity

将所有内容放在一起时，计算步骤可以总结为以下查询步骤。

- set up variable Gravitational coefficient
    WITH vars AS (
      6.67430e-11 AS g_coeff 
    ),
    -- pull m1, m2,. distance from tables
    gravity_calculation AS (
      SELECT
        m1,
        m2,
        distance,
        POWER(distance, 2), dist_square
        ((SELECT g_coeff as from vars) * m1 * m2) / POWER(distance, 2) AS gravity
    -- pull m1, m2,. distance from tables
    SELECT
      m1,
      m2,
      distance,
      dist_square
      gravity
    FROM
      gravity_calculation;