ローコードでDatabricks SQLダッシュボードを作成する

[ノーコードでDatabricks SQLダッシュボードを作成する](https://qiita.com/taka_yayoi/items/d2b7b3226e722e4011fa)では、1行のPython/SQLを記述することなくダッシュボードを作成する手順をご紹介しました。ノーコード開発はコーディングを必要としないため、敷居が低いというメリットがある一方、コーディングによって得られる柔軟性を犠牲にしているといえます。ノーコードで対応しきれないユースケースに対しては、何かしらのコーディングが必要となります。

こちらでは可能な限りコーディングの量を減らしつつも、柔軟性を確保し機能性の高いダッシュボードを作成する手順をご紹介します。

:::note warn
「ローコード」の定義は人によって異なるかと思います。本書ではSQLのコーディングのみをご紹介していますが、人によってはコーディングが多いと感じられる場合があることをご承知おきください。
:::

# テーブルの作成

[ノーコードでDatabricks SQLダッシュボードを作成する](https://qiita.com/taka_yayoi/items/d2b7b3226e722e4011fa)では、最初からDatabricksワークスペースに存在しているテーブルを参照してダッシュボードを作成しました。こちらでは、外部からデータを取り込んでテーブルを作成する流れをご紹介します。このステップはノーコードとなります。

## データのダウンロード

ここでは、[政府統計の総合窓口](https://www.e-stat.go.jp/)で公開されている[男女別人口－全国，都道府県（大正９年～平成27年）](https://www.e-stat.go.jp/stat-search/files?page=1&layout=datalist&toukei=00200521&tstat=000001011777&cycle=0&tclass1=000001094741&tclass2val=0)のデータを使用します。リンク先にアクセスし、表番号1のCSVをダウンロードします。
![Screen Shot 2022-03-23 at 8.55.44.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/73025652-98ab-3857-0238-6023f625ce24.png)

このCSVファイルの文字コードはShift_JISなので、UTF-8への変換が必要となります。Databricksにおける文字コードはUTF-8となります。

こちらの記事を参考にしていただくなどして、メモ帳でUTF-8を指定して保存し直してください。

https://aprico-media.com/posts/6994

## Databrikcsへのデータのアップロード

DatabricksワークスペースにCSVファイルをアップロードしてテーブルを作成します。

1. Databricksワークスペースにログインし、サイドバーの**データ**をクリックし右上の**テーブルを作成**ボタンをクリックします。
![Screen Shot 2022-03-23 at 9.01.21.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/d5c0b630-5d04-6ab5-0f3f-68f5557a522b.png)
1. テーブル作成画面に移動します。テーブルの格納場所を**DBFSターゲットディレクトリ**で指定します。
1. **ファイル**のボックスに、上のステップで準備したCSVファイルをドラッグ&ドロップします。
![Screen Shot 2022-03-23 at 9.05.26.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/f5d2a8e0-6ae7-c51e-90c4-030e2866b276.png)
1. **UIでテーブルを作成**ボタンをクリックします。
1. テーブルのプレビューを表示するために使用するクラスターを**クラスター**から選択します。クラスターが起動していない場合には起動してください。クラスターを選択したら**テーブルをプレビュー**ボタンをクリックします。
![Screen Shot 2022-03-23 at 9.06.58.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/fa5d1611-e57e-a728-a8be-b87d71d774bc.png)
1. テーブルのプレビューが表示されますので、以下の設定を行います。
![Screen Shot 2022-03-23 at 9.08.02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/f393e141-04f6-b1ce-ec1d-e5e227bf6664.png)
    - **テーブル名**: 作成するテーブルの名称を指定します。
    - **データベース**: テーブルを作成するデータベースを選択します。デフォルトのデータベースは`default`となります。
    - **1行目はヘッダー**: このCSVファイルの1行目はヘッダーなのでチェックします。
    - カラムのデータ型: 以下のカラムのデータ型を`INT`に設定します。
        - 和暦（年）
        - 西暦（年）
        - 人口（総数）
        - 人口（男）
        - 人口（女） 

    以下のように設定を行います。
![Screen Shot 2022-03-23 at 9.13.03.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/18920953-d2a4-8160-1083-0dd47891875c.png)

1. **テーブルを作成**ボタンをクリックしてテーブルを作成します。テーブルの作成が完了するとテーブルが表示されます。ここまではすべてノーコードです。
![Screen Shot 2022-03-23 at 9.18.56.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/099e9293-eb2b-98c7-cf69-7887d87244ec.png)

# ダッシュボードの作成

上のステップで作成したテーブルに対して、[ノーコードでDatabricks SQLダッシュボードを作成する](https://qiita.com/taka_yayoi/items/d2b7b3226e722e4011fa)で紹介した[クイックダッシュボード](https://qiita.com/taka_yayoi/items/d2b7b3226e722e4011fa#%E3%83%80%E3%83%83%E3%82%B7%E3%83%A5%E3%83%9C%E3%83%BC%E3%83%89%E3%81%AE%E4%BD%9C%E6%88%90)を使用すれば、テーブルの作成からダッシュボードの作成まですべてをノーコードで完了することが可能です。

しかし、ここでは西暦、都道府県でのフィルタリングなどが行えるより機能的なダッシュボードを作成するために、SQLによるコーディングを行います。このダッシュボードでは、上で作成したテーブルにアクセスし、集計を行うための以下の4つのクエリー(SQL)を作成します。

1. 西暦一覧
1. 都道府県一覧
1. 都道府県別人口遷移
1. 人口

Databricksワークスペースのサイドバーにあるペルソナスイッチャーで**SQL**を選択してDatabricks SQLに移動します。
![Screen Shot 2022-03-23 at 9.22.17.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/a8236ef8-34fb-e1b2-a590-0f9d67cef04f.png)

## クエリーの作成

1. サイドメニューの**クエリ**を選択します。
1. 画面右上の**クエリを作成**ボタンをクリックします。
1. クエリエディタに移動します。この画面でSQLを記述してクエリーを作成します。
![Screen Shot 2022-03-23 at 9.27.22.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/b8c283ad-4703-65a5-c670-df3c4d64d759.png)
1. Databricks SQLでクエリーを実行するには[SQLエンドポイント](https://qiita.com/taka_yayoi/items/23d7789198c2dcd66381)が必要となります。SQLエンドポイントが起動していない場合には起動の上、画面上部中央からSQLエンドポイントを選択してください。
1. 画面左側でデータベース、テーブルを一覧することができます。テーブル名、カラム名の右にある **>>** をクリックすると右側のエディタにテーブル名、カラム名を入力することができます。
![Screen Shot 2022-03-23 at 9.27.22.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/c6614b77-c84a-e6b2-bea4-315dd55ebc94.png)
1. クエリーを記述したら画面右上の**実行**でクエリーの動作確認を行い、問題がなければ**保存**ボタンをクリックしてクエリーを保存します。タブをクリックしてクエリー名を変更します。

まず、フィルタリングの部品を作るための2つのクエリーを作成します。

```sql:西暦一覧
SELECT
  `西暦（年）`
FROM
  <データベース名>.<テーブル名>
WHERE
  `西暦（年）` IS NOT NULL
GROUP BY
  `西暦（年）`
ORDER BY
  `西暦（年）` DESC
```

```sql:都道府県一覧
SELECT
  `都道府県名`,
  `都道府県コード`
FROM
  <データベース名>.<テーブル名>
WHERE
  `都道府県名` IS NOT NULL
  AND `都道府県名` NOT IN ("人口集中地区", "人口集中地区以外の地区")
GROUP BY
  `都道府県名`,
  `都道府県コード`
ORDER BY
  `都道府県コード` ASC
```

上のフィルタリング条件を入力として集計を行う、以下の2つのクエリーを作成します。`{{}}`で囲まれている部分がパラメーターとして置き換えられます。

```sql:都道府県別人口遷移
SELECT
  *
FROM
  <データベース名>.<テーブル名>
WHERE
  (
    '全国' in ({{ 都道府県名 }})
    OR `都道府県名` IN ({{ 都道府県名 }})
  )
  AND `都道府県名` NOT IN ("全国", "人口集中地区", "人口集中地区以外の地区")
ORDER BY
  `人口（総数）` DESC
```

上のクエリーを実行すると、以下のように結果の上部にフィルタリングのための部品が表示されます。この部品が上で作成したクエリーの結果を読み込むように設定を行います。

- **Type**: クエリベースのドロップダウンリスト
- **クエリー**: 都道府県一覧
- **複数の値を許容**: チェックします
- **引用符**: 引用符を選択します

![Screen Shot 2022-03-23 at 9.37.57.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/fac79dfb-ba56-ec52-156d-a2f0780af681.png)


```sql:人口
SELECT
  *
FROM
  <データベース名>.<テーブル名>
WHERE
  (
    '全国' in ({{ 都道府県名 }})
    OR `都道府県名` IN ({{ 都道府県名 }})
  )
  AND `西暦（年）` = {{ 西暦 }}
  AND `都道府県名` NOT IN ("全国", "人口集中地区", "人口集中地区以外の地区")
ORDER BY
  `人口（総数）` DESC
```

上のクエリーを実行すると、以下のように結果の上部にフィルタリングのための部品が表示されます。この部品が上で作成したクエリーの結果を読み込むように設定を行います。

**都道府県名**
- **Type**: クエリベースのドロップダウンリスト
- **クエリー**: 都道府県一覧
- **複数の値を許容**: チェックします
- **引用符**: 引用符を選択します

**西暦**
- **Type**: クエリベースのドロップダウンリスト
- **クエリー**: 西暦一覧

![Screen Shot 2022-03-23 at 9.41.06.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/a472819c-470c-f430-f9e0-872fe75f0541.png)

## 可視化(ビジュアライゼーション)の作成

上で作成したクエリーは結果をテーブル形式で返却します。これを様々な形で可視化することができます。

クエリー*都道府県別人口遷移*では折れ線グラフで可視化を行います。画面右側中央の **+可視化を追加** をクリックします。以下の設定を行います。設定が完了したら**保存**をクリックします。

- **Visualization Type**: Line
- **X Column**: 西暦（年）
- **Y Column**: 人口（総数）、Sum
- **Group By**: 都道府県名
- **X Axis**タブの**Sort Values**をオンに

![Screen Shot 2022-03-23 at 9.43.39.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/af76ee7d-1385-fae6-4548-5acd9851f67f.png)

可視化のタブをクリックし、*時系列変化*と名前をつけます。

クエリー*人口*では、人口総数を地図上に描画します。以下の設定を行い、設定が完了したら**保存**をクリックします。

- **Visualization Type**: Map(Choropleth)
- **Map**: Japan/Prefectures
- **Key Column**: 都道府県名
- **Target Field**: Name(local)
- **Value Column**: 人口（総数）

![Screen Shot 2022-03-23 at 9.50.49.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/ec201d8c-62fc-e179-b333-d87d52eb2164.png)

可視化のタブをクリックし、*地図*と名前をつけます。

## ダッシュボードの作成

サイドメニューの**ダッシュボード**クリックし、画面右上の**ダッシュボードを作成**ボタンをクリックします。ダッシュボードに名前をつけて**保存**ボタンをクリックします。

キャンバスが表示されますので、ここで上のステップで作成した可視化を配置していきます。
![Screen Shot 2022-03-23 at 9.53.48.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/099e385e-dd0e-c521-9a34-a5cb7b04b0a7.png)

1. 画面右上の**可視化を追加**ボタンをクリックします。
1. クエリー一覧が表示されるので、追加したい可視化を含むクエリーと可視化を選択します。
    - 人口: 地図
    - 都道府県別人口遷移: 時系列変化 
1. **ダッシュボードレベルフィルタを使用**にチェックをつけます。
![Screen Shot 2022-03-23 at 9.58.02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/74bf3b98-47ed-afe0-4f31-bf328c39a4b6.png)
1. **編集完了**をクリックします。

これでダッシュボードの完成です。画面上部に表示されている都道府県名、西暦を変更して**変更を適用**をクリックし、変更が適用されることを確認してください。
![Screen Shot 2022-03-23 at 9.59.08.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/f227a346-1007-6d5f-9eb3-2d31f542f610.png)
![Screen Shot 2022-03-23 at 9.59.47.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/28769693-1a1a-184e-b9ed-20922a731568.png)

この他にも要件に応じて様々な可視化を追加することができるので、いろいろ試してみてください。
![Screen Shot 2022-03-23 at 10.01.15.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/ea9b62ad-4f7a-74dd-3c9e-e3046f60af1c.png)

# 参考資料

- [Databricks SQLのコンセプト](https://qiita.com/taka_yayoi/items/eaf79254afd4eb620a6e)
- [Databricks SQLクイックスタート：サンプルダッシュボードギャラリーからダッシュボードをインポートしてDatabricks SQLを学ぶ](https://qiita.com/taka_yayoi/items/0e3e0730b71de28e4c08)
- [Databricks SQLのダッシュボード](https://qiita.com/taka_yayoi/items/834bef9aa784e1a045d8)
- [DatabricksのSQLエンドポイント](https://qiita.com/taka_yayoi/items/23d7789198c2dcd66381)


### Databricks 無料トライアル

[Databricks 無料トライアル](https://databricks.com/jp/try-databricks)