メモ:Azure Cognitive Search デフォルトのアナライザー の ストップワード(1文字限定) を調べる

カスタムアナライザー使うのに ja.microsoft がどんなストップワード設定してるか確認用。
※複数文字は日本語なので取り合えずいいや。

雑いけど、ひらがな1文字ずつに分かれるテキスト渡して消えたやつがストップワードだ!って感じで。
テキストの分析 (Azure Cognitive Search REST API) | Microsoft Docs

{
  "text": "あ い う え お か き く け こ さ し す せ そ た ち つ て と な に ぬ ね の は ひ ふ へ ほ ま み む め も や ゆ よ ら り る れ ろ わ を ん が ぎ ぐ げ ご ざ じ ず ぜ ぞ だ ぢ づ で ど ば び ぶ べ ぼ ぱ ぴ ぷ ぺ ぽ",
  "analyzer": "ja.microsoft"
}

ScriptDom で サブクエリを CTE に書き換えるサンプル(叩き台)

だいぶ昔に書いた
SQL Server のバージョン毎の TSqlParser - お だ のスペース
にコメントが付いてたので雑にサンプル。

 yutap01

だいぶ昔の投稿についての質問になります。
よろしければお知恵を拝借したいのですが、

TSqlParserを使って、複雑なTSQLを複数のSQLに分解したいと考えています。
具体的には、SQL文から自動的にサブクエリをwith句として外に抽出するという処理なのですが、具体的な実装方法についてご教授いただけないでしょうか?

from で join とかするサブクエリは、
QueryDerivedTable Class (Microsoft.SqlServer.TransactSql.ScriptDom) | Microsoft Docs
っぽいです。 = や in のサブクエリは
ScalarSubquery Class (Microsoft.SqlServer.TransactSql.ScriptDom) | Microsoft Docs
なのかな?

CTE 作るとこまではサクッと出来たけど、元の From のサブクエリを消すのは面倒そうなので、手抜き。
サブクエリの中のサブクエリとかはゴリゴリやらないとダメかもね。
エイリアスが被る可能性がありそう

select * from 
(select s.Id from (select Id from Table1) s) sub1
inner join 
(select s.Id from (select Id from Table2) s) sub2
on sub1.Id = sub2.Id

サンプルコード

雑なサンプルなので

使ってます。

using Microsoft.SqlServer.TransactSql.ScriptDom;
using System;
using System.Collections.Generic;
using System.IO;

namespace TSqlParser
{
    class Program
    {
        static void Main(string[] args)
        {
            var baseQuery = @"select
  [SubQuery1].[Id]
  , [SubQuery1].[Name]
  , [Table1].[Id]
  , [Table1].[Name]
from 
  ( select
      [Id]
      , [Name]
    from [Table2]
  ) SubQuery1 inner join [Table1] on 
    [SubQuery1].[Id] = [Table1].[Id]
  inner join [Table4] on 
    [Table1].[Id] = [Table4].[Id]
  inner join (
    select 
      [Id]
      , [DivId]
    from [Table5]
    where [EntryDate] >= '2021-01-01'
  ) [SubQuery2] on
    [Table1].[Id] = [SubQuery2].[Id]
where
  [Table4].[Name] like '%hoge%'
  and [SubQuery2].[DivId] > 2
order by
  [SubQuery1].[Id]";

            var parser = new TSql150Parser(false);
            IList<ParseError> errors;
            var parsed = parser.Parse(new StringReader(baseQuery), out errors);
            var v = new SubQueryVisitor();
            parsed.Accept(v);

            var cte = v.SelectStatement.WithCtesAndXmlNamespaces 
                ?? new WithCtesAndXmlNamespaces();

            foreach (var sub in v.SubQueries)
            {
                cte.CommonTableExpressions.Add(new CommonTableExpression()
                {
                    QueryExpression = sub.QueryExpression,
                    ExpressionName = sub.Alias
                });
                sub.QueryExpression = null;
            }
            
            v.SelectStatement.WithCtesAndXmlNamespaces = cte;            

            var generator = new Sql150ScriptGenerator();
            generator.GenerateScript(parsed, out var s);
            foreach (var sub in v.SubQueries)
            {
                s = s.Replace($"() AS [{sub.Alias.Value}]", $"[{sub.Alias.Value}]");
            }
            Console.WriteLine(s);            

            parser.Parse(new StringReader(s), out errors);
            Console.WriteLine("-- エラー件数 --");
            Console.WriteLine(errors.Count);
            Console.ReadKey();
        }
    }

    public class SubQueryVisitor : TSqlFragmentVisitor
    {
        public List<QueryDerivedTable> SubQueries { get; set; } 
            = new List<QueryDerivedTable>();
        public SelectStatement SelectStatement { get; set; }
        public override void ExplicitVisit(SelectStatement node)
        {
            SelectStatement = node;
            base.ExplicitVisit(node);
        }
        public override void ExplicitVisit(QueryDerivedTable node)
        {
            SubQueries.Add(node);
            // サブクエリのエイリアスを [] で囲ってない場合も [] で囲むようにする。(手抜きの Replace用に)
            node.Alias.QuoteType = QuoteType.SquareBracket;
            base.ExplicitVisit(node);
        }
    }
}

メモ:Swashbuckle Swagger で 40系の ProducesResponseType(typeof(void), ...) が ProblemDetails に変わる回避策

ProducesResponseType(typeof(void), ...) causes ApiExplorer to indicate the return type is ProblemDetails · Issue #7874 · dotnet/aspnetcore · GitHub

40系はデフォだと ProblemDetails Class (Microsoft.AspNetCore.Mvc) | Microsoft Docs になってしまうので、
ProducesErrorResponseTypeAttribute Class (Microsoft.AspNetCore.Mvc) | Microsoft Docs でデフォルトの type を変える。

例:403 は void で 400 は IdentityError[] の時

    [Produces("application/json")]
    [Route("api/[controller]")]
    [ApiController]
    public class AccountController : ControllerBase
    {
        [HttpPost("register")]
        [ProducesErrorResponseType(typeof(void))] // これが無いと 403 が ProblemDetails になる
        [ProducesResponseType(403)] // [ProducesResponseType(typeof(void), 403)] にしても ProducesErrorResponseType が無いと ProblemDetails になる
        [ProducesResponseType(typeof(IdentityError[]), 400)]
        [ProducesResponseType(typeof(string), 200)]
        public async Task<object> Hoge([FromBody] HogeModel model)
        {
            // ...
        }

1/30(土) meetup app osaka@5 に参加します #meetupapp

meetup app osaka@5 - connpass

このご時世なんで オンラインの ZOOM ですが、お時間があればぜひ~。
SQL DB をソースに Azure Cognitive Search を使うときのメモ - お だ のスペース
や関係ない質問でも答えれる限り答えま~。

座談会っぽい感じみたいなんで楽しくやりましょう!

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

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

価格

Cognitive Search
料金 - Search | Microsoft Azure
インデックス作るのに AI 使うと追加でお金掛かりそう。
AI エンリッチメントの概念 - Azure Cognitive Search | Microsoft Docs
Cognitive Services をスキルセットにアタッチする - Azure Cognitive Search | Microsoft Docs

チュートリアル

Azure SQL データにインデックスを付ける C# チュートリアル - Azure Cognitive Search | Microsoft Docs

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

Azure SQL データを検索する - Azure Cognitive Search | Microsoft Docs

変更追跡したい場合は、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]"
は "["、"]" で列名囲ってるように見えるけど囲うと例外でてやられる。。

フィールドに属性付けないと使えない
検索インデックスの作成 - 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"
}  

ハイライト

highlighthighlightPreTaghighlightPostTag で結果に 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": ”さfさdふぁsだsdfさd”,
            "Col3": "あかかかっかあsdfdさ",
            "Col4": "SQLWorld is Worldwide!",
            "Col5": null,
            "Col6": [
                12,
                26
            ],
            "Col7": [
                "てすとあああ",
                "てすとかかか"
            ],
        }
    ]
}

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

メモ:T-SQL FOR XML は XML にするから XML でエスケープ必要な文字がエスケープされる

メモ:T-SQL 単一列の結果表をシンプルな JsonArray にしたい - お だ のスペース

で、XML にしたいわけじゃないのに FOR XML 使ってますが、こういう時に XMLエスケープ必要な文字があった場合困ることが起きます。

前回のデータを変えて試してみます。

create table [データ] (
  [Id] bigint not null identity(1, 1) primary key
  , [Name] nvarchar(20) not null
)
go
insert into [データ] ([Name]) values 
(N'あいう&えお')
, (N'かき<div>くけこ</div>')
select concat(',', '"', [Name], '"') from [データ]
for xml path('')
,"あいう&amp;えお","かき&lt;div&gt;くけこ&lt;/div&gt;"

エスケープされてますね。XML 化するのでまあ当然。。

これをエスケープしてない文字列としてとる方法があります。

まず FOR XML クエリの TYPE ディレクティブ - SQL Server | Microsoft Docs を使って XML 型として返します。

select concat(',', '"', [Name], '"') from [データ] 
for xml path(''), type
,"あいう&amp;えお","かき&lt;div&gt;くけこ&lt;/div&gt;"

結果は変わりませんが、XML 型 になっています。
これで XML 型のメソッドが使えます。
xml データ型のメソッド - SQL Server | Microsoft Docs
今回は value() メソッド (xml データ型) - SQL Server | Microsoft Docs を使って値を全部取り出します。

select (
  select concat(',', '"', [Name], '"') from [データ] 
  for xml path(''), type
).value('.', 'nvarchar(max)')
,"あいう&えお","かき<div>くけこ</div>"

はい、エスケープされてない生文字列が取れました。