SQL實(shí)現(xiàn)數(shù)據(jù)質(zhì)量DQC實(shí)踐

Data Observability in Practice Using SQL

在本系列文章中，我們會(huì)介紹如何從0到1做DQC。

數(shù)據(jù)可能由于很多原因而出現(xiàn)錯(cuò)誤，比如數(shù)據(jù)重復(fù)，schema變動(dòng)等。DQC是我們保證數(shù)據(jù)準(zhǔn)確性的第一道防線。好的DQC應(yīng)當(dāng)在觀察到數(shù)據(jù)出現(xiàn)問(wèn)題時(shí)，能夠自動(dòng)進(jìn)行報(bào)警，進(jìn)而通知相關(guān)人員進(jìn)行修復(fù)。

DQC有五個(gè)方面：

在技術(shù)方面有一句名言，TALK IS CHEAP, SHOW ME CODE。那我們?cè)挷欢嗾f(shuō)，開(kāi)始操作吧。

我們使用了一個(gè)天文數(shù)據(jù)集來(lái)進(jìn)行演示。這個(gè)數(shù)據(jù)集用Python生成，生成的過(guò)程中，模擬了在生產(chǎn)環(huán)境中遇到的各種數(shù)據(jù)問(wèn)題。

數(shù)據(jù)庫(kù)方面我們使用了SQLite 3.32.3。

演示數(shù)據(jù)的Schema如下：

? _id: 每個(gè)星球的uid

? distance: 該星球與地球的距離，單位是光年

? g: 引力常數(shù)。每個(gè)星球的重力都是g的倍數(shù)

? orbital_period：?jiǎn)蝹€(gè)軌道周期的長(zhǎng)度，以天為單位

? avg_temp：表面平均溫度，單位為開(kāi)氏度

? date_added：星球被發(fā)現(xiàn)的日期

為了盡可能真實(shí)地模擬生產(chǎn)環(huán)境可能遇到的各種情況，除了_id，其它的字段都有可能是NULL。

我們先來(lái)簡(jiǎn)單看看數(shù)據(jù):

sqlite> SELECT * FROM EXOPLANETS LIMIT 5;

在這篇文章中，我們會(huì)演示如何觀察數(shù)據(jù)的Freshness和Distribution。在后面的文章中，我們會(huì)考慮演示如何保證剩下的特性。

要做DQC，我們首先需要保證數(shù)據(jù)的新鮮程度。它能告訴我們數(shù)據(jù)上次更新的時(shí)間。假如有一個(gè)小時(shí)報(bào)，它的數(shù)據(jù)看起來(lái)和上個(gè)小時(shí)的一模一樣，那我們就要去排查哪兒發(fā)生了什么問(wèn)題了。

在我們的模擬數(shù)據(jù)中，我們用date_added這一列表明每顆星球的發(fā)現(xiàn)時(shí)間。所以我們能看到每天發(fā)現(xiàn)的星球數(shù)量:

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

結(jié)果如下：

大概每天都會(huì)發(fā)現(xiàn)100個(gè)新的星球。結(jié)果用圖表展示如下圖所示:

那有了上面的結(jié)果，我們?nèi)绾稳ゲ榭磾?shù)據(jù)是否新鮮呢？

我們可以通過(guò)引入一個(gè)新的度量值-DAYS_SINCE_LAST_UPDATE。它表示date_added之間的gap。如果數(shù)據(jù)沒(méi)有問(wèn)題，這個(gè)值應(yīng)該每天都是1。當(dāng)大于1時(shí)，說(shuō)明數(shù)據(jù)間隔超過(guò)了一天。那很明顯數(shù)據(jù)是有問(wèn)題的。

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_UPDATEFROM  UPDATES;

這個(gè)是用的SQLLite的語(yǔ)法，如果使用其它數(shù)據(jù)庫(kù)，那么語(yǔ)法可能有些差異，自己微調(diào)一下即可。

結(jié)果如下：

用柱狀圖表示如下：

我們可以看到，大多數(shù)都是1。但是也有少量凸起。

我們首先找出來(lái)數(shù)據(jù)中差異超過(guò)一天的數(shù)據(jù):

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_UPDATESWHERE  DAYS_SINCE_LAST_UPDATE > 1;

結(jié)果如下:

我們可以看到，在2020-05-14，表中最新的數(shù)據(jù)是8天前的！

那觀察到這個(gè)結(jié)果之后，下一步就是要發(fā)送警報(bào)了。在發(fā)送警報(bào)之前，我們需要確定有多少到底數(shù)據(jù)差異幾天才表明有數(shù)據(jù)有異常。在上面的SQL中， DAYS_SINCE_LAST_UPDATE > 1這里表示我們認(rèn)為只要間隔超過(guò)一天數(shù)據(jù)就有異常。這個(gè)需要根據(jù)自己的場(chǎng)景去進(jìn)行調(diào)整。

接下來(lái)我們需要評(píng)估每個(gè)字段的值的分布情況。這讓我們可以早日發(fā)現(xiàn)數(shù)據(jù)里的異常。比如原來(lái)一列的NULL值比率是10%，突然有一天到達(dá)了90%。那么數(shù)據(jù)肯定是有問(wèn)題的。

SELECT  DATE_ADDED,  CAST(    SUM(      CASE        WHEN DISTANCE IS NULL THEN 1        ELSE 0      END    ) AS FLOAT) / COUNT(*) AS DISTANCE_NULL_RATE,  CAST(    SUM(      CASE        WHEN G IS NULL THEN 1        ELSE 0      END    ) AS FLOAT) / COUNT(*) AS G_NULL_RATE,  CAST(    SUM(      CASE        WHEN ORBITAL_PERIOD IS NULL THEN 1        ELSE 0      END    ) AS FLOAT) / COUNT(*) AS ORBITAL_PERIOD_NULL_RATE,  CAST(    SUM(      CASE        WHEN AVG_TEMP IS NULL THEN 1        ELSE 0      END    ) AS FLOAT) / COUNT(*) AS AVG_TEMP_NULL_RATEFROM  EXOPLANETSGROUP BY  DATE_ADDED;

結(jié)果如下:

通用公式 CAST (SUM (CASE WHEN SOME _ metric IS NULL THEN 1 ELSE 0 END) AS FLOAT)/COUNT (*) ，按 DATE_ADDED 列分組,我們能看到NULL值得分布情況。

我們可以看到，有一些列上的NULL分布有凸起，所以我們也應(yīng)當(dāng)檢測(cè)。我們現(xiàn)在只看AVG_TEMP這一列。

WITH NULL_RATES AS(  SELECT    DATE_ADDED,    CAST(      SUM(        CASE          WHEN AVG_TEMP IS NULL THEN 1          ELSE 0        END      ) AS FLOAT) / COUNT(*) AS AVG_TEMP_NULL_RATE   FROM    EXOPLANETS  GROUP BY    DATE_ADDED)
SELECT  *FROM  NULL_RATESWHERE  AVG_TEMP_NULL_RATE  > 0.9;

我們的檢測(cè)腳本很簡(jiǎn)單，只要NULL值在某一天比率超過(guò)90%，就報(bào)警。

注意，在這兩個(gè)查詢中，閾值都是0.9。我們實(shí)際上是在說(shuō): “任何高于90% 的無(wú)效率都是個(gè)問(wèn)題，我需要了解它?！?/span>

但如果我們應(yīng)用滾動(dòng)平均值的概念來(lái)進(jìn)行閾值設(shè)定，是不是更好一些呢？

WITH NULL_RATES AS(  SELECT    DATE_ADDED,    CAST(SUM(CASE WHEN AVG_TEMP IS NULL THEN 1 ELSE 0 END) AS FLOAT) / COUNT(*) AS AVG_TEMP_NULL_RATE  FROM    EXOPLANETS  GROUP BY    DATE_ADDED),
NULL_WITH_AVG AS(  SELECT    *,    AVG(AVG_TEMP_NULL_RATE) OVER (      ORDER BY DATE_ADDED ASC      ROWS BETWEEN 14 PRECEDING AND CURRENT ROW) AS TWO_WEEK_ROLLING_AVG  FROM    NULL_RATES  GROUP BY    DATE_ADDED)
SELECT  *FROM  NULL_WITH_AVGWHERE  AVG_TEMP_NULL_RATE - TWO_WEEK_ROLLING_AVG > 0.3;

實(shí)際上就是當(dāng)前日期和最近兩周內(nèi)的平均NULL比例做一個(gè)對(duì)比。

結(jié)果如下:

可以看到，現(xiàn)在我們的檢測(cè)都是通過(guò)SQL實(shí)現(xiàn)，但隨著生產(chǎn)環(huán)境數(shù)據(jù)的增加，觀測(cè)指標(biāo)的增多，使用機(jī)器學(xué)習(xí)去做是不是更好的呢？

翻譯來(lái)源：https://www.montecarlodata.com/data-observability-in-practice-using-sql-1/