如何在 Azure AI 搜尋中編制來自 Azure SQL 的數據索引

發行項
03/10/2024

在本文中，瞭解如何設定索引器，以從 Azure SQL 資料庫或 Azure SQL 受控實例匯入內容，並讓它可在 Azure AI 搜尋中搜尋。

本文補充建立 索引器 ，其中包含 Azure SQL 特有的資訊。它會使用 REST API 來示範所有索引器通用的三部分工作流程：建立數據源、建立索引、建立索引器。

本文也提供：

描述 Azure SQL 索引器所支援的變更偵測原則，以便設定累加式索引編製。
常見問題（FAQ）區段，以取得功能相容性相關問題的解答。

注意

索引器無法進行即時數據同步處理。索引器最多每五分鐘可以重新編製數據表的索引。如果數據更新需要儘快反映在索引中，建議您直接推送更新的數據列。

必要條件

具有單一數據表或檢視中數據的 Azure SQL 資料庫，或具有公用端點的 SQL 受管理執行個體。

如果您的數據很大，或需要使用 SQL 的原生變更偵測功能進行累加式索引編製，請使用數據表。

如果您需要合併多個數據表的數據，請使用檢視。大型檢視不適用於 SQL 索引器。因應措施是建立新的數據表，只是為了擷取至 Azure AI 搜尋服務索引。您將能夠使用 SQL 整合式變更追蹤，這比高水印更容易實作。
讀取許可權。 Azure AI 搜尋支援 SQL Server 驗證，其中會在連接字串上提供使用者名稱和密碼。或者，您可以設定受控識別並使用 Azure 角色。

若要完成本文中的範例，您需要 REST 用戶端。

建立 Azure SQL 索引器的其他方法包括 Azure 入口網站中的 Azure SDK 或匯入數據精靈。如果您使用 Azure 入口網站，請確定已在 Azure SQL 防火牆中啟用所有公用網路的存取權，且用戶端可透過輸入規則存取。

定義數據源

數據源定義會指定要用來識別數據變更之索引、認證和原則的數據。數據源會定義為獨立資源，以便可供多個索引器使用。

建立資料來源或更新資料來源以設定其定義：

 POST https://myservice.search.windows.net/datasources?api-version=2020-06-30
 Content-Type: application/json
 api-key: admin-key

 {
     "name" : "myazuresqldatasource",
     "description" : "A database for testing Azure AI Search indexes.",
     "type" : "azuresql",
     "credentials" : { "connectionString" : "Server=tcp:<your server>.database.windows.net,1433;Database=<your database>;User ID=<your user name>;Password=<your password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;" },
     "container" : { 
         "name" : "name of the table or view that you want to index",
         "query" : null (not supported in the Azure SQL indexer)
         },
     "dataChangeDetectionPolicy": null,
     "dataDeletionDetectionPolicy": null,
     "encryptionKey": null,
     "identity": null
 }

為遵循 Azure AI 搜尋命名慣例的數據源提供唯一的名稱。
將「type」設定為 "azuresql" （必要）。
將「認證」設定為連接字串：
- 您可以從 Azure 入口網站取得完整存取權連接字串。使用 ADO.NET connection string 選項。設定使用者名稱和密碼。
- 或者，您可以指定未包含資料庫秘密的受控識別連接字串格式如下：Initial Catalog|Database=<your database name>;ResourceId=/subscriptions/<your subscription ID>/resourceGroups/<your resource group name>/providers/Microsoft.Sql/servers/<your SQL Server name>/;Connection Timeout=connection timeout length;。
如需詳細資訊，請參閱使用受控識別連線至 Azure SQL 資料庫索引器。

將搜尋欄位新增至索引

在搜尋索引中，新增對應至 SQL 資料庫中欄位的欄位。使用對等數據類型，確定搜尋索引架構與來源架構相容。

建立或更新索引，以定義將儲存資料的搜尋欄位：

POST https://[service name].search.windows.net/indexes?api-version=2020-06-30
Content-Type: application/json
api-key: [Search service admin key]
{
    "name": "mysearchindex",
    "fields": [{
        "name": "id",
        "type": "Edm.String",
        "key": true,
        "searchable": false
    }, 
    {
        "name": "description",
        "type": "Edm.String",
        "filterable": false,
        "searchable": true,
        "sortable": false,
        "facetable": false,
        "suggestions": true
    }
  ]
}

建立可唯一識別每個搜尋文件的檔索引鍵字段（“key”： true）。這是搜尋索引中唯一需要的欄位。一般而言，數據表的主鍵會對應至索引鍵字段。檔索引鍵必須是唯一且非 Null。這些值可以是源數據中的數值，但在搜尋索引中，索引鍵一律為字串。
建立更多欄位以新增更多可搜尋的內容。如需指引，請參閱建立索引。

對應數據類型

SQL 資料類型	Azure AI 搜尋欄位類型	備註
bit	Edm.Boolean、Edm.String
int、smallint、tinyint	Edm.Int32、Edm.Int64、Edm.String
bigint	Edm.Int64、Edm.String
real、float	Edm.Double、Edm.String
smallmoney， money decimal numeric	Edm.String	Azure AI 搜尋不支援將十進位類型 `Edm.Double` 轉換成，因為這樣做會失去精確度
char、nchar、varchar、nvarchar	Edm.String Collection(Edm.String)	如果字串代表字串的 JSON 陣列，則 SQL 字串可用來填入 Collection（`Edm.String`）字段： `["red", "white", "blue"]`
smalldatetime、datetime、datetime2、date、datetimeoffset	Edm.DateTimeOffset、Edm.String
uniqueidentifer	Edm.String
地理位置	Edm.GeographyPoint	僅支援具有 SRID 4326 之 POINT 類型的 geography 實例（這是預設值）
rowversion	不適用	數據列版本數據行無法儲存在搜尋索引中，但可用於變更追蹤
time、timespan、binary、varbinary、image、xml、geometry、CLR 類型	不適用	不支援

設定和執行 Azure SQL 索引器

建立索引和數據源之後，您就可以開始建立索引器。索引器組態會指定控制運行時間行為的輸入、參數和屬性。

藉由提供名稱並參考數據源和目標索引，建立或更新索引器：

POST https://[service name].search.windows.net/indexers?api-version=2020-06-30
Content-Type: application/json
api-key: [search service admin key]
{
    "name" : "[my-sqldb-indexer]",
    "dataSourceName" : "[my-sqldb-ds]",
    "targetIndexName" : "[my-search-index]",
    "disabled": null,
    "schedule": null,
    "parameters": {
        "batchSize": null,
        "maxFailedItems": 0,
        "maxFailedItemsPerBatch": 0,
        "base64EncodeKeys": false,
        "configuration": {
            "queryTimeout": "00:04:00",
            "convertHighWaterMarkToRowVersion": false,
            "disableOrderByHighWaterMarkColumn": false
        }
    },
    "fieldMappings": [],
    "encryptionKey": null
}

在參數下，組態區段具有 Azure SQL 特有的參數：
- SQL 查詢執行的預設查詢逾時為 5 分鐘，您可以加以覆寫。
- “convertHighWaterMarkToRowVersion” 會針對高水位標記變更偵測原則進行優化。變更偵測原則是在數據源中設定。如果您使用原生變更偵測原則，此參數不會有任何作用。
- “disableOrderByHighWaterMarkColumn” 會導致高水印原則使用的 SQL 查詢省略 ORDER BY 子句。如果您使用原生變更偵測原則，此參數不會有任何作用。
如果功能變數名稱或類型有差異，或者如果您需要搜尋索引中的多個來源欄位版本，請指定欄位對應。
如需其他屬性的詳細資訊，請參閱建立索引器。

索引器會在建立時自動執行。您可以將 [已停用] 設定為 true 來防止此狀況。若要控制索引器執行，請視需要執行索引器或按排程放置索引器。

檢查索引器狀態

若要監視索引器狀態和執行歷程記錄，請傳送取得索引器狀態要求：

GET https://myservice.search.windows.net/indexers/myindexer/status?api-version=2020-06-30
  Content-Type: application/json  
  api-key: [admin key]

回應包含狀態和已處理的項目數目。看起來應該類似下列範例：

    {
        "status":"running",
        "lastResult": {
            "status":"success",
            "errorMessage":null,
            "startTime":"2022-02-21T00:23:24.957Z",
            "endTime":"2022-02-21T00:36:47.752Z",
            "errors":[],
            "itemsProcessed":1599501,
            "itemsFailed":0,
            "initialTrackingState":null,
            "finalTrackingState":null
        },
        "executionHistory":
        [
            {
                "status":"success",
                "errorMessage":null,
                "startTime":"2022-02-21T00:23:24.957Z",
                "endTime":"2022-02-21T00:36:47.752Z",
                "errors":[],
                "itemsProcessed":1599501,
                "itemsFailed":0,
                "initialTrackingState":null,
                "finalTrackingState":null
            },
            ... earlier history items
        ]
    }

執行歷程記錄包含最多 50 個最近完成的執行，這些執行順序會以反向時間順序排序，讓最新的執行先行執行。

為新的、已變更和已刪除的數據列編製索引

如果您的 SQL 資料庫支援變更追蹤，搜尋索引器可以只挑選後續索引器上新的和更新的內容。

若要啟用累加式索引編製，請在數據源定義中設定 “dataChangeDetectionPolicy” 屬性。這個屬性會告知索引器數據表或檢視表上使用哪些變更追蹤機制。

針對 Azure SQL 索引器，有兩個變更偵測原則：

“SqlIntegratedChangeTrackingPolicy” （僅適用於數據表）
“HighWaterMarkChangeDetectionPolicy” （適用於數據表和檢視表）

SQL 整合式變更追蹤原則

建議您使用「SqlIntegratedChangeTrackingPolicy」，以提升其效率和識別已刪除的數據列的能力。

資料庫需求：

SQL Server 2012 SP3 和更新版本 (若您在 Azure VM 上使用 SQL Server)
Azure SQL 資料庫或 SQL 受管理執行個體
只限資料表（沒有檢視表）
在資料庫上，啟用數據表的變更追蹤
資料表上沒有複合主鍵（主鍵包含一個以上的資料行）
數據表上沒有叢集索引。因應措施是，相較於叢集索引，任何叢集索引都必須卸除並重新建立為非叢集索引，不過，相較於叢集索引，來源中的效能可能會受到影響

變更偵測原則會新增至數據源定義。若要使用此原則，請建立或更新您的數據源，如下所示：

POST https://myservice.search.windows.net/datasources?api-version=2020-06-30
Content-Type: application/json
api-key: admin-key
    {
        "name" : "myazuresqldatasource",
        "type" : "azuresql",
        "credentials" : { "connectionString" : "connection string" },
        "container" : { "name" : "table name" },
        "dataChangeDetectionPolicy" : {
            "@odata.type" : "#Microsoft.Azure.Search.SqlIntegratedChangeTrackingPolicy"
    }

使用 SQL 整合式變更追蹤原則時，請勿指定個別的數據刪除偵測原則。 SQL 整合式變更追蹤原則內建支持識別已刪除的數據列。不過，若要自動偵測已刪除的數據列，搜尋索引中的檔索引鍵必須與 SQL 資料表中的主鍵相同。

注意

使用 TRUNCATE TABLE 從 SQL 資料表中移除大量數據列時，索引器必須重設以重設變更追蹤狀態以挑選數據列刪除。

高水位變更偵測原則

此變更偵測原則依賴數據表或檢視中的「高水印」數據行，以擷取上次更新數據列的版本或時間。如果您使用檢視，則必須使用高水印原則。

高水印數據行必須符合下列需求：

所有插入都會指定數據行的值。
專案的所有更新也會變更數據行的值。
此數據行的值會隨著每個插入或更新而增加。
您可以有效率地執行具有下列 WHERE 和 ORDER BY 子句的查詢： WHERE [High Water Mark Column] > [Current High Water Mark Value] ORDER BY [High Water Mark Column]

注意

強烈建議針對高水印數據行使用 rowversion 數據類型。如果使用任何其他數據類型，則不保證變更追蹤會擷取與索引器查詢同時執行之交易中的所有變更。在具有只讀複本的組態中使用 rowversion 時，您必須將索引器指向主要複本。只有主要復本可用於數據同步案例。

變更偵測原則會新增至數據源定義。若要使用此原則，請建立或更新您的數據源，如下所示：

POST https://myservice.search.windows.net/datasources?api-version=2020-06-30
Content-Type: application/json
api-key: admin-key
    {
        "name" : "myazuresqldatasource",
        "type" : "azuresql",
        "credentials" : { "connectionString" : "connection string" },
        "container" : { "name" : "table or view name" },
        "dataChangeDetectionPolicy" : {
            "@odata.type" : "#Microsoft.Azure.Search.HighWaterMarkChangeDetectionPolicy",
            "highWaterMarkColumnName" : "[a rowversion or last_updated column name]"
        }
    }

注意

如果源數據表在高水印數據行上沒有索引，SQL 索引器所使用的查詢可能會逾時。特別是， ORDER BY [High Water Mark Column] 子句需要索引在數據表包含許多數據列時有效率地執行。

convertHighWaterMarkToRowVersion

如果您針對高水位標記數據行使用 rowversion 數據類型，請考慮在索引器組態中設定 convertHighWaterMarkToRowVersion 屬性。將此屬性設定為 true 會導致下列行為：

針對索引器 SQL 查詢中的高水位標記數據行，使用 rowversion 數據類型。使用正確的數據類型可改善索引器查詢效能。
在索引器查詢執行之前，從 rowversion 值減去其中一個。具有一對多聯結的檢視可能有具有重複 rowversion 值的數據列。減去一個可確保索引器查詢不會遺漏這些數據列。

若要啟用此屬性，請使用下列組態建立或更新索引器：

    {
      ... other indexer definition properties
     "parameters" : {
            "configuration" : { "convertHighWaterMarkToRowVersion" : true } }
    }

queryTimeout

如果您遇到逾時錯誤，請將 queryTimeout 索引器組態設定設為高於預設 5 分鐘逾時的值。例如，若要將逾時設定為10分鐘，請使用下列組態建立或更新索引器：

    {
      ... other indexer definition properties
     "parameters" : {
            "configuration" : { "queryTimeout" : "00:10:00" } }
    }

disableOrderByHighWaterMarkColumn

您也可以停用 ORDER BY [High Water Mark Column] 子句。不過，不建議這麼做，因為如果索引器執行因錯誤而中斷，索引器必須稍後執行時重新處理所有數據列，即使索引器在中斷時已處理幾乎所有的數據列也一樣。若要停用 ORDER BY 子句，請使用 disableOrderByHighWaterMarkColumn 索引器定義中的設定：

    {
     ... other indexer definition properties
     "parameters" : {
            "configuration" : { "disableOrderByHighWaterMarkColumn" : true } }
    }

虛刪除數據行刪除偵測原則

從源資料表中刪除資料列時，您可能也想要從搜尋索引中刪除那些數據列。如果您使用 SQL 整合式變更追蹤原則，則會為您處理這項作業。不過，高水位標記變更追蹤原則無法協助您刪除的數據列。怎麼辦？

如果從數據表實際移除數據列，Azure AI 搜尋就無法推斷不存在的記錄是否存在。不過，您可以使用「虛刪除」技術，以邏輯方式刪除數據列，而不需從數據表中移除數據列。將數據行新增至數據表或檢視表，並將數據列標示為使用該數據行刪除。

使用虛刪除技術時，您可以在建立或更新數據源時，指定虛刪除原則，如下所示：

    {
        …,
        "dataDeletionDetectionPolicy" : {
           "@odata.type" : "#Microsoft.Azure.Search.SoftDeleteColumnDeletionDetectionPolicy",
           "softDeleteColumnName" : "[a column name]",
           "softDeleteMarkerValue" : "[the value that indicates that a row is deleted]"
        }
    }

softDeleteMarkerValue 必須是數據源 JSON 表示法中的字串。使用實際值的字串表示。例如，如果您有一個整數數據行，其中已刪除的數據列會以值 1 標記，請使用 "1"。如果您有一個 BIT 資料行，其中已刪除的數據列會標示為布爾值 true 值，請使用字串常值 "True" 或 "true"，則案例並不重要。

如果您要從 Azure 入口網站設定虛刪除原則，請勿在虛刪除標記值周圍加上引號。欄位內容已瞭解為字串，並會自動轉譯為 JSON 字串。在上述範例中，只要在入口網站的欄位中輸入 1或 Truetrue 。

常見問題集

問：我可以編製 Always Encrypted 數據行的索引嗎？

否。 Azure AI 搜尋服務索引器目前不支援 Always Encrypted 資料行。

問：我可以搭配在 Azure 中 IaaS VM 上執行的 SQL 資料庫使用 Azure SQL 索引器嗎？

是。不過，您必須允許搜尋服務連線到您的資料庫。如需詳細資訊，請參閱設定從 Azure AI 搜尋服務索引器到 Azure VM 上的 SQL Server 連線。

問：是否可以搭配執行內部部署的 SQL 資料庫使用 Azure SQL 索引器？

無法直接進行。我們不建議您或支援直接連線，因為這樣做會要求您將資料庫開啟至因特網流量。客戶已使用 Azure Data Factory 等網橋技術，成功使用此案例。如需詳細資訊，請參閱使用 Azure Data Factory 將數據推送至 Azure AI 搜尋服務索引。

問：我可以在故障轉移叢集中使用次要複本作為數據源嗎？

要看情況而定。如需數據表或檢視表的完整索引，您可以使用次要複本。

針對累加式索引編製，Azure AI 搜尋支援兩個變更偵測原則：SQL 整合式變更追蹤和高水位標記。

在唯讀複本上，SQL 資料庫不支援整合式變更追蹤。因此，您必須使用高水位標記原則。

我們的標準建議是針對高水印數據行使用rowversion數據類型。不過，使用rowversion依賴函 MIN_ACTIVE_ROWVERSION 式，而只讀複本不支援此功能。因此，如果您使用 rowversion，您必須將索引器指向主要複本。

如果您嘗試在唯讀複本上使用 rowversion，您會看到下列錯誤：

次要（只讀）可用性復本不支援使用 rowversion 資料行進行變更追蹤。請更新數據源，並指定主要可用性複本的連線。目前的資料庫 'Updateability' 屬性為 'READ_ONLY'“。

問：我是否可以針對高水位變更追蹤使用替代的非rowversion數據行？

我們不建議使用。只有 rowversion 允許可靠的數據同步處理。不過，視您的應用程式邏輯而定，如果下列情況可能很安全：

您可以確定當索引器執行時，數據表上沒有要編製索引的未完成交易（例如，所有數據表更新都會以排程的批次方式發生，而 Azure AI 搜尋索引器排程設定為避免與數據表更新排程重疊）。
您定期執行完整重新編製索引，以挑選任何遺漏的數據列。