Share via

Моделирование реляционных данных SQL для импорта и индексирования в поиске ИИ Azure

  • Статья

Поиск ИИ Azure принимает плоский набор строк в качестве входных данных в конвейер индексирования. Если исходные данные исходят из присоединенных таблиц в реляционной базе данных SQL Server, в этой статье объясняется, как создать результирующий набор и как моделировать связь родительского-дочернего объекта в индексе поиска ИИ Azure.

Как иллюстрация, мы называем гипотетической базой данных отелей на основе демонстрационных данных. Предположим, что база данных состоит из Hotels$ таблицы с 50 отелями, а Rooms$ также стол с номерами различных типов, тарифами и удобствами, в общей сложности 750 номеров. Между таблицами существует связь "один ко многим". В нашем подходе представление предоставляет запрос, возвращающий 50 строк, по одной строке для каждого отеля, со связанными сведениями о комнате, внедренными в каждую строку.

Таблицы и представления в базе данных Hotels

Проблема c денормализацией данных

Одна из проблем при работе с отношениями "один ко многим" заключается в том, что стандартные запросы, созданные на основе присоединенных таблиц, возвращают денормализованные данные, которые не работают хорошо в сценарии поиска ИИ Azure. Давайте рассмотрим следующий пример, в котором объединяются таблицы гостиниц и номеров.

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

Результаты этого запроса возвращают все поля таблицы Hotels и все поля таблицы Rooms, где для каждого значения номера повторяются все сведения о соответствующей гостинице.

Денормализованные данные с избыточной информацией о гостиницах после добавления полей из таблицы Rooms

На первый взгляд этот запрос завершается успешно (то есть возвращает все данные в неструктурированном наборе строк), но он не обеспечивает правильную структуру документа для удобного поиска. Во время индексирования поиск ИИ Azure создает один документ поиска для каждой строки приема. Если документы поиска будут выглядеть так, как показано выше, в поиске будет много дублирующихся результатов. Например, для одной гостиницы Twin Dome создается семь отдельных документов. Запрос вида "hotels in Florida" (гостиницы Флориды) вернет семь одинаковых результатов только для Twin Dome, сдвигая другие релевантные результаты поиска далеко вниз.

Чтобы получить, как ожидается, по одному документу на каждую гостиницу, нужно предоставить набор строк с полной информацией и правильной степенью детализации. В этой статье описано, как это сделать.

Определение запроса, который возвращает внедренный код JSON

Чтобы обеспечить ожидаемый интерфейс поиска, набор данных должен состоять из одной строки для каждого документа поиска в службе "Поиск ИИ Azure". В нашем примере это означает одну строку для каждой гостиницы. Но нам важно, чтобы пользователи могли выполнять поиск по другим полям с информацией о номерах, например по стоимости за одну ночь, о размере и количестве кроватей или о наличии вида на пляж. Все эти сведения входят в информацию о номере.

Решение состоит в том, чтобы получить сведения о номерах в формате вложенных данных JSON и поместить эту структуру JSON в отдельное поле в представлении, что мы и сделаем на втором шаге.

  1. Предположим, что у вас есть две присоединенные таблицы, 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

  2. Создайте представление, которое включает все поля из родительской таблицы (SELECT * from dbo.Hotels$) с одним дополнительным полем Rooms для выходных данных вложенного запроса. Предложение FOR JSON AUTO для SELECT * from dbo.Rooms$ форматирует выходные данные в код 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 в самом низу. Поле Rooms существует только в этом представлении HotelRooms.

    Представление HotelRooms

  3. Выполните команду SELECT * FROM dbo.HotelRooms, чтобы получить этот набор строк. Запрос возвращает 50 строк, то есть по одной на гостиницу, с информацией о номерах в формате коллекции JSON.

    Набор строк из представления HotelRooms

Теперь этот набор строк готов к импорту в поиск ИИ Azure.

Примечание.

При таком подходе предполагается, что внедренный код JSON не превышает ограничение на размер столбца в SQL Server.

Использование сложной коллекции для множественных объектов в связи "один ко многим"

На стороне поиска ИИ Azure создайте схему индекса, которая моделирует связь "один ко многим" с помощью вложенных JSON. Результирующий набор, созданный в предыдущем разделе, обычно соответствует схеме индекса, предоставленной далее (мы вырезаем некоторые поля для краткости).

Следующий пример аналогичен примеру из статьи Моделирование сложных типов данных. Структура Rooms, с которой мы имели дело в этой статье, располагается в коллекции полей индекса с названием hotels. В этом примере также показан сложный тип для Address, который отличается от комнат в том, что он состоит из фиксированного набора элементов, в отличие от нескольких произвольных элементов, разрешенных в коллекции.

{
  "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 результаты поиска легко попадают в сущности на основе отеля, сохраняя контекст отдельных комнат и их атрибутов.

Поведение аспектов в подполях сложного типа

Поля с родительскими полями, такими как поля в адресе и комнатах, называются подполями. Хотя атрибут "facetable" можно назначить подфилду, количество аспектов всегда предназначено для основного документа.

Для сложных типов, таких как Address, где в документе есть только один адрес или город или адрес или состояниеProvince, поведение аспектов работает должным образом. Однако в случае с номерами, в которых существует несколько поддокументов для каждого основного документа, количество аспектов может быть вводящим в заблуждение.

Как отмечалось в сложных типах модели: "Количество документов, возвращаемых в результатах аспектов, вычисляется для родительского документа (отель), а не поддокументов в сложной коллекции (номера). Например, предположим, что в гостинице 20 номеров типа "люкс". Учитывая этот параметр аспектов аспект=Номера и тип, количество аспектов является одним для отеля, а не 20 для номеров".

Следующие шаги

Используя собственный набор данных, создайте и загрузите индекс с помощью мастера импорта данных. Этот мастер обнаруживает встроенную коллекцию JSON, такую как структура Rooms, и выводит схему индекса с коллекцией сложного типа.

Индекс, выводимый мастером импорта данных

Чтобы ознакомиться с основными шагами выполнения мастера импорта данных, воспользуйтесь приведенным ниже кратким руководством.

Краткое руководство. Создание индекса поиска с помощью портала Azure