如何为 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 行为

后续步骤

其他资源