【Databricks】Dashboardsでピボットテーブルを触ってみる

はじめに

Streamlit in Snowflake + dbt Cloudでデータの可視化をしてみる！でピボットテーブルもどきをStreamlitで作成しましたが、
DatabricksではDashboardsにデフォルトで存在したため触ってみました！

GUIでサクサク作れるのと、
5000万(50M)件のダミーデータに対しても反応が早かったので、
気になる方は是非試してみてください。

事前準備

• Databricks
  • SQL Warehouse
• ダミーデータ
  • (任意) コード作成用AI

ダミーデータ作成

ここでは5000万件のダミーデータをコード作成用AIの力を借りてSQLでサクッと作ります。

このAIだと6割ぐらいのコードが生成されるので、
残り4割は修正 or チャットを続けながら作成してください。

テーブルの条件は下記のとおりです。

カラム名	制約	補足
顧客ID	not null・unique
購入ID	not null・unique
購入日	not null	2023年の1月1日から1年の間の日付をランダムに選択
商品名	not null	「商品A」から「商品E」までの5種類からランダムに選択
SKU番号	not null	商品Aは「10001」、商品Bは「10002」といったように、商品名と1対1で対応
数量	not null	1～5個の間でランダムに選択
単価	not null	商品Aは1000円、商品Bは1500円といったふうに、商品ごとに固定金額を設定
合計金額	not null	数量 x 単価の金額
支払い方法	not null	「クレジットカード」「現金」「電子決済」の3つの方法からランダムに選択
購入場所	not null	「オンライン」「店舗A」「店舗B」の3つからランダムに選択
性別		「男性」「女性」「その他」「NULL」のいずれかが割り当てられ、NULLになる確率は25%
メールアドレス		60%の確率でNULLになり、それ以外は「customer○○@example.com」のように番号が付いたアドレスがランダムに割り当てられる
年代		10代、20代、…60代のように、10年ごとの年代が割り当てられます。30%の確率でNULL

※支払い方法と購入場所の組み合わせで「オンライン」・「現金」という方法がありますが、これはコンビニ支払いだな〜と読み替えてください。

SQLは下記になります。

CREATE OR REPLACE TABLE purchase_history_5m AS
SELECT
    -- 顧客IDを1000人分の範囲でランダムに設定
    CAST(ROUND(RAND() * 1000) AS INT) AS `顧客ID`,
    
    -- 購入IDを一意に設定
    MONOTONICALLY_INCREASING_ID() AS `購入ID`,
    
    -- 購入日は過去1年のランダムな日付に設定
    DATE_ADD(TO_DATE('2023-01-01'), CAST((RAND() * 365) AS INT)) AS `購入日`,
    
    -- 商品名をランダムに設定し、NULLが発生しないようELSEを追加
    CASE CAST(RAND() * 5 AS INT)
        WHEN 0 THEN '商品A'
        WHEN 1 THEN '商品B'
        WHEN 2 THEN '商品C'
        WHEN 3 THEN '商品D'
        WHEN 4 THEN '商品E'
        ELSE '商品A' -- デフォルトで商品Aを設定
    END AS `商品名`,
    
    -- SKU番号を商品名と1:1の関係で設定
    CASE
        WHEN `商品名` = '商品A' THEN 10001
        WHEN `商品名` = '商品B' THEN 10002
        WHEN `商品名` = '商品C' THEN 10003
        WHEN `商品名` = '商品D' THEN 10004
        WHEN `商品名` = '商品E' THEN 10005
        ELSE 10001 -- デフォルトで商品AのSKU番号を設定
    END AS `SKU番号`,
    
    -- 数量を1〜5の範囲でランダムに設定
    CAST(ROUND(RAND() * 4 + 1) AS INT) AS `数量`,
    
    -- 商品ごとに単価を固定設定
    CASE
        WHEN `商品名` = '商品A' THEN 1000
        WHEN `商品名` = '商品B' THEN 1500
        WHEN `商品名` = '商品C' THEN 2000
        WHEN `商品名` = '商品D' THEN 2500
        WHEN `商品名` = '商品E' THEN 3000
        ELSE 1000 -- デフォルトで商品Aの単価を設定
    END AS `単価`,
    
    -- 合計金額を数量×単価で計算
    `数量` * `単価` AS `合計金額`,
    
    -- 支払い方法は3種類からランダムに設定
    CASE CAST(RAND() * 3 AS INT)
        WHEN 0 THEN 'クレジットカード'
        WHEN 1 THEN '現金'
        WHEN 2 THEN '電子決済'
        ELSE 'クレジットカード' -- デフォルトでクレジットカードを設定
    END AS `支払い方法`,
    
    -- 購入場所を3種類からランダムに設定
    CASE CAST(RAND() * 3 AS INT)
        WHEN 0 THEN 'オンライン'
        WHEN 1 THEN '店舗A'
        WHEN 2 THEN '店舗B'
        ELSE 'オンライン' -- デフォルトでオンラインを設定
    END AS `購入場所`,
    
    -- 性別は「男性」「女性」「その他」「NULL」のいずれかをランダムに設定（25%の確率でNULL）
    CASE
        WHEN RAND() < 0.25 THEN NULL
        WHEN RAND() < 0.33 THEN '男性'
        WHEN RAND() < 0.5 THEN '女性'
        ELSE 'その他'
    END AS `性別`,
    
    -- メールアドレスは60%の確率でNULL、それ以外はランダムに生成
    CASE
        WHEN RAND() < 0.6 THEN NULL
        ELSE CONCAT('customer', CAST(ROUND(RAND() * 100000) AS INT), '@example.com')
    END AS `メールアドレス`,
    
    -- 年代は10代〜60代の範囲でランダムに設定（30%の確率でNULL）
    CASE
        WHEN RAND() < 0.3 THEN NULL
        ELSE CONCAT(CAST(ROUND(RAND() * 5 + 1) * 10 AS INT), '代')
    END AS `年代`

-- ダミーデータを5000万件行生成
FROM RANGE(50000000)

DatabricksのSQL→SQL Editerから下記を実行して確認しましょう！

SELECT * FROM <catalog_name>.<schema>.purchase_history_5m

ピボットテーブル作成

SQL→Dashboards→create dashboardから新しいダッシュボードを作成します。

ここからはCanvasとDataをそれぞれ作成していきます。

Canvas

Dashboardで何が見たいか先に決めます。
今回は下記3点が見たいのでCanvasで作成していきます。

• ピボットテーブル：一番みたい
• テーブルの件数：あると裏側で走るクエリの実行速度がなんとなくわかる
  • 件数が多いと遅くなる程度ですがあると便利
• テーブルのカラム情報：ピボットテーブルのRows・Columns・Valueからも判断できますが、常に見えている方が楽なので追加

Add a visualizationから3つのVisualizationを作成してCanvasにおいていきます。

各Visualization内で種類を選べるため

• ピボットテーブル：Pivot
• テーブルの件数：Counter
• テーブルのカラム情報：Table
  を設定しておきます。

配置は下記のようにしています。
(ここは自由ですので皆さんの好きなように置いてください)

Data

ここからは実際のデータを入れていきます。
Dataタブから2種類のDatasetsをSQLで作成していきます。

• purchase_history_5m：ピボットテーブル・件数で使用
• purchase_history_5m_col：カラム情報で使用

purchase_history_5m
(実はデータの確認クエリと一緒)

SELECT * FROM <catalog_name>.<schema>.purchase_history_5m

purchase_history_5m_col
(information_schemaから取得)

SELECT
  column_name,
  data_type
FROM
  <catalog_name>.information_schema.columns
WHERE
  table_name = 'purchase_history_5m'

最後に各VisualizationのDatasetに作成したDataを連携させればOK。
(表示形式を少しいじっていますが次章で紹介します。)

ピボットテーブルの表示形式

そのままのピボットテーブルだと分析しづらいため、Rows・ColumnsやValuesのStyleやFormat等について紹介します。

Rows・Columns

日付や数値データに対して ValueやFormatを決めることができます。
特に日付では、DAILY~YEARLYまでの粒度で選べるのでとても嬉しいですね！

YEARLY

WEEKLY

数値データはValuesとも被りますが

• データの表示で通貨表示か割合表示か
• 数字の略し方はどうするか (万・億とか、0EN乗表示ですね)
• 小数点以下の数字は何桁まで表示するか (有効桁数)
  の設定ができます。

Values

Format・Style・Valueの3種類あります。
それぞれに付いて触ってみた感想や良さそうな設定を共有していきます。

ピボットテーブルでStyleやFormatを設定してもカラムを削除すると設定も全て飛んでしまうのでそこは惜しいなと感じますね。(特に円やCompact表示が楽だったため永続してほしい)

Format

Valueは先程の数値データと同じくデータ表示・数字の略し方・有効桁数が設定できます。
私は下記の設定が好きなので共有しておきますね。

データ表示
円表示

数字の略し方・有効桁数
Compact (万・億の表示)
1桁

Style

Styleに関してはColor Scaleや条件に合うセルだけ着色みたいな機能があります。
(ほぼ、スプシやエクセルに近い感覚です。)
ただ、ピボットテーブルで弄りながらカラースケールまで変わると見づらくなるため、
ある程度見たい形が決まってから表示したほうが良いと思います。

参考例を載せておきます。

いい感じに色がついたカラーマップ(Red Yellow Green)

カラーマップのリバース表示

デイリー/性別・年代別で売上のカラーマップを見てみる

色の扱いは難しすぎますね〜
そんな使い方できるだ、、程度に見てください。

Value

集計方法が選べます。
今回はSUMのみ選んでいますが、用途に応じて COUNTやAVGを眺めるのも良さそう。
(複雑なデータを表示したい場合は別のグラフが良いと思います。)

さいごに

今回はDatabricksのDashboardsでピボットテーブルを触ってみました！
また、軽く触りたいけど、データが・・・
という方に向けて簡単なダミーデータを作成するクエリも紹介したため、
良ければ試してもらえると嬉しいです。

ピボットテーブルのRows・Columns・Valuesからカラムを消してしまうと設定も消えてしまうのがちょっと惜しいですが、
それ以外はここで作ったピボットテーブルもどきと比べて速度・使いやすさで段違いでした。

なので、次はSnowflakeのMARTにあるデータをDatabricksのcatalogに転送して、
Dashboardsのピボットテーブルで見れたらいいな〜と考えてます！

以上hamaでした〜