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のデータ品質スキャンを使えば、外部キー制約を満たしていることを定期的に確認できそう。
他に外部キー制約を満たしていることを確認するための良い方法があれば、教えてください。
以上