Como modelar dados SQL relacionais para importação e indexação no Azure AI Search

O Azure AI Search aceita um conjunto de linhas simples como entrada para o pipeline de indexação. Se os dados de origem forem originados de tabelas unidas em um banco de dados relacional do SQL Server, este artigo explica como construir o conjunto de resultados e como modelar uma relação pai-filho em um índice do Azure AI Search.

A título de ilustração, referimo-nos a uma hipotética base de dados de hotéis, baseada em dados de demonstração. Suponha que o banco de dados consiste em uma Hotels$ tabela com 50 hotéis e uma Rooms$ tabela com quartos de diferentes tipos, tarifas e comodidades, para um total de 750 quartos. Há uma relação um-para-muitos entre as tabelas. Em nossa abordagem, uma exibição fornece a consulta que retorna 50 linhas, uma linha por hotel, com detalhes de quarto associados incorporados em cada linha.

O problema dos dados desnormalizados

Um dos desafios em trabalhar com relações um-para-muitos é que as consultas padrão criadas em tabelas unidas retornam dados desnormalizados, o que não funciona bem em um cenário de Pesquisa de IA do Azure. Considere o exemplo a seguir que une hotéis e quartos.

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

Os resultados dessa consulta retornam todos os campos Hotel, seguidos por todos os campos Quarto, com informações preliminares do hotel repetidas para cada valor de quarto.

Embora essa consulta seja bem-sucedida na superfície (fornecendo todos os dados em um conjunto de linhas planas), ela falha ao fornecer a estrutura de documento correta para a experiência de pesquisa esperada. Durante a indexação, o Azure AI Search cria um documento de pesquisa para cada linha ingerida. Se seus documentos de pesquisa se parecessem com os resultados acima, você teria percebido duplicatas - sete documentos separados apenas para o Old Century Hotel. Uma consulta sobre "hotéis na Flórida" retornaria sete resultados apenas para o Old Century Hotel, empurrando outros hotéis relevantes para os resultados de pesquisa.

Para obter a experiência esperada de um documento por hotel, você deve fornecer um conjunto de linhas com a granularidade correta, mas com informações completas. Este artigo explica como.

Definir uma consulta que retorna JSON incorporado

Para fornecer a experiência de pesquisa esperada, seu conjunto de dados deve consistir em uma linha para cada documento de pesquisa no Azure AI Search. No nosso exemplo, queremos uma fila para cada hotel, mas também queremos que os nossos utilizadores possam pesquisar noutros campos relacionados com o quarto que lhes interessam, como a taxa por noite, o tamanho e o número de camas, ou uma vista para a praia, que fazem parte de um detalhe do quarto.

A solução é capturar os detalhes da sala como JSON aninhado e, em seguida, inserir a estrutura JSON em um campo em uma exibição, conforme mostrado na segunda etapa.

1. Suponha que você tenha duas mesas Hotels$ unidas e Rooms$, que contêm detalhes de 50 hotéis e 750 quartos e estão unidas no campo HotelID. Individualmente, estas mesas contêm 50 hotéis e 750 quartos relacionados.

   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. Crie um modo de exibição composto por todos os campos na tabela pai (SELECT * from dbo.Hotels$), com a adição de um novo campo Quartos que contém a saída de uma consulta aninhada. A FOR JSON AUTO cláusula sobre SELECT * from dbo.Rooms$ estruturas a saída como 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
   

   A captura de tela a seguir mostra a exibição resultante, com o campo Rooms nvarchar na parte inferior. O campo Quartos existe apenas na vista HotelRooms.

   

3. Execute SELECT * FROM dbo.HotelRooms para recuperar o conjunto de linhas. Essa consulta retorna 50 linhas, uma por hotel, com informações de quarto associadas como uma coleção JSON.

   

Esse conjunto de linhas agora está pronto para importação para o Azure AI Search.

Use uma coleção complexa para o lado "muitos" de um relacionamento um-para-muitos

No lado da Pesquisa de IA do Azure, crie um esquema de índice que modele a relação um-para-muitos usando JSON aninhado. O conjunto de resultados que você criou na seção anterior geralmente corresponde ao esquema de índice fornecido a seguir (cortamos alguns campos para abreviar).

O exemplo a seguir é semelhante ao exemplo em Como modelar tipos de dados complexos. A estrutura Quartos , que tem sido o foco deste artigo, está na coleção de campos de um índice chamado hotéis. Este exemplo também mostra um tipo complexo para Address, que difere de Rooms porque é composto por um conjunto fixo de itens, em oposição ao número múltiplo e arbitrário de itens permitidos em uma coleção.

{
  "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 }
      ]
    }
  ]
}

Dado o conjunto de resultados anterior e o esquema de índice acima, você tem todos os componentes necessários para uma operação de indexação bem-sucedida. O conjunto de dados nivelado atende aos requisitos de indexação, mas preserva informações detalhadas. No índice Azure AI Search, os resultados da pesquisa caem facilmente em entidades baseadas em hotéis, preservando o contexto de quartos individuais e seus atributos.

Comportamento de facetas em subcampos de tipo complexos

Os campos que têm um pai, como os campos em Endereço e Salas, são chamados de subcampos. Embora você possa atribuir um atributo "facetable" a um subcampo, a contagem da faceta é sempre para o documento principal.

Para tipos complexos como Address, onde há apenas um "Address/City" ou "Address/stateProvince" no documento, o comportamento da faceta funciona conforme o esperado. No entanto, no caso das salas, onde existem vários subdocumentos para cada documento principal, as contagens de facetas podem ser enganosas.

Como observado em Tipos complexos de modelos: "as contagens de documentos retornadas nos resultados da faceta são calculadas para o documento pai (um hotel), não para os subdocumentos de uma coleção complexa (salas). Por exemplo, suponha que um hotel tenha 20 quartos do tipo "suíte". Dado este parâmetro faceta facet=Quartos/Tipo, a contagem de facetas é uma para o hotel, não 20 para os quartos."

Próximos passos

Usando seu próprio conjunto de dados, você pode usar o assistente Importar dados para criar e carregar o índice. O assistente deteta a coleção JSON incorporada, como a contida em Rooms, e infere um esquema de índice que inclui uma coleção de tipos complexa.

Experimente o início rápido a seguir para aprender as etapas básicas do assistente Importar dados.