如何在 Azure AI 搜索中索引来自 Azure SQL 的数据

项目
09/09/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 身份验证，其中用户名和密码在连接字符串上提供。

要完成本文中的示例，你需要一个 REST 客户端。

创建 Azure SQL 索引器的其他方法包括Azure 门户中的 Azure SDK 或导入数据向导。如果使用 Azure 门户，请确保在 Azure SQL 防火墙中启用对所有公用网络的访问权限，并且客户端可以通过入站规则进行访问。

定义数据源

数据源定义指定要编制索引的数据、凭据和用于标识数据更改的策略。数据源定义为独立的资源，以便它可以被多个索引器使用。

创建数据源或更新数据源以设置其定义：

 POST https://myservice.search.azure.cn/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.chinacloudapi.cn,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;。

将搜索字段添加到索引

在搜索索引中，添加与 SQL 数据库中的字段对应的字段。通过使用等效的数据类型，确保搜索索引架构与源架构相兼容。

创建或更新索引以定义将要存储数据的搜索字段：

POST https://[service name].search.azure.cn/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
    }
  ]
}

创建用于唯一标识每个搜索文档的文档键字段（“键”：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 集合 (Edm.String)	如果 SQL 字符串表示 JSON 字符串数组，该字符串可用于填充 Collection(`Edm.String`) 字段：`["red", "white", "blue"]`
smalldatetime、datetime、datetime2、date、datetimeoffset	Edm.DateTimeOffset、Edm.String
uniqueidentifer	Edm.String
geography	Edm.GeographyPoint	仅支持具有 SRID 4326（这是默认值）的类型 POINT 的地理实例
rowversion	不适用	行版本列不能存储在搜索索引中，但可用于更改跟踪
time、timespan、binary、varbinary、image、xml、geometry、CLR 类型	不适用	不支持

配置并运行 Azure SQL 索引器

创建索引和数据源后，便可以准备创建索引器。索引器配置指定控制运行时行为的输入、参数和属性。

通过为索引器命名并引用数据源和目标索引来创建或更新索引器：

POST https://[service name].search.azure.cn/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.azure.cn/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 数据库或 SQL 托管实例
只有表（无视图）
在数据库上，为表启用更改跟踪
表上没有组合主键（包含多个列的主键）
表上没有聚集索引。解决方法是删除任何聚集索引并将其重新创建为非聚集索引，但与使用聚集索引相比，源中的性能可能会受影响

更改检测策略已添加到数据源定义。若要使用此策略，按如下所示创建或更新数据源：

POST https://myservice.search.azure.cn/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.azure.cn/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 列编制索引？

不，Always Encrypted 列当前不受 Azure AI 搜索索引器支持。

问：是否可以将 Azure SQL 索引器与在 Azure 中 IaaS VM 上运行的 SQL 数据库配合使用？

是的。但是，需要允许搜索服务连接到数据库。有关详细信息，请参阅配置从 Azure AI 搜索索引器到 Azure VM 上的 SQL Server 的连接。

问：是否可以将 Azure SQL 索引器与本地运行的 SQL 数据库配合使用？

无法直接配合使用。不建议使用也不支持直接连接，因为这样做需要使用 Internet 流量打开数据库。对于此方案，客户已使用诸如 Azure 数据工厂之类的桥技术取得了成功。有关详细信息，请参阅使用 Azure 数据工厂将数据推送到 Azure AI 搜索索引。

问：是否可以将故障转移群集中的次要副本用作数据源？

视情况而定。对于表或视图的完整索引编制，可以使用辅助副本。

对于增量索引，Azure AI 搜索支持两个更改检测策略：“SQL 集成的更改跟踪”和“高使用标记”。

在只读副本上，SQL 数据库不支持集成的更改跟踪。因此，必须使用高使用标记策略。

我们的标准建议是为高使用标记列使用 rowversion 数据类型。但是，使用 rowversion 依赖于 MIN_ACTIVE_ROWVERSION 函数，该函数在只读副本上不受支持。因此，如果使用 rowversion，必须将索引器指向主要副本。

如果尝试在只读副本上使用 rowversion，则会看到以下错误：

“次要(只读)可用性副本不支持使用 rowversion 列进行更改跟踪。请更新数据源并指定与主要可用性副本的连接。当前数据库的“Updateability”属性为‘READ_ONLY’”。

问：是否可以使用替代的非 rowversion 列进行高使用标记更改跟踪？

不建议这样做。只有 rowversion 能够实现可靠的数据同步。不过，取决于你的应用程序逻辑，如果满足以下条件，则会很可靠：

你可以确保当索引器运行时在要编制索引的表上不存在未完成的事务（例如，所有表更新按计划批量进行，并且 Azure AI 搜索索引器已计划设置为避免与表更新计划重叠）。
你定期执行完整重新索引来补充任何缺少的行。

通过