分析基盤のためのテーブルとカラム
データ分析基盤を構築する上で、テーブルとカラムの設計は非常に重要です。分析に必要なデータを効率的に取得・加工するために、適切なテーブル構成とカラム設計を行う必要があります。
マスタとトランザクション
マスタテーブルとトランザクションテーブルは、データベース設計で重要かつ基本的な構成です。
下記で紹介するテーブルは全て履歴テーブルです。
マスタテーブル
基本的に静的なマスターデータを格納するテーブルのことです。
様々なトランザクションシステムから参照されるため、履歴管理が不可欠で、データ品質と整合性が重視されます。
OLAPデータベースでは、特に以下のようなカラムを持つことがあります。
バージョン(履歴ID)
有効開始日
有効終了日
変更操作種別 (INSERT/UPDATE/DELETE)
変更者情報
トランザクションテーブル
業務アプリケーションにおける日々の取引や発生事象のデータを格納するテーブルのことです。
時系列データが蓄積されるため、データ量が肥大化しやすくなります。
OLAPデータベースでは、特に以下のようなカラムを持つことがあります。
連携元作成日時
連携元変更日時
変更操作種別 (INSERT/UPDATE/DELETE)
変更者情報
削除フラグ(論理削除)
パーティショニングカラム(年月日など)
(ソーストランザクションID)
(ソースシステムID)
最新テーブルと履歴テーブル
最新テーブル
常に最新状態のデータのみを保持するテーブルのことです。
OLTPではよく見る設計です。UPDATEすることで過去のデータを残さず、データ量が比較的少なくなるのでパフォーマンスが良くなります。
OLAPでは、最新データのみを見せるリバースETLやその他バッチ処理、ダッシュボード等で利用するケースがあります。
履歴テーブル
データの変更履歴を永続的に保持するためのテーブルです。
上記のマスタとトランザクションで紹介したテーブルはどちらも履歴テーブルになります。
カラム作成方法
カラムは、テーブル作成時に定義することができます。また、後からALTER TABLEコマンドを使用して追加することも可能です。
前処理段階でカラム追加
以下の対象カラムは事前に作成しておくと分析時に便利です。
有効開始日
有効終了日
パーティショニングカラム(年月日など)
例えば、effective_start_date(有効開始日)カラムをSQLで追加する場合は以下のようなクエリを作成します。
SELECT
id,
created_at,
CASE
WHEN created_at IS NULL THEN NULL
ELSE DATE_ADD(LAG(created_at) OVER (PARTITION BY id ORDER BY created_at), INTERVAL 1 DAY)
END AS effective_start_date
FROM table;
また、パーティショニングカラム(年月日など)をSQLで追加する場合は以下のようなクエリを作成します。
SELECT
id,
updated_at,
YEAR(updated_at) AS year,
MONTH(updated_at) AS month,
DAY(updated_at) AS day
FROM table;
上記のようにSQLで書くことができれば View(または materialized View )上でカラム追加を実現できます。
データ取得(連携)時に取得する
OLAPでは下記のように、どうしてもOLTP上から取得しないといけないカラムが存在します。
これらはCDC(変更データキャプチャ)やらツールやらでカラム追加を行う必要があります。
連携元作成日時
連携元変更日時
変更操作種別 (INSERT/UPDATE/DELETE)
変更者情報
(ソーストランザクションID)
(ソースシステムID)
この部分に関しては、別記事で紹介したいと思います。
スロー・チェンジ・ディメンション(Slowly Changing Dimensions)
スタースキーマを実装する場合、ソースデータの変更をどのように表現するかを決めることは重要で、ファクトに比べるとディメンションはゆっくりと変更されることから「スロー・チェンジ・ディメンション」と呼びます。
SCD(Slowly Changing Dimensions)は、全部でType 0 から Type 7 まであります。
上記で紹介したテーブルは下記のような対応になります。
Type1: 最新テーブル
Type2: 履歴テーブル
まとめ
本記事では、分析基盤における主要なテーブル構成と各テーブルに必要なカラムについて解説し、カラム作成方法やOLAP分析で必要となる追加カラムについても詳しく説明しました。
意外とこのあたりの話(どういうカラムを持たせるべきか等)は語られていないと思っており、この記事を書いてみました。どなたかの一助になれば幸いです。
参考
データエンジニアリングの基礎
Clarifying Data Warehouse Design with Historical Dimensions
Slowly Changing Dimensionの基礎と発展
この記事が気に入ったらサポートをしてみませんか?