Come modellare i dati SQL relazionali per l'importazione e l'indicizzazione in Ricerca di intelligenza artificiale di Azure

Ricerca di intelligenza artificiale di Azure accetta un set di righe flat come input per la pipeline di indicizzazione. Se i dati di origine provengono da tabelle unite in un database relazionale di SQL Server, questo articolo illustra come costruire il set di risultati e come modellare una relazione padre-figlio in un indice di Ricerca di intelligenza artificiale di Azure.

Come illustrazione, si fa riferimento a un ipotetico database di hotel, in base ai dati demo. Si supponga che il database sia costituito da una tabella Hotels$ con 50 hotel e un tavolo Rooms$ con camere di diversi tipi, tariffe e servizi, per un totale di 750 camere. Esiste una relazione uno-a-molti tra le tabelle. Nell'approccio, una visualizzazione fornisce la query che restituisce 50 righe, una riga per ogni hotel, con i dettagli della stanza associati incorporati in ogni riga.

Tabelle e viste nel database hotels

Problema dei dati denormalizzati

Una delle sfide legate all'uso delle relazioni uno-a-molti è che le query standard basate su tabelle unite in join restituiscono dati denormalizzati, che non funzionano correttamente in uno scenario di Ricerca di intelligenza artificiale di Azure. Si consideri l'esempio seguente che unisce hotel e camere.

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

I risultati di questa query restituiscono tutti i campi Hotel, seguiti da tutti i campi Camera, con informazioni preliminari sull'hotel ripetute per ogni valore della stanza.

Dati denormalizzati, dati di hotel ridondanti quando vengono aggiunti campi della stanza

Anche se questa query ha esito positivo sulla superficie (fornendo tutti i dati in un set di righe flat), non riesce a fornire la struttura del documento corretta per l'esperienza di ricerca prevista. Durante l'indicizzazione, Ricerca intelligenza artificiale di Azure crea un documento di ricerca per ogni riga inserita. Se i documenti di ricerca hanno un aspetto simile ai risultati precedenti, si sarebbero percepiti duplicati , sette documenti separati per l'Old Century Hotel da soli. Una query su "hotel in Florida" restituisce sette risultati solo per Old Century Hotel, spingendo gli altri hotel pertinenti in profondità nei risultati della ricerca.

Per ottenere l'esperienza prevista di un documento per hotel, è necessario fornire un set di righe con la granularità corretta, ma con informazioni complete. Questo articolo spiega come.

Definire una query che restituisce JSON incorporato

Per offrire l'esperienza di ricerca prevista, il set di dati deve essere costituito da una riga per ogni documento di ricerca in Ricerca di intelligenza artificiale di Azure. Nel nostro esempio, vogliamo una riga per ogni hotel, ma vogliamo anche che i nostri utenti possano effettuare ricerche in altri campi correlati alla camera che interessano loro, come la tariffa a notte, le dimensioni e il numero di letti o la vista sulla spiaggia, tutti elementi che fanno parte dei dettagli della camera.

La soluzione consiste nell'acquisire i dettagli della stanza come JSON annidato e quindi inserire la struttura JSON in un campo in una visualizzazione, come illustrato nel secondo passaggio.

  1. Si supponga di avere due tabelle unite, Hotels$ e Rooms$, che contengono i dettagli per 50 hotel e 750 camere e sono unite nel campo HotelID. Singolarmente, queste tabelle contengono 50 hotel e 750 camere correlate.

    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. Creare una vista composta da tutti i campi della tabella padre (SELECT * from dbo.Hotels$), con l'aggiunta di un nuovo campo Camere contenente l'output di una query nidificata. Una clausola FOR JSON AUTO in SELECT * from dbo.Rooms$ struttura l'output come 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
    

    Lo screenshot seguente mostra la visualizzazione risultante, con il campo nvarchar Camere nella parte inferiore. Il campo Camere esiste solo nella visualizzazione HotelRoom.

    Vista HotelRoom

  3. Eseguire SELECT * FROM dbo.HotelRooms per recuperare il set di righe. Questa query restituisce 50 righe, una per hotel, con informazioni sulla camera associate come raccolta JSON.

    Set di righe dalla visualizzazione HotelRoom

Questo set di righe è ora pronto per l'importazione in Ricerca di intelligenza artificiale di Azure.

Nota

Questo approccio presuppone che JSON incorporato sia inferiore ai limiti massimi delle dimensioni delle colonne di SQL Server.

Usare una raccolta complessa per il lato "molti" di una relazione uno-a-molti

Sul lato Ricerca intelligenza artificiale di Azure creare uno schema di indice che modella la relazione uno-a-molti usando JSON annidato. Il set di risultati creato nella sezione precedente corrisponde in genere allo schema dell'indice fornito successivamente (vengono tagliati alcuni campi per brevità).

L'esempio seguente è simile all'esempio riportato in Come modellare tipi di dati complessi. La struttura Camere, che è stata l'obiettivo di questo articolo, è nella raccolta di campi di un indice denominato hotels. Questo esempio mostra anche un tipo complesso per Indirizzo, che differisce da Camere in quanto è composto da un set fisso di elementi, anziché dal numero arbitrario di elementi consentiti in una raccolta.

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

Dato il set di risultati precedente e lo schema di indice precedente, sono disponibili tutti i componenti necessari per un'operazione di indicizzazione riuscita. Il set di dati flat soddisfa i requisiti di indicizzazione, ma mantiene le informazioni dettagliate. Nell'indice di Ricerca di intelligenza artificiale di Azure i risultati della ricerca rientrano facilmente nelle entità basate su hotel, mantenendo al tempo stesso il contesto delle singole stanze e i relativi attributi.

Comportamento dei facet nei sottocampi di tipo complesso

I campi che dispongono di un elemento padre, ad esempio i campi in Indirizzo e Camere, vengono chiamati sottocampi. Sebbene sia possibile assegnare un attributo "facetable" a un sottocampo, il conteggio del facet è sempre per il documento principale.

Per tipi complessi come Address, in cui è presente un solo "Indirizzo/Città" o "Indirizzo/Stato/Provincia" nel documento, il comportamento del facet funziona come previsto. Tuttavia, nel caso di Camere, in cui sono presenti più documenti secondari per ogni documento principale, i conteggi dei facet possono essere fuorvianti.

Come indicato in Tipi complessi di modelli: "i conteggi dei documenti restituiti nei risultati del facet vengono calcolati per il documento padre (un hotel), non per i documenti secondari in una raccolta complessa (camere). Si supponga, ad esempio, che un hotel abbia 20 camere di tipo "suite". Dato questo parametro facet facet=Camere/Tipo, il conteggio dei facet è uno per l'hotel, non 20 per le camere".

Passaggi successivi

Usando il proprio set di dati, è possibile usare la procedura guidata Importa dati per creare e caricare l'indice. La procedura guidata rileva la raccolta JSON incorporata, ad esempio quella contenuta in Camere, e deduce uno schema di indice che include una raccolta di tipi complessa.

Indice dedotto dalla procedura guidata Importa dati

Provare la guida introduttiva seguente per informazioni sui passaggi di base della procedura guidata Importa dati.