SE の雑記

Build 2020 の開催が深夜なのでポータルを見ていたら、Azure Synapse Analytics (ワークスペース) が利用できるようになっていました。

今まで触れていなかったので、操作しながらのメモを。

Synapse Analytics (ワークスペース) のドキュメントについては、Azure Synapse Analytics (ワークスペース プレビュー) とは で公開されていますので、調査が必要な際には、こちらを確認するとよいかと思います。

Azure Synapse Analytics ワークスペースとは？

Azure Synapse Analytics (旧称 SQL DW) アーキテクチャ / Azure Synapse Analytics のチート シート から確認をすることができますが、Synapse Analytics は複数のコンポーネントで構成されています。

• SQL Analytics:完全な T-SQL ベースの分析
  • SQL プール (プロビジョニング済み DWU での従量課金) ? 一般公開
  • SQL オンデマンド (処理された TB 単位の課金) ? (プレビュー)
• Spark:緊密に統合された Apache Spark (プレビュー)
• データ統合:ハイブリッド データ統合 (プレビュー)
• Studio: 統一ユーザー エクスペリエンス。 (プレビュー)

Synapse Analytics はデータの分析を行うために様々な機能が含まれています。

この中には、次のような機能が含まれています。

• T-SQL ベースで分析を行う機能
• Spark ベースで分析を行う機能
• ETL/ELT やデータ可視化を行う機能
• データ操作を行うための統一的な UX

ワークスペースのデプロイ

T-SQL ベースで MPP 型の RDBMS に分析を行うための機能が、従来まで SQL Data Warehouse (SQLDW) と呼ばれていた機能となり、この部分が SQL Pool として、Synapse Analytics というブランドに統合が行われ、Synapse Analytics の SQL プールとして単体でデプロイができるようになっています。
ワークスペースの機能は不要で、MPP の RDBMS が必要な場合は、従来型の SQL DW としてデプロイすることができます。
(Synapse ワークスペースに統合した形で MPP 型の RDBMS を作成したい場合は、ワークスペースを作成してから、SQL プールを作成することになります。)

それ以外の分析の機能については、Synapse Analytics ワークスペースの機能として展開を行うことになります。

MPP の RDBMS は必要ないが、Azure Data Lake Storage Gen2 (ADL Gen2) を使用したデータレイク (Data Lake) を作成し、効率的に分析を行いたいというような場合は、Synapse Analytics ワークスペースをデプロイすることで、実現することができます。

実際の Synapse Anlytics ワークスペースのデプロイの画面がこちらです。
(現時点では、日本にはデプロイすることができないようでアジア パシフィックについては「オーストラリア東部」「東南アジア」の 2 個所を選択することができるようになっていました)

デプロイ時には ADL Gen2 を使用することが必須となっています。
新規作成を行った画面がこちらになるのですが、指定したアカウントで ADL Gen2 のストレージアカウントが作成され、ファイルシステムの名称でコンテナーが作成されるようです。

ここで指定した、ADL Gen2 が Synapse ワークスペースでリンクされたサービスとして認識され、ワークスペース内のデータや Azure Data Factory (ADF) で操作可能なリソースとしてデフォルトで設定が行われていました。

デプロイはシンプルな画面ですので、マネージド ID の有効化なども実施することができます。

デプロイ時のセキュリティ面の情報として、次のドキュメントへのリンクが設定されていましたので、プライベートエンドポイントや、ADL Gen2 のセキュリティに関しての考慮はこれらのドキュメントを確認するとよさそうですね。

• Secure your Synapse workspace (preview)
• Azure Synapse Analytics のマネージド仮想ネットワーク (プレビュー)

デプロイを実施すると、次のような環境が出来上がります。

• ADL Gen 2 がリンクされた Synapse Analytics ワークスペース
• SQL プールに SQL オンデマンドが作成

料金

料金についても https://azure.microsoft.com/ja-jp/pricing/details/synapse-analytics/ が最新の情報に更新されています。

SQL DW 部分は従来通りですが、ワークスペース部分の機能の料金については、次の 3 種類で構成されるようです。
(これらに加えて、Data Lake として使用する ADL Gen2 も別途必要になりますが)

• SQL オンデマンド
• Spark Pool
• Synapse Pipelines

SQL プール

SQL プールは T-SQL でアクセスすることが可能な環境となります。

TDS (Tabular Data Stream) によりアクセス可能なツールであれば、この SQL プールに接続をしてデータの分析を行うことができます。

SQL Server に接続可能なツールであれば、一般的なものでしたら、SQL プールに接続できるかと思います。

実際に利用するまできちんと理解できていなかったのですが、Synapse Analytics ワークスペースをデプロイすると、デフォルトでワークスペース名で、

• SQL エンドポイント
  • SQL Database の論理サーバー相当のエンドポイントとなり、SQL プールの MPP のデータベースに接続
    • このエンドポイントの論理サーバー配下に Single Database を作成することはできないようです。
• SQL オンデマンド エンドポイント
  • SQL オンデマンドのアクセスポイント

の 2 種類のエンドポイントが作成されます。

どちらのエンドポイントも T-SQL ベースでアクセスができる環境ですので、SSMS 等の TDS でアクセスできるツールから接続することができます。
(SSMS でなくても、SQL Server ベースの環境に接続可能なツールであれば、操作は可能かと)

初期状態では、どちらのエンドポイントでもデータベースは存在していませんので、必要に応じてデータベースを作成します。

SQL プール (SQL エンドポイント) にデータベースを作成

デフォルトで SQL オンデマンドの SQL プールは作成されており、SQL プールから新規をクリックすると、ワークスペースの SQL エンドポイント配下に MPP の RDBMS を作成することができます。

DB の復元にも対応しており、すでに作成している SQL プールからデータベースをリストアするということもできるようです。

Synapse ワークスペースではなく、SQL プール単体で作成 (または、SQL DW として作成) していた場合には、それらの DB を、ワークスペースの SQL プールとしてリストアすることで、ワークスペースのエンドポイントに DB を移行するということもできました。

実際に復元ポイントからリストアしてみたものがこちらです。

従来までの方法で、SQL プールを作成すると「SQL Database の論理サーバーに関連づいた DB」として、作成されていましたが、ワークスペースの SQL プールから作成した場合は、SQL プールに関連づいたデータベースとして作成が行われるようです。

一時停止なども行うことができますので、後の使用感については、通常の SQL プールと同じかと思います。

SQL オンデマンド (SQL オンデマンド エンドポイント) にデータベースを作成

こちらは、Synapse Analytics ワークスペースからの新機能ですね。

• クイック スタート:SQL オンデマンドの使用

SQL オンデマンドを MPP 型の RDBMS を作成することなく、ADL Gen2 や BLOB 上のデータを T-SQL で操作をすることができます。

SQL オンデマンドの操作方法

SSMS からも操作することができますが、今回は Synase Studio から操作を行いたいと思います。

Synapse Studio に接続をして、SQL Script を選択します。

デフォルトでは「SQL on-demand」という、SQL オンデマンドのプールが選択されていますので、このまま作業を行います。
ワークスペースに SQL プールを作成した場合は、その DB も選択ができるようになっています。

デフォルトでは master データベースのみが存在しており、この master では様々な操作が制限されているようですので、最初にデータベースを作成します。
(資格情報の作成ぐらいは master でもできるみたいですが、外部テーブル用のオブジェクトは作成できないようですので、おとなしくユーザーデータベースを作成した方が良いかと)

CREATE DATABASE OndemandDB

CREATE DATABASE を行うと、SQL オンデマンド エンドポイントで接続している論理サーバーの配下に DB が実際に作成されますので、以降はこの DB を選択した状態で作業を行います。

外部データへのアクセス

SQL オンデマンドはメタデータ管理用のデータベースを使用することはできますが、このデータベース自身にはテーブルの定義のみを格納し、実データについては外部ストレージに格納することになります。

冒頭で紹介した、クイック スタート:SQL オンデマンドの使用 の内容に沿って作業を実施していくことになりますが、ストレージへのアクセス権周りについては少し気を付けておく必要がありそうです。

SQL オンデマンドがストレージのファイルにアクセスするためのアクセス制御についてですが、Azure Synapse Analytics で SQL オンデマンド (プレビュー) のストレージ アカウント アクセスを制御する の記事を参照しましょう。

Synapse Studio から SQL を実行する場合、Azure AD ユーザーによって、アクセスが行われることになります。

サポートされているストレージと承認の種類 に記載されていますが、ADL Gen2 の場合は、「SAS」または「ユーザー ID」により、ストレージにアクセスが行われます。

BLOB ストレージのファイルに対してクエリを実行する場合には、次のように OPEN ROWSET を使用したクエリの実行を行うことが可能です。

SELECT  *
FROM OPENROWSET
(
    BULK 'https://synapseadlgen2.blob.core.windows.net/adlfs/'
    , FORMAT = 'CSV'
    , FIELDTERMINATOR =','
    , ROWTERMINATOR = '\n'
    , FIRSTROW = 2
)
WITH
(
    C1 int,
    C2 int,
    C3 int
) AS T

ストレージアカウントへのアクセスについて

Synapse Studio から実行した際に、次のようなエラーが発生する場合は、ストレージアカウントの IAM の設定を見直してみてください。

Failed to execute query. Error: Content of directory on path 'https://synapseadlgen2.blob.core.windows.net/adlfs/' cannot be listed.
Total execution time: 00:00:00.821

オンデマンド SQL の資格情報 (Credentials) には、デフォルトでは次の 2 種類が登録されています。

(Managed Identity はワークスペースマネージド ID の設定によって作成有無の状況が変わるかもしれませんね)

資格情報を作成する に記載されていますが、SQL オンデマンドではデフォルトでは AAD パススルーにより「UserIdentity」によるストレージアカウントのアクセスが行われます。

注意

このセクションのサンプルを正常に実行するには、SAS トークンを使用する必要があります。

SAS トークンの使用を開始するには、この記事に説明されている UserIdentity を削除する必要があります。

SQL オンデマンドでは、既定で常に AAD パススルーが使用されます。

既定では、AAD パススルーされているため、ストレージにアクセスを行う際には Synapse Studio にアクセスしている AAD のログイン権限が使用されます。

そのため、Synapse Studio にアクセスしている AAD アカウントが該当のストレージにアクセスが許可されていない場合は上述のようなエラーが発生することになります。

今回は使用している AAD のログインにひとまず「ストレージ BLOB データの所有者」を付与して、アクセス可能にしています。

必要となるアクセス権については Azure Synapse Analytics で SQL オンデマンド (プレビュー) のストレージ アカウント アクセスを制御する に記載されていますので詳細はこちらを確認しましょう。

重要

ID を使用してデータにアクセスするには、Storage Blob データの所有者/共同作成者/閲覧者のロールを持っている必要があります。 ストレージ アカウントの所有者であっても、Storage Blob データのいずれかのロールに自分自身を追加する必要があります。

Azure Data Lake Store Gen2 でのアクセス制御の詳細については、「Azure Data Lake Storage Gen2 のアクセス制御」という記事をご覧ください。

AAD 認証を使用するケースでは、「Microsoft アカウント」「他の AAD テナント」のユーザーでは検証が難しい機能もありそうでしたので、AAD 認証のテストを実施する場合には、Synapse Analytics ワークスペースのサブスクリプションで使用しているテナントの AAD アカウントを使用した方が、変にアクセス権で悩まずに検証ができるかと。

正常にクエリが実行できれば、ストレージ内のファイルを検索することができます。

外部テーブルのアクセスの再利用

頻繁に、データを確認する必要があるのであれば、ビューとして OPENROWSET を指定したクエリを作成する / 外部テーブルとして作成する ことで、ストレージの情報を確認する際の再利用性が向上します。

ビューであれば、次のようなクエリで作成できます。

CREATE VIEW V_StorageData
AS
SELECT  *
FROM OPENROWSET
(
    BULK 'https://synapseadlgen2.blob.core.windows.net/adlfs/data/'
    , FORMAT = 'CSV'
    , FIELDTERMINATOR =','
    , ROWTERMINATOR = '\n'
    , FIRSTROW = 2
)
WITH
(
    C1 int,
    C2 int,
    C3 int
) AS T

外部テーブルの参照については、Create and use external tables in SQL on-demand (preview) using Azure Synapse Analytics を参照するのが良いかと思います。

日本語のドキュメントと英語のドキュメントだと記載内容が異なっており、英語のドキュメントの SAS を使用した手順であれば、外部テーブルのアクセスを問題なく検証することができます。

英語版だと DB スコープの資格情報で SAS を登録してアクセスする流れとなっており、この手順でしたら実行可能でした。

User Identity / Managed Identity の接続はまだうまくできていないので、ここは要検証ですが。

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Put very strong password here!'
GO

CREATE DATABASE SCOPED CREDENTIAL [ADLGen2]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = '<SAS Signature>'
GO

CREATE EXTERNAL DATA SOURCE [CsvDataSource] WITH (
    LOCATION = 'https://synapseadlgen2.blob.core.windows.net/adlfs',
    CREDENTIAL = ADLGen2
    );
GO

CREATE EXTERNAL FILE FORMAT CSVFileFormat
WITH (  
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (
        FIELD_TERMINATOR = ',',
        STRING_DELIMITER = '"'
    )
);
GO

CREATE EXTERNAL TABLE csvExternamTable
(
    C1 int,
    C2 int,
    C3 int
)
WITH (
    LOCATION = 'data/',
    DATA_SOURCE = CsvDataSource,
    FILE_FORMAT = CSVFileFormat
);
GO

SELECT * FROM csvExternamTable
GO

外部テーブルの利用は CETAS (Create External Table As SELECT) も使用することができます。

(実行していたらエラー出ていたのですが、ADL Gen2 にエクスポートはできていました)

CREATE EXTERNAL TABLE [dbo].[CETAS] WITH (
        LOCATION = 'export/',
        DATA_SOURCE = [CsvDataSource],
        FILE_FORMAT = [CSVFileFormat]
) AS
SELECT * FROM csvExternamTable

従来までは、SQL プールを作成して PolyBase の機能等を活用することで、Data Lake のファイルの検索を実施することができましたが、SQL オンデマンドを使用した場合は、RDBMS を作成することなく、Data Lake のデータを検索することが可能となります。

ビューや外部テーブルの情報については、メタデータとしてデータベースに保存しておくことができるので、再利用性の高いクエリの準備 (SQL スクリプトを Synapse Sutdio に保存しておくこともできるので組み合わせることで再利用性がさらに高まるかと) や、CETAS を使用することで、途中結果や最終結果をフォーマットを変えながら Data Lake に保存しておくことも可能となっています。

もう一つ、ユニークなのは Azure Synapse Analytics の共有メタデータ ではないでしょうか。

Spark プールに次のようなデータベースを作成したとします。

SQL オンデマンドと Spark プールはメタデータの同期が有効になっており、Spark プールに作成したデータベースについては、SQL オンデマンドのエンドポイントの論理サーバー上にデータベースとして認識され、Spark で作成したテーブルについては、外部テーブルとして認識されます。

(認識にはタイムラグがあるので、しばらくすると SQL オンデマンド上に認識されます。)

上記の外部テーブルは Synapse ワークスペースで使用している ADL Gen2 の Spark のデータを格納しているディレクトリを使用した外部テーブルとして作成されているようですね。

(今回、SparkSQL で LOCATION を指定していないので既定のロケーションになっているようですが)

SQL オンデマンドと Spark プールで作成したテーブルを SQL Pool から参照するということもできるようなのですが、それを実現するためのメタデータ同期の有効化がわかっておらず、今のところは、SQL オンデマンドから SparkSQL のテーブルの参照までが検証できているところとなっています。

ファイル情報の取得

外部データソースのファイルへのアクセスですが、データを取得する以外にも、ファイルのプロパティを確認する機能なども利用することができます。

SELECT  *,T.filename(),T.filepath()
FROM OPENROWSET
(
    BULK 'https://synapseadlgen2.blob.core.windows.net/adlfs/data/'
    , FORMAT = 'CSV'
    , FIELDTERMINATOR =','
    , ROWTERMINATOR = '\n'
    , FIRSTROW = 2
)
WITH
(
    C1 int,
    C2 int,
    C3 int
) AS T

Samples for Azure Synapse Analytics (workspace preview) にサンプルが公開されるようですので、こちらも適宜ウォッチしていきたいですね。