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

最近，有人问我如何简化我们的 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;