AAtsushi's Blog
データエンジニアリング

Dataplexで外部キー制約を満たしているかのデータ品質チェック

BigQueryの外部キー制約は強制適用されないため、外部キー参照先の値（またはNULLのみ）しか入らないようにユーザー側で管理する必要がある。

そこでDataplexを使用して定期的に外部キー制約に沿った値になっているか確認できないかと考えた。

調べたところ、Dataplexを使用してSQL行チェックルールを使用して、外部キー参照先の値のみになっているかどうか定期的に確認できた。

以下は、確認結果についてのメモです。

まずはテスト用に2つのテーブルを作成した。

company_listテーブルには、会社IDと会社名(name)の列が存在する。staffテーブルは、スタッフのID、名前、会社名(company_name)の列が存在する。

staffテーブルのcompany_name列が、company_listテーブルのname列を外部参照する。

  • company_listテーブル

  • staffテーブル

以下はデータセット、テーブル作成のTerraformコード。

resource "google_bigquery_dataset" "default" {
  dataset_id                  = "test_dataset"
  friendly_name               = "test"
  description                 = "This is a test description"
  location                    = "US"
  # default_table_expiration_ms = 3600000
}

resource "google_bigquery_table" "company_list" {
  dataset_id = google_bigquery_dataset.default.dataset_id
  table_id   = "company_list"

  schema = <<EOF
  [
    {
      "name": "id",
      "type": "INT64",
      "mode": "NULLABLE"
    },
    {
      "name": "name",
      "type": "STRING",
      "mode": "NULLABLE"
    }
  ]
  EOF

  table_constraints {
    primary_key {
      columns = ["name"]
    }
  }
}

resource "google_bigquery_table" "staff" {
  dataset_id = google_bigquery_dataset.default.dataset_id
  table_id   = "staff"

  schema = <<EOF
  [
    {
      "name": "id",
      "type": "INT64",
      "mode": "NULLABLE"
    },
    {
      "name": "person_name",
      "type": "STRING",
      "mode": "NULLABLE"
    },
    {
      "name": "company_name",
      "type": "STRING",
      "mode": "NULLABLE"
    }
  ]
  EOF

  table_constraints {
    foreign_keys {
      referenced_table {
        project_id  = data.google_project.project.project_id
        dataset_id  = google_bigquery_table.company_list.dataset_id
        table_id    = google_bigquery_table.company_list.table_id
      }
      column_references {
        referencing_column = "company_name"
        referenced_column = "name"
      }
    }
  }
}

さらに、Dataplexのデータ品質チェックを作成する。SQLでは、DISTINCTで会社名を参照先のテーブルを取り出して、それらの会社名のいずれかに含まれていることを確認する。

resource "google_dataplex_datascan" "check_foreign_key_staff_table" {
  location     = "us-central1"
  display_name = "Check if satisfy foreign key"
  data_scan_id = "check-foreign-key-staff-table"

  data {
    resource = "//bigquery.googleapis.com/${google_bigquery_table.staff.id}"
  }

  execution_spec {
    trigger {
      on_demand {}
    }
  }

  data_quality_spec {
    sampling_percent = 100

    rules {
      name = "check-foreign-key"
      column = "company_name"
      dimension = "CONSISTENCY"
      row_condition_expectation {
        sql_expression = <<EOL
          company_name IN (
            SELECT
              DISTINCT name
              FROM ${google_bigquery_table.company_list.project}.${google_bigquery_table.company_list.dataset_id}.${google_bigquery_table.company_list.table_id}
              -- FROM句でプロジェクト名を指定しないと、table not found的なエラーになるので要注意
          )
        EOL
      }
    }
  }
}

では、実際にデータ品質スキャンを実行して確かめる。

それぞれのテーブルには次のようなデータを入れた。

この状態でデータ品質スキャンを実行すると成功した。

次に失敗するパターンも試してみた。staffテーブルにQuad.Incというcompnany_listテーブルに存在しない会社名のレコードを挿入した。

この状態でデータ品質スキャンを実行すると想定通り失敗となった。

ちなみにstaffテーブルにレコードが0件の状態も試してみたが、想定通り成功になった。

このようにしてDataplexのデータ品質スキャンを使えば、外部キー制約を満たしていることを定期的に確認できそう。

他に外部キー制約を満たしていることを確認するための良い方法があれば、教えてください。

以上

← 記事一覧へ戻る