如何为 Azure AI 搜索中要导入和编制索引的 SQL 关系数据建模

项目
04/12/2024

Azure AI 搜索接受使用平展行集作为索引编制管道的输入。如果源数据源自 SQL Server 关系数据库中的联接表，你可以参考本文来构造结果集，以及在 Azure AI 搜索索引中为父子关系建模。

为便于演示，我们基于演示数据引用一个虚构的酒店数据库。假设该数据库包括一个 Hotels$ 表（其中包含 50 家酒店），以及一个 Rooms$ 表（其中包含各种类型的提供不同价格和设施的总共 750 间客房）。表之间存在一对多关系。在我们的方法中，某个视图提供返回 50 行（每家酒店各对应一行）以及嵌入到每行中的关联客房详细信息的查询。

酒店数据库中的表和视图

反规范化数据的问题

使用一对多关系的一个难题在于，基于联接表生成的标准查询返回反规范化的数据，这在 Azure AI 搜索方案中无法顺利运行。考虑以下联接酒店和客房的示例。

SELECT * FROM Hotels$
INNER JOIN Rooms$
ON Rooms$.HotelID = Hotels$.HotelID

此查询的结果返回所有酒店字段，后接所有客房字段，以及针对每个客房值重复的初步酒店信息。

反规范化数据，添加客房字段后多余的酒店数据

尽管此查询表面上可以成功（提供平整行集中的所有数据），但它无法为预期的搜索体验提供正确的文档结构。在编制索引期间，Azure AI 搜索会为引入的每个行创建一个搜索文档。如果搜索文档类似于上述结果，则你会看到重复项 - 单单是 Twin Dome 酒店就有七个不同的文档。根据“佛罗里达州的酒店”进行查询只会返回 Twin Dome 酒店的七条结果，而其他相关酒店则深藏在搜索结果中。

若要获得每家酒店只有一个文档的预期体验，应以适当的粒度提供一个行集，但同时需要提供完整的信息。本文介绍了如何执行此操作。

定义返回嵌入式 JSON 的查询

若要提供预期的搜索体验，数据集应为 Azure AI 搜索中的每个搜索文档包括一行。在本示例中，我们希望每家酒店只有一行，但同时希望用户能够搜索他们所关心的其他客房相关字段，例如，每晚的价格、床位的尺寸和数量，或者沙滩景观，所有这些信息都是客房详细信息的一部分。

解决方法是捕获嵌套 JSON 格式的客房详细信息，然后将 JSON 结构插入到视图中的字段，如第二步中所述。

假设你有两个联接的表：Hotels$ 和 Rooms$，其中包含 50 家酒店和 750 间客房的详细信息，这两个表已在 HotelID 字段中联接。这些表各自包含 50 家酒店和 750 间相关的客房。

CREATE TABLE [dbo].[Hotels$](
  [HotelID] [nchar](10) NOT NULL,
  [HotelName] [nvarchar](255) NULL,
  [Description] [nvarchar](max) NULL,
  [Description_fr] [nvarchar](max) NULL,
  [Category] [nvarchar](255) NULL,
  [Tags] [nvarchar](255) NULL,
  [ParkingIncluded] [float] NULL,
  [SmokingAllowed] [float] NULL,
  [LastRenovationDate] [smalldatetime] NULL,
  [Rating] [float] NULL,
  [StreetAddress] [nvarchar](255) NULL,
  [City] [nvarchar](255) NULL,
  [State] [nvarchar](255) NULL,
  [ZipCode] [nvarchar](255) NULL,
  [GeoCoordinates] [nvarchar](255) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Rooms$](
  [HotelID] [nchar](10) NULL,
  [Description] [nvarchar](255) NULL,
  [Description_fr] [nvarchar](255) NULL,
  [Type] [nvarchar](255) NULL,
  [BaseRate] [float] NULL,
  [BedOptions] [nvarchar](255) NULL,
  [SleepsCount] [float] NULL,
  [SmokingAllowed] [float] NULL,
  [Tags] [nvarchar](255) NULL
) ON [PRIMARY]
GO

创建一个视图，其中包含父表 (SELECT * from dbo.Hotels$) 中的所有字段，加上包含嵌套查询的输出的新 Rooms 字段。 SELECT * from dbo.Rooms$ 中的 FOR JSON AUTO 子句将输出结构化为 JSON。
```
CREATE VIEW [dbo].[HotelRooms]
AS
SELECT *, (SELECT *
         FROM dbo.Rooms$
         WHERE dbo.Rooms$.HotelID = dbo.Hotels$.HotelID FOR JSON AUTO) AS Rooms
FROM dbo.Hotels$
GO
```
以下屏幕截图显示了生成的视图，底部是 Rooms nvarchar 字段。 “客房”字段仅存在于 HotelRooms 视图中。
运行 SELECT * FROM dbo.HotelRooms 检索行集。此查询返回 50 行（每家酒店对应一行），以及 JSON 集合形式的关联客房信息。

此行集现已准备好导入 Azure AI 搜索。

注意

此方法假设嵌入的 JSON 低于 SQL Server 的最大列大小限制。

为一对多关系的“多”端使用复杂集合

在 Azure AI 搜索端，创建一个使用嵌套式 JSON 为一对多关系建模的索引架构。你在上一部分创建的结果集通常对应于接下来提供的索引架构（为简洁起见，我们截掉了一些字段）。

以下示例类似于如何为复杂数据类型建模中的示例。 Rooms 结构（本文的重点所在）位于索引的名为 hotels 的字段集合中。此示例还显示了 Address 的复杂类型，该类型与 Rooms 的不同之处在于，它包含一组固定的项，而不是集合中允许的多个任意数量的项。

{
  "name": "hotels",
  "fields": [
    { "name": "HotelId", "type": "Edm.String", "key": true, "filterable": true },
    { "name": "HotelName", "type": "Edm.String", "searchable": true, "filterable": false },
    { "name": "Description", "type": "Edm.String", "searchable": true, "analyzer": "en.lucene" },
    { "name": "Description_fr", "type": "Edm.String", "searchable": true, "analyzer": "fr.lucene" },
    { "name": "Category", "type": "Edm.String", "searchable": true, "filterable": true, "facetable": true },
    { "name": "ParkingIncluded", "type": "Edm.Boolean", "filterable": true, "facetable": true },
    { "name": "Tags", "type": "Collection(Edm.String)", "searchable": true, "filterable": true, "facetable": true },
    { "name": "Address", "type": "Edm.ComplexType",
      "fields": [
        { "name": "StreetAddress", "type": "Edm.String", "filterable": false, "sortable": false, "facetable": false, "searchable": true },
        { "name": "City", "type": "Edm.String", "searchable": true, "filterable": true, "sortable": true, "facetable": true },
        { "name": "StateProvince", "type": "Edm.String", "searchable": true, "filterable": true, "sortable": true, "facetable": true }
      ]
    },
    { "name": "Rooms", "type": "Collection(Edm.ComplexType)",
      "fields": [
        { "name": "Description", "type": "Edm.String", "searchable": true, "analyzer": "en.lucene" },
        { "name": "Description_fr", "type": "Edm.String", "searchable": true, "analyzer": "fr.lucene" },
        { "name": "Type", "type": "Edm.String", "searchable": true },
        { "name": "BaseRate", "type": "Edm.Double", "filterable": true, "facetable": true },
        { "name": "BedOptions", "type": "Edm.String", "searchable": true, "filterable": true, "facetable": false },
        { "name": "SleepsCount", "type": "Edm.Int32", "filterable": true, "facetable": true },
        { "name": "SmokingAllowed", "type": "Edm.Boolean", "filterable": true, "facetable": false},
        { "name": "Tags", "type": "Edm.Collection", "searchable": true }
      ]
    }
  ]
}

在给定上述结果集和上述索引架构后，你便获得了成功完成索引编制操作所需的全部组件。平展数据集符合索引编制要求，同时可以保留详细信息。在 Azure AI 搜索索引中，搜索结果很容易变成基于酒店的实体，同时可以保留各个客房及其属性的上下文。

具有父级的字段（如“地址”和“客房”下的字段）称为“子字段”。尽管可以将“可分面”属性分配给子字段，但该 facet 的计数始终用于主文档。

对于像“地址”这样的复杂类型，文档中只有一个“地址/城市”或“地址/stateProvince”，facet 行为会按预期工作。但是，对于“客房”，如果每个主文档有多个子文档，则 facet 计数可能会产生误导。

如为复杂类型建模中所述：“facet 结果中返回的文档计数是根据父文档（酒店）计算的，而不是根据复杂集合中的子文档（客房）计算的。例如，假设某家酒店有 20 间“套房”类型的客房。如果此 facet 参数为 facet=Rooms/Type，则 facet 计数将是 1（对应于酒店），而不是 20（对应于客房）。”

后续步骤

可以使用自己的数据集通过导入数据向导来创建和加载索引。该向导将检测嵌入的 JSON 集合（例如 Rooms 中包含的集合），并推断包含复杂类型集合的索引架构。

请尝试学习以下快速入门，以了解导入数据向导的基本步骤。

快速入门：使用 Azure 门户创建搜索索引

如何为 Azure AI 搜索中要导入和编制索引的 SQL 关系数据建模

反规范化数据的问题

定义返回嵌入式 JSON 的查询

为一对多关系的“多”端使用复杂集合

复杂类型子字段上的 facet 行为

后续步骤

其他资源