Snowflake 上の Superset

ソース側の 3 つの運用ダッシュボードの構築: 接続、データセット、チャート、組み立て。

このガイドでは、http://localhost:8088 (admin / admin) の Apache Superset で 3 つのダッシュボードすべてを作成する手順を説明します。

作業の順序:

Superset を起動し、Snowflake 接続を登録する
すべてのデータセットを作成する (名前付きデータセットとして保存した SQL クエリ)
チャートを作成し、ダッシュボードを組み立てる

ステップ 1: Superset を起動して Snowflake に接続する

Superset を起動する

この Superset イメージは Snowflake と ClickHouse のドライバを含むようカスタマイズされています。初回実行では Docker にビルドさせるため --build を使います:

cd "$(git rev-parse --show-toplevel)/workshop_public/snowflake_migration_lab/01-setup-snowflake/superset"
source ../.env
docker compose up -d --build

データベース接続を登録する (自動)

superset/ ディレクトリから次を実行します:

source ../.env && ./init_superset.sh

このスクリプトは Superset の準備完了を待ってから、NYC Taxi — Snowflake (Source) を自動的に登録します。期待される出力:

>>> Superset is up.
>>> Authenticated.
>>> CSRF token obtained.
>>> Registering: NYC Taxi — Snowflake (Source)
    Registered successfully.

データベース接続を登録する (手動での代替手順)

UI から登録したい場合:

Settings → Database Connections → + Database に移動
Snowflake を選択
SQLAlchemy URI を入力 — パスワードに含まれる特殊文字は URL エンコードしてください (# → %23、! → %21、@ → %40 など):

snowflake://<USER>:<URL_ENCODED_PASSWORD>@<SNOWFLAKE_ORG>-<SNOWFLAKE_ACCOUNT>/NYC_TAXI_DB/ANALYTICS?warehouse=ANALYTICS_WH&role=ANALYST_ROLE

Display Name を設定: NYC Taxi — Snowflake (Source)
Advanced → SQL Lab で Allow this database to be explored と Allow DML を有効にする
Test Connection をクリック → "Connection looks good!" と表示されるはず
Connect をクリック

ステップ 2: すべてのデータセットを作成する

すべてのチャートは仮想データセット — 名前付きデータセットとして保存した SQL クエリ — を使います。

各データセットの作成方法:

Datasets → + Dataset に移動
データベースを選択: NYC Taxi — Snowflake (Source)
Create dataset from SQL query をクリックし、以下の SQL を貼り付ける
示された名前で保存する
保存後: Datasets → 鉛筆アイコン → Columns タブ → "Sync columns from source" → Save を行います。この手順を省くと、チャートビルダーでカラムが 0 件と表示されます。

ダッシュボード 1 のデータセット

`ops_hourly_revenue` — borough 別の時間別売上

スキーマ: ANALYTICS

SELECT
    DATE_TRUNC('hour', pickup_at)                      AS hour_bucket,
    pickup_borough,
    COUNT(*)                                           AS trip_count,
    SUM(total_amount_usd)                              AS total_revenue,
    AVG(tip_amount_usd / NULLIF(fare_amount_usd, 0))  AS avg_tip_rate,
    AVG(trip_distance_miles)                           AS avg_distance_miles
FROM ANALYTICS.FACT_TRIPS
WHERE pickup_at >= DATEADD('day', -7, CURRENT_TIMESTAMP())
  AND pickup_borough IS NOT NULL
GROUP BY 1, 2
ORDER BY 1 DESC, total_revenue DESC

`ops_zone_agg` — zone 別集計

スキーマ: ANALYTICS

SELECT
    hour_bucket,
    zone_id,
    trips,
    revenue,
    avg_distance
FROM ANALYTICS.AGG_HOURLY_ZONE_TRIPS
WHERE hour_bucket >= DATEADD('day', -7, CURRENT_TIMESTAMP())

`ops_payment_split` — 支払い種別の内訳

スキーマ: ANALYTICS

SELECT
    payment_type,
    COUNT(*)              AS trip_count,
    SUM(total_amount_usd) AS total_revenue
FROM ANALYTICS.FACT_TRIPS
WHERE pickup_at >= DATEADD('day', -7, CURRENT_TIMESTAMP())
GROUP BY 1

ダッシュボード 2 のデータセット

`exec_rolling_avg` — 7 日移動平均

スキーマ: ANALYTICS

SELECT
    pickup_at::DATE                                          AS trip_date,
    COUNT(*)                                                 AS daily_trip_count,
    AVG(trip_distance_miles)                                 AS daily_avg_distance,
    AVG(AVG(trip_distance_miles)) OVER (
        ORDER BY pickup_at::DATE
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    )                                                        AS rolling_7d_avg_distance,
    SUM(total_amount_usd)                                    AS daily_revenue,
    SUM(SUM(total_amount_usd)) OVER (
        ORDER BY pickup_at::DATE
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    )                                                        AS rolling_7d_revenue
FROM ANALYTICS.FACT_TRIPS
GROUP BY 1
ORDER BY 1 DESC
LIMIT 365

`exec_top_trips` — borough ごとの高額トリップ上位 10 件

スキーマ: ANALYTICS

Snowflake の QUALIFY を使用しています — 重要な移行課題です。ClickHouse へ書き換える際はサブクエリが必要になります。

SELECT
    trip_id,
    pickup_at,
    pickup_borough,
    total_amount_usd,
    tip_amount_usd,
    trip_distance_miles,
    ROW_NUMBER() OVER (
        PARTITION BY pickup_borough
        ORDER BY total_amount_usd DESC
    ) AS rank_in_borough
FROM ANALYTICS.FACT_TRIPS
WHERE pickup_at::DATE = CURRENT_DATE() - 1
QUALIFY rank_in_borough <= 10
ORDER BY pickup_borough, rank_in_borough

`exec_surge` — サージ料金の影響

スキーマ: ANALYTICS

SELECT
    CASE
        WHEN surge_multiplier >= 2.0 THEN 'High Surge (2x+)'
        WHEN surge_multiplier >= 1.5 THEN 'Medium Surge (1.5–2x)'
        WHEN surge_multiplier > 1.0  THEN 'Low Surge (1–1.5x)'
        ELSE 'No Surge (1x)'
    END                                    AS surge_category,
    COUNT(*)                               AS trip_count,
    ROUND(AVG(total_amount_usd), 2)        AS avg_total_fare,
    ROUND(AVG(fare_amount_usd), 2)         AS avg_base_fare,
    ROUND(AVG(surge_multiplier), 2)        AS avg_surge
FROM ANALYTICS.FACT_TRIPS
WHERE surge_multiplier IS NOT NULL
GROUP BY 1
ORDER BY avg_surge DESC

ダッシュボード 3 のデータセット

`dqa_rating_dist` — ドライバー評価の分布

スキーマ: RAW ← データセット作成時にここを変更してください

VARIANT のコロンパス構文で RAW.TRIPS_RAW を直接クエリします。これは意図的に遅いクエリであり、ClickHouse ベンチマークの対象です。

SELECT
    ROUND(TRIP_METADATA:driver.rating::FLOAT, 1)                           AS rating_bucket,
    COUNT(*)                                                               AS trip_count,
    ROUND(AVG(TOTAL_AMOUNT), 2)                                            AS avg_fare,
    ROUND(AVG(DATEDIFF('minute', PICKUP_DATETIME, DROPOFF_DATETIME)), 1)   AS avg_duration_minutes
FROM RAW.TRIPS_RAW
WHERE TRIP_METADATA:driver IS NOT NULL
  AND TRIP_METADATA:driver.rating IS NOT NULL
GROUP BY 1
ORDER BY 1

`dqa_vehicle` — 車種別の売上

スキーマ: ANALYTICS

SELECT
    vehicle_type,
    COUNT(*)                 AS trip_count,
    SUM(total_amount_usd)    AS total_revenue,
    AVG(total_amount_usd)    AS avg_fare,
    AVG(trip_distance_miles) AS avg_distance
FROM ANALYTICS.FACT_TRIPS
WHERE vehicle_type IS NOT NULL
GROUP BY 1
ORDER BY total_revenue DESC

`dqa_traffic` — 交通レベルとトリップ所要時間

スキーマ: ANALYTICS

SELECT
    traffic_level,
    COUNT(*)                   AS trip_count,
    AVG(duration_minutes)      AS avg_duration_minutes,
    AVG(trip_distance_miles)   AS avg_distance_miles,
    AVG(total_amount_usd)      AS avg_fare
FROM ANALYTICS.FACT_TRIPS
WHERE traffic_level IS NOT NULL
GROUP BY 1
ORDER BY avg_duration_minutes DESC

`dqa_platform` — アプリプラットフォームの傾向

スキーマ: ANALYTICS

SELECT
    pickup_at::DATE       AS trip_date,
    app_platform,
    COUNT(*)              AS trip_count,
    AVG(surge_multiplier) AS avg_surge
FROM ANALYTICS.FACT_TRIPS
WHERE app_platform IS NOT NULL
  AND pickup_at >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY 1, 2
ORDER BY 1 DESC

ステップ 3: ダッシュボードを作成する

10 個のデータセットがすべて揃いました。チャートを作成し、ダッシュボードに追加します。

ダッシュボード 1: Operations Command Center

目的: 直近 7 日間を示すリアルタイムの運用ビュー。Part 2 で受講者が最初に ClickHouse へ向け直すダッシュボードです。

ダッシュボードを作成する:

Dashboards → + Dashboard
タイトル: Operations Command Center
自動リフレッシュ: 15 分ごと (··· → Edit dashboard → Auto-refresh)

チャート 1: 時間別トリップ数 (Line chart)

Chart type: Line Chart
Dataset: ops_hourly_revenue
X-axis: hour_bucket
Metrics: SUM(trip_count)
Series: pickup_borough
Title: Trips per Hour — Last 7 Days

チャート 2: borough 別売上 (Bar chart)

Chart type: Bar Chart
Dataset: ops_hourly_revenue
X-axis: pickup_borough
Metrics: SUM(total_revenue)
Sort: メトリクスの降順
Title: Total Revenue by Borough — Last 7 Days

チャート 3: 支払い種別の内訳 (Pie chart)

Chart type: Pie Chart
Dataset: ops_payment_split
Dimension: payment_type
Metric: SUM(trip_count)
Show labels: オン
Title: Trip Count by Payment Type

チャート 4: 合計トリップ数 (Big Number)

Chart type: Big Number with Trendline
Dataset: ops_hourly_revenue
Metric: SUM(trip_count)
Title: Total Trips (Last 7 Days)

チャート 5: borough パフォーマンスのサマリ (Table)

Chart type: Table
Dataset: ops_hourly_revenue
Columns: pickup_borough, SUM(trip_count), SUM(total_revenue), AVG(avg_tip_rate)
Row limit: 10
Sort: SUM(total_revenue) の降順
Title: Borough Performance Summary

レイアウト:

[ Total Trips — Big Number ]  [ Total Revenue — Big Number (add 2nd)  ]
[ Trips per Hour — Line chart (full width)                            ]
[ Revenue by Borough — Bar ]  [ Payment Type Split — Pie             ]
[ Borough Performance — Table (full width)                            ]

ダッシュボード 2: Executive Weekly Report

目的: 週次ビジネスレビュー向けの戦略的ビュー。ウィンドウ関数と、ClickHouse では書き換えが必要な Snowflake 固有の構文 (QUALIFY) を示します。

ダッシュボードを作成する:

Dashboards → + Dashboard
タイトル: Executive Weekly Report
自動リフレッシュ: 1 時間

チャート 6: 7 日移動平均の売上トレンド (Line chart)

Chart type: Line Chart
Dataset: exec_rolling_avg
X-axis: trip_date
Metrics: MAX(daily_revenue), MAX(rolling_7d_revenue)
Title: Daily Revenue with 7-Day Rolling Average

チャート 7a: 日次トリップ量 (Big Number)

Chart type: Big Number with Trendline
Dataset: exec_rolling_avg
Metric: MAX(daily_trip_count)
Title: Daily Trip Volume (Last Year)

チャート 7b: 7 日移動平均の走行距離 (Line chart)

Chart type: Line Chart
Dataset: exec_rolling_avg
X-axis: trip_date
Metrics: MAX(rolling_7d_avg_distance)
Title: Rolling 7-Day Average Distance (miles)

チャート 8: borough ごとのトリップ上位 10 件 (Table)

Chart type: Table
Dataset: exec_top_trips
Query Mode: RAW RECORDS ← 重要: このデータセットは QUALIFY を使うため、Superset が再集計してはいけません
Columns: pickup_borough, rank_in_borough, total_amount_usd, tip_amount_usd, trip_distance_miles, pickup_at
Sort By: total_amount_usd の降順
Row limit: 60
Title: Top 10 Trips per Borough — Yesterday
メモ: QUALIFY を使用 — Snowflake 固有であり、ClickHouse ではサブクエリとして書き換える必要があります

チャート 9: サージ料金の内訳 (Mixed chart)

Chart type: Mixed Chart ← Bar Chart ではなくこちらを使ってください。Bar Chart は第 2 軸をサポートしていません
Dataset: exec_surge
X-axis: surge_category
Query A — Bar: メトリクス SUM(trip_count)、ラベル Trip Count
Query B — Line: メトリクス MAX(avg_total_fare)、ラベル Avg Total Fare、Y 軸: Right
Sort: SUM(trip_count) の降順
Title: Trip Volume and Average Fare by Surge Category

チャート 10: サージの分布 (Pie chart)

Chart type: Pie Chart
Dataset: exec_surge
Dimension: surge_category
Metric: SUM(trip_count)
Title: Surge Pricing Distribution

レイアウト:

[ Rolling Revenue — Line chart (full width)                              ]
[ Daily Trip Volume — Big Number (50%) ]  [ Avg Distance — Line (50%)   ]
[ Top 10 Trips — Table (60%) ]  [ Surge Distribution — Pie (40%)        ]
[ Surge Breakdown — Bar chart (full width)                               ]

ダッシュボード 3: Driver & Quality Analytics

目的: ドライバーのパフォーマンスとトリップ品質の深掘り。VARIANT アクセスで RAW.TRIPS_RAW を直接クエリするため、意図的に最も遅いダッシュボードになっています。Part 2 の ClickHouse パフォーマンスベンチマークのベースラインとして、ここでのクエリ時間を記録してください。

ダッシュボードを作成する:

Dashboards → + Dashboard
タイトル: Driver & Quality Analytics
自動リフレッシュ: 1 時間

チャート 11: ドライバー評価別のトリップ数 (Bar chart)

Chart type: Bar Chart
Dataset: dqa_rating_dist
X-axis: rating_bucket
Metrics: SUM(trip_count)
Title: Trip Count by Driver Rating
メモ: VARIANT アクセスで RAW.TRIPS_RAW をスキャンします — ClickHouse とのクエリ時間の違いを観察してください

チャート 12: 評価別の平均運賃 (Line chart)

Chart type: Line Chart
Dataset: dqa_rating_dist
X-axis: rating_bucket
Metrics: MAX(avg_fare)
Title: Average Fare by Driver Rating

チャート 13: 車種別の売上 (横向きの棒グラフ)

Chart type: Bar Chart (horizontal)
Dataset: dqa_vehicle
X-axis: vehicle_type
Metrics: SUM(total_revenue), SUM(trip_count) (第 2 軸)
Title: Revenue and Trip Count by Vehicle Type

チャート 14: 交通レベルの影響 (Bar chart)

Chart type: Bar Chart
Dataset: dqa_traffic
X-axis: traffic_level
Metrics: MAX(avg_duration_minutes), MAX(avg_distance_miles) (第 2 軸)
Title: Average Trip Duration and Distance by Traffic Level

チャート 15: アプリプラットフォーム別の日次トリップ数 (Line chart)

Chart type: Line Chart
Dataset: dqa_platform
X-axis: trip_date
Metrics: SUM(trip_count)
Series: app_platform
Title: Daily Trips by App Platform — Last 30 Days

チャート 16: プラットフォーム別のサージ (Table)

Chart type: Table
Dataset: dqa_platform
Columns: app_platform, SUM(trip_count), AVG(avg_surge)
Row limit: 10
Title: Surge by Platform

レイアウト:

[ Trip Count by Rating — Bar ]  [ Avg Fare by Rating — Line            ]
[ Revenue by Vehicle Type — Horizontal bar (full width)                ]
[ Traffic Level Impact — Bar (50%) ]  [ Surge by Platform — Table (50%)]
[ Daily Trips by Platform — Line chart (full width)                    ]

ステップ 4: 検証する

各ダッシュボードを開き、すべてのチャートがエラーなく読み込まれることを確認する
ダッシュボード 3 については、Snowflake UI → Activity → Query History で dqa_rating_dist のクエリ実行時間を確認し、移行のベンチマークとして保存する

ステップ 5: 再利用のためにエクスポートする

ダッシュボードが完成したら、以降の実行で自動インポートできるようにエクスポートします:

各ダッシュボードを開き → ··· → Export (.zip として保存されます)
ファイルを superset/dashboards/ に配置する:
- 01_operations_command_center.zip
- 02_executive_weekly_report.zip
- 03_driver_quality_analytics.zip
./init_superset.sh を再実行する — 以降のセットアップで自動的にインポートされます

重要 — コミット済み ZIP 内のプレースホルダー認証情報。

コミットされている各 *.zip では、databases/*.yaml のデータベース接続情報が伏せられています:
sqlalchemy_uri: snowflake://LAB_USER:XXXXXXXXXX@MYORG-MYACCOUNT/NYC_TAXI_DB/ANALYTICS?role=ANALYST_ROLE&warehouse=ANALYTICS_WH
自動インポート (./init_superset.sh) — そのまま動作します。スクリプトはインポートの前に .env から実際の Snowflake 接続を登録し、各インポートの後に正しい URI を再適用する (init_superset.sh の _update_db を参照) ため、プレースホルダーの値は実際の認証情報で上書きされます。

Superset UI からの手動インポート — インポートされたデータベースはプレースホルダーの URI で作成され、接続できません。インポート後に Settings → Database Connections → Edit でそのエントリを開き、sqlalchemy_uri を実際の Snowflake URI に置き換えてください (例: snowflake://<USER>:<PASSWORD>@<ORG>-<ACCOUNT>/NYC_TAXI_DB/ANALYTICS?role=ANALYST_ROLE&warehouse=ANALYTICS_WH)。

自分のダッシュボードを再エクスポートする場合 — Superset はエクスポート時に、あなたのアカウントロケーターとユーザー名を databases/*.yaml に埋め込みます。再エクスポートした ZIP をコミットする前に、それらの値を MYORG-MYACCOUNT / LAB_USER に戻して伏せ、アカウント識別子が git 履歴に漏れないようにしてください。

このページの内容