Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@taka_yayoi(Takaaki Yayoi)inデータブリックス・ジャパン株式会社

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

Last updated at Posted at 2022-09-02

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

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

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

Screen Shot 2022-09-07 at 14.17.51.png

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

Databricks SQLへのアクセス

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

SQLウェアハウスの作成

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

クエリーの実行

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

  1. サイドメニューのクエリをクリックします。作成済みのクエリーの一覧が表示されるので、クエリーを作成をクリックします。
    Screen Shot 2022-09-02 at 16.30.08.png

  2. SQLエディタが表示されます。こちらでSQLを記述してクエリーを作成します。
    Screen Shot 2022-09-02 at 10.14.47.png

  3. 上のステップで作成したSQLウェアハウスを選択します。
    Screen Shot 2022-09-02 at 16.31.27.png

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

    hive_metastoreをクリックしてリストを展開します。samplesを選択します。
    Screen Shot 2022-09-02 at 16.32.50.png

  5. samples配下のデータベースnytaxiを選択します。
    Screen Shot 2022-09-02 at 16.33.31.png

  6. データベースnytaxi配下のテーブルtripsが表示されます。
    Screen Shot 2022-09-02 at 10.35.26.png

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

  8. これで準備が整いました。
    Screen Shot 2022-09-02 at 10.39.54.png

データの説明

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

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エディタからでもアクセスできます。

Screen Shot 2022-09-02 at 16.39.59.png

レコード数のカウント

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

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

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

  3. すべてを実行をクリックすることでSQLが実行されます。
    Screen Shot 2022-09-02 at 16.45.49.png

  4. 画面右下に結果が表示されます。他のクエリーを実行する際には同じ手順を踏みます。
    Screen Shot 2022-09-02 at 13.35.22.png

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

  6. Tableの右にある + をクリックします。メニューが展開されるのでVisualizationを選択します。
    Screen Shot 2022-09-02 at 16.47.43.png

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

  8. Visualization typeから可視化のタイプを選択します。ここではCounterを選択します。
    Screen Shot 2022-09-02 at 13.40.15.png

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

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

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

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

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

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

乗車時間の分布

  1. タブの隣の + をクリックして新規クエリを作成を選択します。
    Screen Shot 2022-09-02 at 14.15.20.png

  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軸のラベル: 乗車回数合計

    Screen Shot 2022-09-02 at 14.26.00.png

降車時間の分布

  1. タブの隣の + をクリックして新規クエリを作成を選択します。
    Screen Shot 2022-09-02 at 14.15.20.png

  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軸のラベル: 降車回数合計

    Screen Shot 2022-09-02 at 14.26.00.png

移動距離と料金の関係

  1. タブの隣の + をクリックして新規クエリを作成を選択します。
    Screen Shot 2022-09-02 at 14.15.20.png

  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: 曜日

    Screen Shot 2022-09-02 at 14.41.56.png

ダッシュボードの作成

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

フィルターの追加

  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を変更するとウィジェットが表示されます。ギアマークをクリックします。
    Screen Shot 2022-09-02 at 15.38.58.png

  5. Typeでクエリーベースのドロップダウンリストを選択します。クエリーで先ほど作成した乗車zipを選択します。
    Screen Shot 2022-09-02 at 15.41.46.png

  6. 複数の値を許容チェックボックスをチェックしておきます。
    Screen Shot 2022-09-02 at 15.42.24.png

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

  8. pickupzipで10001を選択し、変更を適用をクリックして動作を確認します。
    Screen Shot 2022-09-02 at 15.44.11.png
    Screen Shot 2022-09-02 at 15.44.54.png

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

次のステップ

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

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

Databricks 無料トライアル

Databricks 無料トライアル

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?