Cara memodelkan data SQL relasional untuk impor dan pengindeksan di Azure AI Search

  • Artikel

Azure AI Search menerima set baris datar sebagai input ke alur pengindeksan. Jika data sumber Anda berasal dari tabel yang digabungkan dalam database relasional SQL Server, artikel ini menjelaskan cara membuat kumpulan hasil, dan cara memodelkan hubungan induk-anak dalam indeks Pencarian Azure AI.

Sebagai ilustrasi, kami merujuk ke database hotel hipotetis, berdasarkan data demo. Asumsikan Hotels$ database terdiri dari tabel dengan 50 hotel, dan Rooms$ tabel dengan kamar dengan berbagai jenis, tarif, dan fasilitas, dengan total 750 kamar. Ada hubungan satu-ke-banyak antara tabel. Dalam pendekatan kami, tampilan menyediakan kueri yang mengembalikan 50 baris, satu baris per hotel, dengan detail kamar terkait yang disematkan ke dalam setiap baris.

Tabel dan tampilan pada database Hotel

Masalah denormalisasi pada data

Salah satu tantangan dalam bekerja dengan hubungan satu-ke-banyak adalah bahwa kueri standar yang dibangun pada tabel yang digabungkan mengembalikan data denormalisasi, yang tidak berfungsi dengan baik dalam skenario Pencarian Azure AI. Pertimbangkan contoh berikut yang menggabungkan hotel dan kamar.

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

Hasil dari kueri ini memunculkan semua bidang Hotel, diikuti oleh semua bidang Kamar beserta informasi awal tentang hotel yang diulang untuk setiap nilai kamar.

Denormalisasi pada Data, data hotel berlebihan ketika bidang kamar ditambahkan

Meskipun kueri ini berhasil di permukaan (menyediakan semua data dalam kumpulan baris datar), kueri gagal memberikan struktur dokumen yang tepat untuk pengalaman pencarian yang diharapkan. Selama pengindeksan, Azure AI Search membuat satu dokumen pencarian untuk setiap baris yang diserap. Jika dokumen pencarian Anda terlihat seperti hasil di atas, Anda akan melihat duplikat - tujuh dokumen terpisah untuk hotel Twin Dome saja. Sebuah kueri tentang "hotel di Florida" akan memunculkan tujuh hasil hanya untuk hotel Twin Dome, menyebabkan hotel-hotel lain yang relevan terdorong jauh ke dalam hasil pencarian.

Untuk mendapatkan pengalaman yang diharapkan dari satu dokumen per hotel, Anda harus menyediakan kumpulan baris dengan granularitas yang tepat beserta informasi secara lengkap. Artikel ini menjelaskan bagaimana.

Menentukan kueri yang memunculkan JSON yang disematkan

Untuk memberikan pengalaman pencarian yang diharapkan, himpunan data Anda harus terdiri dari satu baris untuk setiap dokumen pencarian di Azure AI Search. Dalam contoh ini, kami membuat satu baris untuk setiap hotel, tetapi kami juga ingin pengguna kami dapat mencari di bidang kamar terkait lainnya sesuai kebutuhan, seperti tarif per malam, ukuran dan jumlah tempat tidur, atau pemandangan pantai, yang semuanya merupakan bagian dari detail kamar.

Solusinya adalah mengambil detail kamar sebagai JSON bersarang, lalu memasukkan struktur JSON pada bidang dalam tampilan, seperti yang ditunjukkan pada langkah kedua.

  1. Asumsikan Anda memiliki dua tabel gabungan, Hotels$ dan Rooms$, yang berisi detail untuk 50 hotel dan 750 kamar dan bergabung di bidang HotelID. Secara individual, Tabel-tabel ini berisi 50 hotel dan 750 kamar terkait.

    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. Buat tampilan yang terdiri dari semua bidang dalam tabel induk (SELECT * from dbo.Hotels$), dengan penambahan bidang Kamar baru yang berisi output kueri bertumpuk. Klausul FOR JSON AUTO pada SELECT * from dbo.Rooms$ membuat struktur output sebagai 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

    Cuplikan layar berikut memperlihatkan tampilan hasil, dengan bidang nvarchar Kamar pada bagian bawah. Bidang Kamar hanya tersedia pada tampilan HotelRooms.

    Tampilan HotelRooms

  3. Jalankan SELECT * FROM dbo.HotelRooms untuk mendapatkan kumpulan baris. Kueri ini memunculkan 50 baris, satu per hotel beserta informasi kamar terkait sebagai koleksi JSON.

    Kumpulan baris dari tampilan HotelRooms

Set baris ini sekarang siap untuk diimpor ke Azure AI Search.

Catatan

Pendekatan ini memperkirakan bahwa JSON yang disematkan berada di bawah batas ukuran kolom maksimum dari SQL Server.

Gunakan koleksi kompleks untuk sisi "banyak" dari hubungan satu ke banyak

Di sisi Azure AI Search, buat skema indeks yang memodelkan hubungan satu-ke-banyak menggunakan JSON berlapis. Tataan hasil yang Anda buat di bagian sebelumnya umumnya sesuai dengan skema indeks yang disediakan selanjutnya (kami memotong beberapa bidang untuk brevity).

Contoh berikut mirip dengan contoh dalam Cara membuat model jenis data kompleks. Struktur Kamar, yang telah menjadi fokus artikel ini, terletak dalam koleksi bidang indeks bernama hotel. Contoh ini juga menunjukkan jenis kompleks untuk Alamat, yang berbeda dari Rooms karena terdiri dari sekumpulan item tetap, dibandingkan dengan beberapa item selangit yang diizinkan dalam koleksi.

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

Melihat tataan hasil sebelumnya dan skema indeks di atas, Anda sudah memiliki semua komponen yang diperlukan untuk operasi pengindeksan yang sukses. Kumpulan data yang diratakan memenuhi persyaratan pengindeksan namun mempertahankan informasi detail. Dalam indeks Pencarian Azure AI, hasil pencarian mudah jatuh ke entitas berbasis hotel, sambil mempertahankan konteks kamar individual dan atributnya.

Perilaku faset pada subbidang jenis kompleks

Bidang yang memiliki induk, seperti bidang di bawah Alamat dan Ruang, disebut subbidang. Meskipun Anda dapat menetapkan atribut "faset" ke subbidang, jumlah faset selalu untuk dokumen utama.

Untuk jenis kompleks seperti Alamat, di mana hanya ada satu "Alamat/Kota" atau "Alamat/stateProvince" dalam dokumen, perilaku faset berfungsi seperti yang diharapkan. Namun, dalam kasus Rooms, di mana ada beberapa subdokumen untuk setiap dokumen utama, jumlah faset dapat menyesatkan.

Seperti yang tercantum dalam Jenis kompleks model: "jumlah dokumen yang dikembalikan dalam hasil faset dihitung untuk dokumen induk (hotel), bukan subdokumen dalam koleksi kompleks (kamar). Misalnya, anggap hotel memiliki 20 kamar jenis "suite". Mengingat parameter faset ini facet=Rooms/Type, jumlah faset adalah satu untuk hotel, bukan 20 untuk kamar."

Langkah berikutnya

Dengan menggunakan himpunan data Anda, Anda bisa menggunakan wizard Impor data untuk membuat dan memuat indeks. Panduan mendeteksi koleksi JSON yang disematkan, seperti yang terkandung dalam Kamar, dan menyimpulkan skema indeks yang menyertakan koleksi tipe kompleks.

Indeks disimpulkan oleh wizard Impor data

Cobalah mulai cepat berikut ini untuk mempelajari langkah-langkah dasar wizard Impor data.

Mulai cepat: Membuat indeks pencarian menggunakan portal Microsoft Azure