Azure Purview で Azure SQL Database からストアドプロシージャの Lineage を抽出する

## はじめに

Azure Purview では、Azure Data Factory や Azure Synapse Analytics などからスキャンしたデータの遷移情報を、Lineage という機能で可視化できます。

その Lineage 機能が強化され、Azure SQL Database で実行されるストアドプロシージャの情報も可視化できるようになりました（2022 年 4 月現在プレビュー中）。
[Introducing dynamic lineage extraction from Azure SQL Databases in Azure Purview](https://azure.microsoft.com/en-us/blog/introducing-dynamic-lineage-extraction-from-azure-sql-databases-in-azure-purview/)

Microsoft の発表によると、下記のようなストアドプロシージャを実行した際の動的なメタデータを、Azure Purview で Lineage として表示できるようです。

- 実行状況
- クエリの影響を受けた行数
- ストアドプロシージャが実行されたクライアント
- ユーザ情報など

今回は、Azure SQL Database 上でストアドプロシージャを実行し、その結果を Azure Purview でスキャンして Lineage として表示されるか検証しました。

## 想定読者

- Azure Purview に Azure SQL Database のデータをスキャンした経験がある方
- Azure SQL Database でストアドプロシージャを作成・実行した経験がある方

上記のご経験がない場合は、下記のドキュメントを参照いただけると、以降の作業イメージが掴みやすいかと思います。
[Azure Purview の Azure SQL Database のデータベースに接続する](https://docs.microsoft.com/ja-jp/azure/purview/register-scan-azure-sql-database?tabs=managed-identity#authentication-for-a-scan)
[ストアド プロシージャの作成](https://docs.microsoft.com/ja-jp/sql/relational-databases/stored-procedures/create-a-stored-procedure?view=sql-server-ver15)

## 前提条件

Azure SQL Database で実行したストアドプロシージャを Lineage 表示する前提条件として、下記の全てを満たしている必要があります。

- Azure SQL Database で Azure AD 認証が構成されている
  - 後述のマネージド ID によるスキャンの認証設定を行う際に必須
  - 参考：[Configure and manage Azure AD authentication with Azure SQL](https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell)
- Azure Purview で Azure SQL Database のデータスキャンを作成する際に、マネージド ID による認証を構成する
  - 本検証ではシステム割り当てマネージド ID による認証を構成
  - 参考：[スキャンの認証](https://docs.microsoft.com/ja-jp/azure/purview/register-scan-azure-sql-database?tabs=managed-identity#authentication-for-a-scan)
- Azure AD アカウントを使用して Azure SQL Database にサインインし、Purview マネージド ID にアクセス許可を割り当て、DB マスターキーを作成する
  - 本検証では`db_owner`権限を設定
  - 参考：[系列抽出でスキャンを設定するための前提条件](https://docs.microsoft.com/ja-jp/azure/purview/register-scan-azure-sql-database?tabs=managed-identity#prerequisites-for-setting-up-scan-with-lineage-extraction)

## Lineage 抽出を有効にしたスキャンの作成

Azure Purview Studio のデータマップから、Azure SQL Database のコレクションを参照し、「新しいスキャン」を選択します。
![Linege_1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1871738/105bf906-0029-812c-ec4c-9dabace19331.png)


スキャンの設定画面が表示されるので、下記の通り設定します。

- データベース名：前提条件を満たす Azure SQL Database サーバを入力
- 資格情報：`Azure Purview MSI（システム）`（マネージド ID による認証を構成した場合）
- 系列の抽出：`オン`

注意点として、**「系列の抽出」の項目をオンにしたスキャンは Purview アカウントにつき 1 つしか作成できません（2022 年 4 月現在）。**
「接続のテスト」を選択すると、Azure SQL Database の認証が正しく設定されているか検証されます。

「接続のテスト」に成功したことを確認し、「続行」を選択します。
![Linege_2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1871738/6df0ab3e-0cd5-9b9a-dbb7-72cec6593513.png)

スキャン対象となる Azure SQL Database のテーブルを選択し、「続行」を選択します。
![Linege_3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1871738/5ea12e38-863a-7625-6c09-a635f7c93912.png)

スキャンルールセットを選択し、「続行」を選択します。
本検証では、デフォルトで表示される`AzureSqlDataWarehouse`を使用します。
![Linege_4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1871738/bfddd774-7cc3-d6e5-7534-cd4aad210fdb.png)

スキャンのトリガーを選択します。
「定期的」ではスキャンのスケジューリングが可能ですが、今回の検証では「一度だけ」に設定し「続行」を選択します。
![Linege_5.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1871738/c12824da-a537-aa22-22c7-23644d563e0c.png)

スキャンの設定情報を確認し、「保存して実行」を選択します。
![Linege_7.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1871738/72409962-959c-a5b5-5635-a2bf9ecd06c8.png)

なお、スキャン対象の DB マスターキーが作成されていない場合、下記のメッセージが表示され、スキャンの保存および実行ができません。
`Master key must be updated or created before lineage can be collected from the Azure SQL Database`
![Linege_6.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1871738/18376111-8c7a-901d-5bad-1a05b056c17a.png)

この段階では、**既に Azure SQL Database 上に実行済みのストアドプロシージャがあったとしても、その Lineage は表示されません。**

ドキュメントを参照すると下記の記述があるため、スキャン作成後にストアドプロシージャを実行する必要があります。
`系列がキャプチャされるのは、正常にスキャンが設定された後に行われたストアド プロシージャの実行に対してです。 過去のストアド プロシージャ実行からの系列はキャプチャされません。`
[Lineage 抽出におけるトラブルシューティング](https://docs.microsoft.com/ja-jp/azure/purview/register-scan-azure-sql-database?tabs=managed-identity#troubleshooting-steps)

## Azure SQL Database でのストアドプロシージャ作成・実行

本検証では、SQL Server Management Studio（SSMS）を使用して Azure SQL Database 上にストアドプロシージャを作成し実行しました。
[SQL Server Management Studio を使用したストアドプロシージャの作成](https://docs.microsoft.com/ja-jp/sql/relational-databases/stored-procedures/create-a-stored-procedure?view=sql-server-ver15#SSMSProcedure)

検証の目的は Lineage 表示の確認のため、ストアドプロシージャはパラメータの指定等を行わず、テーブル内のデータを`SELECT`するだけの単純なものとしました。

```sql:SelectTestData.sql
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SelectTestData]
AS
BEGIN
    SET NOCOUNT ON
    SELECT *
    FROM dbo.PurchaseOrderDetail
END;
```

なお、データを取得する`PurchaseOrderDetail`テーブルは、Microsoft ドキュメント内のサンプルを流用し、テストデータは筆者が入力しました。

| PurchaseOrderID | LineNumber | ProductID | UnitPrice | OrderQty | ReceivedQty | RejectedQty | DueDate                 |
| --------------- | ---------- | --------- | --------- | -------- | ----------- | ----------- | ----------------------- |
| 1               | 1          | 1         | 1000.00   | 10       | 10          | 0           | 2022-01-01 00:00:00.000 |
| 2               | 2          | 2         | 2000.00   | 20       | 20          | 0           | 2022-01-02 00:00:00.000 |
| 3               | 3          | 3         | 3000.00   | 30       | 30          | 0           | 2022-01-03 00:00:00.000 |

上記のストアドプロシージャを SSMS にて作成・実行し、想定通り`PurchaseOrderDetail`テーブルのデータがすべて参照されることを確認しました。
![Linege_8.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1871738/d895b8f8-f4b3-daf2-fd0b-7f58473a3138.png)

ここまでで、Azure Purview で Azure SQL Database 上のストアドプロシージャを Lineage 表示する準備が整いました。

## Lineage 表示の確認

再度 Azure Purview Studio に戻り、Azure SQL Database のアセットを確認すると、先ほど実行したストアドプロシージャ`[dbo].[SelectTestData]`が含まれています。
![Linege_9.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1871738/5d682c96-f116-0292-4cb7-cf4b10e49ce4.png)

`[dbo].[SelectTestData]`のアセットを選択し、「系列」のメニューを確認すると、`PurchaseOrderDetail`テーブルに紐づいたストアドプロシージャであることが確認できます。
![Linege_10.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1871738/0b263716-72a1-b3e6-fb8f-18d2818175e4.png)

さらに、「プロパティ」のメニューから「関連するアセット」を選択すると、ストアドプロシージャ実行時の動的なメタデータを確認できます。
![Linege_11.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1871738/8ab3b701-899c-bb7a-42ea-6a329063cfc8.png)
![Linege_12.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1871738/048240d5-1d52-d7d8-5292-b4aed92b2334.png)

## Lineage 表示が可能なストアドプロシージャ実行

今回は単純に`SELECT`を行うだけのストアドプロシージャを実行し、Lineage 表示を確認しましたが、ドキュメントを確認する限りでは、テーブルあるいは DB 間のデータコピーといった処理も Lineage 抽出できるようです。
[Azure SQL Database アセットを検索し、ランタイムの系列を表示する](https://docs.microsoft.com/ja-jp/azure/purview/register-scan-azure-sql-database?tabs=sql-authentication#search-azure-sql-database-assets-and-view-runtime-lineage)

一方で、下記の注意点も記載されています。
`データベースで、ストアド プロシージャが何度も実行される大量のワークロードを処理している場合は、最新の実行だけがフィルター処理されます。 6 時間の時間枠内の早い段階で実行されるストアド プロシージャや、高いクエリ負荷を作成する実行インスタンスは抽出されません。 ストアド プロシージャの実行からの系列が見つからない場合は、サポートにお問い合わせください。`

この中で、**高いクエリ負荷を作成する実行インスタンスは、Lineage 抽出されない**という記載は若干抽象的です。

そのため、現段階ではストアドプロシージャ実行による負荷がどの程度であれば Lineage として抽出可能なのか、ユーザ側で判断する必要がありそうです。

## まとめ

今回は、Azure Purview にて Azure SQL Database 上で実行されたストアドプロシージャを Lineage 表示するプレビュー機能を検証しました。

認証や DB マスターキーの作成など、Azure SQL Database 周りの前提条件はありますが、ストアドプロシージャの実行情報やデータの流れを視覚的に把握できる便利な機能かと思います。

Azure Purview の Lineage 表示に対応するサービスは、リリースから拡充し続けているため、今後も注視していく予定です。