Databricks SQLでは、samplesというカタログの配下にサンプルデータがあり、これを使ってクイックにDatabricks SQLの機能を試すことができます。今回はこの中にあるニューヨークにおけるタクシーの乗降記録データを使ってダッシュボードを作る流れを体験いただきます。

アナリストによるDatabricks SQLの一般的な使い方は以下のようなものとなります。通常ステップ2からステップ5は試行錯誤を伴う繰り返しの作業となります。

データをアップロードする。(管理者が準備するケースもあります)
クエリーを実行するなどしてデータを理解する。
目的を踏まえて解析(抽出、集計、並び替えなど)の切り口を考える。
切り口に応じたクエリーを記述して実行する。
クエリー実行結果をどのように可視化するのかを考えて可視化を行う。(ビジュアライゼーションの作成)
可視化を組み合わせてダッシュボードを作成する。
ダッシュボードを公開する。

注意
画面はAzure Databricksですが、他のクラウドのDatabricksでも使用できます。

Databricks SQLへのアクセス

SQLウェアハウスの作成

クエリーの実行

Databricks SQLではデータベースに問い合わせを行うクエリーを作成し、ビジュアライゼーションを作成し、ダッシュボードを作成する流れとなります。

データの説明

使用するデータ：ニューヨークのタクシーの乗降記録のデータ

Databricks SQLでテーブルを参照するには、以下のようにカタログ、データベース、テーブルの3レベルの名前空間でテーブルを指定する必要があります。

samples.nyctaxi.trips

列名	データ型	説明
tpep_pickup_datetime	TIMESTAMP	乗車時刻
tpep_dropoff_datetime	TIMESTAMP	降車時刻
trip_distance	DOUBLE	移動距離(マイル)
fare_amount	DOUBLE	料金(ドル)
pickup_zip	INT	乗車地点の郵便番号
dropoff_zip	INT	降車地点の郵便番号

ダッシュボードの作成

今回は以下の流れでダッシュボードを作成します。ご自身でカスタマイズしてみてください。

レコード数をカウントするクエリーの作成、ビジュアライゼーションの作成
乗車時間の分布を計算するクエリーの作成、ビジュアライゼーションの作成
降車時間の分布を計算するクエリーの作成、ビジュアライゼーションの作成
移動距離と料金の関係を確認するためのクエリーの作成、ビジュアライゼーションの作成
ダッシュボードの作成
フィルターの追加

SQLエディタの画面構成

以降の作業で使用するSQLエディタの画面構成を以下に示します。クエリーを開くとSQLエディタが表示されます。サイドメニューのSQLエディタからでもアクセスできます。

レコード数のカウント

テーブルtripsの各行が一つのタクシーの移動を表しているので行数をカウントすることで、テーブルに含まれている移動の数を得ることができます。

SQLエディタに以下のSQLを記述します。

SQL

SELECT
count(*) as total_trips
FROM samples.nyctaxi.trips

すべてを実行をクリックすることでSQLが実行されます。
画面右下に結果が表示されます。他のクエリーを実行する際には同じ手順を踏みます。
次に、この結果を用いてダッシュボードの部品となるビジュアライゼーションを作成します。
Tableの右にある + をクリックします。メニューが展開されるのでVisualizationを選択します。
ビジュアライゼーションエディタが開きます。
Visualization typeから可視化のタイプを選択します。ここではCounterを選択します。
選択するビジュアライゼーションによって設定項目は異なります。ここでは、Counter labelに移動総数と入力します。
また、ビジュアライゼーションを識別しやすくするためにCounter 1と表示されている箇所にカーソルを移動して移動総数カウンターと名前を入力します。
保存をクリックするとビジュアライゼーションエディタが閉じます。
Tableの隣にビジュアライゼーション移動総数カウンターが追加されました。別のクエリーを作成した場合にも同様にビジュアライゼーションを追加することができます。
最後にクエリーに名前をつけて保存します。New Queryとなっている箇所にカーソルを移動して移動総数と入力します。
保存をクリックします。

乗車時間の分布

タブの隣の + をクリックして新規クエリを作成を選択します。

クエリー名を乗車時間の分布、以下のSQLでクエリーを作成します。

SQL

SELECT
    CASE 
        WHEN T.pickup_hour = 0 THEN '00:00'
        WHEN T.pickup_hour = 1 THEN '01:00'
        WHEN T.pickup_hour = 2 THEN '02:00'
        WHEN T.pickup_hour = 3 THEN '03:00'
        WHEN T.pickup_hour = 4 THEN '04:00'
        WHEN T.pickup_hour = 5 THEN '05:00'
        WHEN T.pickup_hour = 6 THEN '06:00'
        WHEN T.pickup_hour = 7 THEN '07:00'
        WHEN T.pickup_hour = 8 THEN '08:00'
        WHEN T.pickup_hour = 9 THEN '09:00'
        WHEN T.pickup_hour = 10 THEN '10:00'
        WHEN T.pickup_hour = 11 THEN '11:00'
        WHEN T.pickup_hour = 12 THEN '12:00'
        WHEN T.pickup_hour = 13 THEN '13:00'
        WHEN T.pickup_hour = 14 THEN '14:00'
        WHEN T.pickup_hour = 15 THEN '15:00'
        WHEN T.pickup_hour = 16 THEN '16:00'
        WHEN T.pickup_hour = 17 THEN '17:00'
        WHEN T.pickup_hour = 18 THEN '18:00'
        WHEN T.pickup_hour = 19 THEN '19:00'
        WHEN T.pickup_hour = 20 THEN '20:00'
        WHEN T.pickup_hour = 21 THEN '21:00'
        WHEN T.pickup_hour = 22 THEN '22:00'
        WHEN T.pickup_hour = 23 THEN '23:00'
    ELSE 'N/A'
    END AS `乗車時刻`,
    T.num AS `乗車回数`
FROM
(SELECT
  hour(tpep_pickup_datetime) AS pickup_hour,
  COUNT(*) AS num
FROM
  `samples`.`nyctaxi`.`trips`
GROUP BY
  1) T

以下の設定でビジュアライゼーションを作成します。
- ビジュアライゼーションの名前: 乗車時間の棒グラフ
- Visualization type:Bar
- Horizontal chartのチェックを解除
- X column: 乗車時刻
- Y column: 乗車回数
- Y軸のラベル: 乗車回数合計

降車時間の分布

タブの隣の + をクリックして新規クエリを作成を選択します。

クエリー名を降車時間の分布、以下のSQLでクエリーを作成します。

SQL

SELECT
    CASE 
        WHEN T.dropoff_hour = 0 THEN '00:00'
        WHEN T.dropoff_hour = 1 THEN '01:00'
        WHEN T.dropoff_hour = 2 THEN '02:00'
        WHEN T.dropoff_hour = 3 THEN '03:00'
        WHEN T.dropoff_hour = 4 THEN '04:00'
        WHEN T.dropoff_hour = 5 THEN '05:00'
        WHEN T.dropoff_hour = 6 THEN '06:00'
        WHEN T.dropoff_hour = 7 THEN '07:00'
        WHEN T.dropoff_hour = 8 THEN '08:00'
        WHEN T.dropoff_hour = 9 THEN '09:00'
        WHEN T.dropoff_hour = 10 THEN '10:00'
        WHEN T.dropoff_hour = 11 THEN '11:00'
        WHEN T.dropoff_hour = 12 THEN '12:00'
        WHEN T.dropoff_hour = 13 THEN '13:00'
        WHEN T.dropoff_hour = 14 THEN '14:00'
        WHEN T.dropoff_hour = 15 THEN '15:00'
        WHEN T.dropoff_hour = 16 THEN '16:00'
        WHEN T.dropoff_hour = 17 THEN '17:00'
        WHEN T.dropoff_hour = 18 THEN '18:00'
        WHEN T.dropoff_hour = 19 THEN '19:00'
        WHEN T.dropoff_hour = 20 THEN '20:00'
        WHEN T.dropoff_hour = 21 THEN '21:00'
        WHEN T.dropoff_hour = 22 THEN '22:00'
        WHEN T.dropoff_hour = 23 THEN '23:00'
    ELSE 'N/A'
    END AS `降車時間`,
    T.num AS `降車回数`
FROM
(SELECT
  hour(tpep_dropoff_datetime) AS dropoff_hour,
  COUNT(*) AS num
FROM
  `samples`.`nyctaxi`.`trips`
GROUP BY
  1) T

以下の設定でビジュアライゼーションを作成します。
- ビジュアライゼーションの名前: 降車時間の棒グラフ
- Visualization type:Bar
- Horizontal chartのチェックを解除
- X column: 降車時刻
- Y column: 降車回数
- Y軸のラベル: 降車回数合計

移動距離と料金の関係

タブの隣の + をクリックして新規クエリを作成を選択します。

クエリー名を移動距離と料金の関係、以下のSQLでクエリーを作成します。

SQL

SELECT
  T.weekday,
  CASE
    WHEN T.weekday = 1 THEN '日曜日'
    WHEN T.weekday = 2 THEN '月曜日'
    WHEN T.weekday = 3 THEN '火曜日'
    WHEN T.weekday = 4 THEN '水曜日'
    WHEN T.weekday = 5 THEN '木曜日'
    WHEN T.weekday = 6 THEN '金曜日'
    WHEN T.weekday = 7 THEN '土曜日'
    ELSE 'N/A'
  END AS `曜日`, 
  T.fare_amount AS `料金`, 
  T.trip_distance AS `移動距離`
FROM
  (
    SELECT
      dayofweek(tpep_pickup_datetime) as weekday,
      *
    FROM
      `samples`.`nyctaxi`.`trips`
    WHERE pickup_zip in (10018) AND trip_distance < 10
  ) T
ORDER BY
  T.weekday

以下の設定でビジュアライゼーションを作成します。
- ビジュアライゼーションの名前: 移動距離と料金の散布図
- Visualization type: Scatter
- X column: 移動距離
- Y column: 料金
- Group by: 曜日

ダッシュボードの作成

サイドメニューの作成→ダッシュボードを選択します。
ニューヨークタクシーダッシュボードなどわかりやすいダッシュボードの名前をつけます。
保存をクリックします。
ダッシュボードのキャンバスが表示されます。
右上の追加ボタンを押し、可視化を選択します。
これまでに作成したクエリーが一覧されます。
クエリー移動総数を選択します。Select existing visualizationの下のドロップダウンリストから移動総数カウンターを選択し、ダッシュボードに追加をクリックします。
これでビジュアライゼーションがダッシュボードに追加されました。
他のビジュアライゼーションも追加します。ビジュアライゼーションはドラッグして位置やサイズを調整することができます。好きなレイアウトに調整したら編集完了をクリックします。

フィルターの追加

乗車地点のZIPコードでフィルタリングできるようにします。

以下のクエリー乗車zipを作成します。

SQL

SELECT
  DISTINCT pickup_zip
from samples.nyctaxi.trips
ORDER BY pickup_zip ASC

これまでに作成したクエリーにパラメーターを渡せるように、クエリーパラメーターを設定します。クエリーのSQLを更新して保存します。
移動総数
```
SELECT
  count(*) as total_trips
FROM
  samples.nyctaxi.trips
WHERE
  pickup_zip IN ({{ pickupzip }})
```
{{}}で囲んだ部分がフィルターで選択された値で置き換えられるようになります。SQLを変更するとウィジェットが表示されます。ギアマークをクリックします。
Typeでクエリーベースのドロップダウンリストを選択します。クエリーで先ほど作成した乗車zipを選択します。
複数の値を許容チェックボックスをチェックしておきます。
OKをクリックします。
pickupzipで10001を選択し、変更を適用をクリックして動作を確認します。
クエリーを保存します。

他のクエリーも更新します。SQL更新後、上で行ったウィジェットの設定を行います。

乗車時間の分布

SELECT
    CASE 
        WHEN T.pickup_hour = 0 THEN '00:00'
        WHEN T.pickup_hour = 1 THEN '01:00'
        WHEN T.pickup_hour = 2 THEN '02:00'
        WHEN T.pickup_hour = 3 THEN '03:00'
        WHEN T.pickup_hour = 4 THEN '04:00'
        WHEN T.pickup_hour = 5 THEN '05:00'
        WHEN T.pickup_hour = 6 THEN '06:00'
        WHEN T.pickup_hour = 7 THEN '07:00'
        WHEN T.pickup_hour = 8 THEN '08:00'
        WHEN T.pickup_hour = 9 THEN '09:00'
        WHEN T.pickup_hour = 10 THEN '10:00'
        WHEN T.pickup_hour = 11 THEN '11:00'
        WHEN T.pickup_hour = 12 THEN '12:00'
        WHEN T.pickup_hour = 13 THEN '13:00'
        WHEN T.pickup_hour = 14 THEN '14:00'
        WHEN T.pickup_hour = 15 THEN '15:00'
        WHEN T.pickup_hour = 16 THEN '16:00'
        WHEN T.pickup_hour = 17 THEN '17:00'
        WHEN T.pickup_hour = 18 THEN '18:00'
        WHEN T.pickup_hour = 19 THEN '19:00'
        WHEN T.pickup_hour = 20 THEN '20:00'
        WHEN T.pickup_hour = 21 THEN '21:00'
        WHEN T.pickup_hour = 22 THEN '22:00'
        WHEN T.pickup_hour = 23 THEN '23:00'
    ELSE 'N/A'
    END AS `乗車時刻`,
    T.num AS `乗車回数`
FROM
(SELECT
  hour(tpep_pickup_datetime) AS pickup_hour,
  COUNT(*) AS num
FROM
  `samples`.`nyctaxi`.`trips`
WHERE
  pickup_zip IN ({{ pickupzip }})
GROUP BY
  1) T

降車時間の分布

SELECT
    CASE 
        WHEN T.dropoff_hour = 0 THEN '00:00'
        WHEN T.dropoff_hour = 1 THEN '01:00'
        WHEN T.dropoff_hour = 2 THEN '02:00'
        WHEN T.dropoff_hour = 3 THEN '03:00'
        WHEN T.dropoff_hour = 4 THEN '04:00'
        WHEN T.dropoff_hour = 5 THEN '05:00'
        WHEN T.dropoff_hour = 6 THEN '06:00'
        WHEN T.dropoff_hour = 7 THEN '07:00'
        WHEN T.dropoff_hour = 8 THEN '08:00'
        WHEN T.dropoff_hour = 9 THEN '09:00'
        WHEN T.dropoff_hour = 10 THEN '10:00'
        WHEN T.dropoff_hour = 11 THEN '11:00'
        WHEN T.dropoff_hour = 12 THEN '12:00'
        WHEN T.dropoff_hour = 13 THEN '13:00'
        WHEN T.dropoff_hour = 14 THEN '14:00'
        WHEN T.dropoff_hour = 15 THEN '15:00'
        WHEN T.dropoff_hour = 16 THEN '16:00'
        WHEN T.dropoff_hour = 17 THEN '17:00'
        WHEN T.dropoff_hour = 18 THEN '18:00'
        WHEN T.dropoff_hour = 19 THEN '19:00'
        WHEN T.dropoff_hour = 20 THEN '20:00'
        WHEN T.dropoff_hour = 21 THEN '21:00'
        WHEN T.dropoff_hour = 22 THEN '22:00'
        WHEN T.dropoff_hour = 23 THEN '23:00'
    ELSE 'N/A'
    END AS `降車時間`,
    T.num AS `降車回数`
FROM
(SELECT
  hour(tpep_dropoff_datetime) AS dropoff_hour,
  COUNT(*) AS num
FROM
  `samples`.`nyctaxi`.`trips`
WHERE
  pickup_zip IN ({{ pickupzip }})
GROUP BY
  1) T

移動距離と料金の関係

SELECT
  T.weekday,
  CASE
    WHEN T.weekday = 1 THEN '日曜日'
    WHEN T.weekday = 2 THEN '月曜日'
    WHEN T.weekday = 3 THEN '火曜日'
    WHEN T.weekday = 4 THEN '水曜日'
    WHEN T.weekday = 5 THEN '木曜日'
    WHEN T.weekday = 6 THEN '金曜日'
    WHEN T.weekday = 7 THEN '土曜日'
    ELSE 'N/A'
  END AS `曜日`, 
  T.fare_amount AS `料金`, 
  T.trip_distance AS `移動距離`
FROM
  (
    SELECT
      dayofweek(tpep_pickup_datetime) as weekday,
      *
    FROM
      `samples`.`nyctaxi`.`trips`
    WHERE pickup_zip in ({{ pickupzip }})
  ) T
ORDER BY
  T.weekday

フィルターのクエリーパラメーターのマッピング

この時点でダッシュボードを表示すると、それぞれのビジュアライゼーションにフィルターウィジェットが追加された状態になっています。これを一つのウィジェットで操作できるように変更します。
ダッシュボードの右上のをクリックし編集をクリックします。
ビジュアライゼーションの上にカーソルを移動すると右上にが表示されるので、これをクリックしてメニューを展開します。ウィジェット設定を変更を選択します。
パラメーターの値のソースのウィジェットのパラメータの右にある鉛筆アイコンをクリックします。
新規ダッシュボードパラメータを選択してOKを押します。
Saveをクリックします。
これでダッシュボード上のすべてのビジュアライゼーションに適用されるフィルターが作成されます。
他のビジュアライゼーションについても同じ設定を行います。この際には、すでにダッシュボードパラメータが作成されているので既存のダッシュボードパラメータを選択します。設定が終わったら編集完了をクリックします。
これでダッシュボードにフィルターが追加され、このフィルターですべてのビジュアライゼーションをコントロールすることができます。

次のステップ

ここで紹介したダッシュボードは、ダッシュボードギャラリーからクイックにインポートすることもできます。他のビジュアライゼーションやフィルターも追加されているので動作を確認してみてください。

また、自分でデータをアップロードしてダッシュボードを作成することも可能です。

Databricks 無料トライアル

Databricks 無料トライアル