使用SQL实现数据的可观察性

在本系列文章中, we walk through how you can create your own 数据可观测性 monitors from scratch, 映射到 数据健康的五个关键支柱. Part 1 of this series 是 adapted from Barr Moses 和 瑞安·卡恩斯’ O’Reilly training, 管理 数据停机时间:对数据管道应用可观察性, 该行业第一个关于数据可观察性的课程. 相关的练习是可用的 在这里,本文中显示的经过调整的代码是可用的 在这里.

从空值和重复行, 到建模错误和模式更改, 数据可能因多种原因而中断. 数据测试推荐一个正规滚球网站抵御坏数据的第一道防线吗, 但是,如果数据在其生命周期中破裂,会发生什么呢? 

推荐一个正规滚球网站称这种现象为数据停机, 它指的是数据丢失的时间段, 错误的, 或者不准确. 数据停机时间 提示推荐一个正规滚球网站提出以下问题:

  • 数据是最新的吗?
  • 数据是否完整?
  • 字段是否在预期范围内?
  • null率比应该的值高还是低?
  • 模式改变了吗?

在数据中断时触发警报,防止数据停机, data teams can leverage a tried 和 true tactic from our friends in software engineering: 监控和可观测性

推荐一个正规滚球网站定义 数据可观测性 as an organization’s ability to answer these questions 和 assess the health of their data ecosystem. Reflecting key variables of data health, the five pillars of 数据可观测性 are: 

  • 新鲜:我的数据是最新的? 在我的数据没有被更新的时间间隔?
  • 分布:我的数据在现场层面的健康程度? 我的数据是否在预期范围内?
  • 体积:是我的数据摄入量达到预期阈值?
  • 模式:我的数据管理系统的正式结构发生了变化?
  • 血统:如果我的一些数据坏了,上游和下游受到什么影响? 我的数据源如何相互依赖?

以这种概念性的方式谈论数据的可观察性是一回事, 但彻底的治疗应该会拉开帷幕 在代码的底层,数据的可观察性实际上是什么样子的?

要完全回答这个问题是困难的, 因为细节将取决于个人对数据仓库的选择, 数据湖, BI工具, 首选语言和框架, 等等. Even so, addressing these problems using lightweight tools like SQLite 和 Jupyter could be useful.

在这篇文章中, we walk through an example data ecosystem to create our own data quality monitors in SQL 和 explore what 数据可观测性 looks like in practice.

让推荐一个正规滚球网站来看看.

实践中的数据可观察性

本教程基于 Ex埃尔吉希县1 奥莱利的课程 管理 数据停机时间. You’re welcome to try out these exercises on your own using a Jupyter Notebook 和 SQL. 推荐一个正规滚球网站将详细介绍,包括练习 2, 34,在以后的文章中.

推荐一个正规滚球网站的样本数据生态系统使用 模拟天文数据 关于宜居行星. 为了练习的目的, 我用Python生成数据集, modeling anomalies off of real incidents I’ve come across in production environments. 此数据集完全免费使用,并且 跑龙套的文件夹 in 存储库 contains the code that generated the data, if you’re interested.

我使用的是SQLite 3.32.3, which should make the database accessible from either the comm和 prompt or SQL files with minimal setup. 这些概念可以扩展到任何查询语言 这些实现 can be extended to MySQL, 雪花, other database environments with minimal changes.

输入的数据库条目 系外行星 包含如下信息:

0._id:表示该行星对应的UUID.
1. 距离:距离地球的距离,以光年为单位.
2. g:地表重力的倍数 g,重力常数.
3. orbital_period:以天为单位的单轨道周期的长度.
4. avg_temp:平均表面温度,单位是开尔文.
5. date_added: The date our system discovered the planet 和 added it automatically to our databases.

请注意一个或多个 距离, g, orbital_period, avg_temp 可能是 因为丢失或错误的数据而导致的.

sqlite> SELECT * FROM 系外行星 LIMIT 5;

请注意,这个练习是追溯的——推荐一个正规滚球网站正在查看历史数据. 在生产数据环境中, 数据可观测性 is real time 和 applied at each stage of the data life cycle, thus will involve a slightly different implementation than what is done 在这里.

为了练习的目的, we’ll be building 数据可观测性 algorithms for freshness 和 distribution, 但是在以后的文章中, 推荐一个正规滚球网站将解决剩下的五大支柱——甚至更多.

新鲜

推荐一个正规滚球网站监测数据可观察性的第一个支柱是新鲜度, which can give us a strong indicator of when critical data assets were last updated. 如果一份定时更新的报告突然看起来很陈腐, this type of anomaly should give us a strong indication that something is off.

首先,请注意 DATE_ADDED 列. SQL不存储添加单个记录时的元数据. So, to visualize freshness in this retroactive setting, we need to track that information ourselves.

分组的 DATE_ADDED 专栏可以让推荐一个正规滚球网站深入了解其中的原因 系外行星 每日更新. 例如,推荐一个正规滚球网站可以查询每天新增的id数量:

你可以自己运行这个 美元sqlite3系外行星.db < queries/freshness/rows-added.sql in 存储库. 推荐一个正规滚球网站得到以下数据:

基于推荐一个正规滚球网站的数据集的图形表示,它看起来像 系外行星 每天持续更新约100个新条目, 尽管有很多天没有数据进来的间隙.

Recall that with freshness, we want to ask the question “is my data up to date?”,因此, knowing about those gaps in table updates is essential to underst和ing the reliability of our data.

策划rows_added vs. date_added
新鲜异常!

该查询通过引入一个度量来操作新鲜度 DAYS_SINCE_LAST_UPDATE. (注:由于本教程使用SQLite3, MySQL中计算时间差的SQL语法是不同的, 雪花, 和其他环境).

结果表格上写着“日期” X的最新数据 系外行星 是 Y 天大的.“这是没有明确从 DATE_ADDED 列 in the table — but applying 数据可观测性 gives us the tools to uncover it.

策划days_since_last_update vs. date_added

现在,推荐一个正规滚球网站有了检测新鲜度异常所需的数据. 剩下要做的就是设置一个 阈值 参数 Y - 多少天才算多? 参数将查询转换为检测器, since it decides what counts as anomalous (read: worth alerting) 和 what doesn’t. (关于设置阈值参数的详细信息将在后面的文章中介绍!).

返回给推荐一个正规滚球网站的数据代表了新鲜度事件发生的日期.

2020年5月14日,表中最新数据为8天前的数据! 这样的停机可能表示推荐一个正规滚球网站的数据管道出现了故障, would be good to know about if we’re using this data for anything worthwhile (和 if we’re using this in a production environment, 机会是, 推荐一个正规滚球网站是).

新鲜度检测

特别注意查询的最后一行: DAYS_SINCE_LAST_UPDATE > 1;.

这里,1是a 模型参数 -这个数字没有任何“正确”之处, 尽管改变它会影响推荐一个正规滚球网站认为事件发生的日期. 数字越小,推荐一个正规滚球网站就能发现更多真实的异常现象(高 回忆), but 机会是, several of these “anomalies” will not reflect real outages. 数字越大, the greater the likelihood all anomalies we catch will reflect true anomalies (高 精度),但推荐一个正规滚球网站可能会错过一些.

出于本例的目的, we could change 1 to 7 和 thus only catch the two worst outages on 2020–02–08 和 2020–05–14. 这里的任何选择都将反映特定的用例和目标, is an important balance to strike that comes up again 和 again when applying 数据可观测性 at scale to production environments.

下面,推荐一个正规滚球网站使用同样的新鲜度检测器,但是 DAYS_SINCE_LAST_UPDATE > 3; 作为门槛. 其中两个较小的中断现在没有被检测到.

图片的文章
请注意这两个未检测到的中断——它们必须少于3天的间隔.

现在,推荐一个正规滚球网站用同样的新鲜度检测器来观察 DAYS_SINCE_LAST_UPDATE > 7; 现在作为门槛. 除了两次最大的停电,现在所有的停电都没有被发现.

图片的文章

就像行星,最优模型参数位于a “适居带” 或者被认为太低和太高的值之间的“甜蜜点”. 这些数据可观察性概念(以及更多)!)将在后面的文章中讨论.

分布

接下来,推荐一个正规滚球网站想要评估推荐一个正规滚球网站数据的实地水平和分布健康状况. 分布告诉推荐一个正规滚球网站所有数据的期望值, 以及每个值出现的频率. 一个最简单的问题是,“我的数据多久出现一次? ”? 在许多情况下, some level of incomplete data is acceptable — but if a 10% null rate turns into 90%, 推荐一个正规滚球网站会想知道的.

这个查询返回大量数据! 发生了什么?

的一般公式 CAST(SUM(CASE WHEN SOME_METRIC IS 零 THEN 1 ELSE 0 END) AS FLOAT) / COUNT(*),当按 DATE_ADDED 列,告诉推荐一个正规滚球网站速率  值 SOME_METRIC 在每天批量的新数据中 系外行星. 通过查看原始输出很难获得某种感觉, 但视觉效果可以帮助解释这种异常现象:

The visuals make it clear that t在这里 are null rate “spike” events we should be detecting. 让推荐一个正规滚球网站关注最后一个指标, AVG_TEMP就目前而言,. 推荐一个正规滚球网站可以用a来检测零峰值 简单的阈值:

As detection algorithms go, this approach is something of a blunt instrument. Sometimes, patterns in our data will be simple enough for a 阈值 like this to do the trick. 但在其他情况下,数据会很嘈杂或有其他并发症,比如 季节性这就要求推荐一个正规滚球网站改变推荐一个正规滚球网站的方法.

图片的文章

For example, detecting 2020–06–02, 2020–06–03, 2020–06–04 seems redundant. 推荐一个正规滚球网站可以过滤掉发生在其他警报之后的日期:

注意,在这两个查询中,关键参数都是 0.9. We’re effectively saying: “any null rate 高er than 90% is a problem, I need to know about it.”

图片的文章

In this instance, we can (和 should) be a bit more intelligent by applying the concept of 移动平均 使用一个更智能的参数:

图片的文章

一个澄清:注意在第28行,推荐一个正规滚球网站使用数量进行过滤 AVG_TEMP_零_RATE——TWO_WEEK_ROLLING_AVG. 在其他情况下,推荐一个正规滚球网站可能需要 ABS () 这个误差量,但不是这里,原因是  rate “spike” is much more alarming if it represents an increase from the previous average. 在任何时候进行监控可能都不值得 S的频率突然下降,而检测到a  加息是显而易见的.

T在这里 are, of course, increasingly sophisticated metrics for anomaly detection like z得分 和 自回归建模 这超出了推荐一个正规滚球网站的范围. This tutorial just provides the basic scaffolding for field-health monitoring in SQL; I hope it can give you ideas for your own data!

接下来是什么?

This brief tutorial intends to show that “数据可观测性” is not as mystical as the name suggests, 用一种全面的方法来了解你的数据健康状况, you can ensure 高 data trust 和 reliability at every stage of your pipeline.

事实上, the core principles of 数据可观测性 are achievable using plain SQL “detectors,” provided some key information like record timestamps 和 historical table metadata are kept. It’s also worth noting that key ML-powered 参数 tuning is m和atory for end-to-end 数据可观测性 systems that grow with your production environment.

Stay tuned for future articles in this series that focus on monitoring anomalies in distribution 和 schema, 沿袭和元数据在数据可观察性中的作用, how to monitor these pillars together at scale to achieve more reliable data.

在此之前,希望您不会出现数据停机!

对如何在尺度上应用数据可观察性有兴趣? 接触 瑞安 剩下的 蒙特卡罗团队.