如何建立 SQL 關聯式資料的模型，以便在 Azure AI 搜尋服務中匯入和編製索引

發行項
03/20/2024

Azure AI 搜尋服務接受一般資料列集做為編製管線索引的輸入。如果您的來源資料源自 SQL Server 關聯式資料庫中的聯結資料表，本文會說明如何建構結果集，以及如何在 Azure AI 搜尋服務索引中建立父子關聯性的模型。

如圖所示，我們會以示範資料為基礎，參考假設的 Hotels 資料庫。假設資料庫包含 Hotels$ 50 家酒店的數據表，以及 Rooms$ 具有不同類型、費率和設施的數據表，總共有 750 個房間。資料表之間有一對多關聯性。在我們的方法中，檢視表會提供可傳回 50 個資料列的查詢，每家旅館有一個資料列，還有內嵌於每個資料列中相關聯的房間詳細資料。

Hotels 資料庫中的數據表和檢視

反正規化資料的問題

處理一對多關聯性的其中一項挑戰，就是建置於聯結資料表上的標準查詢會傳回反正規化資料，這在 Azure AI 搜尋服務情節中無法正常運作。請考慮下列聯結旅館和房間的範例。

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

此查詢的結果會傳回所有旅館欄位，後面接著所有房間欄位，且每個房間值的初步旅館資訊會重複。

新增房間欄位時，反正規化數據、備援旅館數據

雖然此查詢表面上成功 (提供一般資料列集中的所有資料)，但無法為預期的搜尋體驗提供適當的文件結構。在編製索引期間，Azure AI 搜尋服務會為每個擷取的資料列建立一份搜尋文件。如果您的搜尋文件看起來像上述結果，您只會察覺重複的項目 - Twin Dome 旅館獨自有七份個別文件。「佛羅里達州的旅館」查詢只會傳回 Twin Dome 旅館的七筆結果，並將其他相關旅館深入推送至搜尋結果。

若要取得每家旅館有一份文件的預期體驗，您應該以適當的細微性提供一個資料列集，但包含完整的資訊。本文說明執行方法。

定義可傳回內嵌 JSON 的查詢

若要提供預期的搜尋體驗，資料集應該在 Azure AI 搜尋服務中包含每份搜尋文件有一個資料列。在我們的範例中，我們希望每家旅館有一個資料列，但我們也希望使用者能夠搜尋他們關心的其他房間相關欄位，例如每晚費率、房間大小和數量或海景，這些全都屬於房間詳細資料。

解決方案是將房間詳細資料擷取為巢狀 JSON，然後將 JSON 結構插入檢視中的欄位，如第二個步驟所示。

假設您有兩個聯結的數據表 Hotels$ ，以及 Rooms$，其中包含 50 個旅館和 750 個房間的詳細數據，並且已加入 HotelID 字段。這些資料表個別包含 50 家旅館和 750 個相關房間。

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

建立由父代資料表中的所有欄位所組成的檢視 (SELECT * from dbo.Hotels$)，並新增包含巢狀查詢輸出的新 Rooms 欄位。 SELECT * from dbo.Rooms$ 上的 FOR JSON AUTO 子句將輸出構造為 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
```
下列螢幕擷取畫面顯示所產生的檢視，其中 Rooms nvarchar 欄位位於底部。 Rooms 欄位只存在於 HotelRooms 檢視中。
執行 SELECT * FROM dbo.HotelRooms 以擷取資料列集。此查詢會傳回 50 個資料列，每個旅館一個資料列，還有相關聯的房間資訊做為 JSON 集合。

此資料列集現在已準備好匯入 Azure AI 搜尋服務中。

注意

此方法假設內嵌的 JSON 在 SQL Server 的最大資料行大小限制之下。

針對一對多關聯性的「多」端使用複雜的集合

在 Azure AI 搜尋服務端，建立索引結構描述，以使用巢狀 JSON 建立一對多關聯性的模型。您在上一節中建立的結果集通常會對應至下一個提供的索引架構（我們剪下一些欄位以求簡潔）。

下列範例類似於如何建立複雜資料類型的模型中的範例。本文的重點是 Rooms 結構，其位於名為 hotels 的索引欄位集合中。此範例也會針對 Address 顯示複雜類型，其與 Rooms 不同，因為它是由一組固定的項目所組成，而不是集合中允許的多個任意數的項目。

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

假設先前的結果集和上述索引架構，您擁有成功編製索引作業所需的所有元件。壓平合併的資料集符合索引編製需求，但仍會保留詳細資訊。在 Azure AI 搜尋索引中，搜尋結果很容易落入以酒店為基礎的實體，同時保留個別房間及其屬性的內容。

具有父代的欄位 (例如位址和會議室底下的欄位) 稱為「子欄位」。雖然您可以將「可多面向」屬性指派給子欄位，但 Facet 的計數一律適用於主文檔。

對於位址等複雜類型，其中文件中只有一個 "Address/City" 或 "Address/stateProvince"，Facet 行為會如預期般運作。不過，如果是會議室，其中每份主文件都有多個子文件，Facet 計數可能會產生誤導。

如模型複雜類型中所述：「Facet 結果中所傳回的文件計數是針對父文件 (飯店) 計算而來，而不是複雜集合中的子文件 (房間)。例如，假設飯店有 20 間類型為「套房」的房間。如果有此 Facet 參數 facet=Rooms/Type，則 Facet 計數會是一間飯店，而不是 20 間房間。」

下一步

使用您自己的資料集，您可使用匯入資料精靈來建立和載入索引。此精靈會偵測內嵌的 JSON 集合，例如 Rooms 內含的 JSON 集合，並推斷包含複雜類型集合的索引結構描述。

請嘗試下列快速入門，以了解匯入資料精靈的基本步驟。

快速入門：使用 Azure 入口網站建立搜尋索引

如何建立 SQL 關聯式資料的模型，以便在 Azure AI 搜尋服務中匯入和編製索引

反正規化資料的問題

定義可傳回內嵌 JSON 的查詢

針對一對多關聯性的「多」端使用複雜的集合

複雜類型子欄位上的 Facet 行為

下一步

其他資源