Share via

Relationele SQL-gegevens modelleren voor importeren en indexeren in Azure AI Search

  • Artikel

Azure AI Search accepteert een platte rijenset als invoer voor de indexeringspijplijn. Als uw brongegevens afkomstig zijn van gekoppelde tabellen in een relationele SQL Server-database, wordt in dit artikel uitgelegd hoe u de resultatenset maakt en hoe u een bovenliggende en onderliggende relatie in een Azure AI Search-index modelleert.

Ter illustratie verwijzen we naar een hypothetische hotelsdatabase op basis van demogegevens. Stel dat de database bestaat uit een Hotels$ tabel met 50 hotels en een Rooms$ tabel met kamers met verschillende soorten, tarieven en voorzieningen voor in totaal 750 kamers. Er is een een-op-veel-relatie tussen de tabellen. In onze benadering biedt een weergave de query die 50 rijen retourneert, één rij per hotel, met bijbehorende kamerdetails die zijn ingesloten in elke rij.

Tabellen en weergave in de database Hotels

Het probleem van gedenormaliseerde gegevens

Een van de uitdagingen bij het werken met een-op-veel-relaties is dat standaardquery's die zijn gebouwd op gekoppelde tabellen, gedenormaliseerde gegevens retourneren, wat niet goed werkt in een Azure AI Search-scenario. Bekijk het volgende voorbeeld dat deelneemt aan hotels en kamers.

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

Resultaten van deze query retourneren alle velden Hotel, gevolgd door alle velden Room, waarbij voorlopige hotelinformatie voor elke kamerwaarde wordt herhaald.

Gedenormaliseerde gegevens, redundante hotelgegevens wanneer ruimtevelden worden toegevoegd

Hoewel deze query op het oppervlak slaagt (alle gegevens in een platte rijset leveren), mislukt het leveren van de juiste documentstructuur voor de verwachte zoekervaring. Tijdens het indexeren maakt Azure AI Search één zoekdocument voor elke opgenomen rij. Als uw zoekdocumenten eruitzien zoals de bovenstaande resultaten, zou u dubbele waarden hebben waargenomen: zeven afzonderlijke documenten voor het Twin Dome hotel alleen. Een query op "hotels in Florida" retourneert zeven resultaten voor alleen het Twin Dome-hotel, waarbij andere relevante hotels diep in de zoekresultaten worden gepusht.

Als u de verwachte ervaring van één document per hotel wilt krijgen, moet u een rijenset opgeven op de juiste granulariteit, maar met volledige informatie. In dit artikel wordt uitgelegd hoe u dit doet.

Een query definiëren die ingesloten JSON retourneert

Als u de verwachte zoekervaring wilt leveren, moet uw gegevensset uit één rij bestaan voor elk zoekdocument in Azure AI Search. In ons voorbeeld willen we één rij voor elk hotel, maar we willen ook dat onze gebruikers kunnen zoeken op andere kamergerelateerde velden waar ze om geven, zoals het nachttarief, de grootte en het aantal bedden, of een uitzicht op het strand, die allemaal deel uitmaken van een kamerdetail.

De oplossing is om de ruimtedetails vast te leggen als geneste JSON en vervolgens de JSON-structuur in een veld in een weergave in te voegen, zoals wordt weergegeven in de tweede stap.

  1. Stel dat u twee samengevoegde tafels hebt en Hotels$Rooms$, die details bevatten voor 50 hotels en 750 kamers en lid zijn van het veld HotelID. Individueel bevatten deze tafels 50 hotels en 750 gerelateerde kamers.

    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. Maak een weergave die bestaat uit alle velden in de bovenliggende tabel (SELECT * from dbo.Hotels$), met de toevoeging van een nieuw veld Ruimten dat de uitvoer van een geneste query bevat. Een FOR JSON AUTO-component voor SELECT * from dbo.Rooms$ structureert de uitvoer als 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

    In de volgende schermopname ziet u de resulterende weergave, met het veld Rooms nvarchar onderaan. Het veld Kamers bestaat alleen in de weergave Hotelrooms.

    HotelRooms uitzicht

  3. Voer uit SELECT * FROM dbo.HotelRooms om de rijset op te halen. Deze query retourneert 50 rijen, één per hotel, met bijbehorende kamerinformatie als een JSON-verzameling.

    Rowset vanuit hotelrooms weergave

Deze rijenset is nu klaar voor import in Azure AI Search.

Notitie

Bij deze benadering wordt ervan uitgegaan dat ingesloten JSON onder de maximale kolomgroottelimieten van SQL Server valt.

Een complexe verzameling gebruiken voor de 'veel'-kant van een een-op-veel-relatie

Maak aan de zijde van Azure AI Search een indexschema dat de een-op-veel-relatie modelleerde met behulp van geneste JSON. De resultatenset die u in de vorige sectie hebt gemaakt, komt doorgaans overeen met het indexschema dat u hierna hebt opgegeven (we knippen enkele velden voor kortheid).

Het volgende voorbeeld is vergelijkbaar met het voorbeeld in Het modelleren van complexe gegevenstypen. De structuur Ruimten , die de focus van dit artikel heeft gehad, bevindt zich in de veldenverzameling van een index met de naam hotels. In dit voorbeeld ziet u ook een complex type voor Adres, dat verschilt van Ruimten omdat deze bestaat uit een vaste set items, in plaats van het veelvoud, het willekeurige aantal items dat is toegestaan in een verzameling.

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

Gezien de vorige resultatenset en het bovenstaande indexschema hebt u alle vereiste onderdelen voor een geslaagde indexeringsbewerking. De platgemaakte gegevensset voldoet aan de indexeringsvereisten, maar behoudt gedetailleerde informatie. In de Azure AI Search-index vallen zoekresultaten eenvoudig in entiteiten op basis van hotels, terwijl de context van afzonderlijke ruimten en hun kenmerken behouden blijft.

Facetgedrag op subvelden van complex type

Velden met een bovenliggend item, zoals de velden onder Adres en Ruimten, worden subvelden genoemd. Hoewel u een 'facetable'-kenmerk kunt toewijzen aan een subveld, is het aantal facetten altijd voor het hoofddocument.

Voor complexe typen zoals Adres, waarbij er slechts één adres/plaats of adres/stateProvince in het document staat, werkt het facetgedrag zoals verwacht. In het geval van ruimten, waarbij er echter meerdere subdocumenten voor elk hoofddocument zijn, kunnen de facetten misleidend zijn.

Zoals vermeld in complexe typen modellen: "het aantal documenten dat wordt geretourneerd in de facetresultaten worden berekend voor het bovenliggende document (een hotel), niet de subdocumenten in een complexe verzameling (ruimten). Stel dat een hotel 20 kamers van het type 'suite' heeft. Gezien deze facetparameter facet=Kamers/Type, is het aantal facetten één voor het hotel, niet 20 voor de kamers."

Volgende stappen

Met uw eigen gegevensset kunt u de wizard Gegevens importeren gebruiken om de index te maken en te laden. De wizard detecteert de ingesloten JSON-verzameling, zoals de verzameling in Ruimten, en zorgt voor een indexschema dat een complexe typeverzameling bevat.

Index afgeleid door de wizard Gegevens importeren

Probeer de volgende quickstart om de basisstappen van de wizard Gegevens importeren te leren.

Quickstart: Een zoekindex maken met behulp van Azure Portal