Como modelar dados SQL relacionais para importação e indexação na IA do Azure Search

A IA do Azure Search aceita um conjunto de linhas simples como entrada para o pipeline de indexação. Este artigo explica como criar o conjunto de resultados e modelar um relacionamento de pai e filho em um índice da IA do Azure Search, em situações em que os dados de origem vêm de tabelas unidas em um banco de dados relacional do SQL Server.

Como exemplo, nos referimos a um banco de dados hipotético sobre hotéis, com base nos dados de demonstração. Suponha que o banco de dados consiste na tabela Hotels$, com 50 hotéis, e na tabela Rooms$, com 750 quartos de variados tipos, preços e amenidades. As tabelas têm uma relação de um-para-muitos. Em nossa abordagem, uma exibição mostra a consulta que retorna 50 linhas, uma linha por hotel, com os dados dos quartos inseridos em cada linha.

O problema dos dados desnormalizados

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

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

Os resultados da consulta retornam todos os campos de hotel, seguidos por todos os campos de quarto, com a repetição das informações preliminares de hotel para cada valor de quarto.

Embora a consulta pareça ter sucesso (ela entrega todos os dados em um conjunto de linhas simples), ela não oferece a estrutura de documento certa para a experiência de pesquisa esperada. Durante a indexação, a IA do Azure Search cria um documento de pesquisa para cada linha inserida. Se os documentos de pesquisa fossem como aqueles acima, os resultados seriam duplicados: sete documentos apenas para o hotel Twin Dome. Uma consulta de "hotéis na Flórida" retornaria sete resultados apenas para o hotel Twin Dome, colocando outros hotéis relevantes ao final dos resultados da pesquisa.

Para ter um documento por hotel, conforme o esperado, você deve usar um conjunto de linhas com a granularidade certa, mas com informações completas. Este artigo explica como fazer isso.

Definir uma consulta que retorna JSON inserido

Para proporcionar a experiência de pesquisa esperada, o conjunto de dados deve consistir em uma linha para cada documento de pesquisa na IA do Azure Search. No nosso exemplo, queremos uma linha para cada hotel, mas também queremos que os usuários possam pesquisar outros campos importantes relacionados aos quartos. Por exemplo, o preço por noite, o tamanho e número das camas ou uma vista para o mar, informações que fazem parte dos detalhes do quarto.

A solução é registrar os detalhes do quarto em código JSON aninhado e inserir a estrutura de JSON em um campo de uma exibição, conforme mostrado na segunda etapa.

1. Suponha que há duas tabelas unidas, Hotels$ e Rooms$, que contêm os detalhes de 50 hotéis e 750 quartos e são unidas pelo campo HotelID. Individualmente, as tabelas 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 uma exibição composta de todos os campos na tabela pai (SELECT * from dbo.Hotels$), com a adição de um novo campo Rooms que contém a saída de uma consulta aninhada. A cláusula FOR JSON AUTO em SELECT * from dbo.Rooms$ estrutura a saída no formato 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 abaixo mostra a exibição resultante, com o campo nvarchar Rooms na parte inferior. O campo Rooms existe apenas na exibição HotelRooms.

   

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

   

Agora, esse conjunto de linhas está pronto para ser importado para a IA do Azure Search.

Usar uma coleção complexa para o lado "muitos" de uma relação de um para muitos

Na IA do Azure Search, crie um esquema de índice que modela o relacionamento de um para muitos usando JSON aninhado. O conjunto de resultados criado na seção anterior geralmente corresponde ao esquema de índice fornecido a seguir (cortamos alguns campos para abreviar).

O exemplo abaixo é semelhante ao exemplo em Como modelar tipos de dados complexos. A estrutura Rooms, que tem sido o foco deste artigo, está na coleção de campos de um índice chamado hotels. O exemplo também mostra o tipo complexo para Address, que é diferente de Rooms porque é composto por um conjunto fixo de itens, ao contrário do número 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 }
      ]
    }
  ]
}

Com 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 as informações detalhadas. No índice da IA do Azure Search, os resultados da pesquisa recaem facilmente sobre as entidades que representam hotéis, preservando o contexto dos quartos individuais e dos atributos deles.

Comportamento de faceta em subcampos de tipo complexo

Campos que têm um pai, como os campos em Address e Rooms, são chamados de subcampos. Embora você possa atribuir um atributo de “com faceta” a um subcampo, a contagem da faceta sempre é para o documento principal.

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

Conforme observado em Tipos complexos de modelo: "as contagens de documentos retornadas nos resultados da faceta são calculadas para o documento pai (um hotel), não os subdocumentos em uma coleção complexa (rooms). Por exemplo, suponha que um hotel tenha 20 salas do tipo "Suite". Devido ao parâmetro facet=Rooms/Type dessa faceta, a contagem de facetas é uma para o hotel, não 20 para os quartos".

Próximas etapas

Usando um conjunto de dados próprio, você pode usar o Assistente para importação de dados para criar e carregar o índice. O assistente detecta a coleção JSON inserida, como aquela contida em Rooms, e infere um esquema de índice que inclui uma coleção de tipos complexos.

Veja o guia de início rápido abaixo para aprender as etapas básicas do Assistente para importação de dados.