使用SQL和机器学习进行大规模自动化数据质量测试

数据管道可能因一百万种不同的原因而中断，但是我们如何确保实时识别和处理这种“数据停机时间”呢？有时，只需要一些SQL，Jupyter Notebook和一些机器学习即可。

随着公司依赖越来越多的数据来为日益复杂的管道提供动力，这些数据必须可靠，准确和可信赖。当数据中断时（无论是由于架构更改，空值，重复还是其他原因），我们都需要知道并且要快速。如果我们不小心，过时的表或错误的度量如果不加以检查，可能会迅速影响下游的仪表板和使用者。

我们使用数据停机时间来指代数据丢失，错误或其他不准确的时间段。如果您是数据专业人员，则可能会问以下问题：

• 数据是最新的吗？
• 数据是否完整？
• 字段是否在预期范围内？
• 空率是高于还是低于应有的值？
• 模式已更改吗？

要回答这些问题，我们可以从软件工程师的剧本中摘录一个页面：[数据可观察性]。数据工程师将数据可观察性定义为组织回答这些问题并评估其数据生态系统的健康状况的能力。反映数据健康状况的关键变量，数据可观察性的五个支柱是：

• 新鲜度：我的数据是最新的吗？是否有时间间隔未更新我的数据？
• 分布：我的数据在现场级别上有多健康？我的数据在预期范围内吗？
• 数量：我的数据摄入量是否达到预期的阈值？
• 模式：数据管理系统的正式结构是否已更改？
• 沿袭：如果我的某些数据出现故障，那么上游和下游会受到什么影响？我的数据源如何相互依赖？

在本系列文章中，我们将拉开帷幕，并研究代码中的数据可观察性。

在我们的数据观测的实践系列的最后一篇文章，我们会退一步，想想是什么让一个很好的数据质量监控一般。也许您已经阅读了第I部分和第II部分，然后对自己进行了思考，“这些都是有趣的练习，但是我们如何才能真正在实际的生产环境**中大规模应用这些概念呢？” 使用来自机器学习领域的概念，我们可以回答这个问题。

机器学习对于大规模的数据可观察性至关重要。配备了机器学习功能的检测器可以更灵活地应用于大量表，而无需随着数据仓库的增长而进行人工检查和制定规则。此外，机器学习检测器可以实时学习和适应数据，并捕获人眼无法看到的复杂的季节性模式。

让我们深入研究-不需要任何事先的机器学习经验。

一、我们的数据环境

欢迎您使用Jupyter Notebook和SQL自己尝试这些练习。

您可能从第I部分和第II部分中还记得，我们正在处理有关宜居系外行星的模拟天文数据。不幸的是，这些数据没有任何真实的东西-它是出于教学目的而制造的-但如果您愿意，您可以假装它是直接从Perseverance流式传输的。:)

我们使用Python生成了数据集，对数据进行了建模，并对在生产环境中遇到的实际事件进行了异常处理。

在本练习中，我们使用SQLite 3.32.3，它应该以最小的设置从命令提示符或SQL文件访问数据库。这些概念实际上可以扩展到任何查询语言，并且这些实现可以以最小的更改扩展到MySQL，Snowflake和其他数据库环境。

在本文中，我们将把注意力集中在EXOPLANETS表格上：

$ sqlite3 EXOPLANETS.db
sqlite> PRAGMA TABLE_INFO(EXOPLANETS);
0 | _id            | TEXT | 0 | | 0
1 | distance       | REAL | 0 | | 0
2 | g              | REAL | 0 | | 0
3 | orbital_period | REAL | 0 | | 0
4 | avg_temp       | REAL | 0 | | 0
5 | date_added     | TEXT | 0 | | 0

请注意，EXOPLANETS配置为手动跟踪重要的元数据片段-date_added列-该列记录了我们的系统发现行星的日期并将其自动添加到我们的数据库中。在第一部分中，我们使用了一个简单的SQL查询来可视化每天添加的新条目数：

SELECT
  DATE_ADDED,
  COUNT(*) AS ROWS_ADDED
FROM
  EXOPLANETS
GROUP BY
  DATE_ADDED;

该查询产生的数据如下所示：

date_added  ROWS_ADDED
2020-01-01  84
2020-01-02  92
2020-01-03  101
2020-01-04  102
2020-01-05  100
...  ...
2020-07-14  104
2020-07-15  110
2020-07-16  103
2020-07-17  89
2020-07-18  104

换句话说，该EXOPLANETS表通常每天更新约100个条目，但在某些天没有输入数据时会“脱机”。我们引入了一个指标DAYS_SINCE_LAST_UPDATE来跟踪表格的这一方面：

WITH UPDATES AS(
  SELECT
    DATE_ADDED,
    COUNT(*) AS ROWS_ADDED
  FROM
    EXOPLANETS
  GROUP BY
    DATE_ADDED
)

SELECT
  DATE_ADDED,
  JULIANDAY(DATE_ADDED) - JULIANDAY(LAG(DATE_ADDED) OVER(
    ORDER BY DATE_ADDED
  )) AS DAYS_SINCE_LAST_UPDATE
FROM
  UPDATES;

结果看起来像这样：

DATE_ADDED  DAYS_SINCE_LAST_UPDATE
2020–01–01  
2020–01–02  1
2020–01–03  1
2020–01–04  1
2020–01–05  1
...  ...
2020–07–14  1
2020–07–15  1
2020–07–16  1
2020–07–17  1
2020–07–18  1

稍作修改，我们就在查询中引入了阈值参数，以创建新鲜度检测器。我们的检测器将返回其中最新数据EXOPLANETS早于1天的所有日期。

WITH UPDATES AS(
  SELECT
    DATE_ADDED,
    COUNT(*) AS ROWS_ADDED
  FROM
    EXOPLANETS
  GROUP BY
    DATE_ADDED
),

NUM_DAYS_UPDATES AS (
  SELECT
    DATE_ADDED,
    JULIANDAY(DATE_ADDED) - JULIANDAY(LAG(DATE_ADDED)
      OVER(
        ORDER BY DATE_ADDED
      )
    ) AS DAYS_SINCE_LAST_UPDATE
  FROM
    UPDATES
)

SELECT
  *
FROM
  NUM_DAYS_UPDATES
WHERE
  DAYS_SINCE_LAST_UPDATE > 1;

DATE_ADDED  DAYS_SINCE_LAST_UPDATE
2020–02–08  8
2020–03–30  4
2020–05–14  8
2020–06–07  3
2020–06–17  5
2020–06–30  3

此图中的峰值表示EXOPLANETS表正在处理旧数据或“陈旧”数据的实例。在某些情况下，此类中断可能是标准的操作程序-也许我们的望远镜需要维护，因此整个周末都没有记录任何数据。但是，在其他情况下，中断可能代表了数据收集或转换的真正问题-也许我们将日期更改为ISO格式，并且传统上推送新数据的工作现在失败了。我们可能会发现，较长时间的中断会变得更糟，但是除此之外，我们如何保证仅在数据中检测到真正的问题？

简短的答案：您不能。建立完美的预测变量是不可能的（无论如何对于任何有趣的预测问题）。但是，我们可以使用机器学习中的一些概念来以更结构化的方式描述问题，从而大规模地提供数据可观察性和信任度。

二、通过机器学习改善警报

误报和误报

每当我们发出有关数据管道损坏的警报时，我们都必须质疑警报是否准确。警报是否指示出真正的问题？我们可能会担心以下两种情况：

1. 已发出警报，但没有真正的问题。我们浪费了用户响应警报的时间。
2. 存在真正的问题，但未发出警报。我们让一个真正的问题未被发现。

这两种情况被描述为误报（预测为异常，实际上是正常的）和误报（预测为OK，实际上是异常的），我们希望避免它们。发出误报就像是在哭泣的狼一样-我们敲响了警钟，但一切都还好。同样，发布虚假否定消息就像睡在警卫队上一样-出了点问题，但是我们什么也没做。

我们的目标是尽可能避免这些情况，并专注于最大化真实肯定（预测异常，实际上是一个问题）和真实否定（预测正常，实际上正常）。

精度和召回率

因此，我们希望有一个好的检测方案来最小化误报和误报率。在机器学习实践中，考虑相关但更富洞察力的术语，精度和召回率更为常见：

常，精确度告诉我们发出警报时多久才对。具有高精度输出的模型具有令人信服的警报，因为它们的高精度保证了它们很少哭泣。

回顾一下，通常可以告诉我们实际上要提醒多少个问题。具有较高召回率的模型是可靠的，因为其较高的召回率保证了他们很少在工作中睡觉。

扩展我们的隐喻，精确度很高的模型就是很少哭泣的模型-当它发出警报时，您最好相信它。同样，具有良好召回作用的模型就像是好警犬-您可以放心，该模型将解决所有真正的问题。

平衡精度和召回率

当然，问题在于您无法同时拥有两全其美的优势。请注意，这两者之间存在明显的权衡。我们如何获得完美的精度？很简单：警惕什么-睡觉值班所有的时间-迫使我们有0％的误报率。问题？召回将是可怕的，因为我们的假阴性率将是巨大的。

同样，我们如何获得完美的召回率？也很简单：对一切都保持警惕-抓住一切机会哭泣的狼-强制将假阴性率设为0％。正如预期的那样，我们的误报率会受到影响，从而影响精度。

解决方案：单一目标

我们的数据世界是由可量化的目标运行的，在大多数情况下，我们希望单个目标进行优化，而不是两个。我们可以将精度和召回率结合到一个称为*F* -score的度量中：

F_beta之所以称为加权*F*分数，是因为不同的beta称量精度值和计算中的召回率不同。总的来说，一个F_beta分数说：“我认为召回beta与精确同等重要。”

当为时beta = 1，方程式的值均相等。设置beta > 1和回忆对于获得更高的分数将更为重要。换句话说，beta > 1“与偶尔引起虚假警报相比，我更关心捕获所有异常情况。” 同样，setbeta < 1和precision更为重要。beta < 1说：“我更关心警报的真实性，而不是抓住每一个实际的问题。”

三、检测新鲜事件

有了新词汇，让我们回到检测EXOPLANETS表格中新鲜事件的任务。我们使用一种简单的预测算法，因为我们通过设置一个模型参数将查询转换为检测器X。我们的算法说：“任何中断时间超过X数天就是一个异常，我们将为此发出警报。” 即使在这种情况下，精度，召回率和F分数也可以为我们提供帮助！

为了进行展示，我们将新鲜度中断纳入其中，EXOPLANETS并分配了地面真相标签，以编码每次中断是否是真正的事件。没有某种基础的事实就不可能计算模型的准确性，因此思考如何为用例生成这些信息总是有帮助的。回想一下，表中总共有6次中断时间超过1天EXOPLANETS：

DATE_ADDED  DAYS_SINCE_LAST_UPDATE
2020–02–08  8
2020–03–30  4
2020–05–14  8
2020–06–07  3
2020–06–17  5
2020–06–30  3

可以任意地说，2020-02-08年和2020-05-14年的事件是真实的。每个时间为8天，因此它们会出现问题是有道理的。另一方面，假设2020–03–30和2020–06–07的中断不是实际事件。这些中断分别为4天和3天，因此这并不奇怪。最后，让我们在2020年6月17日和2020年6月30日，在中断5天和3天分别，也成为真正的事件。

INCIDENT  NOT INCIDENT
2020-02-08 (8 days)  2020-03-30 (4 days)
2020-05-14 (8 days)  2020-06-07 (3 days)
2020-06-17 (5 days)  
2020-06-30 (3 days)

以这种方式选择了我们的基本事实后，我们看到更长的中断时间更有可能是实际问题，但是并不能保证。这种弱关联将使好的模型有效但不完善，就像在更复杂的实际用例中一样。

现在，假设我们将阈值设置为3天，换句话说，“每次中断时间超过3天就是一个异常。” 这意味着我们可以正确检测到2020-02-08、2020-05-14和2020-06-17的异常，因此我们有3个真实的正值。但是，很不幸，我们发现2020-03-30不是一个事件，因此我们有1个误报。3 true positives / (3 true positives + 1 false positive)表示我们的精度是0.75。此外，我们未能将2020–06–30检测为事件，这意味着我们有1个假阴性。3 true positives / (3 true positives + 1 false negative)表示我们的召回率也为0.75。F1分数，由公式给出

表示我们的F1分数也为0.75。不错！

现在，假设我们将阈值设置为更高，为5天。现在，我们仅检测到最长的停机时间2020-02-08和2020-05-14。这些结果都是真实事件，因此我们没有误报，这意味着我们的精确度是1 –完美！但是请注意，我们无法检测到其他真正的异常，即2020-06-17和2020-06-30，这意味着我们有2个假阴性。2 true positives / (2 true positives + 2 false negatives)表示我们的召回率为0.5，比以前更差。我们的召回遭受了损失，这是有道理的，因为我们选择了门槛较高的保守分类器。我们的F1得分可以再次使用上述公式计算，结果为0.667。

如果我们根据设置的阈值绘制精度，召回率和F1，我们会看到一些重要的模式。首先，具有低阈值的主动检测器具有最佳召回率，因为它们可以更快地发出警报，从而发现更多真正的问题。另一方面，更多的无源探测器具有更好的精度，因为它们仅警告最可能是真实的最严重异常。F1分数在这两个极端之间的某个位置达到峰值-在这种情况下，阈值为4天。找到最佳位置是关键！

最后，让我们看一下最后的比较。请注意，我们只查看了F1分数，该分数权衡了精度和召回率。当我们查看其他值时会发生什么beta？

回想一下，将军F_beta说：“召回时间和精确度一样重要。” 因此，我们应该期望优先考虑召回时F2高于F1-这正是我们在小于4的阈值下看到的结果。同时，对于较大的阈值，F0.5分数更高，这表明保守分类器有更多的余地精度更高。

四、借助机器学习实现大规模的数据可观测性

我们通过机器学习概念进行了快速浏览。现在，这些概念如何帮助我们将检测器应用于生产环境？关键在于了解对于任何异常检测问题都没有完美的分类器。有总是假阳性和假阴性，或同样的精度和召回之间的权衡。您必须问自己：“我如何权衡这两者之间的权衡？是什么决定了我的模型参数的“最佳位置”？” 选择F_beta要优化的分数将隐式地决定如何权衡这些事件，从而决定分类问题中最重要的因素。

另外，请记住，没有某种可与模型预测相比较的基础事实，对模型准确性的任何讨论都是不完整的。在知道自己有一个良好的分类之前，您需要先知道它是什么。

希望您没有数据停机！

文丨Soundhearer

图丨来源于网络