ADLSにあるデータを使ってDatabricks SQLでダッシュボードを作成する

タイトルの通り、ADLSに配置したCSVファイルから[Databricks SQL](https://www.databricks.com/jp/product/databricks-sql)のダッシュボードを作る流れを説明します。

# ファイルのアップロード

ストレージアカウントとコンテナーを作成し、CSVファイルをアップロードします。こちらのファイルは日本国内のCOVID-19感染者数データを加工したものとなっています。

[オープンデータ｜厚生労働省](https://www.mhlw.go.jp/stf/covid-19/open-data.html)で公開されている[新規陽性者数の推移（日別）](https://covid19.mhlw.go.jp/public/opendata/newly_confirmed_cases_daily.csv)を加工しています。

![Screen Shot 2022-08-19 at 20.41.52.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/3a3bc9c5-085a-714e-56e5-e41f3b92fcef.png)

# Azure DatabricksからADLSにアクセス

:::note info
**注意**
WASBドライバーを用いたアクセスやファイルシステムのマウントは非推奨となっています。ここではABFSドライバーによるアクセス方法を説明します。詳細は[こちら](https://qiita.com/taka_yayoi/items/f1f8b7cc8af68860d6de)も参照ください。
:::

```py:Python
spark.conf.set("fs.azure.account.key.takaucstorageaccount.dfs.core.windows.net", "<アクセスキー>")
```

ディレクトリの中身を確認します。

```py:Python
display(dbutils.fs.ls("abfss://<コンテナー名>@<ストレージアカウント名>.dfs.core.windows.net/adls_data"))
```
![Screen Shot 2022-08-22 at 10.59.24.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/03c8cbe0-6a7f-930b-9fc2-8b1686745190.png)

# テーブルの作成

ABFSドライバー経由でCSVファイルにアクセスできるので、`CREATE TABLE`文を使ってCSVファイルから直接テーブルを作成します。

```sql:SQL
CREATE TABLE default.covid_cases(
  Prefecture STRING,
  Cases INT,
  date_parsed TIMESTAMP
) USING CSV LOCATION "abfss://<コンテナー名>@<ストレージアカウント名>.dfs.core.windows.net/adls_data/japan_cases_20220818.csv" OPTIONS("header" = "True");
```

# テーブルへのクエリー

```sql:SQL
SELECT * FROM default.covid_cases;
```

この時点でもノートブック上で簡単な可視化を行うことができます。

![Screen Shot 2022-08-19 at 21.26.33.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/b9a246dd-d12b-6d0a-86d4-cf953564fa10.png)
![Screen Shot 2022-08-19 at 21.26.48.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/cd201a0f-4dfb-e94d-4a9e-f3554cbb2348.png)

# ダッシュボードの作成

Databricks SQLに移動します。上で作成したテーブルはDatabricks SQLからも参照できます。

クエリーを作成し、必要な可視化を設定していきます。
![Screen Shot 2022-08-19 at 21.28.18.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/ef87e04a-28fe-e5d4-5303-3e2efe3bffdc.png)

以下のクエリーでは全国の感染者数のデータを除外し、今年のデータのみに限定しています。その他年月で集計できるように`YearMonth`という列を追加しています。
```sql:SQL
SELECT
  *,
  date(date_parsed) AS Date,
  month(date_parsed) AS Month,
  year(date_parsed) AS Year,
  concat(year(date_parsed), "-",  RIGHT(concat("0", month(date_parsed)), 2)) AS YearMonth
FROM
  default.covid_cases
WHERE
  Prefecture <> "ALL" AND date_parsed > "2022-1-1"
```

可視化(ビジュアライゼーション)は時系列変化、地域別、割合などを作成します。
![Screen Shot 2022-08-19 at 21.30.32.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/b1fc2e81-f120-2f7d-877a-30225a21e1c1.png)
![Screen Shot 2022-08-19 at 21.30.44.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/c781942d-6760-059b-9ae6-6dc51678d02c.png)
![Screen Shot 2022-08-19 at 21.30.50.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/5ec0795e-3e4f-c02e-a00a-8a7a84cb3984.png)

また、ダッシュボード上で絞り込みが行えるように、Prefecture(県)や日付でのフィルターを追加しています。
![Screen Shot 2022-08-19 at 21.32.11.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/8c56ec89-190e-0f30-13e4-2bddf866fc27.png)

クエリーの準備が整ったらダッシュボードを作成します。ダッシュボード上に上で作成したビジュアライゼーションを配置していきます。
![Screen Shot 2022-08-19 at 21.34.04.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/59df644f-9a9c-8851-cf6a-cbbf3203c82a.png)

このようなダッシュボードが出来上がりました。フィルターを追加してあるので都道府県や時期での絞り込みもできるようになっています。
![Screen Shot 2022-08-19 at 21.35.20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/82067293-95ee-4802-9b8b-8ac1d1d8a651.png)
![Screen Shot 2022-08-19 at 21.35.38.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/f4523a33-b907-67d6-094f-a9286c3af146.png)

ダッシュボードの詳細に関しては、前処理含めて別に記事を書こうと思っています。

### Databricks 無料トライアル

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