將來自 SQL Database 的參考資料用於 Azure 串流分析作業

發行項
03/11/2024

Azure 串流分析支援以 Azure SQL Database 作為參考資料輸入的來源。在 Azure 入口網站和 Visual Studio 中，您可以搭配串流分析工具使用 SQL Database 作為串流分析作業的參考資料。本文會示範這兩種方法的執行方式。

Azure 入口網站

使用下列步驟來使用 Azure 入口網站將 Azure SQL Database 新增為參考輸入來源：

入口網站必要條件

建立串流分析作業。
建立儲存體帳戶以供串流分析作業使用。

重要

Azure 串流分析會保留此儲存體帳戶內的快照集。設定保留原則時，請務必確保所選的時間範圍有效地包含串流分析作業所需的復原持續時間。
建立具有資料集的 Azure SQL Database，以作為串流分析作業的參考資料。

定義 SQL Database 參考資料輸入

在您的串流資料作業中，選取 [工作拓樸] 底下的 [輸入]。按一下 [新增參考輸入] 並選擇 [SQL Database]。
填寫串流分析輸入設定。選擇資料庫名稱、伺服器名稱、使用者名稱和密碼。如果您想要讓參考資料輸入定期重新整理，請選擇 [開啟] 來以 DD:HH:MM 格式指定重新整理頻率。如果您具有重新整理頻率較短的大型資料集。差異查詢可讓您擷取在開始時間 @deltaStartTime 和結束時間 @deltaEndTime 內插入或刪除的 SQL Database 中的所有資料列，藉此追蹤參考資料內的變更。

請參閱差異查詢。

When SQL Database is selected, the SQL Database New input page appears. There is a configuration form in the left pane, and a Snapshot query in the right pane.

在 SQL 查詢編輯器中測試快照集查詢。如需詳細資訊，請參閱使用 Azure 入口網站的 SQL 查詢編輯器進行連線並查詢資料

在作業設定中指定儲存體帳戶

瀏覽到 [設定] 底下的 [儲存體帳戶設定]，然後選取 [新增儲存體帳戶]。

Storage account settings is selected in the left pane. There is an Add storage account button in the right pane.

啟動工作

在您已設定其他輸入、輸出及查詢之後，便可以啟動串流分析作業。

Visual Studio 適用的工具

使用下列步驟來使用 Visual Studio 將 Azure SQL Database 新增為參考輸入來源：

Visual Studio 必要條件

安裝適用於 Visual Studio 的串流分析工具。支援下列其中一個 Visual Studio 版本：
- Visual Studio 2015
- Visual Studio 2019
熟悉適用於 Visual Studio 的串流分析工具快速指南。
建立儲存體帳戶。

重要

Azure 串流分析會保留此儲存體帳戶內的快照集。設定保留原則時，請務必確保所選的時間範圍有效地包含串流分析作業所需的復原持續時間。

建立 SQL Database 資料表

使用 SQL Server Management Studio 來建立資料表以儲存您的參考資料。請參閱使用 SSMS 設計您的第一個 Azure SQL 資料庫以取得詳細資料。

用於下列範例中的範例資料表是建立自下列陳述式：

create table chemicals(Id Bigint,Name Nvarchar(max),FullName Nvarchar(max));

選擇您的訂用帳戶

在 Visual Studio 的 [檢視] 功能表上，選取 [伺服器總管]。
以滑鼠右鍵按一下 [Azure]，選取 [連線到 Microsoft Azure 訂用帳戶]，然後以您的 Azure 帳戶登入。

建立串流分析專案

選取 [檔案] > [新增專案]。
在左側的範本清單中，選取 [串流分析]，然後選取 [Azure 串流分析應用程式]。
輸入專案的 [名稱]、[位置] 和 [解決方案名稱]，然後選取 [確定]。

定義 SQL Database 參考資料輸入

建立新輸入。
按兩下 [方案總管] 中的 [Input.json]。
填寫 [串流分析輸入設定]。選擇資料庫名稱、伺服器名稱、重新整理類型，以及重新整理頻率。以 DD:HH:MM 的格式指定重新整理頻率。

如果您選擇 [僅執行一次] 或 [定期執行]，系統會在 [Input.json] 檔案節點底下的專案中產生名為 [Input Alias].snapshot.sql 的 SQL 程式碼後置檔案。

如果您選擇 [搭配差異定期重新整理]，系統會產生兩個 SQL 程式碼後置檔案：[Input Alias].snapshot.sql 和 [Input Alias].delta.sql。
在編輯器中開啟該 SQL 檔案，並寫入 SQL 查詢。
如果您是使用 Visual Studio 2019，且已安裝 SQL Server Data Tools，便可以按一下 [執行] 來測試查詢。精靈視窗將會顯示以協助您連線至 SQL Database，而查詢結果則會出現在底部視窗中。

指定儲存體帳戶

開啟 JobConfig.json 以指定儲存 SQL 參考快照集的儲存體帳戶。

Stream Analytics Job Configure Configuration is shown with default values. The Global Storage Settings are highlighted.

於本機測試並部署到 Azure

在將作業部署到 Azure 之前，您可以在本機針對即時輸入資料測試查詢邏輯。如需此功能的詳細資料，請參閱使用適用於 Visual Studio 的 Azure 串流分析工具 (預覽) 在本機測試即時資料。完成測試之後，請按一下 [提交至 Azure]。請參考使用適用於 Visual Studio 的 Azure 串流分析工具建立串流分析作業快速入門來了解如何啟動作業。

差異查詢

使用差異查詢時，建議使用 Azure SQL Database 中的時態表。

在 Azure SQL Database 中建立時態表。

   CREATE TABLE DeviceTemporal
   (
      [DeviceId] int NOT NULL PRIMARY KEY CLUSTERED
      , [GroupDeviceId] nvarchar(100) NOT NULL
      , [Description] nvarchar(100) NOT NULL
      , [ValidFrom] datetime2 (0) GENERATED ALWAYS AS ROW START
      , [ValidTo] datetime2 (0) GENERATED ALWAYS AS ROW END
      , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
   )
   WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DeviceHistory));  -- DeviceHistory table will be used in Delta query

撰寫快照集查詢。

使用 @snapshotTime 參數來指導串流分析執行階段根據系統時間從有效的 SQL Database 時態表中取得參考資料集。如果您未提供此參數，則可能會因時鐘誤差而取得不正確的基底參考資料集。完整快照集查詢的範例如下所示：
```
   SELECT DeviceId, GroupDeviceId, [Description]
   FROM dbo.DeviceTemporal
   FOR SYSTEM_TIME AS OF @snapshotTime
```
撰寫差異查詢。

此查詢會擷取 SQL Database 中在開始時間 @deltaStartTime 和結束時間 @deltaEndTime 內插入或刪除的所有資料列。差異查詢必須傳回和快照集查詢相同的資料行，以集 operation資料行。此資料行會定義是否在 @deltaStartTime 與 @deltaEndTime 之間插入或刪除資料列。如果記錄已插入，結果的資料列會被標示為 1；如果已刪除，則會被標示為 2。查詢也必須新增來自 SQL Server 端的浮水印，以確保系統能適當地擷取差異期間中的所有更新。在沒有浮水印的情況下使用差異查詢，可能會導致不正確的參考資料集。

針對已更新的記錄，時態表會透過擷取插入和刪除作業來進行記錄。串流分析執行階段接著便會將差異查詢的結果套用到先前的快照集，以將參考資料保持為最新狀態。差異查詢的範例如下所示：
```
   SELECT DeviceId, GroupDeviceId, Description, ValidFrom as _watermark_, 1 as _operation_
   FROM dbo.DeviceTemporal
   WHERE ValidFrom BETWEEN @deltaStartTime AND @deltaEndTime   -- records inserted
   UNION
   SELECT DeviceId, GroupDeviceId, Description, ValidTo as _watermark_, 2 as _operation_
   FROM dbo.DeviceHistory   -- table we created in step 1
   WHERE ValidTo BETWEEN @deltaStartTime AND @deltaEndTime     -- record deleted
```
請注意，除了差異查詢之外，串流分析執行階段可能會定期執行快照集查詢以儲存檢查點。
重要

使用參考資料差異查詢時，請勿多次對時態性參考資料表進行相同的更新。這可能會導致產生不正確的結果。以下是可能導致參考資料產生不正確結果的範例：
```
 UPDATE myTable SET VALUE=2 WHERE ID = 1;
 UPDATE myTable SET VALUE=2 WHERE ID = 1;      
```
正確的範例：
```
 UPDATE myTable SET VALUE = 2 WHERE ID = 1 and not exists (select * from myTable where ID = 1 and value = 2);
```
這可確保不會執行重複的更新。

測試查詢

請務必確認您的查詢會傳回串流分析作業將作為參考資料使用的預期資料集。若要測試查詢，請移至入口網站上 [工作拓撲] 區段底下的 [輸入]。您接著可以選取您 SQL Database 參考輸入上的 [範例資料]。在範例可供使用之後，您便可以下載該檔案，並檢查傳回的資料是否與預期一致。如果您想要將開發和測試反覆項目最佳化，建議您使用適用於 Visual Studio 的串流分析工具 \(部分機器翻譯\)。您也可以使用您偏好的任何其他工具，來先確認查詢能從 Azure SQL Database 傳回正確的結果，再將其用於您的串流分析作業。