ClickHouse 上の Superset

このガイドでは、Superset における ClickHouse のデータセット、チャート、ダッシュボードの完全な手動セットアップを説明します。各ビジュアライゼーションが何をしていて、どう作られているかを理解するために、この手順に従ってください。

先に進みたい場合は? インポートスクリプトを実行すれば、すべてが自動的に作成されます:

source .env && source .clickhouse_state
bash superset/add_clickhouse_connection.sh

このスクリプトは接続を作成し、7 つのデータセット、18 のチャート、4 つのダッシュボードを一気にインポートします。このガイドはリファレンスとして、あるいは個々の要素を作り直すために使ってください。

重要 — superset/dashboards/dashboard_export_*.zip 内のプレースホルダー認証情報。

コミットされているダッシュボード ZIP では、databases/*.yaml がプレースホルダーに伏せられています:

sqlalchemy_uri: clickhousedb://default:XXXXXXXXXX@your-instance.clickhouse.cloud:8443/analytics?secure=true

• 自動インポート (add_clickhouse_connection.sh) — そのまま動作します。スクリプトはインポートエンドポイントに POST する前に、.env の ${CLICKHOUSE_HOST} / ${CLICKHOUSE_USER} / ${CLICKHOUSE_PASSWORD} を使って ZIP 内の sqlalchemy_uri を書き換えるため、プレースホルダーのホストが Superset に届くことはありません。
• Superset UI からの手動インポート — インポートされたデータベースは your-instance.clickhouse.cloud で作成され、接続できません。インポート後に Settings → Database Connections → Edit でそのエントリを開き、sqlalchemy_uri を実際の ClickHouse Cloud の URI に置き換えてください (例: clickhousedb://default:<PASSWORD>@<your-host>.clickhouse.cloud:8443/analytics?secure=true)。
• 自分のダッシュボードを再エクスポートする場合 — Superset は実際の ClickHouse ホストをエクスポートに埋め込みます。コミットする前にホストを your-instance.clickhouse.cloud に戻して伏せ、サービス識別子が git 履歴に漏れないようにしてください。

前提条件: analytics レイヤーにデータが入っていること (ステップ 7.3 で dbt run 済み)、およびディクショナリが存在すること (ステップ 7.4 で scripts/04_create_dictionary.sql 済み)。

ステップ 0 — ClickHouse 接続を登録する

1. http://localhost:8088 で Superset にログインします (admin / admin)。

2. Settings → Database Connections に移動します。

3. + Database をクリックします。

4. 一覧から ClickHouse Connect を選択します。

5. 次を入力します:

   項目	値
   Display Name	NYC Taxi — ClickHouse Cloud
   Host	ClickHouse Cloud のホスト名 (.clickhouse_state から)
   Port	8443
   Database	analytics
   Username	default
   Password	ClickHouse Cloud のパスワード
   SSL	有効

6. Test Connection をクリック — 緑色の成功バナーを確認します。

7. Connect をクリックします。

パート 1 — データセットを作成する

データセット 1 — fact_trips (テーブルデータセット)

使用先: Operations Command Center、Executive Weekly Report、Driver Quality Analytics、Capabilities Showcase

1. Datasets → + Dataset に移動します。
2. Database = NYC Taxi — ClickHouse Cloud、Schema = analytics、Table = fact_trips を設定します。
3. Add Dataset and Create Chart をクリック → その後ページを離れます — データセットは保存されています。

データセット 2 — agg_hourly_zone_trips (テーブルデータセット)

使用先: Operations Command Center、Executive Weekly Report

1. Datasets → + Dataset に移動します。
2. Database = NYC Taxi — ClickHouse Cloud、Schema = analytics、Table = agg_hourly_zone_trips を設定します。
3. Save をクリックします。

データセット 3 — CH Approx Unique Trips (uniqHLL12) (仮想)

使用先: Capabilities Showcase — 厳密な uniq() に対する uniqHLL12() の近似カウントを示します。

1. Datasets → + Dataset に移動します。
2. Switch to SQL Lab をクリックします (または Virtual タブを選択します)。
3. Database = NYC Taxi — ClickHouse Cloud を設定します。
4. SQL を貼り付けます:

SELECT
  toDate(pickup_at)                                                     AS day,
  uniq(trip_id)                                                         AS exact_unique_trips,
  uniqHLL12(trip_id)                                                    AS approx_unique_trips,
  round(
    abs(uniq(trip_id) - uniqHLL12(trip_id)) / uniq(trip_id) * 100, 2
  )                                                                     AS pct_error
FROM analytics.fact_trips FINAL
WHERE pickup_at >= today() - INTERVAL 30 DAY
GROUP BY day
ORDER BY day

5. CH Approx Unique Trips (uniqHLL12) という名前を付けて Save をクリックします。

データセット 4 — CH Cohort Retention (仮想)

使用先: Capabilities Showcase — borough 別の移動平均売上に対するウィンドウ関数 (AVG(...) OVER (...)) を示します。

1. Datasets → + Dataset → Virtual に移動します。
2. Database = NYC Taxi — ClickHouse Cloud を設定します。
3. SQL を貼り付けます:

SELECT
  week,
  pickup_borough,
  trips,
  revenue,
  round(avg(revenue) OVER (
    PARTITION BY pickup_borough
    ORDER BY week
    ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
  ), 2) AS rolling_4wk_avg_revenue
FROM (
  SELECT
    toStartOfWeek(pickup_at)       AS week,
    pickup_borough,
    count()                        AS trips,
    round(sum(fare_amount_usd), 2) AS revenue
  FROM analytics.fact_trips FINAL
  GROUP BY week, pickup_borough
)
ORDER BY week DESC, revenue DESC
LIMIT 100

4. CH Cohort Retention という名前を付けて Save をクリックします。

データセット 5 — CH Fare Percentiles (quantileTDigest) (仮想)

使用先: Capabilities Showcase — ClickHouse ネイティブのパーセンタイル関数として quantileTDigest() を示します。

1. Datasets → + Dataset → Virtual に移動します。
2. Database = NYC Taxi — ClickHouse Cloud を設定します。
3. SQL を貼り付けます:

SELECT
  vendor_name,
  quantileTDigest(0.5)(fare_amount_usd)  AS p50_fare,
  quantileTDigest(0.95)(fare_amount_usd) AS p95_fare,
  quantileTDigest(0.99)(fare_amount_usd) AS p99_fare,
  count()                                AS trip_count
FROM analytics.fact_trips FINAL
GROUP BY vendor_name
ORDER BY vendor_name

4. CH Fare Percentiles (quantileTDigest) という名前を付けて Save をクリックします。

データセット 6 — CH Sampling Demo (仮想)

使用先: Capabilities Showcase — フルスキャンに対する rand() % N サンプリングを示し、精度を比較します。

1. Datasets → + Dataset → Virtual に移動します。
2. Database = NYC Taxi — ClickHouse Cloud を設定します。
3. SQL を貼り付けます:

SELECT
  'Full Scan'              AS method,
  count()                  AS trip_count,
  round(avg(fare_amount_usd), 4) AS avg_fare
FROM analytics.fact_trips FINAL
UNION ALL
SELECT
  '~10% (rand() % 10 = 0)' AS method,
  count() * 10              AS trip_count_est,
  round(avg(fare_amount_usd), 4) AS avg_fare
FROM analytics.fact_trips FINAL
WHERE rand() % 10 = 0

4. CH Sampling Demo という名前を付けて Save をクリックします。

データセット 7 — CH Zone Dict Lookup (仮想)

使用先: Capabilities Showcase — JOIN なしでディメンションを付加する dictGet() を示します。

必要な前提: ステップ 7.4 (scripts/04_create_dictionary.sql) で作成したディクショナリ analytics.taxi_zones_dict。

1. Datasets → + Dataset → Virtual に移動します。
2. Database = NYC Taxi — ClickHouse Cloud を設定します。
3. SQL を貼り付けます:

SELECT
  dictGet('analytics.taxi_zones_dict', 'borough', toUInt16(pickup_location_id)) AS borough,
  dictGet('analytics.taxi_zones_dict', 'zone',    toUInt16(pickup_location_id)) AS zone,
  count()                                                                        AS trips,
  round(avg(fare_amount_usd), 2)                                                 AS avg_fare
FROM default.trips_raw
WHERE pickup_at >= today() - INTERVAL 7 DAY
GROUP BY borough, zone
ORDER BY trips DESC

4. CH Zone Dict Lookup という名前を付けて Save をクリックします。

このデータセットは analytics.fact_trips ではなく default.trips_raw (生テーブル) から読み取り、前処理なしでソースレイヤーでディクショナリが機能することを示します。

パート 2 — チャートを作成する

チャートは Charts → + Chart から作成し、データセットを選び、チャートタイプを選び、フィールドを設定してから、記載された名前どおりに Save します。

ダッシュボード 1 — CH Operations Command Center

チャート: CH Total Trips Today

CH Total Trips Today として保存します。

チャート: CH Revenue Today

CH Revenue Today として保存します。

チャート: CH Trip Volume by Hour (24h)

CH Trip Volume by Hour (24h) として保存します。

チャート: CH Revenue by Zone (Top 10)

CH Revenue by Zone (Top 10) として保存します。

ダッシュボード 2 — CH Executive Weekly Report

チャート: CH Daily Revenue (7 days)

CH Daily Revenue (7 days) として保存します。

チャート: CH Top Zones by Revenue

CH Top Zones by Revenue として保存します。

チャート: CH Payment Distribution

CH Payment Distribution として保存します。

チャート: CH Avg Fare by Vendor

CH Avg Fare by Vendor として保存します。

ダッシュボード 3 — CH Driver Quality Analytics

チャート: CH Rating Distribution

CH Rating Distribution として保存します。

チャート: CH High-Rated Driver Revenue

CH High-Rated Driver Revenue として保存します。

チャート: CH Avg Fare by Driver Rating

CH Avg Fare by Driver Rating として保存します。

チャート: CH Top Drivers Leaderboard

CH Top Drivers Leaderboard として保存します。

ダッシュボード 4 — CH Capabilities Showcase

チャート: CH Recent Trips (fact_trips)

CH Recent Trips (fact_trips) として保存します。

チャート: CH Fare Percentiles (quantileTDigest)

CH Fare Percentiles (quantileTDigest) として保存します。

チャート: CH Approx vs Exact Unique Trips (uniqHLL12)

CH Approx vs Exact Unique Trips (uniqHLL12) として保存します。

チャート: CH Sampling Accuracy Demo (SAMPLE 0.1)

CH Sampling Accuracy Demo (SAMPLE 0.1) として保存します。

チャート: CH Zone Lookup via Dictionary (dictGet)

CH Zone Lookup via Dictionary (dictGet) として保存します。

チャート: CH Weekly Revenue Trend (window functions)

CH Weekly Revenue Trend (window functions) として保存します。

パート 3 — ダッシュボードを組み立てる

各ダッシュボードについて:

1. Dashboards → + Dashboard に移動します。
2. タイトルを入力します。
3. Save をクリックし、続いて Edit Dashboard をクリックします。
4. 右側のパネルから各チャートをキャンバスにドラッグします。
5. 完了したら Save をクリックします。

CH — Operations Command Center

タイトル: CH — Operations Command Center

CH — Executive Weekly Report

タイトル: CH — Executive Weekly Report

CH — Driver Quality Analytics

タイトル: CH — Driver Quality Analytics

CH — Capabilities Showcase

タイトル: CH — Capabilities Showcase

検証

パート 3 を終えたら、http://localhost:8088 を開きます。Dashboards に合計 7 件 — Snowflake のダッシュボード 3 件 (パート 1 のセットアップで作成) と、CH — を接頭辞に持つ 4 件 — が表示されるはずです。

トラブルシューティング

fact_trips または agg_hourly_zone_trips が見つからない 先に dbt run を実行してください (ステップ 7.3)。

dictGet が空文字列を返す analytics.taxi_zones_dict ディクショナリが作成されていません。scripts/04_create_dictionary.sql を実行してください (ステップ 7.4)。

仮想データセットが行を返さない 一部のクエリの 30 日／7 日ウィンドウは最近のデータを必要とします。移行データがすべて過去のものである場合は、時間フィルタを固定日付に置き換えてください:

-- Replace: WHERE pickup_at >= today() - INTERVAL 30 DAY
-- With:    WHERE pickup_at >= '2023-01-01'

インポートスクリプトが 403 エラーを返す Superset のセッションクッキーが期限切れです。ログアウトしてログインし直し、再実行してください。