Superset di ClickHouse

Membangun ulang dashboard di atas ClickHouse: tujuh dataset yang melatih uniqHLL12, quantileTDigest, sampling, window function, dan dictGet, lalu 18 chart di empat dashboard.

Panduan ini menuntun Anda melalui setup manual lengkap untuk seluruh dataset, chart, dan dashboard ClickHouse di Superset. Ikuti panduan ini untuk memahami apa yang dilakukan setiap visualisasi dan bagaimana visualisasi itu dibangun.

Ingin melompat ke depan? Jalankan skrip impor agar semuanya dibuat secara otomatis:
source .env && source .clickhouse_state
bash superset/add_clickhouse_connection.sh
Skrip membuat koneksi, lalu mengimpor semua 7 dataset, 18 chart, dan 4 dashboard sekaligus. Gunakan panduan ini sebagai referensi atau untuk membangun ulang bagian tertentu.

Penting — kredensial placeholder di superset/dashboards/dashboard_export_*.zip.

ZIP dashboard yang di-commit memiliki databases/*.yaml yang disunting menjadi placeholder:
sqlalchemy_uri: clickhousedb://default:XXXXXXXXXX@your-instance.clickhouse.cloud:8443/analytics?secure=true
Impor otomatis (add_clickhouse_connection.sh) — bekerja apa adanya. Skrip menulis ulang sqlalchemy_uri di dalam ZIP menggunakan ${CLICKHOUSE_HOST} / ${CLICKHOUSE_USER} / ${CLICKHOUSE_PASSWORD} dari .env Anda sebelum mengirimkannya ke endpoint impor, sehingga host placeholder tidak pernah sampai ke Superset.

Impor manual melalui UI Superset — database yang diimpor akan dibuat dengan your-instance.clickhouse.cloud dan tidak akan terhubung. Setelah impor, buka Settings → Database Connections → Edit pada entri tersebut dan ganti sqlalchemy_uri dengan URI ClickHouse Cloud Anda yang sebenarnya (mis. clickhousedb://default:<PASSWORD>@<your-host>.clickhouse.cloud:8443/analytics?secure=true).

Mengekspor ulang dashboard Anda sendiri — Superset menanamkan host ClickHouse Anda yang sebenarnya ke dalam hasil ekspor. Sebelum meng-commit, sunting host tersebut kembali menjadi your-instance.clickhouse.cloud agar identifier layanan Anda tidak bocor ke riwayat git.

Prasyarat: Lapisan analytics sudah terisi (dbt run dilakukan pada Langkah 7.3) dan dictionary sudah ada (scripts/04_create_dictionary.sql dilakukan pada Langkah 7.4).

Langkah 0 — Daftarkan Koneksi ClickHouse

Masuk ke Superset di http://localhost:8088 (admin / admin).
Buka Settings → Database Connections.
Klik + Database.
Pilih ClickHouse Connect dari daftar.
Isi:

Field Nilai
Display Name NYC Taxi — ClickHouse Cloud
Host hostname ClickHouse Cloud Anda (dari .clickhouse_state)
Port 8443
Database analytics
Username default
Password password ClickHouse Cloud Anda
SSL aktif
Klik Test Connection — pastikan banner sukses berwarna hijau muncul.
Klik Connect.

Field	Nilai
Display Name	`NYC Taxi — ClickHouse Cloud`
Host	hostname ClickHouse Cloud Anda (dari `.clickhouse_state`)
Port	`8443`
Database	`analytics`
Username	`default`
Password	password ClickHouse Cloud Anda
SSL	aktif

Bagian 1 — Buat Dataset

Dataset 1 — `fact_trips` (dataset tabel)

Dipakai oleh: Operations Command Center, Executive Weekly Report, Driver Quality Analytics, Capabilities Showcase

Buka Datasets → + Dataset.
Setel Database = NYC Taxi — ClickHouse Cloud, Schema = analytics, Table = fact_trips.
Klik Add Dataset and Create Chart → lalu berpindah halaman — dataset sudah tersimpan.

Dataset 2 — `agg_hourly_zone_trips` (dataset tabel)

Dipakai oleh: Operations Command Center, Executive Weekly Report

Buka Datasets → + Dataset.
Setel Database = NYC Taxi — ClickHouse Cloud, Schema = analytics, Table = agg_hourly_zone_trips.
Klik Save.

Dataset 3 — `CH Approx Unique Trips (uniqHLL12)` (virtual)

Dipakai oleh: Capabilities Showcase — mendemonstrasikan penghitungan perkiraan uniqHLL12() dibandingkan uniq() yang eksak.

Buka Datasets → + Dataset.
Klik Switch to SQL Lab (atau pilih tab Virtual).
Setel Database = NYC Taxi — ClickHouse Cloud.
Tempelkan 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

Beri nama CH Approx Unique Trips (uniqHLL12) lalu klik Save.

Dataset 4 — `CH Cohort Retention` (virtual)

Dipakai oleh: Capabilities Showcase — mendemonstrasikan window function (AVG(...) OVER (...)) untuk revenue bergulir menurut borough.

Buka Datasets → + Dataset → Virtual.
Setel Database = NYC Taxi — ClickHouse Cloud.
Tempelkan 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

Beri nama CH Cohort Retention lalu klik Save.

Dataset 5 — `CH Fare Percentiles (quantileTDigest)` (virtual)

Dipakai oleh: Capabilities Showcase — mendemonstrasikan quantileTDigest() sebagai fungsi persentil native ClickHouse.

Buka Datasets → + Dataset → Virtual.
Setel Database = NYC Taxi — ClickHouse Cloud.
Tempelkan 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

Beri nama CH Fare Percentiles (quantileTDigest) lalu klik Save.

Dataset 6 — `CH Sampling Demo` (virtual)

Dipakai oleh: Capabilities Showcase — mendemonstrasikan sampling rand() % N dibandingkan full scan, dengan membandingkan akurasinya.

Buka Datasets → + Dataset → Virtual.
Setel Database = NYC Taxi — ClickHouse Cloud.
Tempelkan 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

Beri nama CH Sampling Demo lalu klik Save.

Dataset 7 — `CH Zone Dict Lookup` (virtual)

Dipakai oleh: Capabilities Showcase — mendemonstrasikan dictGet() untuk pengayaan dimensi tanpa JOIN.

Membutuhkan: dictionary analytics.taxi_zones_dict dari Langkah 7.4 (scripts/04_create_dictionary.sql).

Buka Datasets → + Dataset → Virtual.
Setel Database = NYC Taxi — ClickHouse Cloud.
Tempelkan 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

Beri nama CH Zone Dict Lookup lalu klik Save.

Dataset ini membaca dari default.trips_raw (tabel raw) alih-alih analytics.fact_trips untuk menunjukkan dictionary bekerja di lapisan sumber tanpa pra-pemrosesan apa pun.

Bagian 2 — Buat Chart

Buat chart melalui Charts → + Chart, pilih dataset, pilih tipe chart, konfigurasikan field-nya, lalu Save dengan nama persis seperti yang tercantum.

Dashboard 1 — CH Operations Command Center

Chart: CH Total Trips Today

Setelan	Nilai
Dataset	`fact_trips`
Chart type	Big Number
Metric	COUNT(`trip_id`)
Time filter	`pickup_at` = `today : now`
Subheader	`trips today`

Simpan sebagai CH Total Trips Today.

Chart: CH Revenue Today

Setelan	Nilai
Dataset	`fact_trips`
Chart type	Big Number
Metric	SUM(`fare_amount_usd`)
Time filter	`pickup_at` = `today : now`
Subheader	`revenue today`

Simpan sebagai CH Revenue Today.

Chart: CH Trip Volume by Hour (24h)

Setelan	Nilai
Dataset	`agg_hourly_zone_trips`
Chart type	Line Chart (ECharts)
X-axis	`hour_bucket`
Metric	SUM(`trips`)
Time grain	1 hour

Simpan sebagai CH Trip Volume by Hour (24h).

Chart: CH Revenue by Zone (Top 10)

Setelan	Nilai
Dataset	`agg_hourly_zone_trips`
Chart type	Bar Chart
Dimensions	`zone_id`
Metric	SUM(`revenue`)
Row limit	10
Sort bars	aktif

Simpan sebagai CH Revenue by Zone (Top 10).

Dashboard 2 — CH Executive Weekly Report

Chart: CH Daily Revenue (7 days)

Setelan	Nilai
Dataset	`fact_trips`
Chart type	Line Chart (ECharts)
X-axis	`pickup_at`
Metric	SUM(`fare_amount_usd`)
Time grain	1 hour

Simpan sebagai CH Daily Revenue (7 days).

Chart: CH Top Zones by Revenue

Setelan	Nilai
Dataset	`agg_hourly_zone_trips`
Chart type	Bar Chart
Dimensions	`zone_id`
Metric	SUM(`revenue`)
Row limit	20
Sort bars	aktif

Simpan sebagai CH Top Zones by Revenue.

Chart: CH Payment Distribution

Setelan	Nilai
Dataset	`fact_trips`
Chart type	Pie Chart
Dimensions	`payment_type`
Metric	COUNT(`trip_id`)

Simpan sebagai CH Payment Distribution.

Chart: CH Avg Fare by Vendor

Setelan	Nilai
Dataset	`fact_trips`
Chart type	Bar Chart
Dimensions	`vendor_name`
Metric	AVG(`fare_amount_usd`)
Row limit	20
Sort bars	aktif

Simpan sebagai CH Avg Fare by Vendor.

Dashboard 3 — CH Driver Quality Analytics

Chart: CH Rating Distribution

Setelan	Nilai
Dataset	`fact_trips`
Chart type	Bar Chart
Dimensions	`driver_rating`
Metric	COUNT(`trip_id`)
Row limit	20
Sort bars	aktif

Simpan sebagai CH Rating Distribution.

Chart: CH High-Rated Driver Revenue

Setelan	Nilai
Dataset	`fact_trips`
Chart type	Big Number
Metric	SUM(`fare_amount_usd`)
Filter	`driver_rating >= 4.5`
Subheader	`revenue from 4.5+ rated drivers`

Simpan sebagai CH High-Rated Driver Revenue.

Chart: CH Avg Fare by Driver Rating

Setelan	Nilai
Dataset	`fact_trips`
Chart type	Bar Chart
Dimensions	`driver_rating`
Metric	AVG(`fare_amount_usd`)
Row limit	20
Sort bars	aktif

Simpan sebagai CH Avg Fare by Driver Rating.

Chart: CH Top Drivers Leaderboard

Setelan	Nilai
Dataset	`fact_trips`
Chart type	Table
Query mode	Raw records
Columns	`vendor_name`, `vehicle_type`, `driver_rating`, `fare_amount_usd`
Row limit	1000

Simpan sebagai CH Top Drivers Leaderboard.

Dashboard 4 — CH Capabilities Showcase

Chart: CH Recent Trips (fact_trips)

Setelan	Nilai
Dataset	`fact_trips`
Chart type	Table
Query mode	Raw records
Columns	`trip_id`, `pickup_at`, `dropoff_at`, `fare_amount_usd`, `pickup_borough`, `vendor_name`
Row limit	1000

Simpan sebagai CH Recent Trips (fact_trips).

Chart: CH Fare Percentiles (quantileTDigest)

Setelan	Nilai
Dataset	`CH Fare Percentiles (quantileTDigest)`
Chart type	Table
Query mode	Raw records
Columns	`vendor_name`, `p50_fare`, `p95_fare`, `p99_fare`, `trip_count`

Simpan sebagai CH Fare Percentiles (quantileTDigest).

Chart: CH Approx vs Exact Unique Trips (uniqHLL12)

Setelan	Nilai
Dataset	`CH Approx Unique Trips (uniqHLL12)`
Chart type	Table
Query mode	Raw records
Columns	`day`, `exact_unique_trips`, `approx_unique_trips`, `pct_error`

Simpan sebagai CH Approx vs Exact Unique Trips (uniqHLL12).

Chart: CH Sampling Accuracy Demo (SAMPLE 0.1)

Setelan	Nilai
Dataset	`CH Sampling Demo`
Chart type	Table
Query mode	Raw records
Columns	`method`, `trip_count`, `avg_fare`

Simpan sebagai CH Sampling Accuracy Demo (SAMPLE 0.1).

Chart: CH Zone Lookup via Dictionary (dictGet)

Setelan	Nilai
Dataset	`CH Zone Dict Lookup`
Chart type	Table
Query mode	Raw records
Columns	`borough`, `zone`, `trips`, `avg_fare`

Simpan sebagai CH Zone Lookup via Dictionary (dictGet).

Chart: CH Weekly Revenue Trend (window functions)

Setelan	Nilai
Dataset	`CH Cohort Retention`
Chart type	Table
Query mode	Raw records
Columns	`week`, `pickup_borough`, `trips`, `revenue`, `rolling_4wk_avg_revenue`

Simpan sebagai CH Weekly Revenue Trend (window functions).

Bagian 3 — Rakit Dashboard

Untuk setiap dashboard:

Buka Dashboards → + Dashboard.
Masukkan judulnya.
Klik Save lalu Edit Dashboard.
Dari panel kanan, tarik setiap chart ke kanvas.
Klik Save setelah selesai.

CH — Operations Command Center

Judul: CH — Operations Command Center

Baris	Chart
Baris 1	CH Total Trips Today · CH Revenue Today
Baris 2	CH Trip Volume by Hour (24h) · CH Revenue by Zone (Top 10)

CH — Executive Weekly Report

Judul: CH — Executive Weekly Report

Baris	Chart
Baris 1	CH Daily Revenue (7 days) · CH Top Zones by Revenue · CH Payment Distribution · CH Avg Fare by Vendor

CH — Driver Quality Analytics

Judul: CH — Driver Quality Analytics

Baris	Chart
Baris 1	CH Rating Distribution · CH High-Rated Driver Revenue
Baris 2	CH Avg Fare by Driver Rating · CH Top Drivers Leaderboard

CH — Capabilities Showcase

Judul: CH — Capabilities Showcase

Baris	Chart
Baris 1	CH Recent Trips (fact_trips) · CH Fare Percentiles (quantileTDigest)
Baris 2	CH Approx vs Exact Unique Trips (uniqHLL12) · CH Sampling Accuracy Demo (SAMPLE 0.1)
Baris 3	CH Zone Lookup via Dictionary (dictGet) · CH Weekly Revenue Trend (window functions)

Verifikasi

Setelah menyelesaikan Bagian 3, buka http://localhost:8088. Di bawah Dashboards, Anda seharusnya melihat 7 total — 3 dashboard Snowflake (dari setup Bagian 1) dan 4 dengan awalan CH —.

Penanganan Masalah

fact_trips atau agg_hourly_zone_trips tidak ditemukan Jalankan dbt run terlebih dahulu (Langkah 7.3).

dictGet mengembalikan string kosong Dictionary analytics.taxi_zones_dict belum dibuat. Jalankan scripts/04_create_dictionary.sql (Langkah 7.4).

Dataset virtual tidak mengembalikan baris apa pun Jendela 30 hari / 7 hari pada beberapa query memerlukan data terbaru. Jika data migrasi Anda semuanya historis, ganti filter waktu dengan tanggal tetap:

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

Error 403 dari skrip impor Cookie sesi Superset Anda telah kedaluwarsa. Keluar lalu masuk kembali, kemudian jalankan ulang.

Di halaman ini