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

Posted at 2024-12-17

Azureでシンプルなデータ分析基盤を作る検証をしています。
Azureでデータ分析基盤作ってみた - ①Synapseの構築、Azureでデータ分析基盤作ってみた - ②ADFの構築とETL処理という記事の続編として、Synapseでの外部テーブル作成について記載します。

構成

1つのリソースグループの中にストレージアカウント、ADF、Synapseワークスペースを作成します。
ストレージアカウント(ADLS Gen2)に置いたcsvファイルをparquetファイルに変換すると同時に、データ型の変換も行います。
SynapseではサーバーレスSQLプールを使って外部テーブルを作成し、Power BIから外部テーブルを参照できるようにします。

作業内容

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

1. Synapseの構築
リソースグループ、ストレージアカウント、Synapseワークスペースを構築
2. ADFの構築とETL処理
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データベースを選択します。

SQLプール型の選択で「サーバーレス」を選択し、任意のデータベース名を指定して「作成」をクリックします。

データベースが作成できました。

クエリ実行

Develop → + → SQLスクリプトを選択します。

開いたSQLスクリプトで、「次に接続」に「組み込み」、「データベースの使用」に先ほど作成したデータベース名を指定します。
その後、以下のクエリを入力して「実行」をクリックし、データベースのマスターキーと認証情報、外部データソース、外部ファイルフォーマット、外部テーブルを一度に作成します。

-- 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を選択し、先ほど作成したデータベースを展開していくと、外部テーブル、外部データソース、外部ファイルフォーマットが作成されているのが確認できました。

外部テーブルに対してクエリを実行し、ストレージアカウントに作成したoutputフォルダ配下のparquetファイルのデータを取得できるか確認します。
またSQLスクリプトを開き、「次に接続」に「組み込み」、「データベースの使用」に先ほど作成したデータベース名を指定します。
select * from 外部テーブル名を実行したところ、無事csvファイルに格納されていたデータを全件確認することができました！