SQL DB をソースに Azure Cognitive Search を使うときのメモ

Azure SQL Database のデータを Azure Cognitive Search で全文検索するときに見る資料とかメモ
兼 meetup app vol.5？の資料。

変更追跡したい場合は、Change Tracking を使った SQL 統合変更追跡ポリシー
変更の追跡について - SQL Server | Microsoft Docs

要件として使えない場合は、rowversion を使った高基準値変更検出ポリシー
rowversion (Transact-SQL) - SQL Server | Microsoft Docs

高基準値変更検出ポリシーの
"highWaterMarkColumnName" : "[a rowversion or last_updated column name]"
論理削除列削除検出ポリシーの
"softDeleteMarkerValue" : "[the value that indicates that a row is deleted]"
は "["、"]" で列名囲ってるように見えるけど囲うと例外でてやられる。。

'Error with data source: Unclosed quotation mark after the character string '[ColName] SET NO_BROWSETABLE OFF;'.
Incorrect syntax near '[ColName] SET NO_BROWSETABLE OFF;'. Please adjust your data source definition in order to proceed.
Status: 400 (Bad Request)
— oda shinsuke (@shinsukeoda) 2021年1月4日

フィールドに属性付けないと使えない
インデックスを作成する - Azure Cognitive Search | Microsoft Docs

searchable : search (Lucene) で検索可能
filterable：filter (OData) で検索可能
sortable : order by 指定可能
retrievable : これないと検索結果として取得できない

前方一致したい

カスタムアナライザーでやる。
文字列フィールドにカスタムアナライザーを追加する - Azure Cognitive Search | Microsoft Docs

インデックス作るサンプルコード
search-dotnet-getting-started/DotNetHowToIndexers at master · Azure-Samples/search-dotnet-getting-started · GitHub
を参考に該当する箇所変えてってー。

定義

// 継承する必要ないけど、INDEX の定義と
// Analyser / Tokenizer の追加のとこの2か所で NAME 使うので継承してる
public class PrefixCustomAnalyzer : CustomAnalyzer
{
    public const string NAME = "Prefix-Analyzer";
    public PrefixCustomAnalyzer() 
      : this(NAME, PrefixCustomTokenizer.NAME) { }
    public PrefixCustomAnalyzer(string name, 
      LexicalTokenizerName tokenizerName) : base(name, tokenizerName)
    {
        // 小文字でも検索できるように
        TokenFilters.Add(TokenFilterName.Lowercase);
    }
}
public class PrefixCustomTokenizer : EdgeNGramTokenizer
{
    public const string NAME = "Prefix-Tokenizer";
    public PrefixCustomTokenizer() : this(NAME) { }
    public PrefixCustomTokenizer(string name) : base(name)
    {
        MinGram = 1;
        MaxGram = 30; // とりま30文字までで。 max 300文字まで出来る
    }
}
// サンプルでは Hotel だけどここでは変えた
public class PrefixSeachableDocument
{
    [SimpleField(IsFilterable = true, IsKey = true)]
    public string Id { get; set; }
    [SearchableField(IsFilterable = true, 
       AnalyzerName = LexicalAnalyzerName.Values.JaLucene)]
    public string Title { get; set; }
    // Title は前方一致もしたいー
    [SearchableField(IsFilterable = true, 
      AnalyzerName = PrefixCustomAnalyzer.NAME)]
    public string PrefixTitle { get; set; }
}

インデックス作成の箇所

       var searchIndex = new SearchIndex(
         "hotels-sql-idx", searchFields);
        // 作ったAnalyzer と Tokenizer を登録する。
        // 継承したクラスじゃなくても、
        // ここでプロパティを適当に設定するでもOK。
        // Name さえ定義のクラスとあってれば
        searchIndex.Analyzers.Add(new PrefixCustomAnalyzer());
        searchIndex.Tokenizers.Add(new PrefixCustomTokenizer());

親子関係の子も全文検索したい

複合データ型をモデル化する方法 - Azure Cognitive Search | Microsoft Docs
インポートおよびインデックス作成用に SQL リレーショナルデータをモデル化する - Azure Cognitive Search | Microsoft Docs

View でもいいけど、1列に Json で関係しているデータ放り込む。

対応してる型
サポートされているデータ型 (Azure Cognitive Search REST API) | Microsoft Docs

喰わしたテキストがどんな分割されてるかの確認

テキストの分析 (Azure Cognitive Search REST API) | Microsoft Docs

検索

OData の filter と Lucene の search
完全一致や範囲検索は filter、全文検索は search

ドキュメントの検索 (Azure Cognitive Search REST API) | Microsoft Docs

search と filter は同時に指定も可能。

filter

OData 言語の概要 - Azure Cognitive Search | Microsoft Docs
' はエスケープ必要

メモ：コレクション内の検索
OData コレクション演算子のリファレンス - Azure Cognitive Search | Microsoft Docs

search でのメモ

Lucene クエリ構文 - Azure Cognitive Search | Microsoft Docs
+ - & | ! ( ) { } [ ] ^ " ~ * ? : \ / はエスケープ必要

単一項目に対しての条件は、fieldName:searchExpression でフィールド指定で検索する。
複数項目またがって検索する場合は、ドキュメントの検索のパラメータ searchFields を指定し、search はフィールド指定いない。
混合も可能。
例：Col1、Col2、Col3 をおだ、Col4 は SQLWorld で検索

{  
     "count": true,
     "queryType": "full",
     "searchFields": "Col1, Col2, Col3",
     "search": "おだ AND Col4:SQLWorld"
}

ハイライト

highlight、highlightPreTag、highlightPostTag で結果に highlights が取れる。
タグはデフォルトだと、<em></em>

request

{  
     "count": true,
     "highlight": "Col1、Col2、Col3、Col4",  
     "highlightPreTag": "[hoge]",
     "highlightPostTag": "[/hoge]",
     "queryType": "full",
     "searchFields": "Col1, Col2, Col3",
     "search": "Blog AND Col4:SQLWorld"
}

response (手で作ってるから細部は適当)

{
    "@odata.context": "https://xxxxx.search.windows.net/indexes('index-name')/$metadata#docs(*)",
    "@odata.count": 1,
    "value": [
        {
            "@search.score": 1,
            "@search.highlights": {
                "Col1": [
                    "この [hoge]Blog[/hoge] は おだのスペース"
                ],
                "Col4": [
                    "[hoge]SQLWorld[/hoge] is Worldwide!"
                ]
            },
            "Id": "5",
            "Col1": "この Blog は おだのスペース",
            "Col2": ”さｆさｄふぁｓだｓｄｆさｄ”,
            "Col3": "あかかかっかあｓｄｆｄさ",
            "Col4": "SQLWorld is Worldwide!",
            "Col5": null,
            "Col6": [
                12,
                26
            ],
            "Col7": [
                "てすとあああ",
                "てすとかかか"
            ],
        }
    ]
}

ハイライトは HTML にレンダリングするなら、タグにせずに別なのにして HTML エンコードしてからタグに置換した方が良さげ。

価格

チュートリアル

SQL DB をデータソースにする時の考慮事項等