【AWS Glue】データ品質の評価観点と、Data Qualityで利用可能なルールの対応を考えてみた

			
			Rules = [
   (IsComplete "colA") and (IsUnique "colA"),
   (RowCount "colB" > 100) or (IsPrimaryKey "colB"),
   (RowCount > 0) or ((IsComplete "colC") and (IsUnique "colC"))
]

		

			
			# テーブル
+------+------+
|myCol1|myCol2|
+------+------+
|     2|     1|
|     0|     3|
+------+------+

# 評価結果
+----------------------------------------------------------+-------+
|Rule                                                      |Outcome|
+----------------------------------------------------------+-------+
|(ColumnValues "myCol1" > 1) OR (ColumnValues "myCol2" > 2)|Failed |
+----------------------------------------------------------+-------+

		

			
			eval_dq = EvaluateDataQuality().process_rows(
    frame=dyf,
    ruleset=DQ_RULESET,
    additional_options={
        "compositeRuleEvaluation.method":"ROW"
    },
)

		

			
			+------+------+------------------------------------------------------------+---------------------------+
|myCol1|myCol2|DataQualityRulesPass                                        |DataQualityEvaluationResult|
+------+------+------------------------------------------------------------+---------------------------+
|2     |1     |[(ColumnValues "myCol1" > 1) OR (ColumnValues "myCol2" > 2)]|Passed                     |
|0     |3     |[(ColumnValues "myCol1" > 1) OR (ColumnValues "myCol2" > 2)]|Passed                     |
+------+------+------------------------------------------------------------+---------------------------+

		

			
			# Completenessは列全体を評価するため行レベルには非対応
Rules = [
   (Completeness "colA" > 0.95) and (ColumnValues "colA" matches "[a-zA-Z]*")
]

# 出力
IllegalArgumentException: Composite rule evaluation at row level is not supported for the following rules:
(Completeness "colA" > 0.95) and (ColumnValues "colA" matches "[a-zA-Z]*")

		

			
			Rules = [
   (IsComplete "colA") and (ColumnValues "colA" matches "[a-zA-Z]*")
   # または
   (ColumnValues "colA" != NULL) and (ColumnValues "colA" matches "[a-zA-Z]*")
]

		

			
			Rules = [
   # colAとcolBの相関係数が直近10回平均±0.1の範囲に収まっているか
   ColumnCorrelation "colA" "colB" between avg(last(10))-0.1 and avg(last(10))+0.1
]

		

			
			Analyzers = [
   DistinctValuesCount "colA",
   ColumnLength "colA"
]

		

			
			Rules = [
    # 欠損値がないか
    IsComplete "colA",
    # 欠損値の割合が5%未満に収まるか
    Completeness "colB" > 0.95
]

		

			
			Rules = [
    # IsCompleteは複合ルールの評価でも有効
   (IsComplete "colA") and (ColumnValues "colA" matches "[a-zA-Z]*")
   # ColumnValuesで代用することも可能
   (ColumnValues "colA" != NULL) and (ColumnValues "colA" matches "[a-zA-Z]*")
]

		

			
			Rules = [
    # 値がリストの中に含まれるか
    ColumnValues "colA" in ["Gold", "Silver", "Bronze"],
    # 値が範囲内に含まれているか
    ColumnValues "colB" between 0 and 120,
    # 特定の形式に対応しているか
    ColumnValues "colC" matches "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$",
]

		

			
			Rules = [
    # データ型がDateになっているか
    ColumnDataType "colD" = "Date",
    # 文字列長が5から10の間に含まれるか
    ColumnLength "colE" between 5 and 10,
]

		

			
			Rules = [
    # カラム同士の相関係数が0.8より大きいか
    ColumnCorrelation "colF" "colG" > 0.8,
    # カラム数が指定の値範囲に含まれるか
    ColumnCount between 10 and 20
    # 対象の列が存在するか
    ColumnExists "colH"
    # 全列の名前が所定の正規表現と一致するか
    ColumnNamesMatchPattern "aws_.*"
    # 標準偏差が1.5以下であるか
    StandardDeviation "colI" < 1.5
]

		

			
			Rules = [
    # referenceテーブルとcolA列で結合したときのデータ一致率が9割以上
    DatasetMatch "reference" "colA" >= 0.9
]

		

			
			Rules = [
    # colB列の値がreferenceテーブルのcolB列に9割以上含まれる
    ReferentialIntegrity "colB" "reference.coB" >= 0.9
]

		

			
			Rules = [
    # 重複値がないか
    IsUnique "colA",
    # 重複値の割合が5%未満に収まるか
    Uniqueness "colB" > 0.95
    UniqueValueRatio "colC" > 0.95
    # ユニーク値数が10～50種類の範囲に含まれる
    DistinctValuesCount "colD" between 10 and 50
]

		

			
			Rules = [
    # 対象のカラムがプライマリキーになっているか
    IsPrimaryKey "colE",
    # 複合キーにも対応
    IsPrimaryKey "colF" "colG"
]

		

			
			Rules = [
    # referenceテーブルと全カラムの名前とデータ型が一致しているか
    # 順序は考慮しない
    SchemaMatch "reference" = 1.0
]

		

			
			Rules = [
    # 平均値
    Mean "colA" between 1000 and 50000,
    # 標準偏差
    StandardDeviation "colB" < 100,
    # 合計値
    Sum "colC" > 1000000
    # カラム間の相関係数
    ColumnCorrelation "colD" "colE" between -0.8 and -0.4
]

		

			
			Rules = [
    # データセット全体の行数の異常値を検知
    DetectAnomalies "RowCount",
    # 特定のカラムの文字列数の異常値を検知
    DetectAnomalies "ColumnLength" "colF"
]

		

			
			Rules = [
    # データが3日以内、1時間以内に更新されている
    DataFreshness "colA" <= 3 days,
    DataFreshness "colB" <= 1 hours,
    # now()関数を使う場合
    ColumnValues "colC" > (now() - 3 days)
]

		

			
			Rules = [
    # S3上のファイルが24時間以内に更新されている
    FileFreshness "s3://bucket/path/data.parquet" > (now() - 24 hours)
    # フォルダ単位でも指定可能
    FileFreshness "s3://bucket/path/" <= 24 hours,
    # 日付や日時での指定も可能
    FileFreshness "s3://bucket/path/" > "2020-01-01",
    FileFreshness "s3://bucket/path/" between "9:30 AM" and "9:30 PM"
]