miso_soup3 Blog

主に ASP.NET 関連について書いています。

Azure Search、SQL Server Database のコレクション項目を検索対象とする

Azure Search

Azure Search にてデータソースを Azure SQL Database にした場合の、コレクションの項目を検索可能/ファセット可能にする方法です。

例として、次のように SQL Database でテーブルが定義されている場合、Category.Name で Product を検索できるようにします。

Product

Id Name
1 商品名１

Category

Id Name
1 カテゴリ名１

ProductCategory

Id ProductId CategoryId
1 101 17
1 101 18

方法は適当に言うと、SQL の View で [“hoge”, “hoge2”] と Json 形式で出力し、カスタムインデクサーで jsonArrayToStringCollection として定義します。

参考サイト
目次

前提

Azure Search と Azure SQL Database

Azure Search では、”この中から検索してください”と「データソース（またはストア）」を指定します。 現在、Azure Search では、主に以下のデータに対応しています。

  • Azure SQL Database
  • Azure DocumentDB
  • Azure Blob Storage
  • または Azure VM でホストされている SQL Server

この他、API からデータを追加したり、PDF、HTML、CSV、.txt 等のドキュメントにも対応しています（現在プレビュー）。 この記事で対象にしているのは、この中の Azure SQL Database（or Azure VM の SQL Server）です。

ちなみにAzure のポータルサイト上では、"この Azure SQL Databse で検索してください"とフローから設定することができます。

f:id:miso_soup3:20160628153826p:plain

Table, View

そして、Azure SQL Database を指定すると、Azure のポータルでは”どの Table を対象にするか”を設定することができます。 この選択には、Table だけではなく View も指定することができます。 この記事では View を対象とします。理由は、コレクションの項目を検索対象に含めたいからです。

f:id:miso_soup3:20160628153924p:plain

コレクションの項目

「コレクションの項目を検索対象にしたい」というのは、SQL Database でいえば、いわゆる「Children」も検索対象にしたい、ということです。 例えば、 Product テーブルの子である Category の Name でも検索したい、といった用途です。 （Child 単体ではなく、Children 複数です。 ちなみに単体も可能で、この記事のような複雑な設定はいりません。）

ですが、SQL Database のような RDB の場合は、別テーブルで設計されているのがほとんどです。 なので、Table の指定では Category の Name をデータに含められないため、Category も含めて select するような View を定義し、 Azure Search に”この View を検索対象としてください”と指定します。 （他の手段として、Azure SQL Database から他のデータソース― CSV や DocumentDB に変換する、もあると思います。）

前提の説明は以上で、このコレクションの項目を検索可能にする手順を記載します。

SQL Database 側の用意

まず、SQL Database に View を定義し、そのあと Azure Search 側で インデックス、インデクサーを定義します。

View

SQL Database にて View を作成します。とりあえず、Product を取得したいので次のような T-SQL が思い浮かびます。

select * from Product

これに 複数の Category の情報を追加します。複数の項目を出力するには、以下のように JSON 形式で string の配列で出力します。

["カテゴリ名１", "カテゴリ名２", "カテゴリ名３"]

※ [{ id: 1, name: “カテゴリ名１”}, { id: 2, name: “カテゴリ名２”}, …] ではだめです。

View で上記の文字列を出力するために、例えば ProductId を引数として 1 つの Product から Cateogory.Name を取得する関数を作成します。 例えばこんなスカラー値関数になります。

CREATE FUNCTION [dbo].[GetCategoryNameListText]
(
 @productId int
)
RETURNS nvarchar(max)
AS
BEGIN

delare @result NVARCHAR(3000)
set @result=''
select @result = CASE @result
             WHEN '' THEN ' "' + Category.Name + '" '
                     ELSE @result + ', ' + ' "' + Category.Name + '" '
           END

from ProductCategory
    left outer join Category on
        ProductCategory.CategoryId = Category.Id
Where
    ProductCategory.ProductId = @productId
return '[ ' +  @result + ']' 

END

スカラー値関数を定義したので、View で参照します。 例えばこんな View になります。

CREATE VIEW [dbo].[ProductView] AS

select 
    [dbo].[GetCategoryNameListText](Product.Id) as 'CategoryNameCollection',
    Product.*
from Product

そうすると1行の中に Product と複数の Category の情報が格納されます。

Id Name CategoryNameCollection
1 商品名１ [“カテゴリ名１”, “カテゴリ名２”, “カテゴリ名３”]

Azure Search 側の設定

次に Azure Search 側の設定です。

流れ：

  • インデックスの作成
    • SQL Database の View の列名でインデックスを作成します。「CategoryNameCollection」の型は Collection(Edm.String)　で定義します。
  • インデクサーの作成
    • 作成したインデックスの対象のフィールドを「jsonArrayToStringCollection」として変換するように定義したインデクサーを作成します。
  • インデクサーの実行
  • 検索方法

単語：

Azure Search の構成要素として、次のような単語が登場します。（）内は、今回の場合の具体例です。

  • ドキュメント （インデクサーによって作成された、SQL Database のデータ行。1ドキュメント＝1行）
  • データソース、ストア (Azure SQL Database)
  • インデックス
  • インデクサー

インデックスとは、スキーマのようなもので、どういうフィールド名があって、このフィールドは”検索可能”、”並べ替え可能”、”取得可能”といったようなことを定義することです。このフィールド名は、データソース上で参照するときに使われます。 インデクサーはインデックスの定義に基づきデータソースをクロールします。

インデックスの作成

インデックスの作成時に、コレクションの型を「Collection(Edm.String)」とします。 例えば、REST API でインデックスを作成した場合、次のようになります。

POST /indexes?api-version=2015-02-28-Preview
Content-Type: application/json
api-key: [admin key]

Body: 
{
    "name": "productindexlucene",
    "fields": [
        {
            "name": "Id",
            "type": "Edm.String",
            "key": true,
            "retrievable": true
        },
        {
            "name": "Name",
            "type": "Edm.String",
            "searchable": true,
            "filterable": true,
            "sortable": false,
            "facetable": false,
            "key": false,
            "retrievable": true,
            "analyzer": "ja.lucene"
        },
        {
            "name": "CategoryNameCollection",
            "type": "Collection(Edm.String)",
            "searchable": true,
            "filterable": true,
            "sortable": false,
            "facetable": true,
            "key": false,
            "retrievable": true,
            "analyzer": "ja.lucene"
        },

        //...
    ],
    "suggesters": [
        {
            "name": "suggester",
            "searchMode": "analyzingInfixMatching",
            "sourceFields": [ "Name", "NameKana" ]
        }
    ]
}

CategoryNameCollection が Collection(Edm.String) で定義されています。（CategoryNameCollection は、先ほど作成した View の列名と一致します） クエリ文字列「?api-version=2015-02-28-Preview」が必須かどうかは未確認です。（プレビューバージョンのAPIじゃないとダメだったような、そうじゃないような）

インデックスの作成方法ですが、Azure のポータル上や SDK、他ツール等の方法があります。

インデクサーの作成

インデクサーに、このフィールドは「jsonArrayToStringCollection」として解釈してくれ、と定義します。

インデクサーも同様に REST API で作成します。API の仕様は、Create Indexer (Azure Search Service REST API)　 から確認できます。 例えば次のようになります。

POST /indexers?&api-version=2015-02-28-Preview
Content-Type: application/json
api-key: [admin key]

Body:

{ 
    "name" : "productindexer",
    "description" : "",
    "dataSourceName" : "データソース名。Azure Portal から確認できます。",
    "targetIndexName" : "productindexlucene（先ほど作成したインデックス名）",
    "fieldMappings" : [ { "sourceFieldName" : "CategoryNameCollection", "mappingFunction" : { "name" : "jsonArrayToStringCollection" } } ] 
}

「fieldMappings」の設定が大事なとこです。クエリ文字列「?&api-version=2015-02-28-Preview」もおそらく必須。

参照：

インデクサーの実行

あとは、インデクサーを実行します。が、たぶん先の API でインデクサーを作成した時に、自動的に実行されていると思います（スケジュールの設定によるかも？）。（インデクサーの実行は REST API でも Azure のポータル上から可能です。）

進捗状況は、Azure のポータルから確認します。インデクサーが失敗した場合は、エラーの詳細を確認します。 私の場合はこのようなエラーが発生しました。

"The data field 'CategoryNameCollection' has an invalid value '[  'hoge' ]'. The expected type was 'Collection(Edm.String)'."
"The data field 'CategoryNAmeCollection' has an invalid value. The expected type was 'Collection(Edm.String)'."

原因は、ダブルクォートではなくシングルクォートにしてしまったり、Collection(Edm.String)で設定し忘れていたり、です。

インデクサーの実行が成功すれば、Category の Name で検索できたり、ファセットナビゲーションの項目として Category の Name を参照したりできます。また、検索結果の Json にもコレクションとして格納されています。

検索

検索のときは、例えば、”カテゴリ名１”という Category をもつ Product を検索したい場合は、次のようなクエリ文字列で URL を構築します。

?filter=CategoryNameCollection/any(t: t eq 'カテゴリ名１')

これは OData の構文と同様です。

参照： Azure の検索の OData 式の構文

他

REST API をたたく時に使用したコードを gist で貼っておきます。