目的

Azure Synapse AnalyticsでSQLの実行結果を一時ファイルとして保存したい。
→ サーバーから直接クエリを実行するとセッション管理が面倒 + メモリが足りない、、、

これを解決したい！！

実装するたびにドキュメントを読んでいたのでメモついでに投稿。

前提条件

SQLプール：専用SQLプール

参考サイト

(公式ドキュメント)
Synapse SQL で外部テーブルを使用する
 CREATE EXTERNAL DATA SOURCE (Transact-SQL)
CREATE EXTERNAL TABLE AS SELECT (Transact-SQL)
WASB (レガシ) を使用した Azure Blob Storage への接続
 Azure BLOB ファイルシステムドライバー (ABFS): Hadoop 専用の Azure Storage ドライバー
 Azure Data Lake Storage Gen1 - Azure Databricks

手順

手順としては下記になります。

SASトークンを取得またはストレージアカウントのアクセスキーを取得
クレデンシャル(認証情報)の作成
External Data Source(外部データソース)の作成
External File Format(外部ファイルフォーマット)の作成
外部テーブルとして保存

大雑把には、Synapse Analyticsの出力データをBlobに出力するために外部テーブルを作成したい
→ 外部テーブルを作成するために外部データソース(External Data Source)が必要
→ 外部データソース(External Data Source)を作成するためにはクレデンシャルが必要
という感じです。

External File Format(外部ファイルフォーマット)の作成についてはここでは省略します。

クレデンシャル(認証情報)の作成

ここでいうクレデンシャルとは、Synapse AnalyticsでExternal Data Sourceを作成するために必要なものになります。

SASトークンを利用する場合

まず、SASトークンをBlob Storageから取得します。具体的な方法はググるかchat-gptに聞けば分かります。
SASトークンを取得する際のアクセス許可設定は必要に応じて選んでください。
取得したSASトークンを大事に保管します。(Key Vaultとか)
注意点ですが、SASトークンが失効すると当然エラーになるので、保守は気にしたほうが良さそうです。

SASトークンを利用する場合、次のSQLでクレデンシャルを登録します。

CREATE DATABASE SCOPED CREDENTIAL <credential_name>
WITH
    IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = '<SAS_token>';

credential_nameにクレデンシャル名（自分で決める）、SAS_tokenに先ほど取得したSASトークンを入力します。

ストレージアカウントのアクセスキーを利用する場合

まず、アカウントアクセスキーを取得します。ポータルのストレージアカウントから取得できます。
取得したアクセスキーを大事に保管します。(Key Vaultとか)

次のSQLでクレデンシャルを登録します。

CREATE DATABASE SCOPED CREDENTIAL <credential_name>
WITH
    IDENTITY = 'user',
    SECRET = '<storage_account_access_key>';

作成したクレデンシャルの確認

ちゃんと作成できているかは以下のSQLで確認できます。

SELECT *
FROM sys.database_scoped_credentials
WHERE name = '<credential_name>';

External Data Source(外部データソース)の作成

クレデンシャルを作成したら、外部データソースを作成します。
下記のSQLで外部データソースを登録することができます。

CREATE EXTERNAL DATA SOURCE [<data_source_name>] WITH 
(	TYPE = HADOOP,
	LOCATION = '<prefix>://<path>',
	CREDENTIAL = [<credential_name>]
);

data_source_nameは自分で決めるやつです。

prefixとpathはぞれぞれ下記のようになります。

外部データソース	prefix	path
Azure Data Lake Store Gen 1	adl	storage_account.azuredatalake.net
Azure Data Lake Store Gen2	abfs[s]	container@storage_account.dfs.core.windows.net
Azure V2 ストレージアカウント	wasb[s]	container@storage_account.blob.core.windows.net

(参照元：CREATE EXTERNAL DATA SOURCE (Transact-SQL))

どのprefixを使うか問題に関しては下記のドキュメントを読む限りabfsが推奨されているようです。
Azure BLOB ファイルシステムドライバー (ABFS): Hadoop 専用の Azure Storage ドライバー
 WASB (レガシ) を使用した Azure Blob Storage への接続
 Azure Data Lake Storage Gen1 - Azure Databricks

確認方法は以下。

SELECT *
FROM sys.external_data_sources
WHERE type_desc = 'HADOOP'
AND name = '<data_source_name>';

外部テーブルを作成する

SELECT結果を外部テーブルとして作成します。
CETAS(Create External Table As Select)あたりで検索すると記事がヒットします。

CREATE EXTERNAL TABLE { [ [ database_name . [ schema_name ] . ] | schema_name . ] table_name }
    [ (column_name [ , ...n ] ) ]
    WITH (
        LOCATION = '<ディレクトリ構造>' ,
        DATA_SOURCE = <external_data_source_name> ,
        FILE_FORMAT = <external_file_format_name>
    )
    AS <select_statement>
[;]

終わり

これでblobにファイルフォーマットで指定した形でデータが出力されます。
Synapseのスペックによってファイルの数は変わります。

Azure Synapse Analyticsのクエリ実行結果をAzure blob storageのファイルとして作成する

目的