Share via

Jak modelować dane relacyjne SQL na potrzeby importowania i indeksowania w usłudze Azure AI Search

  • Artykuł

Usługa Azure AI Search akceptuje prosty zestaw wierszy jako dane wejściowe potoku indeksowania. Jeśli dane źródłowe pochodzą z tabel sprzężonych w relacyjnej bazie danych programu SQL Server, w tym artykule wyjaśniono, jak utworzyć zestaw wyników oraz jak modelować relację nadrzędny-podrzędny w indeksie usługi Azure AI Search.

Na ilustracji odwołujemy się do hipotetycznej bazy danych hoteli na podstawie danych demonstracyjnych. Załóżmy, że baza danych składa się z Hotels$ tabeli z 50 hotelami i Rooms$ tabelą z pokojami o różnych typach, stawkach i udogodnieniach, w sumie 750 pokoi. Istnieje relacja jeden do wielu między tabelami. W naszym podejściu widok udostępnia zapytanie, które zwraca 50 wierszy, jeden wiersz na hotel ze skojarzonymi szczegółami pokoju osadzonymi w każdym wierszu.

Tabele i widok w bazie danych Hotels

Problem z zdenormalizowanymi danymi

Jednym z wyzwań związanych z pracą z relacjami "jeden do wielu" jest to, że standardowe zapytania oparte na tabelach sprzężonych zwracają zdenormalizowane dane, które nie działają dobrze w scenariuszu usługi Azure AI Search. Rozważmy następujący przykład, który dołącza do hoteli i pokoi.

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

Wyniki tego zapytania zwracają wszystkie pola Hotel, a następnie wszystkie pola Pokój, a następnie wstępne informacje o hotelu powtarzane dla każdej wartości pokoju.

Dane zdenormalizowane, nadmiarowe dane hotelowe po dodaniu pól pokoju

Chociaż to zapytanie powiedzie się na powierzchni (dostarczając wszystkie dane w płaskim zestawie wierszy), kończy się niepowodzeniem w dostarczaniu odpowiedniej struktury dokumentów dla oczekiwanego środowiska wyszukiwania. Podczas indeksowania usługa Azure AI Search tworzy jeden dokument wyszukiwania dla każdego pozyskanego wiersza. Jeśli twoje dokumenty wyszukiwania wyglądały jak powyższe wyniki, postrzegane byłyby duplikaty — siedem oddzielnych dokumentów dla hotelu Twin Dome. Zapytanie dotyczące "hoteli na Florydzie" zwróci siedem wyników tylko w hotelu Twin Dome, pchając inne odpowiednie hotele głęboko do wyników wyszukiwania.

Aby uzyskać oczekiwane środowisko jednego dokumentu na hotel, należy podać zestaw wierszy z odpowiednim szczegółowością, ale z pełnymi informacjami. W tym artykule wyjaśniono, jak to zrobić.

Definiowanie zapytania zwracającego osadzony kod JSON

Aby zapewnić oczekiwane środowisko wyszukiwania, zestaw danych powinien składać się z jednego wiersza dla każdego dokumentu wyszukiwania w usłudze Azure AI Search. W naszym przykładzie chcemy mieć jeden wiersz dla każdego hotelu, ale chcemy, aby nasi użytkownicy mogli wyszukiwać w innych polach związanych z pokojem, o których dbają, takich jak nocna stawka, rozmiar i liczba łóżek lub widok na plażę, z których wszystkie są częścią szczegółów pokoju.

Rozwiązaniem jest przechwycenie szczegółów pomieszczenia jako zagnieżdżonego kodu JSON, a następnie wstawienie struktury JSON do pola w widoku, jak pokazano w drugim kroku.

  1. Załóżmy, Hotels$ że masz dwa sprzężone tabele i Rooms$, które zawierają szczegóły dla 50 hoteli i 750 pokoi i są połączone w polu HotelID. Indywidualnie te tabele zawierają 50 hoteli i 750 powiązanych pokoi.

    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. Utwórz widok składający się ze wszystkich pól w tabeli nadrzędnej (SELECT * from dbo.Hotels$), z dodatkiem nowego pola Rooms zawierającego dane wyjściowe zagnieżdżonego zapytania. Klauzula AUTO for JSON dla SELECT * from dbo.Rooms$ struktur danych wyjściowych jako 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

    Poniższy zrzut ekranu przedstawia wynikowy widok z polem Rooms nvarchar u dołu. Pole Pokoje istnieje tylko w widoku HotelRooms.

    Widok na pokoje hotelowe

  3. Uruchom polecenie SELECT * FROM dbo.HotelRooms , aby pobrać zestaw wierszy. To zapytanie zwraca 50 wierszy, po jednym na hotel, ze skojarzonymi informacjami o pokoju jako kolekcją JSON.

    Zestaw wierszy z widoku HotelRooms

Ten zestaw wierszy jest teraz gotowy do importowania do usługi Azure AI Search.

Uwaga

W tym podejściu przyjęto założenie, że osadzony kod JSON jest w granicach maksymalnego rozmiaru kolumn programu SQL Server.

Używanie złożonej kolekcji dla relacji "wiele" relacji jeden do wielu

Po stronie usługi Azure AI Search utwórz schemat indeksu, który modeluje relację jeden do wielu przy użyciu zagnieżdżonego kodu JSON. Zestaw wyników utworzony w poprzedniej sekcji zazwyczaj odpowiada podanemu schematowi indeksu (wycinamy niektóre pola w celu zwięzłości).

Poniższy przykład jest podobny do przykładu w temacie Jak modelować złożone typy danych. Struktura Rooms , która koncentruje się na tym artykule, znajduje się w kolekcji pól indeksu o nazwie hotels. W tym przykładzie przedstawiono również złożony typ adresu, który różni się od kolumny Rooms w tym, że składa się z stałego zestawu elementów, w przeciwieństwie do wielokrotności, dowolnej liczby elementów dozwolonych w kolekcji.

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

Biorąc pod uwagę poprzedni zestaw wyników i powyższy schemat indeksu, masz wszystkie wymagane składniki dla pomyślnej operacji indeksowania. Spłaszczone zestaw danych spełnia wymagania dotyczące indeksowania, ale zachowuje szczegółowe informacje. W indeksie usługi Azure AI Search wyniki wyszukiwania można łatwo podzielić na jednostki hotelowe, zachowując jednocześnie kontekst poszczególnych pomieszczeń i ich atrybutów.

Zachowanie aspektów w podpolach typu złożonego

Pola, które mają obiekt nadrzędny, takie jak pola w obszarze Adres i Pokoje, są nazywane polami podrzędnymi. Chociaż można przypisać atrybut "facetable" do pola podrzędnego, liczba aspektów jest zawsze dla dokumentu głównego.

W przypadku typów złożonych, takich jak Adres, gdzie w dokumencie występuje tylko jeden typ "Adres/miasto" lub "Adres/stan" w dokumencie, zachowanie aspektu działa zgodnie z oczekiwaniami. Jednak w przypadku pomieszczeń, w których istnieje wiele dokumentów podrzędnych dla każdego dokumentu głównego, liczby aspektów mogą być mylące.

Jak wspomniano w artykule Typy złożone modelu: "liczba dokumentów zwracanych w wynikach aspektów jest obliczana dla dokumentu nadrzędnego (hotel), a nie dokumentów podrzędnych w złożonej kolekcji (pokoje). Załóżmy na przykład, że hotel ma 20 pokoi typu "suite". Biorąc pod uwagę ten parametr facet facet=Rooms/Type, liczba aspektów jest jedną dla hotelu, a nie 20 dla pokoi."

Następne kroki

Korzystając z własnego zestawu danych, możesz użyć Kreatora importu danych, aby utworzyć i załadować indeks. Kreator wykrywa osadzoną kolekcję JSON, taką jak ta zawarta w obszarze Rooms, i wywnioskuje schemat indeksu, który zawiera złożoną kolekcję typów.

Kreator importowania danych wywnioskowany przez indeks

Wypróbuj poniższy przewodnik Szybki start, aby poznać podstawowe kroki kreatora importowania danych.

Szybki start: tworzenie indeksu wyszukiwania przy użyciu witryny Azure Portal