ニューヨークタクシー乗降記録を用いたDatabricks SQLのウォークスルー

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

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

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

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

Databricks SQLへのアクセス

1. Databricksワークスペースにログインします。
   
2. 画面のデフォルト言語設定は英語なので、日本語に変更します。
3. サイドバーから「Settings > User Settings」を選択します。
4. Change your languageから日本語を選択します。
   
5. サイドバーのペルソナスイッチャーでSQLを選択します。
   
6. Databricks SQLのランディングページが表示されます。
   

SQLウェアハウスの作成

1. Databricks SQLにおける計算資源であるSQLウェアハウスを作成します。
2. サイドメニューのSQLウェアハウスをクリックします。
   
3. 画面右上のSQLウェアハウスを作成ボタンをクリックします。
   
4. エンドポイント名にはわかりやすい名前、クラスターサイズはXXSを選択して、作成をクリックします。
   
5. 権限管理が表示されたら右上のxをクリックします。
   
6. SQLウェアハウスのステータスが実行中になるまで待ちます。
   

クエリーの実行

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

1. サイドメニューのクエリをクリックします。作成済みのクエリーの一覧が表示されるので、クエリーを作成をクリックします。
   

2. SQLエディタが表示されます。こちらでSQLを記述してクエリーを作成します。
   

3. 上のステップで作成したSQLウェアハウスを選択します。
   

4. スキーマ(データベース)ブラウザを操作してテーブルを参照します。

   hive_metastoreをクリックしてリストを展開します。samplesを選択します。
   

5. samples配下のデータベースnytaxiを選択します。
   

6. データベースnytaxi配下のテーブルtripsが表示されます。
   

7. テーブルtripsにマウスカーソルを合わせるとボタンが表示されます。をクリックしてデータのプレビューを表示します。
   

8. これで準備が整いました。
   

データの説明

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

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	降車地点の郵便番号

ダッシュボードの作成

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

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

SQLエディタの画面構成

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

レコード数のカウント

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

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

   SQL

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

3. すべてを実行をクリックすることでSQLが実行されます。
   

4. 画面右下に結果が表示されます。他のクエリーを実行する際には同じ手順を踏みます。
   

5. 次に、この結果を用いてダッシュボードの部品となるビジュアライゼーションを作成します。

6. Tableの右にある + をクリックします。メニューが展開されるのでVisualizationを選択します。
   

7. ビジュアライゼーションエディタが開きます。

8. Visualization typeから可視化のタイプを選択します。ここではCounterを選択します。
   

9. 選択するビジュアライゼーションによって設定項目は異なります。ここでは、Counter labelに移動総数と入力します。

10. また、ビジュアライゼーションを識別しやすくするためにCounter 1と表示されている箇所にカーソルを移動して移動総数カウンターと名前を入力します。
    

11. 保存をクリックするとビジュアライゼーションエディタが閉じます。

12. Tableの隣にビジュアライゼーション移動総数カウンターが追加されました。別のクエリーを作成した場合にも同様にビジュアライゼーションを追加することができます。
    

13. 最後にクエリーに名前をつけて保存します。New Queryとなっている箇所にカーソルを移動して移動総数と入力します。
    

14. 保存をクリックします。

乗車時間の分布

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

2. クエリー名を乗車時間の分布、以下の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
   

3. 以下の設定でビジュアライゼーションを作成します。

   • ビジュアライゼーションの名前: 乗車時間の棒グラフ
   • Visualization type:Bar
   • Horizontal chartのチェックを解除
   • X column: 乗車時刻
   • Y column: 乗車回数
   • Y軸のラベル: 乗車回数合計

   

降車時間の分布

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

2. クエリー名を降車時間の分布、以下の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
   

3. 以下の設定でビジュアライゼーションを作成します。

   • ビジュアライゼーションの名前: 降車時間の棒グラフ
   • Visualization type:Bar
   • Horizontal chartのチェックを解除
   • X column: 降車時刻
   • Y column: 降車回数
   • Y軸のラベル: 降車回数合計

   

移動距離と料金の関係

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

2. クエリー名を移動距離と料金の関係、以下の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  
   

3. 以下の設定でビジュアライゼーションを作成します。

   • ビジュアライゼーションの名前: 移動距離と料金の散布図
   • Visualization type: Scatter
   • X column: 移動距離
   • Y column: 料金
   • Group by: 曜日

   

ダッシュボードの作成

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

フィルターの追加

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

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

   SQL

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

3. これまでに作成したクエリーにパラメーターを渡せるように、クエリーパラメーターを設定します。クエリーのSQLを更新して保存します。

   移動総数

   SELECT
     count(*) as total_trips
   FROM
     samples.nyctaxi.trips
   WHERE
     pickup_zip IN ({{ pickupzip }})
   

4. {{}}で囲んだ部分がフィルターで選択された値で置き換えられるようになります。SQLを変更するとウィジェットが表示されます。ギアマークをクリックします。
   

5. Typeでクエリーベースのドロップダウンリストを選択します。クエリーで先ほど作成した乗車zipを選択します。
   

6. 複数の値を許容チェックボックスをチェックしておきます。
   

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

8. pickupzipで10001を選択し、変更を適用をクリックして動作を確認します。
   
   

9. クエリーを保存します。

他のクエリーも更新します。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  

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

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

次のステップ

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

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

Databricks 無料トライアル

Databricks 無料トライアル