PostgreSQLで始めるデータプロファイリング
はじめに
DBを運用していると、各テーブルやカラムの特性を把握することが重要になってきます。以下のようなケースで、データプロファイリングは威力を発揮します。
- 新規プロジェクトでの既存データベースの理解
- データ品質の監視と異常検知
- スキーマ設計やインデックス戦略の最適化
- 統計情報に基づいたクエリチューニング
本記事では、PostgreSQLを使用して効率的にデータプロファイリングを行う方法を解説します。
また、記事の作成にあたって『アジャイルデータモデリング 組織にデータ分析を広めるためのテーブル設計ガイド』(ローレンス・コル、スティーブ・ホブス著/講談社)を参考にさせていただきました。
データプロファイリングスキーマとテーブル
プロファイリング結果を格納するためのスキーマとテーブルを作成します。ここでは、各カラムの統計情報を時系列で保存できる設計を採用しました。
CREATE SCHEMA IF NOT EXISTS profile;
CREATE TABLE IF NOT EXISTS profile.profile_results (
table_name VARCHAR(100) NOT NULL,
column_name VARCHAR(100) NOT NULL,
max_val TEXT,
min_val TEXT,
sample_count BIGINT,
unique_count BIGINT,
min_count BIGINT,
max_count BIGINT,
null_count BIGINT,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
UNIQUE (table_name, column_name)
);
各カラムの役割は以下の通りです。
カラム名 | 説明 | 活用例 |
---|---|---|
max_val | 最大値 | 異常値の検出、データ範囲の確認 |
min_val | 最小値 | データの下限値の把握 |
sample_count | 総行数 | データ量の把握、増加傾向の分析 |
unique_count | ユニーク値数 | カーディナリティの分析、インデックス戦略の検討 |
min_count | 最小値の出現回数 | データの偏り具合の把握 |
max_count | 最大値の出現回数 | 特定値への集中度の確認 |
null_count | NULL値の数 | データの欠損状況の把握 |
動的なプロファイリングクエリの生成
データベースのメタデータ(information_schema.columns)を使用し対象スキーマの全テーブル・カラムに対して自動的にプロファイリングを行います。
クエリの構造
テーブルのカラムごとに、プロファイリング用のSQLを生成します。
Query
\a \t \o output.txt -- unaligned形式、タプルのみ、ファイル出力を一度に設定
SELECT
'WITH extremes AS (
SELECT
MAX(' || column_name || ') as max_val,
MIN(' || column_name || ') as min_val
FROM ' || table_name || '
),
stats AS (
SELECT
e.max_val,
e.min_val,
COUNT(*) as sample_count,
COUNT(DISTINCT t.' || column_name || ') as unique_count,
COUNT(*) FILTER (WHERE t.' || column_name || ' = e.min_val) as min_count,
COUNT(*) FILTER (WHERE t.' || column_name || ' = e.max_val) as max_count,
COUNT(*) FILTER (WHERE t.' || column_name || ' IS NULL) as null_count
FROM ' || table_name || ' t
CROSS JOIN extremes e
GROUP BY e.max_val, e.min_val
)
INSERT INTO profile.profile_results
(table_name, column_name, max_val, min_val, sample_count, unique_count, min_count, max_count, null_count)
SELECT
''' || table_name || ''',
''' || column_name || ''',
max_val,
min_val,
sample_count,
unique_count,
min_count,
max_count,
null_count
FROM stats;'
FROM information_schema.columns
WHERE table_schema = 'public';
\o
実行の結果、以下のようなクエリが生成されます。
Result
WITH extremes AS (
SELECT
MAX(agexxx.....) as max_val,
MIN(agexxx.....) as min_val
FROM table_1
),
stats AS (
SELECT
e.max_val,
e.min_val,
COUNT(*) as sample_count,
COUNT(DISTINCT t.agexxx.....) as unique_count,
COUNT(*) FILTER (WHERE t.agexxx..... = e.min_val) as min_count,
COUNT(*) FILTER (WHERE t.agexxx..... = e.max_val) as max_count,
COUNT(*) FILTER (WHERE t.agexxx..... IS NULL) as null_count
FROM table_1 t
CROSS JOIN extremes e
GROUP BY e.max_val, e.min_val
)
INSERT INTO profile.profile_results
(table_name, column_name, max_val, min_val, sample_count, unique_count, min_count, max_count, null_count)
SELECT
'table_1',
'agexxx.....',
max_val,
min_val,
sample_count,
unique_count,
min_count,
max_count,
null_count
FROM stats;
WITH extremes AS (
SELECT
MAX(booxxx.....) as max_val,
MIN(booxxx.....) as min_val
FROM test_table
),
stats AS (
SELECT
e.max_val,
e.min_val,
COUNT(*) as sample_count,
COUNT(DISTINCT t.booxxx.....) as unique_count,
COUNT(*) FILTER (WHERE t.booxxx..... = e.min_val) as min_count,
COUNT(*) FILTER (WHERE t.booxxx..... = e.max_val) as max_count,
COUNT(*) FILTER (WHERE t.booxxx..... IS NULL) as null_count
FROM test_table t
CROSS JOIN extremes e
GROUP BY e.max_val, e.min_val
)
INSERT INTO profile.profile_results
(table_name, column_name, max_val, min_val, sample_count, unique_count, min_count, max_count, null_count)
SELECT
'test_table',
'booxxx.....',
max_val,
min_val,
sample_count,
unique_count,
min_count,
max_count,
null_count
FROM stats;
WITH extremes AS (
SELECT
MAX(carxxx.....) as max_val,
MIN(carxxx.....) as min_val
FROM table_1
),
stats AS (
SELECT
e.max_val,
e.min_val,
COUNT(*) as sample_count,
COUNT(DISTINCT t.carxxx.....) as unique_count,
COUNT(*) FILTER (WHERE t.carxxx..... = e.min_val) as min_count,
COUNT(*) FILTER (WHERE t.carxxx..... = e.max_val) as max_count,
COUNT(*) FILTER (WHERE t.carxxx..... IS NULL) as null_count
FROM table_1 t
CROSS JOIN extremes e
GROUP BY e.max_val, e.min_val
)
INSERT INTO profile.profile_results
(table_name, column_name, max_val, min_val, sample_count, unique_count, min_count, max_count, null_count)
SELECT
'table_1',
'carxxx.....',
max_val,
min_val,
sample_count,
unique_count,
min_count,
max_count,
null_count
FROM stats;
プロファイリング結果
生成されたクエリを実行後、プロファイリング結果をCSVファイルとして出力します。
\copy (
select *
from profile.profile_results
order by table_name, column_name
) to 'profile_results.csv' with csv header;
分析結果の例
最終的に以下の結果をcsvで得られます(min_valが空になっているレコードがありますが、恐らく空文字("")が数値や論理値よりも小さいとしてMIN()に拾われているためです。そうであれば、クエリの修正は必要なさそうです)。
※構造プロファイリング、リレーションシッププロファイリングとは
所感
構造プロファイリング、リレーションシッププロファイリングについてもクエリを拡張することで同様に実装できそうです。
クラウドではどのようなツールがあるか調べてみたところ、Dataplexが同等の機能を包含しているようでした。特にBigQueryを採用している場合、GCPのエコシステムとの親和性を考慮して、Cloud Run、Cloud Functions、Cloud SchedulerなどGCP系のツールで固める構成に落ち着くことが多いと思います。その場合でも、ポータブルかつ柔軟にテストすることができるSQLスクリプトを書けることには一定の意味があると言えます。
Discussion