ストーリー

DWHの基本概念

OLTP vs OLAP

DWHの基本アーキテクチャ

graph TD
    DB["運用DB<br/>（PostgreSQL）"] --> STG
    API["外部API<br/>（SaaS etc.）"] --> STG
    LOG["ログ<br/>（S3/GCS）"] --> STG

    STG["Staging Area<br/>（生データ一時保存）"]
    STG -- "Transform" --> DWH["DWH Core<br/>（ファクト+ディメンション）"]
    DWH --> DM["Data Marts<br/>（部門別分析ビュー）"]
    DM --> BI["BI / Dashboard<br/>（Looker, Metabase）"]

    classDef source fill:#fee2e2,stroke:#ef4444
    classDef staging fill:#fef3c7,stroke:#f59e0b
    classDef core fill:#dbeafe,stroke:#3b82f6,font-weight:bold
    classDef output fill:#f0fdf4,stroke:#22c55e
    class DB,API,LOG source
    class STG staging
    class DWH core
    class DM,BI output

クラウドDWH比較

Snowflake

-- Snowflake: コンピュートとストレージの完全分離
-- ウェアハウス（コンピュート）を用途別に作成

-- 分析チーム用ウェアハウス（自動スケール）
CREATE WAREHOUSE analytics_wh
  WITH WAREHOUSE_SIZE = 'MEDIUM'
  AUTO_SUSPEND = 300          -- 5分無操作で自動停止
  AUTO_RESUME = TRUE
  SCALING_POLICY = 'STANDARD'
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 4;      -- 最大4クラスタに自動スケール

-- ETLバッチ用ウェアハウス（大型、短時間）
CREATE WAREHOUSE etl_wh
  WITH WAREHOUSE_SIZE = 'X-LARGE'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE;

-- データ共有（Zero-Copy Cloning）
CREATE DATABASE analytics_sandbox CLONE production_dwh;
-- ストレージのコピーなし、メタデータのみ複製

-- Time Travel（過去データへのクエリ）
SELECT * FROM orders
AT(TIMESTAMP => '2024-06-01 00:00:00'::TIMESTAMP);

-- Snowflake のセミ構造化データ対応
CREATE TABLE events (
  event_id STRING,
  event_data VARIANT,  -- JSON をそのまま格納
  created_at TIMESTAMP
);

-- JSONフィールドへの直接クエリ
SELECT
  event_data:user_id::STRING AS user_id,
  event_data:action::STRING AS action,
  event_data:properties.amount::NUMBER AS amount
FROM events
WHERE event_data:action = 'purchase';

BigQuery

-- BigQuery: サーバーレス、従量課金
-- テーブルの作成（パーティション + クラスタリング）

CREATE TABLE `project.dataset.orders`
(
  order_id STRING NOT NULL,
  customer_id STRING NOT NULL,
  order_date DATE NOT NULL,
  category STRING,
  region STRING,
  total_amount NUMERIC,
  status STRING,
  created_at TIMESTAMP
)
PARTITION BY order_date              -- 日付パーティション
CLUSTER BY category, region          -- よく使うフィルタでクラスタリング
OPTIONS(
  partition_expiration_days = 2555,   -- 7年保持
  require_partition_filter = TRUE     -- パーティションフィルタ必須
);

-- コスト最適化: スキャン量の確認
-- DRY RUN でスキャン量を事前確認
SELECT
  order_date,
  category,
  SUM(total_amount) AS daily_revenue
FROM `project.dataset.orders`
WHERE order_date BETWEEN '2024-01-01' AND '2024-06-30'
  AND category = 'electronics'
GROUP BY 1, 2;
-- このクエリは約 2.3GB をスキャンします（$0.012）

-- マテリアライズドビュー
CREATE MATERIALIZED VIEW `project.dataset.daily_revenue`
AS
SELECT
  order_date,
  category,
  region,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_revenue,
  AVG(total_amount) AS avg_order_value
FROM `project.dataset.orders`
GROUP BY 1, 2, 3;
-- 自動的にクエリがMVにリライトされる

Redshift

-- Redshift: MPP（Massively Parallel Processing）アーキテクチャ
-- 分散キーとソートキーの設計が重要

CREATE TABLE orders (
  order_id VARCHAR(36) NOT NULL,
  customer_id VARCHAR(36) NOT NULL,
  order_date DATE NOT NULL,
  total_amount DECIMAL(10,2),
  status VARCHAR(20),
  SORTKEY (order_date),           -- ソートキー: 日付フィルタの高速化
  DISTSTYLE KEY DISTKEY (customer_id)  -- 分散キー: JOIN高速化
);

-- Redshift Spectrum: S3データを直接クエリ
CREATE EXTERNAL SCHEMA spectrum_schema
FROM DATA CATALOG
DATABASE 'raw_data'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftSpectrumRole';

-- S3上のデータとRedshiftテーブルのJOIN
SELECT
  o.order_date,
  o.total_amount,
  l.page_url,
  l.session_duration
FROM orders o
JOIN spectrum_schema.access_logs l
  ON o.customer_id = l.user_id
  AND o.order_date = l.log_date;

DWH選定マトリクス

まとめ

チェックリスト

• OLTPとOLAPの違いを説明できる
• 3大クラウドDWHの特徴と選定基準を理解した
• パーティション、クラスタリングの設計意図を理解した
• コスト最適化の基本的な手法を把握した

次のステップへ

次はデータレイクとレイクハウスの設計を学び、DWHだけでは対応できない大規模データ基盤を理解します。

推定読了時間: 40分