如何在 Azure AI 搜尋服務中為來自 Azure SQL 的資料編制索引

文章
10/16/2024

在本文中，了解如何設定索引子，以從 Azure SQL Database 或 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 SDK 或 Azure 入口網站中的匯入資料精靈。如果您使用的是 Azure 入口網站，請確定已在 Azure SQL 防火牆中啟用所有公用網路的存取權，且用戶端可透過輸入規則加以存取。

定義資料來源

資料來源定義指定要編製索引的資料、認證，以及用於識別資料變更的原則。資料來源定義為獨立的資源，因此可供多個索引子使用。

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

 POST https://myservice.search.windows.net/datasources?api-version=2024-07-01
 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" (必要)。
將 "credentials" 設定為連接字串：
- 您可以從 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 Database 索引子。

將搜尋欄位新增至索引

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

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

POST https://[service name].search.windows.net/indexes?api-version=2024-07-01
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 十進位數值	Edm.String	Azure AI 搜尋服務不支援將十進位類型轉換成 `Edm.Double`，因為這麼做會失去精確度
char、nchar、varchar、nvarchar	Edm.String Collection(Edm.String)	如果 SQL 字串代表下列 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=2024-07-01
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=2024-07-01
  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 Database 或 SQL 受控執行個體
僅限資料表 (沒有檢視表)
在資料庫上，為資料表啟用變更追蹤
資料表上沒有複合主索引鍵 (主索引鍵包含不只一個資料行)
資料表上沒有叢集索引。因應措施是，必須卸除任何叢集索引並重新建立為非叢集索引，不過，相較於具有叢集索引，來源中的效能可能會受到影響

變更偵測原則會新增至資料來源定義。若要使用此原則，請以下列方式建立或更新您的資料來源：

POST https://myservice.search.windows.net/datasources?api-version=2024-07-01
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=2024-07-01
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、True 或 true 即可。

常見問題集

問：我可以對 Always Encrypted 資料行編製索引嗎？

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

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

是。不過，您需要允許搜尋服務連接到資料庫。如需詳細資訊，請參閱在 Azure VM 上設定從 Azure AI 搜尋服務索引子到 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 搜尋服務索引子排程以防止與資料表更新排程重疊)。
您會定期進行完整的重新編製索引，以挑選出任何遺失的資料列。

分享方式：