Azureでデータ分析基盤作ってみた - ③外部テーブル作成

Azureでシンプルなデータ分析基盤を作る検証をしています。
[Azureでデータ分析基盤作ってみた - ①Synapseの構築](https://qiita.com/m-duct/items/0123f072b1d2c0928367)、[Azureでデータ分析基盤作ってみた - ②ADFの構築とETL処理](https://qiita.com/m-duct/items/3d90ce46dd4eabe565b8)という記事の続編として、Synapseでの外部テーブル作成について記載します。

## 構成
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/398131/726ff053-aad9-5f39-551e-5f102a275d74.png)
1つのリソースグループの中にストレージアカウント、ADF、Synapseワークスペースを作成します。
ストレージアカウント(ADLS Gen2)に置いたcsvファイルをparquetファイルに変換すると同時に、データ型の変換も行います。
SynapseではサーバーレスSQLプールを使って外部テーブルを作成し、Power BIから外部テーブルを参照できるようにします。

## 作業内容
以下の順番で作業を行いました。
本記事では「3. 外部テーブル作成」について記載します。

[1. Synapseの構築](https://qiita.com/m-duct/items/0123f072b1d2c0928367)
リソースグループ、ストレージアカウント、Synapseワークスペースを構築
[2. ADFの構築とETL処理](https://qiita.com/m-duct/items/3d90ce46dd4eabe565b8)
ADFインスタンスを構築後、ADFでデータ型およびファイル形式の変換
3. 外部テーブル作成
parqutファイルを参照する外部テーブルをSynapseに作成して、Power BIから参照する（今回はここ）

## 今回の構築範囲
「1.Synapseの構築」で作成したSynpaseワークスペースに「2.ADFの構築とETL処理」で作成したparquetファイルのデータを参照する外部テーブルを作成します。
最終的にPower BIから外部テーブルに接続して、parquetファイルのデータを可視化します。

### 作成するもの
#### SQLデータベース
デフォルトで用意されているmasterデータベースには外部データを参照するためのカスタムデータベースオブジェクト(外部データソースなど)を作成できないため、新規にSQLデータベースを作成します。

#### データベースのマスターキー
認証情報を保護するためにマスターキーを作成します。

#### 認証情報
外部データソースにアクセスするための認証情報を定義します。

#### 外部データソース
外部のストレージサービス（Azure Blob Storageなど）を参照するためのオブジェクト。
このオブジェクトを作成することで、SQLクエリが外部データを参照できるようになります。
今回は①Synapseの構築で作成したストレージアカウントとファイルシステムを参照したいので、`LOCATION = 'abfss://ファイルシステム名@ストレージアカウント名.dfs.core.windows.net'`と指定します。

#### 外部ファイルフォーマット
外部データのファイル形式を指定するためのオブジェクト。
外部ファイル形式を定義することで、データベースエンジンは外部ストレージに格納されているファイルのフォーマットに基づいて正しくデータを解釈し、クエリを実行できるようになります。
今回はADFによってストレージアカウントに作成されたparquetファイルを読み込みたいので、`FORMAT_TYPE = PARQUET`と指定します。

#### 外部テーブル
外部データソースに格納されたデータを参照するためのテーブル。
ストレージアカウント内のファイルからデータを読み取ったり、ファイルにデータを書き込んだりするために、外部テーブルを使用します。
作成するテーブルのカラム名とデータ型はADFで作成したparquetファイルと同じになるようにします。
また、今回は外部データソースの中でもoutputフォルダ内のparquetを参照したいため、with句で以下のように指定します。
```
LOCATION = '/output/',
DATA_SOURCE = blog_data_source,
FILE_FORMAT = blog_file_format
```

#### Power BIレポート
「2.ADFの構築とETL処理」で作成したparquetファイルのデータを可視化するためにPower BIレポートを作成します。

## 構築作業
### データベース作成
Synapse Studioを開き、Data → Workspace → SQLデータベースを選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/398131/e768aa9e-2877-c645-fc8a-9930cbc883e0.png)

SQLプール型の選択で「サーバーレス」を選択し、任意のデータベース名を指定して「作成」をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/398131/001d6701-84cc-281a-9db0-fb0d46d667ec.png)

データベースが作成できました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/398131/26307e2e-30e0-ab0a-29b3-9a711eb3272c.png)

### クエリ実行
Develop → + → SQLスクリプトを選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/398131/3a5a8469-d4a4-7c9e-c0ea-9f0b1e806302.png)

開いたSQLスクリプトで、「次に接続」に「組み込み」、「データベースの使用」に先ほど作成したデータベース名を指定します。
その後、以下のクエリを入力して「実行」をクリックし、データベースのマスターキーと認証情報、外部データソース、外部ファイルフォーマット、外部テーブルを一度に作成します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/398131/8c85d7df-4ec8-b3d7-15d2-a9d2b7c9549d.png)

```
-- DBのマスターキーを作成
CREATE MASTER KEY;
GO

-- 認証情報を作成
CREATE DATABASE SCOPED CREDENTIAL blog_credential
WITH IDENTITY = 'Managed Identity';
GO

-- 外部データソースを作成
CREATE EXTERNAL DATA SOURCE blog_data_source
WITH (
          LOCATION = 'abfss://blog-fs@blogsaccount.dfs.core.windows.net',
          CREDENTIAL = blog_credential
);
GO

-- 外部ファイルフォーマットを作成
CREATE EXTERNAL FILE FORMAT blog_file_format
WITH (
          FORMAT_TYPE = PARQUET
);
GO

-- 外部テーブルを作成
CREATE EXTERNAL TABLE blog_nursery_school (
          [通し番号] INT,
          [設置主体] NVARCHAR(255),
          [保育園名] NVARCHAR(255),
          [所在地] NVARCHAR(255),
          [電話番号] NVARCHAR(255),
          [開設年月日] NVARCHAR(255),
          [保育年齢] NVARCHAR(255),
          [0歳（運用定員）] NVARCHAR(255),
          [1歳（運用定員）] NVARCHAR(255),
          [2歳（運用定員）] NVARCHAR(255),
          [3歳（運用定員）] NVARCHAR(255),
          [4歳（運用定員）] NVARCHAR(255),
          [5歳（運用定員）] NVARCHAR(255),
          [計（運用定員）] NVARCHAR(255),
          [基本保育時間] NVARCHAR(255),
          [延長保育時間（月～金）] NVARCHAR(255),
          [対象年齢] NVARCHAR(255),
          [運営主体] NVARCHAR(255)
)
WITH (
         LOCATION = '/output/',
         DATA_SOURCE = blog_data_source,
         FILE_FORMAT = blog_file_format
);
GO
```

### 外部テーブル確認
Data → Workspaceを選択し、先ほど作成したデータベースを展開していくと、外部テーブル、外部データソース、外部ファイルフォーマットが作成されているのが確認できました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/398131/61064feb-07ec-0325-f3f3-c58a86991f2c.png)

外部テーブルに対してクエリを実行し、ストレージアカウントに作成したoutputフォルダ配下のparquetファイルのデータを取得できるか確認します。
またSQLスクリプトを開き、「次に接続」に「組み込み」、「データベースの使用」に先ほど作成したデータベース名を指定します。
`select * from 外部テーブル名`を実行したところ、無事csvファイルに格納されていたデータを全件確認することができました！
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/398131/c5619ec4-8728-70fd-1d54-cbb1699b5d20.png)

### Power BIレポート作成
Power BI Desktopを起動して「データの変換」を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/398131/d6e11fb4-26fc-c84f-20f0-c456bf2e4c0e.png)

データを取得で「Azure Synapse Analytics SQL」を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/398131/9f2e9427-fb26-f1a8-16f2-395bd9fc1825.png)

以下の情報を入力して「OK」をクリックします。
サーバー：synapseワークスペース名-ondemand.sql.azyresynapse.net（※SynapseワークスペースのサーバーレスSQLエンドポイント）
データベース：上記で作成した任意のデータベース名
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/398131/af534735-5da6-5329-b698-309634b92300.png)

テーブルを選択すると、データが取得できました！
想定通りのデータが取得できていることを確認したら、「OK」をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/398131/dbfb4d7b-047a-e1eb-ae80-63381d8faad4.png)

閉じて適用をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/398131/e0c8b482-c8d0-9ab8-6508-a186c63731ca.png)

データが取得できたので、後は自由にビジュアルを作成します。
今回はテーブルを用意し、通し番号と保育園名、所在地、電話番号をリストアップしてみました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/398131/5c8220d4-3912-29e4-b26e-ab4eb20be7e0.png)


## まとめ
今回の記事ではSynapse内に外部テーブルを作成し、Power BIから外部テーブルを参照できるようにしてみました。
この記事がどなたかのお役に立てば幸いです。