ウェアハウスキー

スタースキーマでは、各ディメンションテーブルにサロゲートキーが付与される。サロゲートキーとは、データウェアハウス専用に作成された一意の識別子であり、本質的な意味を持たない「シーケンス番号」または「ハッシュ値」で構成される。

このサロゲートキーは、「ウェアハウスキー」とも呼ばれ、ディメンションテーブルのプライマリーキーとして機能する。ウェアハウスキーの導入により、データの一意性が保証され、ディメンションテーブルの関連付けがスムーズになる。また、元のシステムのキー（ナチュラルキー）に依存しないため、データの変更に柔軟に対応できるというメリットもある。

当サイトのスタースキーマの記事では、接尾辞 _key によってウェアハウスキーを簡単に識別できるようにする。また、スタースキーマの ER 図ではウェアハウスキーを SK、ナチュラルキーを NK と表記する。

シーケンス番号 vs ハッシュ値

ウェアハウスキーは、シーケンス番号またはハッシュ値で生成される。筆者は、以下の点を考慮してウェアハウスキーにシーケンス番号ではなくハッシュ値を用いるべきだと考えている。

1. 変換プロセスがシンプルになる
2. クエリパフォーマンスが良くなる

1. データ変換プロセス

ハッシュ値で作成するウェアハウスキーとシーケンス番号で作成するときウェアハウスキーを比べると、前者の方が変換プロセスがシンプルである。

シーケンス番号の場合

シーケンス番号を使ってウェアハウスキーを作成する場合、アウトリガーなどの依存関係があるテーブルの変換プロセスが複雑になる可能性がある。

例えば、日付情報を管理する「日付ディメンション」と、顧客情報を管理する「顧客ディメンション」があるとする。顧客ディメンションには、誕生日や初回購入日などのユーザーの行動属性が含まれる可能性があり、これらは日付ディメンションとの外部キーを持っている。

この関係から見て分かるように、日付ディメンションと顧客ディメンションには親子関係が存在する。この場合、顧客ディメンションのデータを読み込むためには、先に日付ディメンションのデータを読み込み、ウェアハウスキーを取得する必要がある。この依存関係により、データ読み込みの順序が制約され、プロセスが複雑になる可能性がある。

dim_date

| date_key | date       | ... |
| -------- | ---------- | --- |
| 1        | 1900-01-01 | ... |
| 2        | 1900-01-02 | ... |
| 3        | 1900-01-03 | ... |
| ...      | ...        | ... |

dim_user

| user_key | user_id | ... | birth_date | birth_date_key |
| -------- | ------- | --- | ---------- | -------------- |
| 1        | u1      | ... | 2000-01-01 | 36525          |
| 2        | u2      | ... | 1992-01-01 | 33604          |
| 3        | u3      | ... | 1900-01-03 | 3              |
| ...      | ...     | ... | ...        | ...            |

ハッシュ値の場合

ハッシュ値を使ってウェアハウスキーを作成する場合、ナチュラルキーに対してハッシュ化を行うので、親テーブルと子テーブルの読み込み処理を分離し、依存関係を解消することができる。これにより、データ読み込みのパフォーマンスが向上し、プロセスがシンプルになる。

dim_date

| date_key                         | date       | ... |
| -------------------------------- | ---------- | --- |
| 57bf835442e2cf6f551f6c52639afc4c | 1900-01-01 | ... |
| 0e2738c72d39084215b2d5fc0bc3ec4c | 1900-01-02 | ... |
| dec2630316eb8450b33c25482b1957d7 | 1900-01-03 | ... |
| ...                              | ...        | ... |

dim_user

| user_key                         | user_id | ... | birth_date | birth_date_key                   |
| -------------------------------- | ------- | --- | ---------- | -------------------------------- |
| e4774cdda0793f86414e8b9140bb6db4 | u1      | ... | 2000-01-01 | dab573b0f8a422a6f9e8033277ce1751 |
| 270c1b084f3f146eb5787075158d9c53 | u2      | ... | 1992-01-01 | 56bfafe1a37d7da1e8564f67c517a15c |
| 532a7b8e0328a8d05a8e6258b28b9a36 | u3      | ... | 1900-01-03 | dec2630316eb8450b33c25482b1957d7 |
| ...                              | ...     | ... | ...        | ...                              |

2. クエリパフォーマンス

ハッシュ値を使用した結合がシーケンス番号を使用した結合よりも高速になりやすい主な理由は以下の 3 点である。

• データサイズの効率性:
  • ハッシュ値は固定長なので、メモリとキャッシュの効率が良い。
  • シーケンス番号は可変長なので、データ量が増えるとサイズが大きくなり、効率が悪くなる。
• インデックスの効率性:
  • ハッシュ値は固定長なので、インデックスの構築とルックアップが高速。
  • シーケンス番号は可変長なので、インデックスのサイズが大きくなり、ルックアップの効率が低下。
• CPU 処理の効率性:
  • ハッシュ値の比較は、CPU の命令レベルで最適化されており、高速。
  • シーケンス番号の比較は、データサイズが大きくなると CPU の処理時間が長くなる。

つまり、ハッシュ値は固定長であるため、メモリ、キャッシュ、インデックス、CPU 処理の効率が良く、結合操作が高速になる。一方、シーケンス番号は可変長であるため、データ量が増えると効率が悪くなり、結合操作のパフォーマンスが低下する可能性がある。

ハッシュ関数の検討

ウェアハウスキーをハッシュ値で作成する場合、いくつかのハッシュ関数の選択肢がある。

	CRC-32	MD5	SHA-1	SHA-256
入力の最大長	Unlimited	Unlimited	$2^{64} -1$ bits	$2^{64} -1$ bits
バイナリ出力	32 bits	128 bits	160 bits	256 bits
16 進数表記の出力	8 characters	32 characters	40 characters	64 characters
ハッシュ値の数	77163	5.06 billion	$1.42 \times 10^{24}$	$1.16 \times 10^{77}$

この表から分かるように、スタースキーマのディメンションテーブルにおける最適なハッシュ関数は、MD5 であると筆者は考えている。MD5 は、50 億レコード以上で衝突のリスクが高まるが、通常、ディメンションテーブルはファクトテーブルのように深い設計にならないからである。深過ぎるディメンションテーブルは、パフォーマンスの観点からも設計を見直した方が良い。

note

ディメンションテーブルは広く、ファクトテーブルは深い。

ハッシュ関数を選ぶ際に重要なのは、データウェアハウスでハッシュ値を使う目的が、暗号化やセキュリティを確保することではなく、ハッシュ関数の性質を活かしてナチュラルキーをハッシュ値に置き換えるだけであることを理解することである。ナチュラルキーが秘密情報を内包していない限り、レインボーテーブルの対策はしなくて良い。

ウェアハウスキーの生成

キーの生成方法

Google SQL

Google SQL では、md5 関数でハッシュ値を作成できるが、これはバイナリ出力なので to_hex 関数で文字列に変換する。

to_hex(md5(NATURAL_KEY))

ディメンションテーブルが複合ナチュラルキーで構成される場合は、concat 関数でキーを連結する。この際、キーの一意性を確保するために区切り文字（任意）を設定する必要がある。

to_hex(md5(concat(NATURAL_KEY1, "||", NATURAL_KEY2, "||", NATURAL_KEY3, ...)))

区切り文字 なし

| SK             | NK1  | NK2  | NK3  |
| -------------- | ---- | ---- | ---- |
| hash("123456") | 12   | 34   | 56   |
| hash("123456") | 12   | null | 3456 |
| hash("123456") | 1234 | 56   | null |

区切り文字 あり

| SK                 | NK1  | NK2  | NK3  |
| ------------------ | ---- | ---- | ---- |
| hash("12||34||56") | 12   | 34   | 56   |
| hash("12||||3456") | 12   | null | 3456 |
| hash("1234||56||") | 1234 | 56   | null |

dbt

dbt_utils パッケージの generate_surrogate_key 関数には、MD5 がハッシュ関数として組み込まれている。

{{ dbt_utils.generate_surrogate_key(['NATURAL_KEY']) }}

この関数は、ディメンションテーブルが複合ナチュラルキーで構成される場合も、区切り文字を適切に挿入してくれるので、そのままウェアハウスキーとして使用して問題ない。

{{ dbt_utils.generate_surrogate_key(['NATURAL_KEY1', 'NATURAL_KEY2', 'NATURAL_KEY3', ...]) }}

キーの生成順序

ウェアハウスキーの生成は、基本的にデータ変換プロセスの最後に行う。これにはいくつかの理由がある。

• データの一貫性や安定性の確保: ウェアハウスキーの生成には、通常、単一または複数の属性が関与する。これらの属性は変換プロセスの中で値が変更される可能性がある。ウェアハウスキーの生成を最後に行うことで、すべての属性の最終的な値を使用でき、データの一貫性や安定性を確保できる。
• パフォーマンスの最適化: ハッシュ計算は、比較的コストの高い操作である。変換プロセスの途中で何度もウェアハウスキーを生成すると、パフォーマンスが低下する可能性がある。ウェアハウスキーの生成を最後に行うことで、キーの生成回数を最小限に抑えることができる。
• 変換ロジックとの分離: ウェアハウスキーの生成は、データの変換ロジックとは独立した操作である。ウェアハウスキーの生成を最後に行うことで、変換ロジックとキーの生成を分離し、コードの可読性とメンテナンス性を向上させることができる。

with first as (
    -- 変換 1
),

second as (
    -- 変換 2
),

︙

select
    -- ウェアハウスキーの生成
    hash(NATURAL_KEY),
    *
from result

まとめ

ウェアハウスキーは、ナチュラルキーのサロゲートキーであり、ディメンションテーブルのプライマリーキーとして機能する。これは、シーケンス番号またはハッシュ値で構成されているが、筆者はデータ変換プロセスとクエリパフォーマンスの観点からハッシュ値で作成する方を推奨している。

ハッシュ値を使う目的は、暗号化やセキュリティを確保することではなく、ハッシュ関数の性質を活かしてナチュラルキーをハッシュ値に置き換えるだけである。

ウェアハウスキーの生成は、データの一貫性や安定性の確保、パフォーマンスの最適化、変換ロジックと分離できる点から、データ変換プロセスの最後に行うと運用がしやすい。

参考文献

• Star Schema: The Complete Reference
• The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling Third Edition