0 フォロワー · 156 投稿

SQLは、リレーショナルデータベースにデータを格納、操作、および取得するための標準言語です。

お知らせ Mihoko Iijima · 4月 24, 2022

開発者のみなさん、こんにちは!

2022年3月9日開催「InterSystems Japan Virtual Summit 2022」のセッション「FHIR SQL Builder」のアーカイブを YouTube に公開いたしました。

(プレイリストはこちら

日本でも徐々に広がりを見せている HL7 FHIR。FHIR を活用したプロジェクトも着実に増えてきています。

取得した FHIR データをどのように利用するのか。その中で、FHIR データを使用して分析したいというニーズは必ず出てくるかと思います。

InterSystems では、FHIR データ分析を容易に可能にする新機能「FHIR SQL Builder」を発表いたしました。

この機能を利用することにより、FHIR リポジトリに格納したデータを通常の SQL で取り出し利用することが可能になります。

この機能の概要と使用方法をデモをまじえてご紹介いたします。

※ FHIR SQL Builder は、現在開発中機能であるため、メニュー名や画面イメージなどが将来変更される場合もあります。予めご了承ください。 
 

【目次】

01:00 FHIRに関する国内の動向

04:00 FHIR SQL Builder のご紹介

10:03 デモ

0
0 189
お知らせ Mihoko Iijima · 4月 3, 2022

開発者のみなさん、こんにちは!

2021年10月に4回シリーズで開催した「InterSystems IRIS 開発者向けウェビナー」第2回目の「Python Gateway のご紹介」のアーカイブをYouTubeに公開いたしました。

InterSystems IRIS / IRIS for Health バージョン2021.1より、External Language Gateway に Python のサポートが追加されました。また、External Language Gateway の機能強化により外部ストアドプロシージャが利用できるようになり、Java、.Net、Python のコードを SQL から直接実行できるようになりました。

ウェビナーでは、Python からの利用法のついて、デモを交えながらご紹介しています。

ぜひご覧ください!

(IRIS 2021.1新機能全体のご紹介については、こちらのYouTubeをご参照ください。)

【目次】

0:00 ご紹介内容の説明

2:26 PythonからIRISにアクセスする方法

5:07 キーバリュー形式でIRISにアクセスできる「Native API」とは

12:04 Python Gatewayのご紹介

16:21 Python Gateway 使用例

22:12 デモ

30:15 まとめ

0
0 118
記事 Toshihiko Minamoto · 4月 1, 2022 7m read

Benjamin De Boe がユニバーサルキャッシュクエリに関する素晴らしい記事を書いていますが、ユニバーサルキャッシュクエリ(UCQ)とは一体何でしょうか。また、昔ながらの埋め込み SQL を書いている場合になぜそれを気にする必要があるのでしょうか。  Caché と Ensemble において、キャッシュクエリは xDBC と動的 SQL を解決するために生成されるでしょうが、  InterSystems IRIS の埋め込み SQL は、キャッシュクエリを使用するように改善されました。そこで、名前に「ユニバーサル」が追加されているのです。  現在では、IRIS で実行されるすべての SQL は、UCQ クラスから実行されるようになっています。

InterSystems はなぜこのように変更したのでしょうか?  良い質問です!  ここでの大きなメリットは、ライブ環境での柔軟性にあります。  以前は、インデックスを追加したり、TuneTable を実行したりすると、キャッシュクエリの SQL はこの新しい情報をすぐに利用していたにもかかわらず、埋め込み SQL はクラスまたはルーチンが手動でコンパイルされるまで変更されないままになっていました。  アプリケーションがデプロイされたクラスを使用していた場合、または OBJ コードのみを出荷していたのであれば、顧客のシステムで再コンパイルすることなど不可能です。  現在では、システム上のすべての SQL ステートメントが最新の def. と利用可能な最新のチューニングデータを使用できるようになっています。  将来的には、InterSystems IRIS には、本番環境システムを毎晩監視して調整し、テーブルがどのようにクエリされているかに応じて SQL プランをカスタマイズする、オプションのツールが用意される予定です。  このツールセットが拡充するにつれ、ユニバーサルキャッシュクエリの力も大きくなるでしょう。

埋め込み SQL は以前より遅くなっていませんか?  その通りとも、そうでないとも言えます。  別のルーチンでタグを呼び出すと、同じルーチンで呼び出すよりも少しコストがかかるため、速度は遅くなりますが、UCQ コード生成は埋め込みとは異なり、これらの変更を使用することで、別のルーチンを呼び出すコスト以上のメリットがあります。  UCQ コードの処理が遅くなっているケースはあるか、と訊かれれば、  「はい」と答えることになりますが、全体的にはパフォーマンスが向上するはずです。  私はずいぶん前から埋め込み SQL を専門としているため、埋め込み SQL は動的 SQL よりも高速だということを常に指摘していますが、  確かに高速と言えども、オブジェクトを高速化するために行われたすべての作業を考えると、この 2 つの手法の差は小さいため、動的 SQL を使用していることをからかったりはしません。

エラーはどのようにしてチェックするようになりましたか?  埋め込み SQL のエラー処理は変更されていません。  エラーに遭遇した場合、SQLCODE が負の数に設定され、%msg がそのエラーの説明に設定されます。  変更されたのは、発生するエラーの種類です。  デフォルトの動作では、SQL は初めてクエリが実行されるまでコンパイルしないようになりました。  つまり、ルーチンのフィールドまたはテーブルのスペルを間違えても、そのルーチンをコンパイルするときにはエラーは報告されません。このエラーは、動的 SQL と同じように、初めて実行したときに報告されます。  SQLCODE はすべての SQL コマンドに設定されていますが、私のように面倒くさがり屋の方は、FETCH の後にのみ SQLCODE をチェックするでしょう。  OPEN でもチェックするのもお勧めです。

               &SQL(DECLARE cur CURSOR FOR
                    SELECT Name,junk 
                    into :var1, :var2
                    FROM Sample.Person)
                &SQL(OPEN cur)
                write !,"Open Status: ",SQLCODE,?20,$G(%msg)
                for  {
                               &SQL(FETCH cur)
                               write !,"Fecth Status: ",SQLCODE,?20,$G(%msg)
                               QUIT:SQLCODE'=0
                               !,var1
                }
                &SQL(CLOSE cur)
                write !,"Close Status: ",SQLCODE,?20,$G(%msg)
                QUIT

上記のコードでは、SELECT に無効なフィールドが含まれています。  ルーチンのコンパイル時には SQL をコンパイルしないため、このエラーは報告されません。  コードを実行すると、OPEN がコンパイルエラーを報告し、FETCH と CLOSE は、カーソルが開かないエラーを報告します。  %msg は変更されないため、どのタイミングで確認しても、役立つ情報を得られます。

USER>d ^Embedded

Open Status:  -29    Field 'JUNK' not found in the applicable tables^DECLARE cur CURSOR FOR SELECT Name , junk INTO compiling embedded cached query from Embedded.mac

Fetch Status:  -102  Field 'JUNK' not found in the applicable tables^DECLARE cur CURSOR FOR SELECT Name , junk INTO compiling embedded cached query from Embedded.mac

Close Status:  -102  Field 'JUNK' not found in the applicable tables^DECLARE cur CURSOR FOR SELECT Name , junk INTO compiling embedded cached query from Embedded.mac

埋め込み SQL を変更したくない場合はどうなりますか?  これは、Frozen Query Plans を使用することで達成できます。  簡単に言うと、IRIS がメジャーアップグレードされるたびに、すべての SQL ステートメントはフリーズされます。そのため、ユーザーが許可しない限り、何も変更されることはありません。  これについては、こちらをお読みください。

さて、UCQ の処理の話に戻りましょう。  アプリケーションの埋め込み SQL プランをフリーズするには、以下の 3 つの方法があります。

  • IRIS.DAT を出荷する場合: 
    1. Do $SYSTEM.OBJ. GenerateEmbedded() で、埋め込み SQL の UTC を生成します。
    2. Do $SYSTEM.SQL.Statement.FreezeAll() で、プランをフリーズします。
    3. IRIS.DAT を出荷します。
  • xml ファイルを使用する場合: 
    1. Do $SYSTEM.OBJ. GenerateEmbedded() で、埋め込み SQL の UTC を生成します。
    2. Do $SYSTEM.SQL.Statement.FreezeAll() で、プランをフリーズします。
    3. Do $SYSTEM.SQL.Statement.ExportAllFrozenPlans() で、フリーズしたプランをエクスポートします。
    4. Do $SYSTEM.SQL.Statement.ImportFrozenPlans() で、アプリケーションを読み込んだ後に、フリーズしたプランを読み込みます。
  • 顧客サイトで UTC プランをフリーズする場合:
    1. 埋め込み SQL のあるコードを顧客システムに読み込みます。
    2. Do $SYSTEM.OBJ. GenerateEmbedded() で、埋め込み SQL の UTC を生成します。
    3. Do $SYSTEM.SQL.Statement.FreezeAll() で、生成されたすべてのプランをフリーズします。
  • 以前の動作に戻すことはできますか?  いいえ。これが現在の動作です。  開発者の観点からは、/compileembedded=1 というフラグをコンパイラのオプションに追加することで、以前の動作に戻すことができます。  これによって、クラスまたはルーチンをコンパイル中に UCQ クラスを生成するようにコンパイラに指示されます。  SQL に問題がある場合は、以前と同様にコンパイル時に報告されます。

    Compiling routine : Embedded.mac
    ERROR: Embedded.mac(5) : SQLCODE=-29 : Field 'JUNK' not found in the applicable tables^DECLARE cur CURSOR FOR SELECT Name , junk INTO compiling embedded cached query from Embedded.mac
    Detected 1 errors during compilation in 0.034s.

    埋め込み SQL を初めて実行するときに UCQ クラスを生成するオーバーヘッドが気になる場合は、このステップをアプリケーションインストールに加えて、これらをすべて予め生成することが可能です: Do $SYSTEM.OBJ. GenerateEmbedded()

    これはユニバーサルキャッシュクエリと埋め込み SQL を非常に全般的に見た概要です。  内部で何が行われているかについての実際の詳細には触れていません。  ユーザーが IRIS で埋め込み SQL を使用する際に遭遇する可能性のあることについて説明することにしました。  全体的に UCQ に移行すると、すべてのタイプの SQL で SQL のパフォーマンスの一貫性が高まり、本番システムでの SQL の更新がより簡単になります。  いくらかの調整が必要となるでしょうが、  コンパイラフラグの追加が大きく役立ちます。  今後は、生成されたコードを新しい場所で探すことに慣れる必要があります。  この件や InterSystems IRIS での SQL に関するその他についてのご質問、コメント、懸念があれば、私にお知らせください。

    0
    0 408
    記事 Tomoko Furuzono · 3月 29, 2022 1m read

    これは、InterSystems FAQサイトの記事です。
     

    SQLの場合、NULLデータと空文字列 ('')は区別されます。
    それぞれの設定・確認方法は、以下のようになります。

    (1) NULLデータ

    【SQL】 

    insert into test (a) values (NULL)
    select * from test where a IS NULL

    【InterSystems ObjectScript】

    set x=##class(User.test).%New()
    set x.a=""

    (2) 空文字列 ('')

    【SQL】

    insert into test (a) values ('')
    select * from test where a = ''

    【InterSystems ObjectScript】

    set x=##class(User.test).%New()
    set x.a=$C(0)

    詳細については、以下ドキュメントをご参照ください。

    NULL および空文字列【IRIS】
    NULL および空文字列

    0
    0 337
    記事 Hiroshi Sato · 3月 23, 2022 2m read

    質問

    現在使っているリレーショナルデータベースからInterSystems IRIS Data Platformに移行することは可能ですか?

    答え

    InterSystems IRIS Data Platform(以下IRISと表記)は、SQLをサポートしていますので、多くの場合リレーショナルデータベースで構築されたアプリケーションは、大部分のコードを変更することなくIRISに移行することができます。

    ただしSQLの各実装毎にSQL標準を拡張した仕様等があり、それにIRISが全て対応していませんので、コードの書き換えが必要なケースもあります。

    また各実装が独自に用意した標準でない通信系API等にも対応していませんので、その場合にはJDBC、ODBCなどの標準的な通信APIに置き換える必要があり、かなり書き換えが必要になります。
    (使用されているSQL文等はそのまま流用可能な場合が多いです。)

    さらにそのままコードを移行できるケースであっても動作保証はできませんので、必ず動作確認のためのテスト作業は必要になります。

    以上のことから他データベースシステムからIRISシステムへの移行には相応の移行コストがかかるため、移行は必ずしも容易とは言えませんが、それでも移行を決断されたお客様はたくさんおられます。

    そしてほとんどのお客様は移行したシステムの性能、開発生産性、保守性、運用の容易さ等に満足されております。

    0
    0 136
    記事 Toshihiko Minamoto · 3月 2, 2022 5m read

    これは、IRIS でリレーショナルデータをクエリするアナリストとアプリケーションに、さらに優れた適応性とパフォーマンスによるエクスペリエンスを提供する IRIS SQL のイノベーションをトピックとした短い連載の 3 つ目の記事です。 2021.2 では連載の最後の記事になるかもしれませんが、この分野ではさらにいくつかの機能強化が行われています。 この記事では、このリリースで収集し始めたヒストグラムという追加のテーブル統計について、もう少し詳しく説明します。

    ヒストグラムとは?

    ヒストグラムは数値フィールド(またはより広範には、厳密な順序を持つデータ)のデータ分布の近似表現です。 このようなフィールドの最小値、最大値、および平均値がわかれば役立ちますが、データが 3 つのポイント間でどのように分布しているかはほとんどわかりません。 ここで役立つのがヒストグラムです。値の範囲をバケットに分割し、バケットごとに出現するフィールド値の数をカウントします。

    これは非常に柔軟な定義であるため、バケットがフィールド値に関して同じ「幅」になるように、またはカバーされるサンプル値の数に関して同じ「大きさ」になるように、バケットのサイズを選択することができます。 後者の場合、各バケットには同じパーセンテージの値が含まれるため、バケットはパーセンタイルを表します。 以下のグラフは、日数で表現された同じバケット幅を使用して、Aviation Demo データセットの EventData フィールドのヒストグラムをプロットしています。

    ヒストグラムが必要な理由

    カリフォルニア州で 2004 年より前のすべてのイベントについて、このデータセットのクエリを実行しているとします。

    SELECT * FROM Aviation.Event WHERE EventDate < '2004-05-01' AND LocationCountry = 'California'

    ランタイムプランの選択」という前の記事では、テーブル統計で LocationCounty のようなフィールドの選択性と潜在的な外れ値をキャプチャする方法についてすでに説明しています。 しかし、そのような個別のフィールド値の統計は、EventDate での < 条件ではあまり実用的ではありません。 この条件の選択制を計算するには、2004 年 5 月 1 日までのすべての潜在的な EventDate 値の選択制を集計する必要があり、クエリのプランニング時に行えるような手っ取り早い見積もりではなく、それだけで非常に厳しいクエリとなる可能性があります。 ここで使用できるのがヒストグラムです。

    EventDate 値の分布のヒストグラムデータを見てみましょう。今回は、データを同じサイズの 16 個のバケットに分割し、各バケットには 6.667% のデータが保持されています。 このようにすると、クエリコストの見積もりに使用できるパーセンタイルと選択制の数値に簡単に変換できます。 このテーブルを読み取るために、4 行目を見てみましょう。値の 20%(各 6.667% の 3 つのバケット)がこのバケットの下限である 2003 年 6 月 22 日より前にあり、さらに 6.667% の値が 2003 年 9 月 19 日まで保持されています。 

    <colgroup><col style="width:48pt" width="64"><col style="width:61pt" width="81"><col style="width:64pt" width="85"></colgroup>
    <td>
      Percentile
    </td>
    
    <td>
      Value
    </td>
    
    <td>
      0%
    </td>
    
    <td>
      21/12/2001
    </td>
    
    <td>
      7%
    </td>
    
    <td>
      02/07/2002
    </td>
    
    <td>
      13%
    </td>
    
    <td>
      19/01/2003
    </td>
    
    <td>
      20%
    </td>
    
    <td>
      22/06/2003
    </td>
    
    <td>
      27%
    </td>
    
    <td>
      19/09/2003
    </td>
    
    <td>
      33%
    </td>
    
    <td>
      30/12/2003
    </td>
    
    <td>
      40%
    </td>
    
    <td>
      01/10/2004
    </td>
    
    <td>
      47%
    </td>
    
    <td>
      01/10/2005
    </td>
    
    <td>
      53%
    </td>
    
    <td>
      20/08/2006
    </td>
    
    <td>
      60%
    </td>
    
    <td>
      14/01/2007
    </td>
    
    <td>
      67%
    </td>
    
    <td>
      02/04/2008
    </td>
    
    <td>
      73%
    </td>
    
    <td>
      14/05/2008
    </td>
    
    <td>
      80%
    </td>
    
    <td>
      29/11/2008
    </td>
    
    <td>
      87%
    </td>
    
    <td>
      01/06/2010
    </td>
    
    <td>
      93%
    </td>
    
    <td>
      30/10/2011
    </td>
    
    <td>
      100%
    </td>
    
    <td>
      26/09/2012
    </td>
    
    Bucket
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15

    上記のクエリ例で使用されているカットオフ日(2004 年 5 月 1 日)は、5 番目のバケットにあり、その日付より前には 33% から 40% の値があります。 バケットが小さくなるにつれ、その_中_の分布はほぼ均一であると見なすことができ、下限と上限の間を単に補完することができます。つまり、この場合、選択性は約 37% となり、これをクエリコストの見積もりに使用することができます。 

    ヒストグラムの使用を可視化するには、もう一つ、累積分布グラフとしてプロットする方法があります。 X 軸で 2004 年 5 月 1 日の線(値)がどのように描かれるかを確認すれば、Y 軸で 約 37% と解釈できます。 

      

    上記の例では、わかりやすくするために上限のみの範囲条件を使用していますが、このアプローチは、下限または間隔条件(BETWEEN 句を使用するなど)を使用しても当然動作します。 

    2021.2 より、文字列を含むすべての照合フィールドのテーブル統計の一環としてヒストグラムを収集しており、それを使用して RTPC の一部として範囲選択性を推定することができるようになっています。 実世界での多くのクエリには日付(およびその他の)フィールドでの範囲条件が伴うため、この IRIS SQL の機能強化によって、多くのお客様のクエリプランに役立つと信じています。いつものように、皆さんの体験をお聞かせください。

    0
    0 110
    記事 Toshihiko Minamoto · 3月 1, 2022 5m read

    これは、適応性とパフォーマンスに優れた SQL エクスペリエンスを提供する 2021.2 SQL 強化機能に関する連載第 2 回目の記事です。 この記事では、前の記事で説明したランタイムプランの選択機能の主要な入力であるテーブル統計の収集におけるイノベーションに焦点を当てます。

    皆さんには次のことを何度もお伝えしてきました:

    Tune tableを実行しましょう!』

    TUNE TABLE SQL コマンドまたは$SYSTEM.SQL.Stats.Table ObjectScript API を通じてテーブルをチューニングすることは、IRIS SQL が適切なクエリプランをはじき出すのに役立つテーブルデータの統計情報を収集することです。 これらの統計情報には、テーブル内のおおよその行数など、オプティマイザーが JOIN の順序(通常、最も小さいテーブルから始めるのが最も効率的です)などを決定する上で役立つ重要な情報が含まれています。 クエリのパフォーマンスについて InterSystems サポートに寄せられる多くの問い合わせは、TUNE TABLE を実行してもう一度試すだけで解消されます。このコマンドを実行することで、既存のプランが無効になり、次の呼び出しによって新しい統計が得られるためです。 サポートへの問い合わせから、こういったユーザーがテーブルの統計情報を収集していなかった理由が 2 つわかりました。テーブル統計について知らなかった、または本番システムでチューニングを実行した際のオーバーヘッドの余裕がなかったという理由です。 2021.2 では、この 2 つの理由に対処しました。

    ブロックレベルのサンプリング

    2 つ目の理由から始めましょう。統計を収集するコストです。 テーブル統計を収集するには多大な I/O が必要となるため、テーブル全体をスキャンしているのであれば、オーバーヘッドも高まります。 API ではすでに、行の一部のみサンプリングすることをサポートしていましたが、この操作にはかなりのコストがかかるというご意見をいただいていました。 2021.2 では、マスターマップグローバルをループすることでランダムな行を選択するのではなく、その下の物理ストレージにすぐにアクセスしてそのグローバルにカーネルが実際のデータベースブロックのランダムなサンプルを取得するように変更しました。 サンプリングされたこれらのブロックから、それらが保存する SQL テーブル行を推論し、通常のフィールド単位の統計情報構築ロジックに対応します。

    これを大規模なビールフェスティバルへの参加に例えると、すべての通路を歩き、いくつかの醸造所のブースを選んでそれぞれのボトルをカートに入れるのではなく、単に主催者に依頼してランダムなボトルが入った木箱を渡してもらうので歩かなくても済む、というものです。 (実際のビール試飲会では、歩き回ったほうが適切ですがwink)。 酔いを醒ますために、以下に、ブロックベースのアプローチ(青い十字)に対する今までの行ベースのアプローチ(赤い十字)をプロットした単純なグラフを示しています。 これは、一部のお客様が TUNE TABLE を実行することを警戒している巨大なテーブルについては大きなメリットがあることを表しています。

     

    ブロックサンプリングの制限はあまりありませんが、最も重要なのは、デフォルトのストレージマッピングでないテーブル(例: %Storage.SQL を使用してグローバル構造をカスタムマッピングしているテーブル)では使用できないことです。 このような場合には、過去に機能していた方法である、行ベースのサンプリングに戻ります。

    自動チューニング

    オーバーヘッドに関する認識の問題が片付いたところで、お客様が TUNE TABLE を実行していなかったもう 1 つの理由について考えましょう。その存在を知らなかったという理由です。 それについて文書化することもできました(また、ドキュメントを改善する余地が常にあることは認識しています)が、この非常に効率的なブロックサンプリングは、私たちが長年求めてきたことを実行する機会であると捉えました。すべてを自動化するということです。 2021.2 からは、統計がまったく提供されていないテーブルに対してクエリを準備する場合、最初に上記のブロックサンプリングメカニズムを使用してそれらの統計を収集し、クエリプランニングに統計を使用し、以降のクエリで使用できるように、テーブルメタデータに保存します。

    仰々しく聞こえるかもしれませんが、上記のグラフは、GB サイズのテーブルでは、この統計収集の作業がわずか数秒で開始していることがわかります。 不適切なクエリプランを使用してそのようなテーブルをクエリしている場合(適切な統計がないため)は、事前に簡易サンプリングを実行するよりもはるかにコストがかかる可能性があります。 もちろん、これは、ブロックサンプリングを使用できるテーブルのみに行い、行ベースのサンプリングのみをサポートする特殊なテーブルの場合は、(残念ながら)統計なしで対処するしかありません。

    他の新機能と同様に、皆さんの最初の体験に関する感想とフィードバックをお送りください。 この分野では、テーブルの使用状況に基づいて統計を最新の状態に維持するなど、自動化に関するアイデアは他にもありますが、そういった機能をラボ外部の使用体験に基づくものにしたいと考えています。

    0
    1 183
    記事 Mihoko Iijima · 2月 28, 2022 7m read

    開発者のみなさん、こんにちは。

    今回は、スーパーやコンビニでもらうレシートを写真で撮り、OCR を使ってレシートの画像から文字列を切り出して IRIS に登録する流れを試してみました。

    サンプルでは、Google の Vision API を利用してレシートの JPG 画像から購入物品をテキストで抽出しています。

    サンプルコード一式 👉 https://github.com/Intersystems-jp/iris-embeddedpython-OCR
     

    最初、オンラインラーニングで使っている 「tesseract-OCR」を使ってみようと思ったのですが、レシートには半角カナが混在していたりで、半カナがなかなかうまく切り出せず、あきらめました・・(半角カナがなかったら日本語もばっちり読めていたのですが・・)

    もし、tesseract-OCR で半カナを切り出す良い方法をご存知の方いらっしゃいましたら、ぜひ教えてください!

    今回試すにあたり、Vision API の使い方を詳しく書いているページがありましたのでコードなど参考させていただきました。ありがとうございました。

    ​​【Google Colab】Vision APIで『レシートOCR』

    0
    1 1425
    記事 Toshihiko Minamoto · 2月 24, 2022 5m read

    InterSystems IRIS Data Platform の 2021.2 リリースには、ミッションクリティカルなアプリケーションを高速で柔軟性に優れ、セキュアに開発するための刺激的な新機能が多数含まれています。 Embedded Python は間違いなく脚光を浴びています(正当な理由で!)が、SQL の分野でも、テーブルデータに関する詳細な統計情報を収集し、それを最適なクエリプランに提供する、より適応性の高いエンジンに向けて大きな一歩を踏み出しました。 この短い連載記事では、2021.2 で新しく追加された 3 つの要素について詳しく説明し、ランタイムプランの選択(RTPC)を手始めに、この目標に向かって進みます。

    これらについて適切な順序で話していくのは困難です(この記事を書く上で、私がどれだけ順序を入れ替えたか想像できないほどです!) というのも、これらが相互に非常にうまく機能するためです。 そのため、ご自由に順序を変えてお読みくださいsmiley

    IRIS クエリ処理について

    IRIS SQL エンジンにステートメントを送信すると、エンジンはリテラル(クエリパラメーター)を置き換えてそのステートメントを正規化された形式に解析し、テーブル構造、インデックス、およびフィールド値に関する統計を見て、正規化されたクエリにとって最も効率的な実行ストラテジーをはじき出します。 これにより、おそらく異なるクエリパラメーター値を使用してクエリをもう一度実行したい場合に、エンジンは同じプランと生成されたコードを再利用することができます。 たとえば、以下のクエリを例にします。

    SELECT * FROM Customer WHERE CountryCode = 'US' AND ChannelCode = 'DIRECT'

    このクエリは以下のような形式に正規化されます。

    SELECT * FROM Customer WHERE CountryCode = ? AND ChannelCode = ?

    そのため、国とチャンネルの様々な組み合わせに対する後続の呼び出しは、即座にキャッシュされた同じクエリクラスを取得するため、計算量の多いプランニング作業を省略することができます。 6 つのチャンネルを通じて世界中にランニングシューズを販売すると仮定しましょう。言い換えると、データは CountryCode とChannelCode の可能な値全体で均等に分散されます。 これらの両方のフィールドに通常のインデックスがある場合、この正規化されたクエリで最も効率的なプランは、対応するインデックスを使用してマスターマップから一致する行にアクセスすることで最も選択的な条件(CountryCode)から始め、その後にマスターマップのすべての行に対し、別の条件(ChannelCode)をチェックします。 

    外れ値

    では、スポーツ用品メーカーではなく、ベルギーチョコレートの型を販売する専門ベンダーだとしましょう(どこから出てきた例でしょうかね wink)。 この場合、顧客の大半(たとえば 60%)がベルギーにいるとします。つまり、「BE」が CountryCode フィールドの_外れ値_であり、テーブルの多数の行を表します。 すると突然、CountryCode でのインデックスに他の使用方法が現れました。ランタイムで取得する CountryCode のクエリパラメーターが「BE」で_あるとした場合_、そのインデックスを最初に使用すると、マスターマップの大半を読み取ることになるため、ChannelCode でのインデックスから始める方がよくなります。 しかし、CountryCode のクエリパラメーター値が別の値で_あるとした場合_、他のすべての国が残りの 40% のベルギー以外の顧客を分割するため、CountryCode でのインデックスの方がはるかに高い価値が出てきます。

    これは、ランタイム時に異なるプランを選択する場合の例です。または、言い換えると、ランタイムプランの選択(RTPC)と言えます。 RTPC は、従来のリテラル置換とキャッシュ済みのクエリルックアップロジックに小さなフックを追加して、CounryCode 列の「BE」値などの外れ値を見つけるメカニズムです。 IRIS SQL クエリ処理のさらに詳細な概要に興味のある方は、こちらの VS2020 動画をご覧ください。

    IRIS SQL は過去に、非常に大雑把なオプトインバージョンをサポートしていましたが、2021.2 で、大幅に軽量化され、より広範な述語条件に対応できる、まったく新しい RTPC インフラストラクチャを導入しました。 このランタイムチェックのオーバーヘッドは確かに最小限であるため、ユーザーが何も行わなくてもこのメリットを得られるように、これをデフォルトでオンにしました(いつものように、アップグレード後にクエリプランを解凍する必要はありません)。

    これまで頻繁に、実世界のデータセットでは外れ値がどれほど一般的であるか(また、厳格に一様な分布がどれほど稀であるか)を見てきましし、パートナーのベンチマークでのテストによって、以下のグラフからわかるように、パフォーマンスと I/O で目を見開くほどの改善が得られることがわかりました。 また、2020.1 のベンチマーク結果も含めているため、リリースのたびにパフォーマンスを改善できるように継続的に取り組んでいること(と結果)を見ていただけるでしょう。

    改善のマイレージは、データセットの外れ値の量とインデックスの可用性によって異なりますが、この変更の可能性には非常に興奮しており、皆さんの体験をお聞かせいただければ幸いです。

    0
    1 198
    記事 Mihoko Iijima · 2月 4, 2022 7m read

    開発者の皆さん、Python好きの皆さん、こんにちは!

    ドキュメントをみながら IRIS 2021.2 に追加された Embedded Python を試してみました!

    IRIS にログインしてるのに Pythonシェルに切り替えできて Python のコードが書けたり、Python で import iris するだけで SQL を実行できたりグローバルを操作できるので、おぉ!✨という感じです。

    ぜひ、みなさんも体感してみてください!

    では早速。

    まず、IRISにログインします。Windows ならターミナルを開きます。Windows 以外は以下実行します。

    IRIS のインストール方法を確認されたい方は、【はじめての InterSystems IRIS】セルフラーニングビデオ:基本その1:InterSystems IRIS Community Edition をインストールしてみよう!をチェックしてみてください!

    iris session iris
    0
    1 838
    記事 Toshihiko Minamoto · 12月 7, 2021 31m read

    キーワード: IRIS、IntegratedML、機械学習、Covid-19、Kaggle 

    目的

    最近、Covid-19患者がICU(集中治療室)に入室するかどうかを予測するKaggleデータセットがあることに気づきました。  231列のバイタルサインや観測で構成される1925件の遭遇記録が含まれる表計算シートで、最後の「ICU」列では「Yes」を示す1と「No」を示す0が使用されています。 既知のデータに基づいて、患者がICUに入室するかどうかを予測することがタスクです。

    このデータセットは、「従来型ML」タスクと呼ばれるものの良い例のようです。 データ量は適切で、品質も比較的適切なようです。 IntegratedMLデモキットに直接適用できる可能性が高いようなのですが、通常のMLパイプラインと潜在的なIntegratedMLアプローチに基づいて簡易テストを行うには、どのようなアプローチが最も単純なのでしょうか。

    範囲

    次のような通常のMLステップを簡単に実行します。

    • データのEDA(探索的データ分析) 
    • 特徴量の選択
    • モデルの選択
    • グリッド検索によるモデルパラメーターのチューニング

    上記との比較で、次を実行します。 

    • SQLを介したIntegratedMLアプローチ

    Docker-composeなどを使用して、AWS Ubuntu 16.04サーバーで実行します。   

    環境

    integredML-demo-templateのDocker環境を再利用します。

    以下のノートブックファイルは「tf2jupyter」で実行しており、IRISとIntegratedMLは「irismlsrv」で実行しています。  Docker-composeはAWS Ubuntu 16.04で実行します。

    データとタスク

    このデータセットには、385人の患者から収集した1925件のレコードが含まれます。各患者にはちょうど5つの遭遇レコードがあります。 231列のうちの1つの列は、このトレーニングと予測のターゲットである「ICU」で、残りの230列は入力として使用することが可能です。 ICUは1または0のバイナリー値です。 カテゴリカル文字列(データフレームでは「オブジェクト」として表されます)のような2つの列を除くと、すべては数値データです。

    import numpy as np 
    import pandas as pd 
    from sklearn.impute import SimpleImputer
    import matplotlib.pyplot as plt
    from sklearn.linear_model import LogisticRegression
    from sklearn.model_selection import train_test_split
    from sklearn.metrics import classification_report, roc_auc_score, roc_curve
    import seaborn as sns
    sns.set(style="whitegrid")
    
    import os
    for dirname, _, filenames in os.walk('./input'):
        for filename in filenames:
            print(os.path.join(dirname, filename))
    ./input/datasets_605991_1272346_Kaggle_Sirio_Libanes_ICU_Prediction.xlsx

     

    df = pd.read_excel("./input/datasets_605991_1272346_Kaggle_Sirio_Libanes_ICU_Prediction.xlsx")
    df
    <th>
      PATIENT_VISIT_IDENTIFIER
    </th>
    
    <th>
      AGE_ABOVE65
    </th>
    
    <th>
      AGE_PERCENTIL
    </th>
    
    <th>
      GENDER
    </th>
    
    <th>
      DISEASE GROUPING 1
    </th>
    
    <th>
      DISEASE GROUPING 2
    </th>
    
    <th>
      DISEASE GROUPING 3
    </th>
    
    <th>
      DISEASE GROUPING 4
    </th>
    
    <th>
      DISEASE GROUPING 5
    </th>
    
    <th>
      DISEASE GROUPING 6
    </th>
    
    <th>
      ...
    </th>
    
    <th>
      TEMPERATURE_DIFF
    </th>
    
    <th>
      OXYGEN_SATURATION_DIFF
    </th>
    
    <th>
      BLOODPRESSURE_DIASTOLIC_DIFF_REL
    </th>
    
    <th>
      BLOODPRESSURE_SISTOLIC_DIFF_REL
    </th>
    
    <th>
      HEART_RATE_DIFF_REL
    </th>
    
    <th>
      RESPIRATORY_RATE_DIFF_REL
    </th>
    
    <th>
      TEMPERATURE_DIFF_REL
    </th>
    
    <th>
      OXYGEN_SATURATION_DIFF_REL
    </th>
    
    <th>
      WINDOW
    </th>
    
    <th>
      ICU
    </th>
    
    <td>
    </td>
    
    <td>
      1
    </td>
    
    <td>
      60th
    </td>
    
    <td>
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      1.0
    </td>
    
    <td>
      1.0
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      0-2
    </td>
    
    <td>
    </td>
    
    <td>
    </td>
    
    <td>
      1
    </td>
    
    <td>
      60th
    </td>
    
    <td>
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      1.0
    </td>
    
    <td>
      1.0
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      2-4
    </td>
    
    <td>
    </td>
    
    <td>
    </td>
    
    <td>
      1
    </td>
    
    <td>
      60th
    </td>
    
    <td>
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      1.0
    </td>
    
    <td>
      1.0
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      NaN
    </td>
    
    <td>
      NaN
    </td>
    
    <td>
      NaN
    </td>
    
    <td>
      NaN
    </td>
    
    <td>
      NaN
    </td>
    
    <td>
      NaN
    </td>
    
    <td>
      NaN
    </td>
    
    <td>
      NaN
    </td>
    
    <td>
      4-6
    </td>
    
    <td>
    </td>
    
    <td>
    </td>
    
    <td>
      1
    </td>
    
    <td>
      60th
    </td>
    
    <td>
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      1.0
    </td>
    
    <td>
      1.0
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      NaN
    </td>
    
    <td>
      NaN
    </td>
    
    <td>
      NaN
    </td>
    
    <td>
      NaN
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      6-12
    </td>
    
    <td>
    </td>
    
    <td>
    </td>
    
    <td>
      1
    </td>
    
    <td>
      60th
    </td>
    
    <td>
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      1.0
    </td>
    
    <td>
      1.0
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      -0.238095
    </td>
    
    <td>
      -0.818182
    </td>
    
    <td>
      -0.389967
    </td>
    
    <td>
      0.407558
    </td>
    
    <td>
      -0.230462
    </td>
    
    <td>
      0.096774
    </td>
    
    <td>
      -0.242282
    </td>
    
    <td>
      -0.814433
    </td>
    
    <td>
      ABOVE_12
    </td>
    
    <td>
      1
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      384
    </td>
    
    <td>
    </td>
    
    <td>
      50th
    </td>
    
    <td>
      1
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      0-2
    </td>
    
    <td>
    </td>
    
    <td>
      384
    </td>
    
    <td>
    </td>
    
    <td>
      50th
    </td>
    
    <td>
      1
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      2-4
    </td>
    
    <td>
    </td>
    
    <td>
      384
    </td>
    
    <td>
    </td>
    
    <td>
      50th
    </td>
    
    <td>
      1
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      4-6
    </td>
    
    <td>
    </td>
    
    <td>
      384
    </td>
    
    <td>
    </td>
    
    <td>
      50th
    </td>
    
    <td>
      1
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      -1.000000
    </td>
    
    <td>
      6-12
    </td>
    
    <td>
    </td>
    
    <td>
      384
    </td>
    
    <td>
    </td>
    
    <td>
      50th
    </td>
    
    <td>
      1
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      1.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      0.0
    </td>
    
    <td>
      ...
    </td>
    
    <td>
      -0.547619
    </td>
    
    <td>
      -0.838384
    </td>
    
    <td>
      -0.701863
    </td>
    
    <td>
      -0.585967
    </td>
    
    <td>
      -0.763868
    </td>
    
    <td>
      -0.612903
    </td>
    
    <td>
      -0.551337
    </td>
    
    <td>
      -0.835052
    </td>
    
    <td>
      ABOVE_12
    </td>
    
    <td>
    </td>
    
     
    1
    2
    3
    4
    ...
    1920
    1921
    1922
    1923
    1924

    1925 rows × 231 columns

    df.dtypes
    PATIENT_VISIT_IDENTIFIER        int64
    AGE_ABOVE65                     int64
    AGE_PERCENTIL                  object
    GENDER                          int64
    DISEASE GROUPING 1            float64
                                   ...   
    RESPIRATORY_RATE_DIFF_REL     float64
    TEMPERATURE_DIFF_REL          float64
    OXYGEN_SATURATION_DIFF_REL    float64
    WINDOW                         object
    ICU                             int64
    Length: 231, dtype: object

    この問題とそのアプローチを組むには、確実にいくつかのオプションがあります。 最初に頭に浮かぶのは、これが「二項分類」の問題と考えられることです。 すべての1925件のレコードを、同じ患者のレコードであるかに関係なく、「ステートレス」の個別のレコードとして扱うことができます。 もちろん、ICUと他の値をすべて数値として扱うのであれば、「回帰」問題としても考えられるでしょう。

    確かにほかのアプローチもあります。 たとえば、データセットに、患者ごとに異なる385件の短い「時系列」があるというように扱うこともできます。 セット全体をTrain/Val/Test用の385個の個別のセットに分解した場合は、CNNやLSTMなどのディープラーニングモデルを使って、個々の患者ごとに各セットに隠れた「症状の発展段階またはパターン」をキャプチャできるでしょうか? できるかもしれませんね。 それを行えば、データ拡張も適用して、様々な方法でテストデータを充実させることができるかもしれません。 これについては、この記事の対象外として別のトピックとしましょう。

    この記事では、いわゆる「従来型ML」とIntegratedML(AutoML)アプローチのクイックランをテストします。  

    「従来型」MLアプローチとは

    このデータセットは、いくつかの値が欠落していることを除き、ほとんどの実際のケースより比較的正規化されたものであるため、特徴量エンジニアリングの部分を省略して、直接列を特徴量として使用できる可能性があります。 では早速、特徴量の選択に進みましょう。

    欠落しているデータを代入する

    まず、すべての欠落している値が単純な代入で埋められていることを確認します。

    df_cat = df.select_dtypes(include=['object'])
    df_numeric = df.select_dtypes(exclude=['object'])
    imp = SimpleImputer(missing_values=np.nan, strategy='mean')
    idf = pd.DataFrame(imp.fit_transform(df_numeric))
    idf.columns = df_numeric.columns
    idf.index = df_numeric.index
    idf.isnull().sum()

     

    特徴量の選択

    データフレームに組み込まれている正規相関関数を使用して、ICUに対する各列の値の相関を計算できます。

    特徴量エンジニアリング - 相関 {#featuring-engineering---correlation}

    idf.drop(["PATIENT_VISIT_IDENTIFIER"],1)
    idf = pd.concat([idf,df_cat ], axis=1)
    cor = idf.corr()
    cor_target = abs(cor["ICU"])
    relevant_features = cor_target[cor_target>0.1]   # correlation above 0.1
    print(cor.shape, cor_target.shape, relevant_features.shape)
    #relevant_features.index
    #relevant_features.index.shape

    これにより、ICUのターゲット値との相関が >0.1である88個の特徴量がリストされます。  これらの列は、モデル入力として直接利用できます。

    また、従来型MLタスクで通常使用されているほかの「特徴量選択手法」もいくつか実行してみました。

    特徴量の選択 - カイ二乗 {#feature-selection---Chi-squared}

    from sklearn.feature_selection import SelectKBest
    from sklearn.feature_selection import chi2
    from sklearn.preprocessing import MinMaxScaler
    X_norm = MinMaxScaler().fit_transform(X)
    chi_selector = SelectKBest(chi2, k=88)
    chi_selector.fit(X_norm, y)
    chi_support = chi_selector.get_support()
    chi_feature = X.loc[:,chi_support].columns.tolist()
    print(str(len(chi_feature)), 'selected features', chi_feature)
    88 selected features ['AGE_ABOVE65', 'GENDER', 'DISEASE GROUPING 1', ... ... 'P02_VENOUS_MIN', 'P02_VENOUS_MAX', ... ... RATURE_MAX', 'BLOODPRESSURE_DIASTOLIC_DIFF', ... ... 'TEMPERATURE_DIFF_REL', 'OXYGEN_SATURATION_DIFF_REL']

    特徴量の選択 - ピアソン相関 

    def cor_selector(X, y,num_feats):
        cor_list = []
        feature_name = X.columns.tolist()
        # calculate the correlation with y for each feature
        for i in X.columns.tolist():
            cor = np.corrcoef(X[i], y)[0, 1]
            cor_list.append(cor)
        # replace NaN with 0
        cor_list = [0 if np.isnan(i) else i for i in cor_list]
        # feature name
        cor_feature = X.iloc[:,np.argsort(np.abs(cor_list))[-num_feats:]].columns.tolist()
        # feature selection? 0 for not select, 1 for select
        cor_support = [True if i in cor_feature else False for i in feature_name]
        return cor_support, cor_featurecor_support, cor_feature = cor_selector(X, y, 88)
    print(str(len(cor_feature)), 'selected features:  ', cor_feature)
    88 selected features:   ['TEMPERATURE_MEAN', 'BLOODPRESSURE_DIASTOLIC_MAX', ... ... 'RESPIRATORY_RATE_DIFF', 'RESPIRATORY_RATE_MAX']

    特徴量の選択 - 再帰的特徴量除去(RFE){#feature-selection---Recursive-Feature-Elimination-(RFE)}

    from sklearn.feature_selection import RFE
    from sklearn.linear_model import LogisticRegression
    rfe_selector = RFE(estimator=LogisticRegression(), n_features_to_select=88, step=100, verbose=5)
    rfe_selector.fit(X_norm, y)
    rfe_support = rfe_selector.get_support()
    rfe_feature = X.loc[:,rfe_support].columns.tolist()
    print(str(len(rfe_feature)), 'selected features: ', rfe_feature)
    Fitting estimator with 127 features.
    88 selected features:  ['AGE_ABOVE65', 'GENDER', ... ... 'RESPIRATORY_RATE_DIFF_REL', 'TEMPERATURE_DIFF_REL']

    特徴量の選択 - Lasso

    ffrom sklearn.feature_selection import SelectFromModel
    from sklearn.linear_model import LogisticRegression
    from sklearn.preprocessing import MinMaxScaler
    X_norm = MinMaxScaler().fit_transform(X)
    embeded_lr_selector = SelectFromModel(LogisticRegression(penalty="l2"), max_features=88)
    embeded_lr_selector.fit(X_norm, y)
    embeded_lr_support = embeded_lr_selector.get_support()
    embeded_lr_feature = X.loc[:,embeded_lr_support].columns.tolist()
    print(str(len(embeded_lr_feature)), 'selected features', embeded_lr_feature)
    65 selected features ['AGE_ABOVE65', 'GENDER', ... ... 'RESPIRATORY_RATE_DIFF_REL', 'TEMPERATURE_DIFF_REL']

    特徴量の選択 - RFツリーベース: SelectFromModel

    from sklearn.feature_selection import SelectFromModel
    from sklearn.ensemble import RandomForestClassifier
    embeded_rf_selector = SelectFromModel(RandomForestClassifier(n_estimators=100), max_features=227)
    embeded_rf_selector.fit(X, y)
    embeded_rf_support = embeded_rf_selector.get_support()
    embeded_rf_feature = X.loc[:,embeded_rf_support].columns.tolist()
    print(str(len(embeded_rf_feature)), 'selected features', embeded_rf_feature)
    48 selected features ['AGE_ABOVE65', 'GENDER', ... ... 'TEMPERATURE_DIFF_REL', 'OXYGEN_SATURATION_DIFF_REL']

    特徴量の選択 - LightGBMまたはXGBoost {#feature-selection---LightGBM-or-XGBoost}

    from sklearn.feature_selection import SelectFromModel
    from lightgbm import LGBMClassifierlgbc=LGBMClassifier(n_estimators=500, learning_rate=0.05, num_leaves=32, colsample_bytree=0.2,
                reg_alpha=3, reg_lambda=1, min_split_gain=0.01, min_child_weight=40)embeded_lgb_selector = SelectFromModel(lgbc, max_features=128)
    embeded_lgb_selector.fit(X, y)embeded_lgb_support = embeded_lgb_selector.get_support()
    embeded_lgb_feature = X.loc[:,embeded_lgb_support].columns.tolist()
    print(str(len(embeded_lgb_feature)), 'selected features:  ', embeded_lgb_feature)
    embeded_lgb_feature.index
    56 selected features:   ['AGE_ABOVE65', 'GENDER', 'HTN', ... ... 'TEMPERATURE_DIFF_REL', 'OXYGEN_SATURATION_DIFF_REL']

    特徴量の選択 - すべてを一緒に {#feature-selection---Ensemble-them-all}

    feature_name = X.columns.tolist()
    # put all selection together
    feature_selection_df = pd.DataFrame({'Feature':feature_name, 'Pearson':cor_support, 'Chi-2':chi_support, 'RFE':rfe_support, 'Logistics':embeded_lr_support, 'Random Forest':embeded_rf_support, 'LightGBM':embeded_lgb_support})
    # count the selected times for each feature
    feature_selection_df['Total'] = np.sum(feature_selection_df, axis=1)
    # display the top 100
    num_feats = 227
    feature_selection_df = feature_selection_df.sort_values(['Total','Feature'] , ascending=False)
    feature_selection_df.index = range(1, len(feature_selection_df)+1)
    feature_selection_df.head(num_feats)
    df_selected_columns = feature_selection_df.loc[(feature_selection_df['Total'] > 3)]
    df_selected_columns

    少なくとも4つの方法で選択された特徴量をリストできます。

    ... ...

    これらの58個の特徴量を選択できます。 一方で、経験からして、特徴量の選択は、必ずしも民主的投票だとは限りません。多くの場合はドメインの問題や特定のデータに特化するものであり、時には、この記事で後で採用する特定のMLモデルやアプローチに特化した選択手法であることもあります。

    特徴量の選択 - サードパーティツール 

    広く使用されている業界ツールやAutoMLツールがあり、たとえば、DataRobotは特徴量の適切な自動選択を行うことができます。  

    当然ながら、上記のDataRobotグラフから、さまざまなRespiratoryRate値とBloodPressure値がICU入室に最も関連性の高い特徴量であることがわかります。    

    特徴量の選択 - 最終選択
    この記事では、簡単な実験を行い、LightGBMによる特徴量の選択が実際にほんの少し良い結果を出したことに気づいたので、この選択方法のみを使用することにします。   

    df_selected_columns = embeded_lgb_feature  # better than ensembled selectiondataS = pd.concat([idf[df_selected_columns],idf['ICU'], df_cat['WINDOW']],1)
    dataS.ICU.value_counts()
    print(dataS.shape)
    (1925, 58)

    58個の特徴量が選択されていることがわかります。多すぎず、少なからず、この特定のシングルターゲット二項分類問題には適切な量だと言えます。 

    データの不均衡

    plt.figure(figsize=(10,5))
    count = sns.countplot(x = "ICU",data=data)
    count.set_xticklabels(["Not Admitted","Admitted"])
    plt.xlabel("ICU Admission")
    plt.ylabel("Patient Count")
    plt.show()

    これはデータが不均衡であることを示し、ICUに入室したというレコードは26%のみです。 これは結果に影響を及ぼすため、SMOTEなどの通常のデータ均衡化アプローチを考えられます。

    ここでは、他のあらゆる種類のEDAを試し、それに応じてさまざまなデータ分布を理解することができます。 

    基本的なLRトレーニングを実行する

    Kaggleサイトには、独自の特徴量カラムの選択に基づいて素早く実行できる優れた簡易トレーニングノートブックがいくつかあります。  とりあえず、トレーニングパイプラインのLR分類器をさっと実行することから始めましょう。 

    data2 = pd.concat([idf[df_selected_columns],idf['ICU'], df_cat['WINDOW']],1)   
    data2.AGE_ABOVE65 = data2.AGE_ABOVE65.astype(int)
    data2.ICU = data2.ICU.astype(int)
    X2 = data2.drop("ICU",1)
    y2 = data2.ICU
    
    from sklearn.preprocessing import LabelEncoder
    label_encoder = LabelEncoder()
    X2.WINDOW = label_encoder.fit_transform(np.array(X2["WINDOW"].astype(str)).reshape((-1,)))
    
    confusion_matrix2 = pd.crosstab(y2_test, y2_hat, rownames=['Actual'], colnames=['Predicted'])
    sns.heatmap(confusion_matrix2, annot=True, fmt = 'g', cmap = 'Reds') print("ORIGINAL")
    print(classification_report(y_test, y_hat))
    print("AUC = ",roc_auc_score(y_test, y_hat),'\n\n')
    print("LABEL ENCODING")
    print(classification_report(y2_test, y2_hat))
    print("AUC = ",roc_auc_score(y2_test, y2_hat))
    y2hat_probs = LR.predict_proba(X2_test)
    y2hat_probs = y2hat_probs[:, 1] fpr2, tpr2, _ = roc_curve(y2_test, y2hat_probs) plt.figure(figsize=(10,7))
    plt.plot([0, 1], [0, 1], 'k--')
    plt.plot(fpr, tpr, label="Base")
    plt.plot(fpr2,tpr2,label="Label Encoded")
    plt.xlabel('False positive rate')
    plt.ylabel('True positive rate')
    plt.title('ROC curve')
    plt.legend(loc="best")
    plt.show()
    
    
    ORIGINAL
                  precision    recall  f1-score   support
               0       0.88      0.94      0.91       171
               1       0.76      0.57      0.65        54
        accuracy                           0.85       225
       macro avg       0.82      0.76      0.78       225
    weighted avg       0.85      0.85      0.85       225
    AUC =  0.7577972709551657 
    LABEL ENCODING
                  precision    recall  f1-score   support
               0       0.88      0.93      0.90       171
               1       0.73      0.59      0.65        54
        accuracy                           0.85       225
       macro avg       0.80      0.76      0.78       225
    weighted avg       0.84      0.85      0.84       225
    AUC =  0.7612085769980507

            

    85%の精度でAUC 76%を達成しているようですが、ICU入院のRecallは59%のみで、偽陰性が多すぎるようです。 それは確かに理想的ではありません。患者のレコードに対して実際のICUリスクを見落とすわけにはいきません。 そこで、すべての後続のタスクでは、うまく行けばある程度均衡のとれた全体的精度でFNを下げてRecallレートを引き上げる方法に目標を定めることにします。

    前のセクションでは、不均衡データについて触れました。最初の直感は、テストセットを層別化(Stratify)し、SMOTEしてより均衡のとれたデータセットを作成することです。

    #テストデータを層別化し、TrainデータとTestデータの比率が同じ1:0になるようにする
    X3_train,X3_test,y3_train,y3_test = train_test_split(X2,y2,test_size=225/1925,random_state=42, stratify = y2, shuffle = True) &lt;span> &lt;/span>
    # train and predict
    LR.fit(X3_train,y3_train)
    y3_hat = LR.predict(X3_test)
    
    #データをSMOTEし、ICU 1:0を均衡のとれた分布にする
    from imblearn.over_sampling import SMOTE sm = SMOTE(random_state = 42)
    X_train_res, y_train_res = sm.fit_sample(X3_train,y3_train.ravel())
    LR.fit(X_train_res, y_train_res)
    y_res_hat = LR.predict(X3_test)
    
    #混同行列などをもう一度描画する
    confusion_matrix3 = pd.crosstab(y3_test, y_res_hat, rownames=['Actual'], colnames=['Predicted'])
    sns.heatmap(confusion_matrix3, annot=True, fmt = 'g', cmap="YlOrBr") 
    print("LABEL ENCODING + STRATIFY")
    print(classification_report(y3_test, y3_hat))
    print("AUC = ",roc_auc_score(y3_test, y3_hat),'\n\n') 
    print("SMOTE")
    print(classification_report(y3_test, y_res_hat))
    print("AUC = ",roc_auc_score(y3_test, y_res_hat)) 
    y_res_hat_probs = LR.predict_proba(X3_test)
    y_res_hat_probs = y_res_hat_probs[:, 1] 
    fpr_res, tpr_res, _ = roc_curve(y3_test, y_res_hat_probs) plt.figure(figsize=(10,10))
    
    #前のようにROC曲線をプロットする

     

    LABEL ENCODING + STRATIFY
                  precision    recall  f1-score   support
               0       0.87      0.99      0.92       165
               1       0.95      0.58      0.72        60
        accuracy                           0.88       225
       macro avg       0.91      0.79      0.82       225
    weighted avg       0.89      0.88      0.87       225
    AUC =  0.7856060606060606 
    SMOTE
                  precision    recall  f1-score   support
               0       0.91      0.88      0.89       165
               1       0.69      0.75      0.72        60
        accuracy                           0.84       225
       macro avg       0.80      0.81      0.81       225
    weighted avg       0.85      0.84      0.85       225
    AUC =  0.8143939393939393

                

    したがって、データのSTRATIFY処理とSMOTE処理はRecallを0.59から 0.75、全体的な精度を0.84に改善しているようです。 

    従来型MLのデータ処理の大半がいつも通り完了したので、このケースの最善のモデルが何であるかを知りたいと思います。それらはもっと優れているのでしょうか。また、比較的包括的な比較を試みることができるでしょうか。

    さまざまなモデルのトレーニング比較を実行する 

    一般的に使用されているMLアルゴリズムを評価し、箱ひげ図によって、結果の比較ダッシュボードを生成してみましょう。

    # アルゴリズムを比較
    from matplotlib import pyplot
    from sklearn.model_selection import train_test_split
    from sklearn.model_selection import cross_val_score
    from sklearn.model_selection import StratifiedKFold
    from sklearn.linear_model import LogisticRegression
    from sklearn.tree import DecisionTreeClassifier
    from sklearn.neighbors import KNeighborsClassifier
    from sklearn.discriminant_analysis import LinearDiscriminantAnalysis
    from sklearn.naive_bayes import GaussianNB
    from sklearn.svm import SVC
    #ランダムフォレストモデルをインポートする
    from sklearn.ensemble import RandomForestClassifier
    from xgboost import XGBClassifier# アルゴリズムを合わせてリストする
    models = []
    models.append(('LR', &lt;strong>LogisticRegression&lt;/strong>(solver='liblinear', multi_class='ovr')))
    models.append(('LDA', LinearDiscriminantAnalysis()))
    models.append(('KNN', &lt;strong>KNeighborsClassifier&lt;/strong>()))
    models.append(('CART', &lt;strong>DecisionTreeClassifier&lt;/strong>()))
    models.append(('NB', &lt;strong>GaussianNB&lt;/strong>()))
    models.append(('SVM', &lt;strong>SVC&lt;/strong>(gamma='auto')))
    models.append(('RF', &lt;strong>RandomForestClassifier&lt;/strong>(n_estimators=100)))
    models.append(('XGB', &lt;strong>XGBClassifier&lt;/strong>())) #clf = XGBClassifier()
    # 代わりに各モデルを評価する
    results = []
    names = []
    for name, model in models:
        kfold = StratifiedKFold(n_splits=10, random_state=1)
        cv_results = cross_val_score(model, X_train_res, y_train_res, cv=kfold, scoring='f1')  ## accuracy, precision,recall 
        results.append(cv_results)
        names.append(name)
        print('%s: %f (%f)' % (name, cv_results.mean(), cv_results.std()))# すべてのモデルのパフォーマンスを比較する。 質問 - それに関する統合アイテムを見たいですか?
    pyplot.figure(4, figsize=(12, 8))
    pyplot.boxplot(results, labels=names)
    pyplot.title('Algorithm Comparison')
    pyplot.show()
    LR: 0.805390 (0.021905) LDA: 0.803804 (0.027671) KNN: 0.841824 (0.032945) CART: 0.845596 (0.053828)
    NB: 0.622540 (0.060390) SVM: 0.793754 (0.023050) RF: 0.896222 (0.033732) XGB: 0.907529 (0.040693)

    上記は、XGB分類子とランダムフォレスト分類子に他のモデルよりも優れたF1スコアがあるように見えます。 

    同じセットの正規化されたテストデータで実際のテスト結果を比較して見ましょう。

    import time
    from pandas import read_csv
    from sklearn.model_selection import train_test_split
    from sklearn.metrics import classification_report
    from sklearn.metrics import confusion_matrix
    from sklearn.metrics import accuracy_score
    from sklearn.svm import SVCfor name, model in models:
        print(name + ':\n\r')
        start = time.clock()
        model.fit(X_train_res, y_train_res)
        print("Train time for ", model, " ", time.clock() - start)
        predictions = model.predict(X3_test) #(X_validation)
        # Evaluate predictions
        print(accuracy_score(y3_test, predictions))  # Y_validation
        print(confusion_matrix(y3_test, predictions))
        print(classification_report(y3_test, predictions))
    LR:
    Train time for  LogisticRegression(multi_class='ovr', solver='liblinear')   0.02814499999999498
    0.8444444444444444
    [[145  20]
     [ 15  45]]
                  precision    recall  f1-score   support
               0       0.91      0.88      0.89       165
               1       0.69      0.75      0.72        60
        accuracy                           0.84       225
       macro avg       0.80      0.81      0.81       225
    weighted avg       0.85      0.84      0.85       225
    
    LDA:
    Train time for  LinearDiscriminantAnalysis()   0.2280070000000194
    0.8488888888888889
    [[147  18]
     [ 16  44]]
                  precision    recall  f1-score   support
               0       0.90      0.89      0.90       165
               1       0.71      0.73      0.72        60
        accuracy                           0.85       225
       macro avg       0.81      0.81      0.81       225
    weighted avg       0.85      0.85      0.85       225
    
    KNN:
    Train time for  KNeighborsClassifier()   0.13023699999999394
    0.8355555555555556
    [[145  20]
     [ 17  43]]
                  precision    recall  f1-score   support
               0       0.90      0.88      0.89       165
               1       0.68      0.72      0.70        60
        accuracy                           0.84       225
       macro avg       0.79      0.80      0.79       225
    weighted avg       0.84      0.84      0.84       225
    
    CART:
    Train time for  DecisionTreeClassifier()   0.32616000000001577
    0.8266666666666667
    [[147  18]
     [ 21  39]]
                  precision    recall  f1-score   support
               0       0.88      0.89      0.88       165
               1       0.68      0.65      0.67        60
        accuracy                           0.83       225
       macro avg       0.78      0.77      0.77       225
    weighted avg       0.82      0.83      0.83       225
    
    NB:
    Train time for  GaussianNB()   0.0034229999999979555
    0.8355555555555556
    [[154  11]
     [ 26  34]]
                  precision    recall  f1-score   support
               0       0.86      0.93      0.89       165
               1       0.76      0.57      0.65        60
        accuracy                           0.84       225
       macro avg       0.81      0.75      0.77       225
    weighted avg       0.83      0.84      0.83       225
    
    SVM:
    Train time for  SVC(gamma='auto')   0.3596520000000112
    0.8977777777777778
    [[157   8]
     [ 15  45]]
                  precision    recall  f1-score   support
               0       0.91      0.95      0.93       165
               1       0.85      0.75      0.80        60
        accuracy                           0.90       225
       macro avg       0.88      0.85      0.86       225
    weighted avg       0.90      0.90      0.90       225
    
    RF:
    Train time for  RandomForestClassifier()   0.50123099999999
    0.9066666666666666
    [[158   7]
     [ 14  46]]
                  precision    recall  f1-score   support
               0       0.92      0.96      0.94       165
               1       0.87      0.77      0.81        60
        accuracy                           0.91       225
       macro avg       0.89      0.86      0.88       225
    weighted avg       0.91      0.91      0.90       225
    
    XGB:
    Train time for  XGBClassifier(base_score=0.5, booster='gbtree', colsample_bylevel=1,
                  colsample_bynode=1, colsample_bytree=1, gamma=0, gpu_id=-1,
                  importance_type='gain', interaction_constraints='',
                  learning_rate=0.300000012, max_delta_step=0, max_depth=6,
                  min_child_weight=1, missing=nan, monotone_constraints='()',
                  n_estimators=100, n_jobs=0, num_parallel_tree=1, random_state=0,
                  reg_alpha=0, reg_lambda=1, scale_pos_weight=1, subsample=1,
                  tree_method='exact', validate_parameters=1, verbosity=None)   1.649520999999993
    0.8844444444444445
    [[155  10]
     [ 16  44]]
                  precision    recall  f1-score   support
               0       0.91      0.94      0.92       165
               1       0.81      0.73      0.77        60
        accuracy                           0.88       225
       macro avg       0.86      0.84      0.85       225
    weighted avg       0.88      0.88      0.88       225

    RFが実際にXGBよりも優れているという結果が出たようです。 XGBが何らかの形で過学習している可能性を示唆しているかもしれません。 RFCの結果はLRよりもわずかに改善されています。

    「グリッド検索によるパラメーターチューニング」をさらに行って、選択されたモデルを実行する

    では、ランダムフォレスト分類子がここで選ばれたモデルと仮定しましょう。  このモデルにさらにグリッド検索を実行して、結果のパフォーマンスをわずかにでも向上させられるかを確認することができます。 

    ここでの目標は、患者が遭遇する潜在的なICUリスクの偽陰性を最小限に抑えることで、このケースのRecallを最適化することに変わりはありませんので、「recall_score」を使用して、グリッド検索を再適合させます。 繰り返しますが、上記のテストセットは常に2915件のレコードの約12%に設定されていたため、ここでも10倍の交差検定を使用します。

    from sklearn.model_selection import GridSearchCV
    # ランダム検索の結果に基づいてパラメーターグリッドを作成する
    
    param_grid = {'bootstrap': [True],
     'ccp_alpha': [0.0],
     'class_weight': [None],
     'criterion': ['gini', 'entropy'],
     'max_depth': [None],
     'max_features': ['auto', 'log2'],             
     'max_leaf_nodes': [None],
     'max_samples': [None],
     'min_impurity_decrease': [0.0],
     'min_impurity_split': [None],
     'min_samples_leaf': [1, 2, 4],
     'min_samples_split': [2, 4],
     'min_weight_fraction_leaf': [0.0],
     'n_estimators': [100, 125],
     #'n_jobs': [None],
     'oob_score': [False],
     'random_state': [None],
     #'verbose': 0,
     'warm_start': [False]
    }#混同行列でファインチューニングする 
    from sklearn.metrics import roc_curve, precision_recall_curve, auc, make_scorer, recall_score, accuracy_score, precision_score, confusion_matrix
    scorers = {
        'recall_score': make_scorer(recall_score),
        'precision_score': make_scorer(precision_score),
        'accuracy_score': make_scorer(accuracy_score)
    }# ベースモデルを作成する
    rfc = RandomForestClassifier()
    # Instantiate the grid search model
    grid_search = GridSearchCV(estimator = rfc, param_grid = param_grid, 
                               scoring=scorers, refit='recall_score', 
                               cv = 10, n_jobs = -1, verbose = 2)train_features = X_train_resgrid_search.fit(train_features, train_labels)
    rf_best_grid = grid_search.best_estimator_rf_best_grid.fit(train_features, train_labels)
    rf_predictions = rf_best_grid.predict(X3_test) 
    print(accuracy_score(y3_test, rf_predictions))  
    print(confusion_matrix(y3_test, rf_predictions))
    print(classification_report(y3_test, rf_predictions))
    0.92
    [[ 46  14]
     [  4 161]]
                  precision    recall  f1-score   support
               0       0.92      0.77      0.84        60
               1       0.92      0.98      0.95       165
        accuracy                           0.92       225
       macro avg       0.92      0.87      0.89       225
    weighted avg       0.92      0.92      0.92       225

    グリッド検索は、FNを同じに保ちながら、実際に全体的な精度を少し上げることができるという結果が得られました。 

    AUC比較もプロットしてみましょう。

    confusion_matrix4 = pd.crosstab(y3_test, rf_predictions, rownames=['Actual'], colnames=['Predicted'])
    sns.heatmap(confusion_matrix4, annot=True, fmt = 'g', cmap="YlOrBr")print("LABEL ENCODING + STRATIFY")
    print(classification_report(y3_test, 1-y3_hat))
    print("AUC = ",roc_auc_score(y3_test, 1-y3_hat),'\n\n')print("SMOTE")
    print(classification_report(y3_test, 1-y_res_hat))
    print("AUC = ",roc_auc_score(y3_test, 1-y_res_hat), '\n\n')print("SMOTE + LBG Selected Weights + RF Grid Search")
    print(classification_report(y3_test, rf_predictions))
    print("AUC = ",roc_auc_score(y3_test, rf_predictions), '\n\n\n')y_res_hat_probs = LR.predict_proba(X3_test)
    y_res_hat_probs = y_res_hat_probs[:, 1]predictions_rf_probs = rf_best_grid.predict_proba(X3_test) #(X_validation)
    predictions_rf_probs = predictions_rf_probs[:, 1]fpr_res, tpr_res, _ = roc_curve(y3_test, 1-y_res_hat_probs)
    fpr_rf_res, tpr_rf_res, _ = roc_curve(y3_test, predictions_rf_probs)plt.figure(figsize=(10,10))
    plt.plot([0, 1], [0, 1], 'k--')
    plt.plot(fpr, tpr, label="Base")
    plt.plot(fpr2,tpr2,label="Label Encoded")
    plt.plot(fpr3,tpr3,label="Stratify")
    plt.plot(fpr_res,tpr_res,label="SMOTE")
    plt.plot(fpr_rf_res,tpr_rf_res,label="SMOTE + RF GRID")
    plt.xlabel('False positive rate')
    plt.ylabel('True positive rate')
    plt.title('ROC curve')
    plt.legend(loc="best")
    plt.show()
    LABEL ENCODING + STRATIFY
                  precision    recall  f1-score   support
               0       0.95      0.58      0.72        60
               1       0.87      0.99      0.92       165
        accuracy                           0.88       225
       macro avg       0.91      0.79      0.82       225
    weighted avg       0.89      0.88      0.87       225
    AUC =  0.7856060606060606 
    
    SMOTE
                  precision    recall  f1-score   support
               0       0.69      0.75      0.72        60
               1       0.91      0.88      0.89       165
        accuracy                           0.84       225
       macro avg       0.80      0.81      0.81       225
    weighted avg       0.85      0.84      0.85       225
    AUC =  0.8143939393939394 
    
    SMOTE + LBG Selected Weights + RF Grid Search
                  precision    recall  f1-score   support
               0       0.92      0.77      0.84        60
               1       0.92      0.98      0.95       165
        accuracy                           0.92       225
       macro avg       0.92      0.87      0.89       225
    weighted avg       0.92      0.92      0.92       225
    AUC =  0.8712121212121211

         

    アルゴリズム比較とさらなるグリッド検索の結果、AUCを78%から87%に引き上げることができ、全体の精度は92%、Recallは77%という結果を得られました。

    「従来型ML」アプローチのまとめ

    では、実際のところこの結果はどうなのでしょうか。 従来型MLアルゴリズムを使った基本的な手動プロセスとしては十分と言えます。  Kaggleコンペティションではどのように見えるでしょうか。 まぁ、リーダーボードには上がらないでしょう。 DataRobotの現在のAutoMLサービスから生のデータセットを実行したところ、最良の結果は、上位43個のモデルを比較したうち、「教師なし学習特徴によるXGBツリー分類子」モデルで、約90%以上相当のAUCを達成すると主張するでしょう。 本当にKaggleに参戦したいのであれば、これが私たちが狙いとする実用的なモデルと言えるでしょう。  最高の結果とモデルの上位リストもGitHubに添付しておきます。 最終的には、ケアサイトに特化した実際のケースについて言えば、この記事の「データとタスク」セクションで述べたように、ある程度カスタマイズされたディープラーニングアプローチにも焦点を当てる必要があると感じています。 もちろん、実際のケースでは、質の高いデータカラムをどこで収集するのかという手前の問題もあるでしょうが。

    IntegratedMLアプローチとは

    上記では、いわゆる従来型MLと呼ばれるプロセスを説明しました。通常は、データEDA、特徴量エンジニアリング、特徴量の選択、モデルの選択、およびグリッド検索などによるパフォーマンスの最適化が含まれるプロセスです。 これがこのタスクにおいて、今のところ思いつく最も単純なアプローチです。まだモデルのデプロイやサービス管理のライフサイクルについては触れていません。これについては次の記事で、Flask/FlasAPI/IRISを利用して、この基本MLモデルをCovid-19X線画像デモサービススタックにデプロイする方法を探りながら、説明したいと思います。

    IRISにはIntegratedMLが組み込まれており、強力なAutoMLオプションの洗練されたSQLラッパーとして用意されています。 パート2では、ビジネスのメリットとして同等のML結果を引き出しながら、特徴量の選択、モデルの選択、パフォーマンスの最適化などを行わずに済むように、上記のタスクを大幅に単純化したプロセスで達成する方法を調べます。

    以上です。同じデータでintegratedMLのクイックランをこの記事に追加するには、10分メモとしては長すぎてしまうため、次のパート2の記事で話すことにします。 

    0
    0 314
    記事 Hiroshi Sato · 11月 11, 2021 1m read

    これは InterSystems FAQ サイトの記事です。
     

    タイムアウトを無効にするためには、DSNの設定でクエリタイムアウト無効に設定します。

    Windowsコントロールパネル > [管理ツール] > [データソース(ODBC)] > [システムDSN]の構成


    で クエリタイムアウト無効 にチェックを入れますと、タイムアウトは無効になります。

    アプリケーション側で変更する場合は、ODBC APIレベルで設定することが可能です。


    ODBCのSQLSetStmtAttr関数をコールする際に、SQL_ATTR_QUERY_TIMEOUT属性を設定してから、データソースに接続してください。

    0
    0 11653
    記事 Megumi Kakechi · 10月 28, 2021 2m read

    これは InterSystems FAQ サイトの記事です。

    TIMESTAMP型はInterSystems製品では、%Library.TimeStampデータタイプ(=%TimeStamp)に対応していて、フォーマットは YYYY-MM-DD HH:MM:SS.nnnnnnnnn です。

    小数点以下の精度を変更したい場合は、以下の方法で設定します。

    1) システムワイドに設定する

    管理ポータル:[システム管理] > [構成] > [SQLとオブジェクトの設定] > [一般SQL設定] 
    GETDATE(), CURRENT_TIME, CURRENT_TIMESTAMP のデフォルト時刻精度 に 0~9 の範囲で桁数を指定できます。

    設定内容の詳細については、下記のドキュメントページをご参照ください。
    追加構成設定~ - 一般SQL設定 - SQL タブ


    2) SQLの関数で指定する

    CURRENT_TIMESTAMP()の引数を利用して、小数点以下の精度を 0~9 の範囲で桁数を指定できます。
    ※CURRENT_TIMESTAMP()で返される値は、実行環境のタイムゾーンの現在の日付時刻を返します。

    0
    0 1504
    記事 Toshihiko Minamoto · 9月 30, 2021 16m read

    はじめに

    Caché 2016.2のフィールドテストはかなり前から利用可能ですので、このバージョンで新しく追加されたドキュメントデータモデルという重要な機能に焦点を当てたいと思います。 このモデルは、オブジェクト、テーブル、および多次元配列など、データ処理をサポートするさまざまな方法として自然に追加されました。 プラットフォームがより柔軟になるため、さらに多くのユースケースに適したものになります。

    いくつかのコンテキストから始めましょう。 NoSQLムーブメントの傘下にあるデータベースシステムを少なくとも1つは知っているかもしれません。 これにはかなりたくさんのデータベースがあり、いくつかのカテゴリにグループ化することができます。 Key/Valueは非常に単純なデータモデルです。 値をデータベースに格納し、それにキーを関連付けることができます。 値を取得する場合は、キーを介してそれにアクセスする必要があります。 適切なキーを選択によってソートが決まり、キーの一部であるものでグループ化する場合に単純な集計に使用できるようになるため、キーの選択が重要な鍵となります。 ただし、値は値にすぎません。 値内の特定のサブ要素にアクセスしたり、それらにインデックスを作成したりすることはできません。 値をさらに活用するには、アプリケーションロジックを書く必要があります。 Key/Valueは、大規模なデータセットと非常に単純な値を操作する必要がある場合に最適ですが、より複雑なレコードを扱う場合には価値が劣ります。

    ドキュメントデータモデルはKey/Valueにとてもよく似ていますが、値はより複雑です。 値はキーに関連付けられたままになりますが、さらに、値のサブ要素にアクセスして特定の要素にインデックスを作成することができます。 つまり、いくつかのサブ要素が制限を満たす特定のドキュメントを検索することもできるということです。 明らかに、NoSQLの世界にはGraphのような他のモデルもさらに存在しますが、ここでは、ドキュメントに焦点を置くことにします。

     

    そもそもドキュメントとは?

    一部の読者を混乱させる傾向があるため、まず最初に、1つ明確にしておきましょう。この記事で「ドキュメント」と言った場合、PDFファイルやWordドキュメントといった物理的なドキュメントを指してはいません。
    この文脈でのドキュメントとは、サブ値を特定のパスと関連付けることのできる構造を指しています。 ドキュメントを記述できるシリアル化形式には、JSONやXMLなどのよく知られたものが様々あります。 通常こういった形式には、共通して次のような構造とデータ型があります。

    1. 順序付けされていないKey/Valueペアの構造
    2. 順序付けされた値のリスト
    3. スカラー値

    1つ目は、XMLの属性要素とJSONのオブジェクトにマッピングされます。 2つ目の構造は、XMLのサブ要素とJSONの配列を使ったリストによって導入されています。 3つ目は、単に、文字列、数値、ブール値といったネイティブのデータ型を利用できるようしています。

    JSONのようなシリアル化された形式でドキュメントを視覚化することは一般的ですが、これは、ドキュメントを表現できる一方法にすぎないことに注意してください。 この記事では、JSONを主なシリアル化形式として使用することにします。JSONサポートの改善機能をうまく利用できるでしょう。この改善についてまだ読んでいない方はこちらをご覧ください。

    ドキュメントはコレクションにグループ化されます。 セマンティックと潜在的に共通の構造を持つドキュメントは同じコレクションに保存する必要があります。 コレクションはその場で作成できるため、事前にスキーマ情報を用意しておく必要はありません。

    コレクションにアクセスするには、データベースハンドルを最初に取得しておく必要があります。 データベースハンドルはサーバーへの接続として機能し、コレクションへの単純なアクセスを提供しますが、分散環境の場合にはさらに複雑なシナリオを処理することもできます。

     

    基本

    まず、Caché Object Scriptで単純なドキュメントを挿入する方法を見てみましょう。

    USER>set db = ##class(%DataModel.Document.Database).$getDatabase()
    
    USER>set superheroes = db.$getCollection("superheroes")
    
    USER>set hero1 = {"name":"Superman","specialPower":"laser eyes"}
    
    USER>set hero2 = {"name":"Hulk","specialPower":"super strong"}
    
    USER>do superheroes.$insert(hero1)
    
    USER>do superheroes.$insert(hero2)
    
    USER>write superheroes.$size()
    2

    上記のコードサンプルでは、まずデータベースハンドルが取得されて、「superheroes」というコレクションが取得されます。 コレクションは明示的に作成されるため、事前に設定する必要はありません。 新しいコレクションにアクセスできるようになったら、ヒーローのSupermanとHulkを表す非常に単純なドキュメントを2つ作成します。 これらは$insert(<document>)への呼び出しでコレクションに保存され、コレクションサイズの最終チェックで、2つのドキュメントを報告します。これは、以前にコレクションが存在していなかったためです。

    $insert()呼び出しは、成功すると、挿入されたドキュメントを返します。 このため、ドキュメントの操作を続行する場合に、自動的に割り当てられたIDを取得することができます。 また、チェーンメソッドも可能になります。

    USER>set hero3 = {"name":"AntMan","specialPower":"can shrink and become super strong"}
    
    USER>write superheroes.$insert(hero3).$getDocumentID()
    3

    このコードスニペットは、別のヒーローオブジェクトを作成し、superheroesコレクションに永続させます。 今回は、メソッド呼び出しの$getDocumentID()$insert()呼び出しに連鎖させ、システムがこのドキュメントに割り当てたIDを取得します。 $insert()は必ず自動的にIDを割り当てます。 独自のIDを割り当てる必要がある場合は、$insertAt(<User-ID>,<document>)呼び出しを利用できます。

    特定のIDでドキュメントを取得する場合は、コレクションに対して$get(<ID>)メソッドを呼び出すことができます。

    USER>set antMan = superHeroes.$get(3)
    
    USER>write antMan.$toJSON()
    {"name":"AntMan","specialPower":"can shrink and become super strong"}

    Supermanとそのhometownを表すドキュメントを更新するとしましょう。 この場合、$upsert(<ID>,<document>)呼び出しを使用して、既存のドキュメントを簡単に更新することができます。

    USER>set hero1.hometown = "Metropolis"
    
    USER>do superheroes.$upsert(1,hero1)
    
    USER>write superheroes.$get(1).$toJSON()
    {"name":"Superman","specialPower":"laser eyes","hometown":"Metropolis"}

    $upsert()は、IDがまだほかに使用されていない場合にドキュメントを挿入するか、そうでない場合に既存のドキュメントを更新します。
    もちろん、$toJSON()を呼び出すだけで、コレクションの全コンテンツをJSONにシリアル化することも可能です。

    USER>write superheroes.$toJSON()
    [
    {"documentID":1,"documentVersion":4,"content":{"name":"Superman","specialPower":"laser eyes","hometown":"Metropolis"}},
    {"documentID":2,"documentVersion":2,"content":{"name":"Hulk","specialPower":"super strong"}},
    {"documentID":3,"documentVersion":3,"content":{"name":"AntMan","specialPower":"can shrink and become super strong"}}
    ]

    コレクションがドキュメントの配列として表されていることがわかります。 各ドキュメントは、ドキュメントIDとドキュメントバージョンでラップされており、同時実行を適切に処理するために使用されます。 実際のドキュメントコンテンツは、ラッパーのプロパティコンテンツに格納されます。 これは、コレクションの完全なスナップショットを取得して移動できるようにするために必要な表現です。
    また、これはモデルの非常に重要な側面をカバーしており、特殊プロパティを予約してドキュメントデータに挿入することはありません。 ドキュメントを適切に処理するためのエンジンが必要とする情報は、ドキュメントの外部に保存されます。  さらに、ドキュメントはオブジェクトまたは配列のいずれかです。 その他の多くのドキュメントストアは、オブジェクトを最上位の要素としてのみ許可しています。

    コレクションでドキュメントを変更するためのAPI呼び出しには他にもたくさんあり、基本的な演算をいくつか見てきました。 ドキュメントの挿入と変更は楽しい作業ですが、実際にデータセットを分析したり、特定の制限を満たすドキュメントを取得したりすると、さらに興味深くなります。 

     

    クエリ

    すべてのデータモデルが有用とみなされるには、何らかのクエリ機能が必要です。  動的スキーマを使用してドキュメントをクエリできるようにするには、2つの潜在的な方法があります。

    1. 動的なドキュメントの性質に対処できる独自のクエリ言語を設計して実装する
    2. 定着している構造化されたクエリ言語にクエリを統合する

    この記事の後の方で説明するいくつかの理由により、コレクションをSQLエンジンに公開することにしました。 SQLの知識を引き続き活用できるというメリットがあります。また、クエリ方言の別のフレーバーを作成しているところでもあります。 実際、SQL ANSI委員会は、JSONの標準拡張機能を提案しており、それに準拠しています。 まとめれば、これらの拡張機能には、JSON関数の2つのカテゴリが含まれています。

    1. リレーションコンテンツからJSONコンテンツに公開するための関数セット
    2. 動的JSONコンテンツをクエリするための関数セット 

    この記事の範囲では、2つ目のカテゴリである動的JSONコンテンツのクエリのみを取り上げ、結果をSQLで処理できるようにテーブルとして利用できるようにします。

    動的コンテンツ(関連するスキーマのないコンテンツ)を公開し、事前に定義されたスキーマを使用してデータを操作するSQLで利用できるようにする魔法の関数は、JSON_TABLEです。 一般に、この関数は2つの引数を取ります。

    1. JSONデータソース
    2. 名前と型で列へのJSONパスのマッピングを指定する定義

    骨に肉付けした例を見てみましょう。

    SELECT name, power FROM JSON_TABLE(
      'superheroes',
      '$' COLUMNS(
        name VARCHAR(100) PATH '$.name',
        power VARCHAR(300) PATH '$.specialPower'
      )
    )
    
    name        power
    ---------   -------------------------------
    Superman    laser eyes
    Hulk        super strong
    AntMan      can shrink and become super strong

    JSON_TABLE関数の最初の引数は、それが作成する仮想テーブルのソースを定義します。 この場合、コレクション「superheroes」をクエリします。 このコレクションのドキュメントごとに行が作られます。
    2つ目の引数は、ドキュメントの特定の値をテーブルの列として公開することを忘れないでください。 この引数は2つ部分で構成されています。最初のステップとして、次の式のコンテキストを設定します。 ドル記号'$'には特別な意味があり、ドキュメントのルートを指しています。 それ以降のすべての式はこのコンテキストを基準としています。
    後に続くのは、COLUMNS句で、カンマ区切りのCOLUMN式のリストです。 COLUMN式ごとに、仮想テーブルの列が作成されます。 「name」と「power」という2つの列をクエリで公開しています。 列「name」はVARCHAR(100)型で定義されていますが、列「power」は300文字に制限されています。 PATH式は、JPL(JSONパス言語)式を使用してドキュメントの特定の値を列に関連付けています。 キー「name」の値は列「name」に公開されますが、キー「specialPower」の値は列「power」にマッピングされます。 JPL式は非常に表現力が高く強力ですが、これについては別のトピックで説明することにします。 このサンプルで使用した式は非常に基本的な式です。

    この構文が初めてであれば、理解するのに少し時間が掛かるかもしれませんが、 JSON_TABLE関数を自然に読み取ると理解しやすいでしょう。 例として、上記のクエリを使います。 ここで表現しているのは、基本的に次のことです。

    コレクション「superheroes」をクエリし、各ドキュメントのルートに式のコンテキストを設定します。 次の2つの列を公開します。

    1. 列「name」を型VARCHAR(100)で公開し、キー「name」の値を挿入します。
    2. 列「power」を型VARCHAR(300)で公開し、キー「specialPower」の値を挿入します。

    前に述べた通り、JPL式は複雑になりがちであるか、たくさんの列を公開したいだけの場合もあります。 そのため、型定義を参照できる標準への拡張を組み込みました。これは基本的に、事前定義済みのCOLUMNS句です。 このようにして、上記のCOLUMNS句を登録することができます。

    do db.$createType("heropower",{"columns":[{"column":"name","type":"VARCHAR(100)","path":"$.name"},{"column":"power","type":"VARCHAR(300)","path":"$.specialPower"}]})
    

    型情報を登録したら、%TYPE式を使って、JSON_TABLE関数でそれを参照することができます。

    SELECT name, power FROM JSON_TABLE(
      'superheroes',
      '$' %TYPE 'heropower'
    )

    これは明らかに、SQLクエリにドキュメントの一貫したビューを提供し、クエリそのものを大幅に簡略化する上で役立ちます。

    高度な内容

    ここまで説明したことのほぼすべてについて補足することはたくさんありますが、ここでは最も重要なことに焦点を当てたいと思います。 最後のセクションを読みながら、JSON_TABLE関数を非常に強力なポイントとしている手掛かりに気づいたかもしれません。 

    1. 仮想テーブルを作成する
    2. JSONのようなデータをソースデータとして消費できる

    最初の項目はそれだけで重要なポイントです。コレクションを簡単にクエリして、別のJSON_TABLE呼び出しまたは正にテーブルと結合することができるからです。 コレクションをテーブルと結合できることは大きなメリットです。要件に応じて、データに完璧なデータモデルを選択できるのです。 
    型安全、整合性チェックが必要であるのに、モデルがあまり進化していませんか? リレーショナルを使いましょう。 他のソースのデータを処理してそのデータを消費する必要があり、モデルが必ず急速に変化するか、アプリケーションユーザーの影響を受ける可能性のあるモデルを保存することを検討していますか? ドキュメントデータモデルを選択しましょう。 モデルはSQLで一緒にまとめることができるので安心です。

    JSON_TABLE関数の2つ目のメリットは、実のところ基盤のデータモデルとは無関係です。 これまでに、JSON_TABLEでコレクションのクエリを説明してきました。 最初の引数は任意の有効なJSON入力にすることもできます。 次の例を考察しましょう。

    SELECT name, power FROM JSON_TABLE(
      '[
        {"name":"Thor","specialPower":"smashing hammer"},
        {"name":"Aquaman","specialPower":"can breathe underwater"}
      ]',
      '$' %TYPE 'heropowers'
    )
    
    name        power
    ---------   -------------------------------
    Thor        smashing hammer
    Aquaman     can breathe underwater

    入力は通常のJSON文字列で、オブジェクトの配列を表します。 構造がsuperheroesコレクションと一致するため、保存された型識別子「heropowers」を再利用することができます。 
    これにより、強力なユースケースが可能になります。 実際にメモリ内のJSONデータをディスクに永続させずにクエリすることができます。 REST呼び出しでJSONデータをリクエストし、クエリを実行してコレクションまたはテーブルを結合できます。 この機能を使用すると、Twitterタイムライン、GitHubリポジトリの統計、株式情報、または単に天気予報のフィードをクエリできます。 この機能は非常に便利であるため、これについては、後日、専用の記事で取り上げたいと思います。

     

    REST対応

    ドキュメントデータモデルでは、初期状態でREST対応のインターフェースが備わっています。 すべてのCRUD(作成、読み取り、更新、削除)とクエリ機能はHTTP経由で利用できます。 完全なAPIについては説明しませんが、ネームスペース「USER」内の「superheroes」コレクションのすべてのドキュメントを取得するサンプルcURLを以下に示します。

    curl -X GET -H "Accept: application/json" -H "Cache-Control: no-cache" http://localhost:57774/api/document/v1/user/superheroes

     

    私のユースケースで使用できますか?

    ドキュメントデータモデルは、InterSystemsのプラットフォームへの重要な追加機能です。 これは、オブジェクトとテーブルに続く、まったく新しいモデルです。 SQLとうまく統合できるため、既存のアプリケーションで簡単に利用することができます。 Caché 2016.1で導入された新しいJSON機能によって、CachéでのJSONの処理が楽しく簡単になります。

    そうは言っても、これは新しいモデルです。 いつ、そしてなぜそれを使用するのかを理解する必要があります。 常に言っていることですが、特定のタスクにはそれに適したツールを選択してください。

    このデータモデルは、動的データを処理する必要がある場合に優れています。 以下に、主な技術的メリットをまとめます。

    • 柔軟性と使いやすさ

    スキーマを予め定義する必要がないため、データの作業環境を素早くセットアップし、データ構造の変更に簡単に適応させることができます。

    • スパース性

    テーブルに300列があっても、各行が入力できるのはその内の15列であることを覚えていますか? これはスパースなデータセットであり、リレーショナルシステムではそれらを最適に処理にできません。 ドキュメントは設計上スパースであり、効率的に保存と処理を行えるようになっています。

    • 階層

    配列やオブジェクトなどの構造化型は、任意の深さでネストすることができます。 つまり、ドキュメント内で関連するデータを保存することができるため、そのレコードにアクセスする必要がある場合の読み取りのI/Oを潜在的に縮小することができます。 データは非正規化して保存できますが、リレーショナルモデルではデータは正規化して保存されます。

    • 動的な型

    特定のキーには、列のように固定されたデータ型がありません。 名前は、あるドキュメントでは文字列であっても、別のドキュメントでは複雑なオブジェクトである可能性があります。 単純なものは単純にしましょう。 複雑になることはありますが、そうなった場合はもう一度単純化しましょう。

    上記の項目はそれぞれ重要であり、適切なユースケースには、少なくとも1つが必要ではありますが、すべての項目が一致することは珍しいことではありません。

    モバイルアプリケーションのバックエンドを構築しているとしましょう。 クライアント(エンドユーザー)は自由に更新できるため、同時に複数のバージョンのインターフェースをサポートする必要があります。 WebServicesを使用するなど、契約によって開発すると、データインターフェースを素早く適応させる能力が低下する可能性があります(ただし、安定性が増す可能性はあります)。 ドキュメントデータモデルには柔軟性が備わっているため、スキーマを素早く進化させ、特定のレコード型の複数のバージョンを処理し、それでもクエリで相関させることができます。

     

    その他のリソース

    この魅力的な新機能についてさらに詳しく知りたい方は、利用可能なフィールドテストバージョン2016.2または2016.3を入手してください。

    『ドキュメントデータモデル学習パス』を必ず確認してください。
    https://beta.learning.intersystems.com/course/view.php?id=9

    今年のグローバルサミットのセッションをお見逃しなく。 「データモデリングリソースガイド」には、関連するすべてのセッションが集められています。
    https://beta.learning.intersystems.com/course/view.php?id=106

    最後に、開発者コミュニティに参加し、質問を投稿しましょう。また、フィードバックもお待ちしています。

    0
    0 200
    記事 Mihoko Iijima · 9月 24, 2021 3m read

    これは InterSystems FAQ サイトの記事です。

    システムユーティリティクラスを利用したプログラムを作成することで出力できます。

    【注意1】出力データの区切り文字がカンマではなくタブで出力されます点、ご注意ください。

    【注意2】エクセルでタブ区切りのファイルを開くため、エクセルの開くメニューから出力したファイルを開いてください。

    処理概要は以下の通りです。

    1) %SYSTEM.SQL クラス(※)の Execute() メソッドを使用します。

    第1引数:SQL文を指定します。

    第2引数:内部日付が格納されている場合 yyyy-mm-dd で出力されるように表示モードを切り替えます。(1:odbcモード)

    詳細は下記ドキュメントページおよびクラスリファレンスをご参照ください。

    ドキュメント - $SYSTEM.SQL.Execute()【IRIS
    クラスリファレンス - $SYSTEM.SQLクラス - Execute()メソッド【IRIS

    ドキュメント - $SYSTEM.SQL.Execute()
    クラスリファレンス - $SYSTEM.SQLクラス - Execute()メソッド

    ※ %SYSTEMパッケージ以下クラスは $system 特殊変数を利用してメソッドを実行できます。

    set rset=$system.SQL.Execute("ここにSQL文",1)
    0
    0 1227
    記事 Mihoko Iijima · 9月 14, 2021 1m read

    これは InterSystems FAQ サイトの記事です。

    管理ポータルの SQL 画面(管理ポータル > システムエクスプローラ > SQL)で、任意の SQL を実行した後、結果の行数などが表示されいる行の右端にある「印刷」のリンクから、CSVファイル出力を選択できます。

    「クエリ印刷」画面で「ファイルにエクスポート」をチェックすると「ファイル形式」の欄が表示されます。

    形式に「CSV」を指定し、出力ファイル名を指定したあと「エクスポート」ボタンをクリックすると出力できます。

    注意:古いバージョンでは正しくCSV出力が行えない場合もあります。新しいバージョンをぜひご利用ください。

    プログラムでCSV出力を行いたい場合は、関連記事をご参照ください。

    0
    0 482
    記事 Toshihiko Minamoto · 9月 14, 2021 10m read

    より産業向けのグローバルストレージスキーム

    この連載の第1回では、リレーショナルデータベースにおけるEAV(Entity-Attribute-Value)モデルを取り上げ、テーブルにエンティティ、属性、および値を保存することのメリットとデメリットについて確認しました。 このアプローチには柔軟性という点でメリットがあるにもかかわらず、特にデータの論理構造と物理ストレージの基本的な不一致などによりさまざまな問題が引き起こされるという深刻なデメリットがあります。

    こういった問題を解決するために、階層情報の保存向けに最適化されたグローバル変数を、EAVアプローチが通常処理するタスクに使用できるかどうかを確認することにしました。

    パート1では、オンラインストア向けのカタログをテーブルを使って作成し、その後で1つのグローバル変数のみで作成しました。 それでは、複数のグローバル変数で同じ構造を実装してみることにしましょう。

    最初のグローバル変数^catalogには、ディレクトリ構造を保存します。 2つ目のグローバル変数^goodには、店の商品を保存します。 ^indexグローバルには、店のインデックスを保存します。 プロパティは階層的なカタログに関連付けられているため、プロパティ用の個別のグローバル変数は作成しません。

    このアプローチでは、エンティティごとに(プロパティを除く)、個別のグローバル変数を使用しているため、論理の観点では優れています。 グローバルカタログ構造は次のようになります。

    Set ^сatalog(root_id, "Properties", "capacity", "name") = "Capacity, GB"
    Set ^сatalog(root_id, "Properties", "capacity", "sort") = 1
    
    Set ^сatalog(root_id, sub1_id, "Properties", "endurance", "name") = "Endurance, TBW"
    Set ^сatalog(root_id, sub1_id, "Properties", "endurance", "sort") = 2
    
    Set ^сatalog(root_id, sub1_id, "goods", id_good1) = 1
    Set ^сatalog(root_id, sub1_id, "goods", id_good2) = 1
    
    Set ^сatalog(root_id, sub2_id, "Properties", "avg_seek_time", "name") = "Rotate speed, ms"
    Set ^сatalog(root_id, sub2_id, "Properties", "avg_seek_time", "sort") = 3
    
    Set ^сatalog(root_id, sub2_id, "goods", id_good3) = 1
    Set ^сatalog(root_id, sub2_id, "goods", id_good4) = 1
    

     

    商品のグローバル変数は、次のようになります。

    Set ^good(id_good, property1) = value1
    Set ^good(id_good, property2) = value2
    Set ^good(id_good, property3) = value3
    Set ^good(id_good, "catalog") = catalog_id
    

     

    もちろん、商品のあるすべてのカタログセクションで、必要なプロパティで並べ替えを行えるようにインデックスが必要となります。 インデックスグローバルは、次のような構造になります。

    Set ^index(id_catalog, property1, id_good) = 1
    ; To quickly get the full path to concrete sub-catalog
    Set ^index("path", id_catalog) = "^catalog(root_id, sub1_id)"
    

     

    したがって、カタログのすべてのセクションで、リストを並べ替えることができます。 インデックスグローバルはオプションです。 カタログのこのセクションの商品数が多い場合にのみ役立ちます。

    デモデータを操作するためのObjectScriptコード

    では、データを操作するために、ObjectScriptを使用しましょう。 まず、特定の商品のプロパティを取得することから始めます。 特定の商品のIDがあり、そのプロパティを並べ替えの値で指定された順序で表示する必要があります。 そのためのコードは次のようになります。

    get_sorted_properties(path, boolTable)
    {
      ; remember all the properties in the temporary global
      While $QLENGTH(@path) > 0 {
        if ($DATA(@path("Properties"))) {
          set ln=""
          for {
            Set ln = $order(@path("Properties", ln))
            Quit: ln = ""
    
            IF boolTable & @path("Properties", ln, "table_view") = 1 {
              Set ^tmp(@path("Properties", ln, "sort"), ln) = @path("Properties", ln, "name")
            }
          ELSE {
            Set ^tmp(@path("Properties", ln, "sort"), ln) = @path("Properties", ln, "name")
          }
        }
      }
    }
    
    print_sorted_properties_of_good(id_good)
    {
      Set id_catalog = ^good(id_good, "catalog")
      Set path = ^index("path", id_catalog)
    
      Do get_sorted_properties(path, 0)
    
      set ln =""
      for {
       Set ln = $order(^tmp(ln))
       Quit: ln = ""
       Set fn = ""
       for {
        Set fn = $order(^tmp(ln, fn))
        Quit: fn = ""
        Write ^tmp(ln, fn), " ", ^good(id_good, fn),!
       }
      }
    }
    

     

    次に、id_catalogに基づいて、カタログセクションの商品を表形式で取得します。

    print_goods_table_of_catalog(id_catalog)
    { 
      Set path = ^index("path", id_catalog)
      Do get_sorted_properties(path, 1)
    
      set id=""
      for {
        Set id = $order(@path("goods"), id)
        Quit: id = ""
    
        Write id," ", ^good(id, "price"), " "
    
        set ln =""
        for {
          Set ln = $order(^tmp(ln))
          Quit: ln = ""
          Set fn = ""
          for {
            Set fn = $order(^tmp(ln, fn))
            Quit: fn = ""
            Write ^tmp(ln, fn), " ", ^good(id, fn)
          }
          Write !
        }
      }
    }
    

     

    可読性: EAV SQLとグローバル変数

    では、EAVとSQLの使用をグローバル変数の使用と比較してみましょう。 コードの明確さについては、これが主観的なパラメーターであることは明らかです。 しかし、例として新しい商品の作成方法を見てみましょう。

    SQLを使用したEAVアプローチから確認します。 まず、オブジェクトのプロパティリストを取得する必要があります。 これは別のタスクであり、非常に時間がかかります。 capacityweight、およびenduranceという3つのプロパティのIDがすでに分かっているとします。

    START TRANSACTION
    INSERT INTO good (name, price, item_count, catalog_id) VALUES ('F320 3.2TB AIC SSD', 700, 10, 15);
    
    SET @last_id = LAST_INSERT_ID ();
    
    INSERT INTO NumberValues ​​Values​​(@last_id, @id_capacity, 3200);
    INSERT INTO NumberValues ​​Values​​(@last_id, @id_weight, 0.4);
    INSERT INTO NumberValues ​​Values​​(@last_id, @id_endurance, 29000);
    COMMIT
    

     

    この例ではプロパティが3つしかないため、例にはそれほど圧倒されません。 一般的なケースでは、トランザクション内のテキストテーブルにいくつかの挿入があります。

    INSERT INTO TextValues ​​Values​​(@last_id, @ id_text_prop1, 'Text value of property 1');
    INSERT INTO TextValues ​​Values​​(@last_id, @ id_text_prop2, 'Text value of property 2');
    ...
    INSERT INTO TextValues Values (@last_id, @id_text_propN, 'Text value of property N');
    

     

    もちろん、数値の代わりに「capacity」を使うというように、IDプロパティの代わりにテキスト表記を使用すれば、SQLバージョンをもう少し簡略することも可能ですが、 SQLの世界では、これは受け入れられません。 エンティティのインスタンスを列挙するには、数値IDを使用するのが慣例です。 このため、インデックス処理が高速化し(インデックス処理のバイトが少なくなるため)、一意性を追跡しやすくなり、新しいIDを自動的に作成しやすくなります。 この場合、挿入フラグメントは次のようになります。

    INSERT INTO NumberValues ​​Values​​(@last_id, 'capacity', 3200);
    INSERT INTO NumberValues ​​Values​​(@last_id, 'weight', 0.4);
    INSERT INTO NumberValues ​​Values​​(@last_id, 'endurance', 29000);
    

     

    次は、同じ例をグローバル変数を使用した場合のコードです。

    TSTART
    Set ^good(id, "name") = "F320 3.2TB AIC SSD"
    Set ^("price") = 700, ^("item_count") = 10, ^("reserved_count") = 0, ^("catalog") = id_catalog
    Set ^("capacity") = 3200, ^("weight") = 0.4, ^("endurance") = 29000
    TCOMMIT
    

     

    では、EAVアプローチで商品を削除してみましょう。

    START TRANSACTION
    DELETE FROM good WHERE id = @ good_id;
    DELETE FROM NumberValues ​​WHERE good_id = @ good_id;
    DELETE FROM TextValues ​​WHERE good_id = @ good_id;
    COMMIT
    

     

    そして、グローバル変数でも同じことを行います。

    Kill ^good(id_good)
    

    2つのアプローチをコードの長さの観点から比較することもできます。 上記の例からわかるように、グローバル変数を使用した方が、コードは短くなります。 これはメリットです。 コードが短くなるほど、エラーの数も減り、コードを理解して管理するのも容易になります。

    一般に、コードが短いほど処理が高速化します。 そして、この場合には、グローバル変数はリレーショナルテーブルよりも低位データ構造であるため、確かにそのとおりです。

    EAVとグローバル変数におけるデータのスケーリング

    次に、水平方向のスケーリングを見てみましょう。 EAVアプローチでは、少なくとも3つの最も大きなテーブル(Good、NumberValues、TextValues)を複数のサーバーに分散する必要があります。 エンティティと属性のあるテーブルにはほとんど情報がないため、これらのテーブルは単純にすべてのサーバーに丸ごとコピーすることができます。

    各サーバーでは、水平方向のスケーリングにより、さまざまな商品がGood、NumberValues、およびTextValuesテーブルに保存されます。 異なる商品でIDが重複しないように、各サーバーの商品に対して特定のIDブロックを割り当てる必要があります。

    グローバルを使って水平方向のスケーリングを行う場合、グローバルでID範囲を構成し、グローバル範囲を各サーバーに割り当てる必要があります。

    複雑さは、EAVとグローバルであまり変わりませんが、EAVアプローチの場合は、3つのテーブルにID範囲を構成しなければなりません。 グローバルの場合は、1つのグローバル変数のみにIDを構成するだけで済みます。 つまり、水平方向のスケーリングを調整するには、グローバル変数の方が簡単と言えます。

    EAVとグローバル変数におけるデータ損失

    最後に、データベースファイルの破損によるデータ損失のリスクを検討してみましょう。 5つのテーブルか3つのグローバル(インデックスグローバルを含む)のどちらにすべてのデータを保存する方が簡単でしょうか。

    3つのグローバルの方が簡単だと思います。 EAVアプローチでは、さまざまな商品のデータがテーブルに混在しているのに対し、グローバルでは情報がより全体的に保存されています。 基盤のブランチは、保存されて順に並べ替えられています。 そのため、データがパスタが絡み合うように保存されるEAVアプローチに比べれば、グローバルの一部の破損によってダメージにつながる可能性は低くなります。

    データ回復におけるもう1つの悩みの種は、情報の表示方法です。 EAVアプローチでは、情報は複数のテーブルに分割されているため、1つにまとめるには特別なスクリプトが必要です。 グローバルの場合は、ZWRITEコマンドを使用するだけで、ノードのすべての値と基盤のブランチを表示することができます。

    InterSystems IRISのグローバル: より優れたアプローチ?

    EAVアプローチは、階層データを保存するためのトリックとして出現しました。 テーブルは元々、ネストされたデータを保存するようには設計されてはいなかったため、 テーブルでグローバルをエミュレーションするのがEAVの事実上のアプローチです。 テーブルがグローバルよりも高位で低速のデータストレージ構造であることを考えると、EAVアプローチは、グローバルと比較した場合に失敗となります。

    個人的な意見を言えば、階層データ構造の場合、グローバルの方がプログラミングの点でより利便性が高く理解しやすいと思います。また、より高速でもあります。

    プロジェクトでEAVアプローチを計画している場合は、InterSystems IRISのグローバルを使用して階層データを保存することを検討するようお勧めします。

    0
    0 430
    記事 Toshihiko Minamoto · 9月 9, 2021 10m read

    はじめに

    この連載の最初の記事では、リレーショナルデータベースのEAV(Entity–Attribute–Value)モデルを見て、それがどのように使用されて、何に役立つのかを確認しましょう。 その上で、EAVモデルの概念とグローバル変数と比較します。

    原則として検索する必要のある、フィールド数、または階層的にネストされたフィールドの数が不明なオブジェクトがある場合があります。

    たとえば、多様な商品群を扱うオンラインストアを考えてみましょう。 商品群ごとに固有の一意のプロパティセットがあり、共通のプロパティもあります。 たとえば、SSDとHDDドライブには共通の「capacity」プロパティがありますが、SSDには「Endurance, TBW」、HDDには「average head positioning time」という一意のプロパティもあります。

    場合によっては、同じ商品でも別のメーカーが製造した場合には、それぞれに一意のプロパティが存在します。

    では、50種の商品群を販売するオンラインストアがあるとしましょう。 各商品群には、数値またはテキストの固有のプロパティが5つあります。

    実際に使用するのは5個だけであっても、各商品に250個のプロパティがあるテーブルを作成するのであれば、ディスク容量の要件が大幅に増える(50倍!)だけでなく、有用性のない空のプロパティによってキャッシュが詰まってしまうため、データベースの速度特性が大幅に減少してしまいます。

    さらに、それだけではありません。 固有のプロパティを持つ新しい商品群を追加するたびに、ALTER TABLEコマンドを使用してテーブルの構造を変更する必要があります。 大規模なテーブルであれば、この操作には数時間、さらには数日間かかる可能性もあり、ビジネスでは許容しかねます。

    これを注意深く読んでいる方は「商品群ごとに異なるテーブルを用意しては?」と言うでしょう。 もちろんその通りではありますが、このアプローチを使用すると、大型ストアの場合には、数万個ものテーブルでデータベースを作成することになり、管理が困難になります。 さらに、サポートする必要のあるコードがますます複雑化してしまいます。

    一方、新しい商品群を追加するときに、データベースの構造を変更する必要はありません。 新しい商品群向けの新しいテーブルを追加すればよいだけだからです。

    いずれにせよユーザーは、ストア内の商品を簡単に検索できること、現在のプロパティを示す便利な表形式で商品を表示できること、そして商品を比較できることが必要です。

    ご想像のとおり、商品群の5個のプロパティのみが必要であるにもかかわらず、商品テーブルにはさまざまなプロパティを示す250個のフィールドがあれば不便であるのと同様に、250個のフィールドを使った検索フォームは、ユーザーにとって非常に不便です。 これは商品の比較にも当てはまります。

    マーケティングデータベースも別の有用な例と言えるでしょう。 それに格納されている人ごとに、絶えず追加、変更、または削除される可能性のある多数のプロパティが必要です(多くの場合はネストされています)。 過去にある商品を特定の数量で購入した、特定の商品群を購入した、何かに参加した、どこかで勤務した、親戚がいる、この都市に住む、特定の社会階級に属する、などのプロパティがあります。 フィールド数は数千個にもなり、変化も絶えないでしょう。 マーケターは常に、さまざまな顧客グループを区別して魅力的な特別オファーを提供する方法を考えています。

    これらの問題を解決すると同時に、明確で確定的なデータベース構造を得るために、Entity-Attribute-Valueアプローチが編み出されました。

    EAVアプローチ

    EAVアプローチの本質は、エンティティ、属性、および属性値を個別に保存することにあります。 一般的に、EAVアプローチを説明するために、Entity、Attribute、およびValueという3つのテーブルのみが使用されます。

    保存するデモデータの構造。

    テーブルを使用したEAVアプローチの実装

    5つ(最後の2つのテーブルを1つに統合することにした場合は4つ)のテーブルを使用したより複雑な例を考察してみましょう。

    最初のテーブルはСatalogです。

    CREATE TABLE Catalog (
    id INT,
    name VARCHAR (128),
    parent INT
    );
    

    このテーブルは実際、EAVアプローチのエンティティに対応しています。 階層的な商品カタログのセクションを保存します。

    2つ目のテーブルは ****Fieldです。

    CREATE TABLE Field (
    id INT,
    name VARCHAR (128),
    typeOf INT,
    searchable INT,
    catalog_id INT,
    table_view INT,
    sort INT
    );
    

    このテーブルでは、属性の名前、型、および属性が検索可能であるかどうかを指定します。 また、プロパティが属する商品を保持しているカタログのセクションも指定します。 catalog_id以下のカタログセクションにあるすべての商品には、このテーブルに保存されているさまざまなプロパティがある場合があります。

    3つ目のテーブルはGoodです。 商品を、商品の価格、商品の合計数量、商品の予約数量、および商品名とともに保存するように設計されています。 厳密にはこのテーブルは必要ではありませんが、個人的には、商品用に別のテーブルを用意しておくと便利だと思います。

    CREATE TABLE Good (
    id INT,
    name VARCHAR (128),
    price FLOAT,
    item_count INT,
    reserved_count,
    catalog_id INT
    );
    

    4つ目のテーブル(TextValues)と5つ目のテーブル(NumberValues)は、商品のテキストの値と数値属性を保存するように設計されており、構造も似ています。

    CREATE TABLE TextValues ​​(
    good_id INT,
    field_id INT,
    fValue TEXT
    );
    
    CREATE TABLE NumberValues ​​(
    good_id INT,
    field_id INT,
    fValue INT
    );
    

    テキスト値と数値に個別のテーブルを使用する代わりに、次の構造で単一のCustomeValuesテーブルを使用することもできます。

    CREATE TABLE CustomValues ​​(
    good_id INT,
    field_id INT,
    text_value TEXT,
    number_value INT
    );
    

    データ型ごとに個別に保存しておけば、速度が向上し、容量を節約できるため、私は別々に保存する方を好んでいます。

    EAVアプローチを使用したデータへのアクセス

    SQLを使用して、カタログ構造マッピングを表示してみましょう。

    SELECT * FROM Catalog ORDER BY id;
    

    これらの値からツリーを作成するには、個別のコードが必要となります。 PHPでは、次のようになります。

    $stmt = $ pdo-> query ('SELECT * FROM Catalog ORDER BY id');
    $aTree = [];
    $idRoot = NULL;
    
    while ($row = $ stmt->fetch())
    {
        $aTree [$row ['id']] = ['name' => $ row ['name']];
    
        if (! $row['parent'])
          $idRoot = $row ['id'];
        else
          $aTree [$row['parent']] ['sub'] [] = $row['id'];
    }
    

    将来的には、ルートノードの $aTree[$ idRoot] から始めると、ツリーを簡単に描画できるようになります。

    では、特定の商品のプロパティを取得しましょう。 

    まず、この商品に固有のプロパティのリストを取得し、その後で、それらのプロパティとデータベースにあるプロパティを接続します。 実際には、示されるすべてのプロパティが入力されているわけではないため、LEFT JOINを使用する必要があります。

    SELECT * FROM
    (
    SELECT g. *, F.name, f.type_of, val.fValue, f.sort FROM Good as g
    INNER JOIN Field as f ON f.catalog_id = g.catalog_id
    LEFT JOIN TextValues ​​as val ON tv.good = g.id AND f.id = val.field_id
    WHERE g.id = $ nGood AND f.type_of = 'text'
    UNION
    SELECT g. *, F.name, f.type_of, val.fValue, f.sort FROM Good as g
    INNER JOIN Field as f ON f.catalog_id = g.catalog_id
    LEFT JOIN NumberValues ​​as val ON val.good = g.id AND f.id = val.field_id
    WHERE g.id = $nGood AND f.type_of = 'number'
    ) t
    ORDER BY t.sort;
    

    数値とテキスト値の両方を保存するために1つのテーブルのみを使用すると、クエリを大幅に簡略化できます。

    SELECT g. *, F.name, f.type_of, val.text_value, val.number_value, f.sort FROM Good as g
    INNER JOIN Field as f ON f.catalog = g.catalog
    LEFT JOIN CustomValues ​​as val ON tv.good = g.id AND f.id = val.field_id
    WHERE g.id = $nGood
    ORDER BY f.sort;
    

    では、$nCatalogカタログセクションに含まれる商品を表形式で取得します。 まず、カタログのこのセクションのテーブルビューに反映する必要があるプロパティのリストを取得します。

    SELECT f.id, f.name, f.type_of FROM Catalog as c
    INNER JOIN Field as f ON f.catalog_id = c.id
    WHERE c.id = $nCatalog AND f.table_view = 1
    ORDER BY f.sort;
    

    次に、テーブルを作成するクエリを構築します。 表形式ビューには、3つの追加プロパティ(Goodテーブルのプロパティのほかに)が必要だとします。 クエリを単純化するために、次を前提としています。

    SELECT g.if, g.name, g.price,
                f1.fValue as f1_val,
                f2.fValue as f2_val,
                f3.fValue as f3_val,
    FROM Good
    LEFT JOIN TextValue as f1 ON f1.good_id = g.id
    LEFT JOIN NumberValue as f2 ON f2.good_id = g.id
    LEFT JOIN NumberValue as f3 ON f3.good_id = g.id
    WHERE g.catalog_id = $nCatalog;
    

    EAVアプローチの長所と短所

    EAVアプローチは明らかに柔軟性のメリットがあります。 テーブルなどの固定されたデータ構造を使用すると、オブジェクトの広範なプロパティセットを保存することが可能になります。 また、データベースのスキーマを変更せずに、別のデータ構造を保存することができます。 

    また、非常に多くの開発者に馴染みのあるSQLも使用することができます。 

    最も明白なデメリットは、データの論理構造と物理ストレージの不一致であり、これによって様々な問題が引き起こされます。 

    さらに、プログラミングには、非常に複雑なSQLクエリが伴うこともよくあります。 EAVデータの表示には標準的に使用されていないツールの作成が必要となるため、デバッグが困難になることがあります。 また、LEFT JOINクエリを使用する必要がある場合があるため、データベースの速度が低下してしまいます。

    グローバル変数: EAVの代替

    私はSQLの世界とグローバル変数の世界の両方に精通しているため、EAVアプローチが解決するタスクにグローバルを使用する方がはるかに魅力的になるのではないかと考えました。

    グローバル変数はまばらで階層的な情報を保存できるデータ構造です。 グローバル変数は階層情報を保存するために慎重に最適化されているというのが非常に重要なポイントです。 グローバル変数自体はテーブルよりも低レベルの構造であるため、テーブルよりもはるかに素早く動作します。

    同時に、グローバル構造自体をデータ構造に従って選択できるため、コードを非常に単純で明確にすることができます。

    デモデータを保存するためのグローバル構造

    グローバル変数はデータを保存する上で非常に柔軟でエレガントな構造であるため、1つのグローバル変数を管理するだけでカタログセクション、プロパティ、および商品などのデータを保存することができます。

    グローバル構造がデータ構造にどれほど似ているのかに注目してください。 このコンプライアンスによって、コーディングとデバッグが大幅に簡略化されます。

    実際には、全ての情報を1つのグローバルに保存したい気持ちが非常に強くても、複数のグローバルを使用することをお勧めします。 インデックス用に別のグローバルを作成することが合理的です。 また、ディレクトリのパーティション構造のストレージを商品から分離することもできます。

    この続きは?

    この連載の2つ目の記事では、EAVモデルに従う代わりに、InterSystems Irisのグローバルにデータを保存する方法の詳細とメリットについて説明します。

    0
    0 2052
    記事 Megumi Kakechi · 9月 1, 2021 1m read

    これは InterSystems FAQ サイトの記事です。

    InterSystems製品のクラスクエリにはSQL文を利用する方法と、ユーザコードによる記述が選択できます。

    スタジオメニューの [クラス] >[追加] > [クエリ] を選択すると、クエリウィザードが起動します。


    画面に、「実装」を選択する項目があり、「このクエリはユーザコードで定義されている」を選択すると SQL文ではなく、ユーザコードによりクエリ定義が行えます。

     
    次画面以降では、必要な情報として、入力パラメータ、結果セットの選択カラムの指定を行います。 ウィザード終了後、ユーザコードのひな型として、以下のメソッドがクラス定義に追加されます。

    {QueryName}Execute()

    {QueryName}Fetch()

    {QueryName}Close()

    ({QueryName}はクエリウィザードで指定するクエリ名です。)

    これらのメソッドに実際に実行したいユーザコードを記述していきます。

    以下に、詳細記述とJavaから呼び出すサンプルがございますのでご参照ください。

    https://github.com/Intersystems-jp/classquery_sample

    0
    0 495
    記事 Mihoko Iijima · 8月 30, 2021 2m read

    これは InterSystems FAQ サイトの記事です。

    InterSystems 製品には、テーブルのコンテンツに関する統計を収集し、クエリの最適化に役立てる「テーブルチューニング機能」があります。

    設定方法は、以下のドキュメントをご参照ください。

    ターミナルでは次のコマンドを実行します。

    do$SYSTEM.SQL.Stats.Table.GatherTableStats("スキーマ名.テーブル名",1,1)

     

     2021.1以前のバージョンでは以下メソッドを利用します。

    Do$system.SQL.TuneTable("スキーマ名.テーブル名",1,1)

     

    また、以下の資料もご参照ください。

    1) パフォーマンス調査の基礎知識として必要なグローバル構造の解説やSQLの動作の仕組みからクエリプランの見方の解説

    インターシステムズ・シンポジア2011の資料

    2) パフォーマンスチューニングの例(P13~)

    インターシステムズ・シンポジア2012の資料

    3) ビットマップ・インデックスの圧縮やオプティマイザ・ヒントなど、Caché SQLのパフォーマンスを最大限に引き出すための情報について

    インターシステムズ・シンポジア2014の資料

    4) パフォーマンスに困ったときにどこに着目し、どのツールで調べていくか、お客様から日々ご相談をいただくカスタマーサポートから、解決に向かうアプローチの「イロハ」をご紹介するビデオ

    動画:Performance 101

    5) 開発者コミュティのパフォーマンス関連情報(performanceタグ)

    performanceタグ

    0
    0 451
    InterSystems公式 Yoichi Miyashita · 7月 28, 2021

    対象バージョン
       InterSystems IRIS および IRIS for Health: 2019.1.0 以降

    対象プラットフォーム  すべて

    InterSystemsは、間違ったクエリ結果が返る問題を修正しました。この問題は上記の InterSystems 製品のバージョンで発生する可能性があります。

    問題の詳細
    以下のすべての条件をみたした場合、クエリ結果が不正確な可能性があります。

    • GROUP BY を使用し、かつ、COUNT などの集計を使って「いない」
    • GROUP BY で指定して「いない」フィールドを SELECT で指定し(*)、かつ、そのフィールドが WHERE 条件に含まれている
    • 単一のインデックスによりクエリ結果が取得でき、かつ、そのインデックスが GROUP BY で指定されたフィールドから始まっている

    (*) グループ化していないフィールドを SELECT に含めることは、 InterSystems SQL独自の拡張であり、SQL標準でないことにご注意ください。

    この問題により、上記をみたすクエリでは WHERE 条件が結果に正しく反映されません。すなわち、条件をみたさない行を含むクエリ結果を返すことになります。
    なお、この警告の最後に、問題の影響を受けるクエリと受けないクエリ例を掲載しています。

    解決方法
    この問題は修正ID: AK1043 で解決します。この修正は今後リリースされるInterSystems IRIS および IRIS for Health 2020.1.2 や 2021.1.1 を含むすべての製品に含まれる予定です。また、お客様のご要望により、修正を現在お使いの製品に対するパッチとして個別に作成してご提供することが可能です。

    ※HealthShare をお使いのお客様へ
    InterSystems にて HealthShare ファミリーの製品をレビューした結果、上記条件をみたすクエリは、製品内部で使用されていないことが判明しました。
    ただし、HealthShare 製品とは別に InterSystems IRIS 機能を使われているお客様は、ご自身で作成されたアプリケーションやカスタマイズがこの問題の影響を受けるかご確認いただく必要があります。
     

    クエリ例
    本問題の影響に関するクエリ例をお伝えします。
    ここでは test.TestTable テーブル を用いて説明します。テーブルには 3つのInteger プロパティと 1つのインデックスが、以下のように定義されています。

    Property Int1 As %Integer;
    Property Int2 As %Integer;
    Property Int3 As %Integer;
    Index GroupBy On (Int1, Int2);

    影響を受けるクエリ
     

    • SELECT Int1, Int2 FROM test.TestTable WHERE Int2 > 1 GROUP BY Int1
      このクエリは上記の条件をみたすため、本問題の影響を受けます。そのため Int2 <= 1 の行も誤って表示されます。  

    • SELECT Int1, ID FROM test.TestTable WHERE Int2 <= 1 GROUP BY Int1
      このクエリも本問題の影響をうけます。IDがインデックスに含まれるため結果をインデックスから直接取得できるからです。 そのため Int2 > 1 の行も誤って表示されます。


    影響を受けないクエリ

    • SELECT Int1, Int2 FROM test.TestTable WHERE Int2 > 1 GROUP BY を使用していないため、影響を受けません。  

    • SELECT Int1, Int2, COUNT(*) FROM test.TestTable WHERE Int2 > 1 GROUP BY Int1 COUNT 集計を使用しているため、影響を受けません。  

    • SELECT Int1, Int2 FROM test.TestTable WHERE Int1 > 1 GROUP BY Int1
    • SELECT Int1, Int2 FROM test.TestTable GROUP BY Int1 いずれのクエリも「GROUP BY で指定していないフィールドが WHERE 条件に含まれている」条件にあてはまらないため、影響を受けません。  

    • SELECT Int1, Int2 FROM test.TestTable WHERE Int1 > 1 GROUP BY Int2 テーブルの複合インデックスが GROUP BY で指定しているフィールドから 始まっていないため、影響を受けません。  

    • SELECT Int1, Int2 FROM test.TestTable WHERE Int2 > 1 AND Int3 > 1 GROUP BY Int1
    • SELECT Int1, Int3 FROM test.TestTable WHERE Int2 > 1 GROUP BY Int 1 いずれのクエリも Int3 フィールドを使っており、このフィールドはインデックスに格納されていません。つまり、このクエリは、インデックスのみから  結果を取得できないため、影響を受けません。
    0
    0 298
    記事 Mihoko Iijima · 7月 15, 2021 5m read

    これは InterSystems FAQ サイトの記事です。

    InterSystems 製品は複数の SQL 文を一度に受け付けませんので、SQL 文での複数結果セットのサポートはありませんが、ストアドプロシージャを作成して複数の結果セットを返すことができます。

    以下、ストアドプロシージャの中では、 %SQL.Statement クラスを利用して SQL を実行し、結果セットを作成し、結果セットを呼び出し元に返すため、ストアドプロシージャ実行時に自動生成されるプロシージャ・コンテキスト(=%sqlcontext 変数に格納)の中に、結果セットを登録しています。

    結果セットの登録は、%Library.ProcedureContext クラスの AddResultSet() メソッドを使用します。

    メモ:ストアドプロシージャ実行時に自動生成されるプロシージャ・コンテキストは %Library.ProcedureContext のインスタンスで %sqlcontext 変数に自動的に設定されます。

    0
    0 817
    記事 Mihoko Iijima · 7月 5, 2021 1m read

    これは InterSystems FAQ サイトの記事です。

    %Persistent クラスに既定で生成される Extent クエリは、ID 順に結果セットを出力することを意図していませんので、これは、仕様通りの動きとなります。

    並び順は、設定されているインデックスにより、変わることがあります。

    0
    0 130
    記事 Toshihiko Minamoto · 5月 18, 2021 12m read

    第2部: インデックス処理

    クラスにどのようなインデックスが必要であるのか、それをどのように定義するのかについて理解できたので、 次に、どのように処理するのかについて確認しましょう。

    クエリプラン

    注意: クラスに変更を適用する場合と同様に、ライブシステムにインデックスを追加する場合にもリスクが伴います。インデックスが入力されているときに、ユーザーがデータにアクセスしたり更新したりすると、クエリ結果が空になったり誤った結果が生じることがあります。また、構築中のインデックスが破損する場合もあります。 ライブシステムでインデックスを定義したり使用したりするには追加の手順があり、それについてはこのセクションで触れていますが、詳細はドキュメントに記載されています。)

    新しいインデックスの準備ができたら、SQLオプティマイザが、クエリを実行する上で最も効率的に読み取れるインデックスであると判断するかどうかを確認できます。 プランを確認するために実際にクエリを実行する必要はありません。 クエリがあれば、プランをプログラムで確認することができます。

                Set query = 1

         Set query(1) = “SELECT SSN,Name FROM Sample.Person WHERE Office_State = 'MA'”

    D $system.SQL.ShowPlan(.query)

    また、システムエクスプローラー -> SQLより、システム管理ポータルのインターフェースに従って確認することもできます。

    ここから、どのインデックスが、テーブルデータ(または「マスターマップ」)をロードする前に使用されているのかを確認できます。 プランの新しいクエリは期待どおりに動作しているのか、 プランのロジックが合理的であるのかを検討しましょう。

    SQLオプティマイザが作成したインデックスを使用していることを確認できたら、これらのインデックスが適切に機能しているかどうかを確認できます。

    インデックスの構築

    (現時点では計画しているだけの段階であり、データが存在しない場合は、ここに説明されている手順は必要ありません。)

    インデックスを定義しても、テーブルのデータが自動的に入力または「構築」されるわけではありません。 まだ構築されていない新しいインデックスをクエリプランに使用すると、クエリ結果が誤りとなったり空になったりするリスクがありますが、 マップの選択可能性を0に設定すると、インデックスの使用準備が整う前にそのインデックスを「無効」にすることができます。0に設定するというのは、基本的に、SQLオプティマイザに、クエリの実行にはそのインデックスを使用できないことを指示していることになります。

    write $SYSTEM.SQL.SetMapSelectability("Sample.Person","QuickSearchIDX",0) ; Set selectability of index QuickSearchIDX false

    上記の呼び出しは、新しいインデックスを追加する前でも使用できることに注意してください。 SQLオプティマイザは、この新しいインデックスの名前と、それが非アクティブであることを認識するため、クエリには使用しません。

    インデックスの入力は、 %BuildIndices メソッド(##class(<class>).%BuildIndices($lb("MyIDX")))を使用するか、システム管理ポータルのSQLページ(アクションドロップダウン)で行うことができます。

    インデックスの構築にかかる時間は、テーブル内の行数とインデックスの種類によって異なります。通常、bitsliceインデックスの作成には時間が掛かります。

    このプロセスが完了したら、もう一度SetMapSelectivityメソッドを使用して、新たに入力されたインデックスを有効にできます(この場合は 1 に設定)。

    インデックスを作成するというのは、基本的に、KILLコマンドとSETコマンドを発行して、インデックスを入力することです。 そのため、このプロセスを実施する間は、ディスク領域がいっぱいにならないように、ジャーナリングを無効にすることを検討してください。  

    新しいインデックスと既存のインデックスを作成する詳細な手順について、特にライブシステムでの手順については、以下にリンクされているドキュメントをご覧ください。

    https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQLOPT_indices#GSQLOPT_indices_build_readwrite

    インデックスの管理

    ようやく、実際にインデックスを使用できるようになりました。 クエリがどれほど効率的に実行するのか、ある特定のインデックスがどれくらいの頻度で使用されるのか、そしてインデックスの一貫性が崩れた場合にどのように対処するのかといったことを考慮する必要があります。

    パフォーマンス

    まず、このインデックスがクエリのパフォーマンスにどのような影響を与えたのかを検討できます。 SQLオプティマイザがクエリに新しいインデックスを使用していることがわかっている場合は、クエリを実行して、そのパフォーマンスの統計(グローバル参照数、読み取られた行数、クエリの準備時間と実行時間、およびディスクで費やされた時間)を収集することができます。

    前の例に戻りましょう。

    SELECT SSN,Name,DOB FROM Sample.Person WHERE Name %STARTSWITH 'Smith,J'

    このクエリのパフォーマンスを支援するために、次のインデックスがあります。

    Index QuickSearchIDX On Name [ Data = (SSN, DOB, Name) ];

    NameIDXインデックスは、すでにNameプロパティにあります。

    直感的には、クエリはQuickSearchIDXを使って実行していることがわかります。NameIDXは、Nameプロパティに基づいていますが、SSNまたはDOBのデータ値が含まれていないため、2番目の選択肢である可能性があります。

    QuickSearchIDXを使ったこのクエリのパフォーマンスは、実行するだけで簡単に確認できます。

    (余談ですが、パフォーマンスの違いを分かりやすくするために、クエリを実行する前に、クエリキャッシュを消去しています。 SQLクエリが実行されると、次回実行時のパフォーマンスを改善できるように、実行に使用されたプランが保存されます。この記事の目的から外れてしまうため詳しい説明はしませんが、SQLパフォーマンスに関するその他の考慮事項として、この記事の最後に資料を記載しておきます。)

    Row count: 31 Performance: 0.003 seconds  154 global references 3264 lines executed 1 disk read latency (ms)

    (率直に)クエリの実行に必要なのは、QuickSearchIDXのみです。

    QuickSearchIDXではなくNameIDXを使ったパフォーマンスを比較して見ましょう。これは、クエリキーワードである%IGNOREINDEXを追加して行います(SQLオプティマイザが特定のインデックスを選択できないようにします)。

    クエリを次のように記述します。

    SELECT SSN,Name,DOB FROM %IGNOREINDEX QuickSearchIDX Sample.Person WHERE Name %STARTSWITH 'Smith,J'

    クエリプランはNameIDXを使用するようになったので、インデックスを介して見つかった関連する行IDを使って、Sample.Personのデータグローバル(または「マスターマップ」)から読み取る必要があることがわかります。

    Row count: 31 Performance: 0.020 seconds  137 global references 3792 lines executed 17 disk read latency (ms) 

    実行に必要な時間、実行される行数、およびディスクのレイテンシが増加しているのがわかります。

    次に、インデックスを全く使用せずに、このクエリを実行してみましょう。 クエリを次のように調整します。

    SELECT SSN,Name,DOB FROM %IGNOREINDEX * Sample.Person WHERE Name %STARTSWITH 'Smith,J'

    インデックスを使用しない場合、条件を満たすかどうか、データの行を確認する必要があります。

    Row count: 31 Performance: 0.765 seconds  149999 global references 1202681 lines executed 517 disk read latency (ms)

    特殊なインデックスであるQuickSearchIDXが、インデックスを使用しない場合よりも100倍以上速く、また一般的なNameIDXを使用した場合よりもほぼ10倍速く、クエリを実行できています。さらに、NameIDXを使用した場合は、インデックスを全く使用しない場合よりも30倍以上速く実行しています。

    この特定の例では、QuickSearchIDXとNameIDXのパフォーマンスの差はわずかですが、数百万行に対して実行するクエリを1日に数百回実行するのであれば、貴重な時間を節約できることがわかるでしょう。

    SQLUtilitiesを使った既存のインデックスの分析

    %SYS.PTools.SQLUtilitiesには、IndexUsage、JoinIndices、TablesScans、TempIndicesなどのプロシージャが含まれています。 これらは、特定のネームスペースにある既存のクエリを分析し、特定のインデックスが使用される頻度、テーブルの各行で反復を選択しているクエリ、そしてインデックスをシミュレートする一時ファイルを生成しているクエリに関する情報を報告します。

    これらのプロシージャを使用することで、インデックスが対処できる可能性があるギャップと、使用されていないまたは非効率であるために削除することを検討した方がよいインデックスを特定することができます。

    これらのプロシージャと使用例についての詳細は、以下のクラスに関するドキュメントをご覧ください。

    https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQLOPT_optquery_indexanalysis

    インデックスに関する問題?

    インデックスを検証することで、インデックスが存在し、クラスの各行で正しく定義されているのかを確認できます。 インデックスが破損した状態になるクラスはありませんが、クエリが空の結果セットや誤った結果セットを返しているようであれば、クラスの既存のインデックスが現在有効であるかを確認することをお勧めします。

    インデックスは、プログラムで次のように検証できます。

                    Set status = ##class(<class>).%ValidateIndices(indices,autoCorrect,lockOption,multiProcess)  

    ここで、インデックスパラメーターはデフォルトで空の文字列です。つまり、すべてのインデックスかインデックスの名前を含む$listbuildオブジェクトを検証します。

    autoCorrectは、デフォルトで0になることに注意してください。 1である場合、検証プロセスで発生するすべてのエラーは修正されます。 機能的にはインデックスを再構築することに変わりませんが、ValidateIndicesのパフォーマンスは比較的に遅くなります。

    詳細は、%Library.Storageクラスのドキュメントをご覧ください。

    https://docs.intersystems.com/latest/csp/documatic/%25CSP.Documatic.cls?PAGE=CLASS&LIBRARY=%25SYS&CLASSNAME=%25Library.Storage#%ValidateIndices

    インデックスの削除

    あるインデックスが不要になった場合、またはテーブルに大規模な変更を行って、後で関連するインデックスを構築する際にパフォーマンスへの影響がないようにする場合は、Studioでインデックスの定義をクラスから取り除き、該当するインデックスグローバルノードを削除することができます。  または、DDLを介してDROP INDEXコマンドを実行することもできます。このコマンドでも、インデックスの定義とデータを消去することができます。 その後、キャッシュ済みクエリをパージすることで、確実に、削除されたインデックスが既存のプランによって使用されなくなります。

    さて今度は?

    インデックスは、SQLパフォーマンスの一部にしかすぎません。 この流れに並行し、インデックスのパフォーマンスと使用状況を監視するオプションは他にもあります。 SQLパフォーマンスを理解するには、次のことについても学ぶことをお勧めします。

    Tune Tables - テーブルに代表的なデータが入力されてから、またはデータの分布が大幅に変化した場合に実行するユーティリティです。 このユーティリティは、クラスの定義に、フィールドの長さや1つフィールドに含まれる一意の値の数など、SQLオプティマイザが効率的に実行するためにクエリプランを選択しやすくするメタデータを提供します。

    Kyle Baxterが、これに関する記事を執筆しています(https://community.intersystems.com/post/one-query-performance-trick-you-need-know-tune-table)。 

    クエリプラン - 基盤のコードがSQLクエリをどのように実行するか論理的に表現したプランです。 クエリが遅い場合、どのクエリプランが生成されているのか、クエリに適しているのか、このクエリをさらに最適化するために何を行えるのかを検討することができます。

    キャッシュドクエリ - 準備済みの動的SQLステートメント - キャッシュドクエリは、基本的にクエリプランの下にあるコードです。

    参考文献

    インデックスの定義と構築に関するドキュメント。 読み取り/書き込みのライブシステムで検討する必要のある追加手順が含まれています。 https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQLOPT_indices

    ISC SQLコマンド – DDLを介したインデックス処理の構文関連資料について、CREATE INDEXとDROP INDEXを参照してください。 これらのコマンドを実行するための適切なユーザー権限が含まれています。 https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_COMMANDS

    ISCクラスでのSQL照合に関する詳細。 デフォルトでは、文字列の値は、インデックスグローバルにSQLUPPER (“ STRING”) として格納されます。

    https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_collation

    [最終更新日: 2020年5月6日 - インデックス構築パフォーマンスとDROP INDEXコマンドの修正。]

    0
    0 365
    記事 Toshihiko Minamoto · 5月 12, 2021 12m read

    これは、SQLインデックスに関する2部構成の記事の前半です。

    第1部 - インデックスを理解する

    インデックスとは?

    最後に図書館に行った時のことを思い出してください。 通常そこには、分野別(そして作者順と題名順)に整理された本が並び、それぞれの棚には、本の分野を説明したコードが記載された本立てがあります。 特定の分野の本を収集する場合、すべての通路を歩いて一冊ずつ本の表紙を読む代わりに、目的の分野の本棚に直接向かって選ぶことができるでしょう。

    SQLインデックスにもこれと同じ機能があります。テーブルの各行にフィールドの値へのクイック参照を提供することで、パフォーマンスを向上させています。

    インデックスの設定は、最適なSQLパフォーマンスを得られるようにクラスを準備する際の主なステップの1つです。

    この記事では、次のことについて説明します。

    1. インデックスとは何か。いつ、なぜそれを使用するか。
    2. どのようなインデックスが存在するか、どのようなシナリオに適しているのか。
    3. インデックスの例
    4. 作成方法
  • インデックスが存在する場合、どのように扱うのか。
  • この記事では、Sampleスキーマのクラスを参照します。 このスキーマは以下に示すGitHubリポジトリにあります。また、CachéとEnsembleでインストールされるSamplesネームスペースでも提供されています。

    https://github.com/intersystems/Samples-Data

    基本

    永続プロパティと、永続データから確実に計算されるプロパティにインデックスを作成できます。

    Sample.CompanyのTaxIDプロパティにインデックスを作成するとしましょう。 StudioまたはAtelierで、以下のコードをクラス定義に追加します。

                    Index TaxIDIdx On TaxID;

    これに相当するDDL SQLステートメントは、次のようになります。

                    CREATE INDEX TaxIDIdx ON Sample.Company (TaxID);

    デフォルトのグローバルインデックス構造は、次のようになります。

                    ^Sample.CompanyI("TaxIDIdx",<TaxIDValueAtRowID>,<RowID>) = ""

    通常のデータグローバルのフィールドより、読み取るサブスクリプトが少ないところに注目してください。

    SELECT Name,TaxID FROM Sample.Company WHERE TaxID = 'J7349'」というクエリを見てみましょう。 論理的に単純なクエリです。このクエリを実行するためのクエリプランは、これを反映しています。

    このプランは基本的に、指定されたTaxID値を持つ行のインデックスグローバルをチェックし、データグローバル(「マスターマップ」)を参照して一致する行を取得するように指定しています。

    ここで、同じクエリを、TaxIDXにインデックスを使わずに考察してみましょう。 クエリプランの効率は、予想どおり、低下します。

    インデックスがない場合、IRISの基盤のクエリ実行は、メモリを読み取って、テーブルの各行にWHERE句の条件を適用します。論理的に言って、TaxIDを共有する会社はないと思うため、この作業をたった1行のためだけに行っているのです!

    もちろん、インデックスを使用するということは、インデックス行データがディスクにあるということですので、 条件の内容とテーブルに含まれるデータの量によっては、インデックスを作成してデータを入力する際に、それ固有の問題が生じる可能性もあります。

    では、プロパティにはいつインデックスを追加すればよいのでしょうか。

    一般的なケースとしては、あるプロパティを頻繁に条件とする場合が挙げられます。 例として個人のSSN(社会保障番号)または銀行口座番号といった識別情報があります。 また、生年月日や口座の資金も考慮できるでしょう。  Sample.Companyに戻ると、高収益の組織に関するデータを収集する場合は、およらくRevenueプロパティのインデックスを作成するとメリットがあるかもしれません。 逆に、条件を付ける可能性が低いプロパティは、インデックス作成にあまり適していません。会社のスローガンや説明などです。

    インデックスの種類も検討する必要がある場合を除けば、「シンプル イズ ベスト」なのです!

    インデックスの種類

    ここでは、6つの主要なインデックスの種類について説明します。標準、ビットマップ、複合、コレクション、ビットスライス、およびデータです。 また、ストリームに基づくiFindインデックスについても簡単に説明します。 上記の例で、標準のインデックスについてすでに触れているため、内容が重複するかもしれません。

    クラス定義にインデックスを作成する方法の例をいくつか紹介しますが、新しいインデックスをクラスに追加するというのは、クラス定義に行を追加するだけではありません。 その他の考慮事項については、この記事の第2部で説明します。

    それでは、例としてSample.Personを使用しましょう。 PersonにはサブクラスのEmployeeがあることに注目してください。いくつかの例を理解する上で関わってくることです。 EmployeeはデータグローバルストレージをPersonと共有し、PersonのすべてのインデックスはEmployeeに継承されます。つまり、Employeeはこれらの継承されたインデックスに、Personのインデックスグローバルを使用しているということです。

    これらのクラスにあまり詳しくない場合のために簡単に説明すると、Personには、SSN、DOB、Name、Home(StateとCityを含む埋め込みAddressオブジェクト)、およびOffice(Address)プロパティがあり、FavoriteColorsというリストコレクションがあります。 Employeeには、さらに私が定義したSalaryプロパティもあります。

    標準

    Index DateIDX On DOB;

    ここでは、「標準」を大まかに使用して、プロパティのプレーンな値(2進数表現ではなく)を格納するインデックスを参照しています。 値が文字列である場合、照合順序(デフォルトではSQLUPPER)で格納されます。

    ビットマップやビットスライスのインデックスに比べ、標準のインデックスは人間が読み取れる形式であり、比較的簡単に管理することができます。 グローバルノードは、テーブルの各行に1つずつあります。

    以下に、DateIDXがグローバルレベルでどのように格納されるのかを示しています。

    ^Sample.PersonI("DateIDX",51274,100115)="~Sample.Employee~" ; Date is 05/20/81

    インデックスの名前の後にある最初のサブスクリプトは日付値で、最後のサブスクリプトはそのDOBを持つPersonのID、そしてグローバルインデックスに格納された値は、このPersonがサブクラスSimple.Employeeのメンバーであることを示していることに注目してください。 このPersonがどのサブクラスのメンバーでもない場合、ノードの値は空の文字列になります。

    この基本構造は、ほとんどの非ビットインデックスと一致します。この場合、複数のプロパティのインデックスはグローバルでサブスクリプトをさらに作成し、ノードに複数の値が格納されると、$listbuildオブジェクトが生成されます。以下はその例です。

                    ^Package.ClassI(IndexName,IndexValue1,IndexValue2,IndexValue3,RowID) = $lb(SubClass,DataValue1,DataValue2)

    ビットマップ - プロパティの値に対応するIDセットのビット単位の表現。

    Index HomeStateIDX On Home.State [ Type = bitmap];

    ビットマップインデックスは、行ごとに格納される標準のインデックスとは対照的に、ユニーク値ごとに格納されます。

    上記の例をさらに詳しく見てみましょう。ID 1のPersonがマサチューセッツ州(MA)に、ID 2がニューヨーク州(NY)に、ID 3がマサチューセッツ州(MA)に、そしてID 4がロードアイランド州(RI)に住んでいるとします。 HomeStateIDXは基本的に次のように格納されます。

    ID

    1

    2

    3

    4

    (…)

    (…)

    0

    0

    0

    0

    -

    MA

    1

    0

    1

    0

    -

    NY

    0

    1

    0

    0

    -

    RI

    0

    0

    0

    1

    -

    (…)

    0

    0

    0

    0

    -

    ニューイングランド州にする人のデータを返すクエリが必要な場合、システムはビットマップインデックスの関連する行にビット単位のORをを実行します。 少なくとも、ID 1、3、および4のPersonオブジェクトをメモリに読み込む必要があることにすぐに気づくでしょう。

    ビットマップは、WHERE句のAND、RANGE、またはOR演算子に対して効率的です。 

    ビットマップインデックスの効率が標準インデックスより低くなる前にプロパティに指定できるユニーク値の数に、公式の上限はありませんが、一般的には、最大10,000個程度の値が経験則とされています。 そのため、ビットマップインデックスは、米国の州において効果的であっても、米国の市または郡のビットマップインデックスにはあまり有用とは言えません。

    また、ストレージの効率についても、考慮する必要があります。 テーブルへの行の追加や行の削除を頻繁に行う予定であれば、ビットマップインデックスのストレージはあまり効果的ではありません。 上記の例を考察してみましょう。何らかの理由で多数の行を削除し、ワイオミング州やノースダコタ州といった人口の少ない州に住む人がテーブルから消えたとします。 つまり、ビットマップには、ゼロのみの行がいくつか存在することになります。 一方で、ビットマップストレージが大きければ、より多くのユニーク値を格納しなければならなくなるため、大型のテーブルに新しい行を作成していけば、いずれは減速していく可能性があります。

    これらの例では、Sample.Personに約150,000行があります。 各グローバルノードには、最大64,000個のIDが格納されるため、ビットマップインデックスグローバルは、MAで3つの部分に分割されます。

          ^Sample.PersonI("HomeStateIDX"," MA",1)=$zwc(135,7992)_$c(0,(...))

    ^Sample.PersonI("HomeStateIDX"," MA",2)=$zwc(404,7990,(…))

    ^Sample.PersonI("HomeStateIDX"," MA",3)=$zwc(132,2744)_$c(0,(…))

    特殊ケース: エクステントビットマップ 

    $<ClassName>とされることの多いエクステントビットマップは、クラスのIDにおけるビットマップインデックスです。IRISはこれを使って行が存在するのかをすばやく検出し、COUNTクエリまたはサブクラスのクエリに役立てています。 これらのインデックスは、ビットマップインデックスがクラスに追加される際に自動的に生成されますが、次のように、クラス定義にビットマップエクステントインデックスを手動で作成することも可能です。


    Index Company [ Extent, SqlName = "$Company", Type = bitmap ];

    DDLのBITMAPEXTENTキーワードを使うこともできます。

    CREATE BITMAPEXTENT INDEX "$Company" ON TABLE Sample.Company

    複合 - 2つ以上のプロパティに基づくインデックス

    Index OfficeAddrIDX On (Office.City, Office.State);

    複合インデックスは通常、2つ以上のプロパティを条件とするクエリが頻繁に発生する場合に使用できます。

    インデックスはグローバルレベルで格納されるため、複合インデックスでは、プロパティの順序が重要になります。 インデックスグローバルの最初のディスク読み取りは保存されるため、選択する頻度の高いプロパティを最初に指定すると、高いパフォーマンス効率を得ることができます。この例では、米国の州の数より都市の数の方が多いため、Office.Cityが最初に指定されています。

    あまり選択しないプロパティを最初に指定すると、スペースの効率性が高くなります。 グローバル構造に焦点を当てれば、Stateを最初に指定すると、インデックスツリーのバランスがより高まります。 考えてみれば、各州には多数の市がありますが、1つの州にしか存在しない市もあるのです。

    また、いずれかのプロパティのみを条件としたクエリを頻繁に実行するのかどうかを検討することもお勧めします。別のインデックスを定義する手間を省けるからです。

    複合インデックスのグローバル構造の例を以下に示します。

    ^Sample.PersonI("OfficeAddrIDX"," BOSTON"," MA",100115)="~Sample.Employee~"

    余談: 複合インデックスかビットマップインデックスか

    複数のプロパティで条件付けするクエリの場合、個別のビットマップインデックスを使った方が1つの複合インデックスよりも効果的かどうかを検討することもできます。

    ビットマップインデックスが各プロパティに適切に適合するのであれば、2つの異なるインデックスに対してビット演算した方が効率的になる可能性があります。

    複合ビットマップインデックスを作成することもできます。これらはユニーク値が、インデックスを作成している複数のプロパティの共通した値となるビットマップインデックスです。 前のセクションで示したテーブルを考察してみましょう。ただし、州の代わりに、州と市のすべての可能な組み合わせ(マサチューセッツ州ボストン、マサチューセッツ州ケンブリッジ、マサチューセッツ州ロサンゼルスなど)を用いたテーブルです。両方の値に適合する行のセルは1となります。

    コレクション - コレクションプロパティに基づくインデックス

    次のように定義されたFavoriteColorsプロパティがあります。

    Property FavoriteColors As list Of %String;

    実演の目的で、インデックスは次のように定義されています。

    Index fcIDX1 On FavoriteColors(ELEMENTS);
    Index fcIDX2 On FavoriteColors(KEYS);

    ここでは、複数の値を含む単一セルのプロパティをより広く参照するために、「コレクション」を使用しています。 ここでは、List OfとArray Ofプロパティが重要で、必要に応じて区切り付きの文字列も指定できます。

    コレクションプロパティは自動的に解析され、インデックスが構築されます。 電話番号などの区切り付きのプロパティでは、このメソッドを明示的に <PropertyName>BuildValueArray(value, .valueArray) と定義する必要があります。

    上記のFavoriteColorsの例で考えると、お気に入りの色が青と白であるPersonのfcIDX1は、次のようになります。

    ^Sample.PersonI("fcIDX1"," BLUE",100115)="~Sample.Employee~"

    (…)

    ^Sample.PersonI("fcIDX1"," WHITE",100115)="~Sample.Employee~"

    そしてfcIDX2は次のようになります。

             ^Sample.PersonI("fcIDX2",1,100115)="~Sample.Employee~"      

    ^Sample.PersonI("fcIDX2",2,100115)="~Sample.Employee~"

    この場合、FavoriteCoslorsはListコレクションであるため、キーに基づくインデックスの有用性は、要素に基づくインデックスよりも低くなります。

    コレクションプロパティのインデックスの作成と管理に関するより詳しい考慮事項については、ドキュメントをご覧ください。

    https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQLOPT_indices#GSQLOPT_indices_collections

    ビットスライス - 数値データのビット文字列表現のビットマップ表現


    Index SalaryIDX On Salary [ Type = bitslice ]; //In Sample.Employee

    ビットスライスインデックスは、どの行に特定の値が含まれるかを表すフラグを含むビットマップインデックスとは異なり、最初に数値を10進数から2進数に変換し、その後で2進数値の各桁にビットマップを作成します。

    上記の例を見てみましょう。現実的に考えられるよう、Salaryを$1000単位として単純化します。つまり、従業員の給与が65であれば、65,000ドルということになります。

    ID 1のEmployeeのSalaryを15、ID 2のSalaryを40、ID 3のSalaryを64、ID 4のSalaryを130とします。 この場合、対応するビット値は次のようになります。

    15

    0

    0

    0

    0

    1

    1

    1

    1

    40

    0

    0

    1

    0

    1

    0

    0

    0

    64

    0

    1

    0

    0

    0

    0

    0

    0

    130

    1

    0

    0

    0

    0

    0

    1

    0

    ビット文字列は8桁を超えています。 対応するビットマップ表現(ビットスライスインデックス値)は、基本的に次ように格納されます。

    ^Sample.PersonI("SalaryIDX",1,1) = "1000" ; Row 1 has value in 1’s place

    ^Sample.PersonI("SalaryIDX",2,1) = "1001" ; Rows 1 and 4 have values in 2’s place

    ^Sample.PersonI("SalaryIDX",3,1) = "1000" ; Row 1 has value in 4’s place

    ^Sample.PersonI("SalaryIDX",4,1) = "1100" ; Rows 1 and 2 have values in 8’s place

    ^Sample.PersonI("SalaryIDX",5,1) = "0000" ; etc…

    ^Sample.PersonI("SalaryIDX",6,1) = "0100"

    ^Sample.PersonI("SalaryIDX",7,1) = "0010"

    ^Sample.PersonI("SalaryIDX",8,1) = "0001"

    Sample.Employeeまたはその行の給与を変更する演算(INSERT、UPDATES、DELETE)では、これらの各グローバルノードまたはビットスライスを更新する必要があることに注意してください。 ビットスライスインデックスをテーブルの複数のプロパティまたは頻繁に変更されるプロパティに追加すると、パフォーマンスにリスクが生じる可能性があります。 一般的に、ビットスライスインデックスの管理には、標準またはビットマップインデックスの管理よりもコストがかかります。

    ビットスライスインデックスは非常に特殊であるため、ユースケースも特殊であり、SUM、COUTN、またはAVGなどの集計計算を実行する必要のあるクエリで使用します。

    さらに、数値に対してのみ効果を発揮するため、文字列は2進数の0に変換されます。

    クエリの条件をチェックするために、インデックスではなくデータテーブルを読み取る必要がある場合、クエリの実行にビットスライスインデックスは選択されません。 Sample.PersonのNameにインデックスがないとします。 Smithという姓の従業員の平均給与を計算する場合(SELECT AVG(Salary) FROM Sample.Employee WHERE Name %STARTSWITH 'Smith,' )、WHERE条件を適用するためにデータ行を読み取る必要があるため、ビットスライスは実際には使用されません。

    行が頻繁に作成または削除されるテーブルのビットスライスとビットマップインデックスについても、同様のストレージに関する懸念があります。

    データ - グローバルノードに格納されているデータのインデックス。

    Index QuickSearchIDX On Name [ Data = (SSN, DOB, Name) ];

    前のいくつのかの例で、「~Sample.Employee~」という文字列がノード自体に値として格納されていることに気づいたかもしれません。 Sample.Employeeは、Sample.Personからインデックスを継承していることを思い出してください。 特にEmployeesをクエリする場合、プロパティ条件に一致するインデックスノードの値を読み取り、そのPersonがEmployeeでもあることを確認します。

    また、格納する値を明示的に定義することもできます。 インデックスグローバルノードでデータを定義すると、データグローバルの読み取りも保存できます。頻繁な選択クエリや順序付けクエリに役立ちます。

    上記のインデックスを例とすると、 名前の全部または一部を指定された人物に関する識別情報を取得する場合(フロントデスクアプリケーションでクライアントの情報を検索する場合など)、「SELECT SSN, Name, DOB FROM Sample.Person WHERE Name %STARTSWITH 'Smith,J' ORDER BY Name」というクエリを実行できます。 Nameをクエリ条件としており、取得しようとしている値はすべてQuickSearchIDXグローバルノード内に格納されているため、このクエリを実行するには、グローバルのみを読み取る必要があります。

    データ値は、ビットマップまたはビットスライスインデックスと保存できないことに注意してください。

    ^Sample.PersonI("QuickSearchIDX"," LARSON,KIRSTEN A.",100115)=$lb("~Sample.Employee~","555-55-5555",51274,"Larson,Kirsten A.")

    iFindインデックス

    このようなインデックスを聞いたことがあるでしょうか? 私にもありません。iFindインデックスは、ストリームプロパティで使用されますが、これを使用するには、クエリにキーワードで名前を指定する必要があります。

    もう少し説明することもできますが、このことについては、Kyle Baxterがすでに有用な記事を執筆しています。

    フリーテキスト検索:SQL開発者が秘密にしているテキストフィールドの検索方法 

    [最終更新日: 2020年4月16日 - 可読性を調整。]

    0
    0 579
    記事 Toshihiko Minamoto · 4月 19, 2021 8m read

    クラスの中で配列プロパティを使い、その要素 (キーと値の両方) によってスピーディに検索を実行できると非常に便利な場合があります (EAV モデルの場合は特に重宝します)。

    それでは、簡単な例を見てみましょう。

    <FONT COLOR="#000080">Class User.eav Extends %Persistent
    </FONT><FONT COLOR="#000000">{
    

    </FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">idx1 On attributes(ELEMENTS) [ </FONT><FONT COLOR="#000080">Data </FONT><FONT COLOR="#000000">= entity ];

    </FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">idx2 On (attributes(KEYS), attributes(ELEMENTS)) [ </FONT><FONT COLOR="#000080">Data </FONT><FONT COLOR="#000000">= entity ];

    </FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">entity;

    </FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">attributes </FONT><FONT COLOR="#000080">As array Of %String</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">SQLTABLENAME </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#800080">"attributes"</FONT><FONT COLOR="#000000">) [ </FONT><FONT COLOR="#000080">SqlFieldName </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#008000">attr </FONT><FONT COLOR="#000000">];

    </FONT><FONT COLOR="#000080">/// d ##class(User.eav).RepopulateAll() ClassMethod </FONT><FONT COLOR="#000000">RepopulateAll() {   </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000000">..</FONT><FONT COLOR="#0000ff">%DeleteExtent</FONT><FONT COLOR="#000000">()      </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">name</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#0000ff">$TR</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"Sibe^rian pi^ne ce^dar"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"^"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#0000ff">$c</FONT><FONT COLOR="#000000">(769))      </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">=..</FONT><FONT COLOR="#0000ff">%New</FONT><FONT COLOR="#000000">()   </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">entity</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"Human"   </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">attributes</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">SetAt</FONT><FONT COLOR="#000000">(22,</FONT><FONT COLOR="#008000">"Age"</FONT><FONT COLOR="#000000">)   </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">attributes</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">SetAt</FONT><FONT COLOR="#000000">(186,</FONT><FONT COLOR="#008000">"Height"</FONT><FONT COLOR="#000000">)   </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">attributes</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">SetAt</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"Jack"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"Name"</FONT><FONT COLOR="#000000">)   </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%Save</FONT><FONT COLOR="#000000">()

      </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">=..</FONT><FONT COLOR="#0000ff">%New</FONT><FONT COLOR="#000000">()   </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">entity</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"Tree"   </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">attributes</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">SetAt</FONT><FONT COLOR="#000000">(186,</FONT><FONT COLOR="#008000">"Age"</FONT><FONT COLOR="#000000">)   </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">attributes</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">SetAt</FONT><FONT COLOR="#000000">(22,</FONT><FONT COLOR="#008000">"Height"</FONT><FONT COLOR="#000000">)   </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">attributes</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">SetAt</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"Pines"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"Family"</FONT><FONT COLOR="#000000">)   </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">attributes</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">SetAt</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">name</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"Name"</FONT><FONT COLOR="#000000">)   </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%Save</FONT><FONT COLOR="#000000">()      </FONT><FONT COLOR="#008000">/*      ;or      &sql(insert into eav(entity) select 'Human' union select 'Tree')   &sql(insert into attributes(eav,element_key,attr)   select 1,'Age',22 union   select 1,'Height',186 union   select 1,'Name','Jack' union   select 2,'Age',186 union   select 2,'Height',22 union   select 2,'Family','Pines' union   select 2,'Name',:name)   */      </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000000">..</FONT><FONT COLOR="#0000ff">Reindex</FONT><FONT COLOR="#000000">() }

    </FONT><FONT COLOR="#000080">/// d ##class(User.eav).Reindex() ClassMethod </FONT><FONT COLOR="#000000">Reindex() {   </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000000">..</FONT><FONT COLOR="#0000ff">%BuildIndices</FONT><FONT COLOR="#000000">(,1)

      </FONT><FONT COLOR="#0000ff">d $system</FONT><FONT COLOR="#008080">.SQL</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">TuneTable</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"SQLUser.eav"</FONT><FONT COLOR="#000000">,1)   </FONT><FONT COLOR="#0000ff">d $system</FONT><FONT COLOR="#008080">.SQL</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">TuneTable</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"SQLUser.attributes"</FONT><FONT COLOR="#000000">,1)   </FONT><FONT COLOR="#0000ff">d $system</FONT><FONT COLOR="#008080">.OBJ</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">Compile</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$classname</FONT><FONT COLOR="#000000">(),</FONT><FONT COLOR="#008000">"cu/multicompile=1"</FONT><FONT COLOR="#000000">) }

    }</FONT>

    データを読み込んだ後に、以下を実行します。
    USER><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">User.eav</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">RepopulateAll</FONT><FONT COLOR="#000000">()</FONT>
    すると、テーブルに以下のデータが表示されます。
    IDentity
    eav テーブル
    1Human
    2Tree
    eavIDattrelement_key
    attributes テーブル
    11||Age22Age
    11||NameJackName
    11||Height186Height
    22||Age186Age
    22||Height22Height
    22||NameSibérian píne cédarName
    22||FamilyPinesFamily
    データを持つグローバル
    USER>zw ^User.eavD
    ^User.eavD=2
    ^User.eavD(1)=$lb("","Human")
    ^User.eavD(1,"attributes","Age")=22
    ^User.eavD(1,"attributes","Height")=186
    ^User.eavD(1,"attributes","Name")="Jack"
    ^User.eavD(2)=$lb("","Tree")
    ^User.eavD(2,"attributes","Age")=186
    ^User.eavD(2,"attributes","Family")="Pines"
    ^User.eavD(2,"attributes","Height")=22
    ^User.eavD(2,"attributes","Name")="Sibérian píne cédar"
    インデックスを持つグローバル
    USER>zw ^User.eavI
    ^User.eavI("idx1"," 186",1)=$lb("","Human")
    ^User.eavI("idx1"," 186",2)=$lb("","Tree")
    ^User.eavI("idx1"," 22",1)=$lb("","Human")
    ^User.eavI("idx1"," 22",2)=$lb("","Tree")
    ^User.eavI("idx1"," JACK",1)=$lb("","Human")
    ^User.eavI("idx1"," PINES",2)=$lb("","Tree")
    ^User.eavI("idx1"," SIBÉRIAN PÍNE CÉDAR",2)=$lb("","Tree")
    ^User.eavI("idx2","Age"," 186",2)=$lb("","Tree")
    ^User.eavI("idx2","Age"," 22",1)=$lb("","Human")
    ^User.eavI("idx2","Family"," PINES",2)=$lb("","Tree")
    ^User.eavI("idx2","Height"," 186",1)=$lb("","Human")
    ^User.eavI("idx2","Height"," 22",2)=$lb("","Tree")
    ^User.eavI("idx2","Name"," JACK",1)=$lb("","Human")
    ^User.eavI("idx2","Name"," SIBÉRIAN PÍNE CÉDAR",2)=$lb("","Tree")

    それでは、以下のクエリを実行しましょう。

    entity
    select entity from eav where attributes->attr = 22
    Human
    Tree

    クエリは実行されるものの、インデックスではなくフルスキャンが使われています。 SMP (System Management Portal) のテーブルを見ると、そこには生成されたはずの idx1 と idx2 がありません。

    これが起るのは、SQL エンジンには、サブテーブルの配列のフィールドだけを基に作成された配列プロパティで、キー (すなわち propArray(KEY)) を持つもののインデックスしか「見えない」ためです。 両方のインデックスに「entity」フィールドがあるにもかかわらず、「attribute」サブテーブルにはそれが見当たりません。

    また、attributes(KEYS) を持たない <FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">idx3 On attributes(ELEMENTS);</FONT> も表示されません。但し、こちらのインデックス

    • <FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">idx4 On (attributes(KEYS), attributes(ELEMENTS));</FONT>
    • <FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">idx5 On (attributes(ELEMENTS), attributes(KEYS));</FONT>
    は表示され、クエリに考慮されます。 しかし、これらはあらゆる種類のクエリに最適という訳ではありません。

    では、SQL エンジンが配列プロパティ要素のインデックスを見えるようにするには、どのメソッドを使うのが一番簡単なのでしょうか?

    Caché 2015.1 では、SetCollectionProjection/GetCollectionProjection メソッドを使ってサブテーブルに表示できるコレクションであれば、それをテーブルフィールドとして表示することができます。

    ですが、この機能はデフォルトで無効になっています。

    これらのメソッドは、Caché の過去のバージョンにはありませんが、手動で有効化できるかを試すことはできます。

    %SYS><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#000000">^%SYS(</FONT><FONT COLOR="#008000">"sql"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"sys"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"collection projection"</FONT><FONT COLOR="#000000">)=1</FONT>

    この変更を実行した後は、必ずクラスをもう一度コンパイルしてください。

    それでは、このパラメーターをオン (1) にして、その動作を見てみましょう。

    SMP にインデックスが表示されるようになりました。また、「eav」テーブルには「attr」という隠れたコレクションフィールドがあります。 しかし、それでも私たちが実行したクエリはインデックス idx1/idx2 を見つけられません。

    それでは、お馴染みの述語 FOR SOME %ELEMENT を使って、この状況を解決しましょう。

    entity
    select entity from eav where for some %element(attr) (%value = 22)
    Human
    Tree

    インデックス idx1 がクエリで使用されています。 これを少しだけ変更します。

    entity
    select entity from eav where for some %element(attr) (%value = 22 and %key'Age')
    Human
    entity
    select entity from eav where for some %element(attr) (%value = 22 and %key'Height')
    Tree
    最後の 2 つの例では、インデックス idx1 の代わりにインデックス idx2 が使用されています。

    UPD: SQLPROJECTION を使えば、これと同じことができます。つまり、以下を実行します。

    <FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">attributes </FONT><FONT COLOR="#000080">As array Of %String</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">SQLPROJECTION </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#800080">"table/column"</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#000080">SQLTABLENAME </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#800080">"attributes"</FONT><FONT COLOR="#000000">) [ </FONT><FONT COLOR="#000080">SqlFieldName </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#008000">attr </FONT><FONT COLOR="#000000">];</FONT>

    この記事は、こちらの記事を翻訳したものです。 [@Evgeny Shvarov]、翻訳作業にご協力いただきありがとうございました。

    この記事は、Habrahabr でもお読みいただけます。

    本記事を書くきっかけとなったサイト: 17383689

    WRC のフレームワークについてヒントをくださった [@Alexander.Koblov] に深く感謝します。

    0
    0 405
    記事 Mihoko Iijima · 4月 15, 2021 2m read

    これは InterSystems FAQ サイトの記事です。

    このエラーが発生する原因としては、アプリケーションの中で既に他のプロセスからロック対象リソースがロックされていて、何らかの理由でそのロックが解放されていないケースが考えられます。

    他のプロセスがロックしている兆候がない場合は、ロックテーブルの空き領域が不足しているケースが想定されます。
    その場合は、メッセージログ(コンソールログ)に LOCK TABLE FULL のメッセージが出力されます。

    トランザクション処理を行なっている場合には、ロック解放の延期が影響しているケースも考えられます。
    トランザクションとロック解放の延期については、以下のドキュメントをご参照下さい。

    ドランザクション処理について【IRIS】

    ドランザクション処理について

    また、トランザクション中に、同一テーブルに対する大量レコードのSQL 文による更新がある場合、ロックしきい値(既定値は1000)に到達してロックエスカレーションが発生し、その結果として、テーブルロック状態になっている可能性もあります。

    このように、ロックタイムアウトエラーの原因は幾つか考えられますので、まずは、管理ポータル(バージョン2010.2以前では、[システム管理ポータル])の、ロックメニューにて、現在のロックの状態をご確認下さい。

    0
    0 850
    記事 Tomoko Furuzono · 4月 13, 2021 1m read

    これは、InterSystems FAQサイトの記事です。

    SQLクエリーの文字列を監査ログに残すことができます。

    詳細は、以下のドキュメントをご参照下さい。

    システム監査イベントについて

    ※%SQLで始まるイベントをご覧ください。 

    InterSystems SQL の使用法

    ※監査関連ページをご覧ください。

    0
    0 230
    記事 Toshihiko Minamoto · 4月 12, 2021 24m read

    (1NF/2NF/3NF) からの引用

    行と列で特定される位置には、それぞれアプリケーションドメインの値が 1 つだけあります (それ以外は何もない)。 その目的によって、同じ値がアトミックであったり、なかったりします。 例えば、「4286」という値は、
    • 「クレジットカードの PIN コード」を意味するのであれば、アトミックとなります (破損している場合や並び替えられている場合は、使用できません)。
    • 単に「連続する番号」であれば、非アトミックとなります (いくつかに分割されていたり、並び替えられていても、値は意味を成します)。

    この記事では、文字列や日付、($LB 形式の) 単純なリスト、「list of <...>」、「array of <...>」といったフィールドの型を伴う SQL クエリのパフォーマンスを向上させる標準的な方法にして検証します。


    はじめに


    それでは、お馴染みの「電話番号の一覧」から見てみましょう。 以下のように、テストデータを作成します。

    create table cl_phones(tname varchar2(100), phone varchar2(30));
    insert into cl_phones(tname,phonevalues ('Andrew','867-843-25');
    insert into cl_phones(tname,phonevalues ('Andrew','830-044-35');
    insert into cl_phones(tname,phonevalues ('Andrew','530-055-35');
    insert into cl_phones(tname,phonevalues ('Max','530-055-35');
    insert into cl_phones(tname,phonevalues ('Max','555-011-35');
    insert into cl_phones(tname,phonevalues ('Josh','530-055-31');
    insert into cl_phones(tname,phonevalues ('Josh','531-051-32');
    insert into cl_phones(tname,phonevalues ('Josh','532-052-33');
    insert into cl_phones(tname,phonevalues ('Josh','533-053-35');

    では、各人物が持つ電話番号をコンマで区切ってまとめたリストを表示します。

    SELECT
       %exact(tnametname,
       LIST(phonephonestr
    FROM cl_phones
    GROUP BY tname

    または以下のコードを使います。

    SELECT
       distinct %exact(tnametname,
       LIST(phone %foreach(tname)) phonestr
    FROM cl_phones
    tnamephonestr
    結果
    Andrew 867-843-25,830-044-35,530-055-35
    Josh 530-055-31,531-051-32,532-052-33,533-053-35
    Max 530-055-35,555-011-35

    インデックスを電話番号別に作成すれば、特定の番号を使って素早く検索することができます。 このソリューションの唯一の欠点は、名前が重複する可能性があるということです。リストの要素が多くなれば、その分データベースも大きくなります。

    従い、同じフィールドに複数の値 (電話番号の一覧やその一部だけを集めた一覧、パスワードなど) をコンマで区切った文字列として格納しておくと、値別に素早く検索できるので、重宝することがあります。 もちろん、そのようなフィールドに対し普通のインデックスを作成し、長い文字列の中の部分文字列を検索することは可能です。しかし、第 1 にそのような要素が数多く含まれる可能性があり、またインデックスも非常に長くなり得るということ、そして第 2 に、そのようなインデックスを使用しても検索処理をまったく加速化できないという点を考慮する必要があります。

    そうでは、どうすれば良いのでしょう。

    このように、コレクションを持つフィールドを扱う状況に対処するために、インデックスの特殊な型が導入されています。 コレクションは「実在するもの」(組み込みの list of <...> および array of <...>) でも「仮想のもの」でも構いません。

    組み込みコレクションの場合、それ専用にこのようなインデックスを作成するのはシステムの仕事であり、開発者はそのプロセスに干渉できません。 但し、仮想コレクションであれば、インデックスの作成は開発者が担います。

    そのような「仮想」コレクションには、シンプルなコンマ区切りの文字列、日付、単純なリストなどがあります。

    コレクションのインデックスには、以下の構文を用います。

    INDEX idx1 ON (MyField(ELEMENTS));
    もしくは
    INDEX idx1 ON (MyField(KEYS));

    インデックスは、propertynameBuildValueArray メソッドを使って作成しますが、これは開発者が自分で実装しなければいけません。

    通常、このメソッドのシグネチャは以下のようになります。

    ClassMethod propertynameBuildValueArray(valueByRef valueArrayAs %Status

    引数の意味

    • value – 複数の要素に分割されるフィールド値
    • valueArray – 個別の要素を含む結果の配列。 配列は、キー / 値の組み合わせで、以下の形式が使われます。 array(key1)=value1 array(key2)=value2 などなど。

    先ほども触れましたが、このメソッドはシステムにより組み込みコレクション用に自動的に生成されるものであり、属性が [Final] であるため、開発者は再定義できません。

    それでは、これらのインデックスを作成し、SQL での活用方法を見てみましょう。

    注意:
     前の例で作られた構造が残らないよう、新しいインデックスを作成する前にグローバルやクラスのストレージスキーマを空にしておくことをおすすめします。

    コンマ区切りの文字列


    では、次のクラスを作成しましょう。

    Class demo.test Extends %Persistent
    {
    

    </FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">iPhones On Phones(ELEMENTS);

    </FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">Phones </FONT><FONT COLOR="#000080">As %String</FONT><FONT COLOR="#000000">;

    </FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">PhonesBuildValueArray(   </FONT><FONT COLOR="#ff00ff">value</FONT><FONT COLOR="#000000">,   </FONT><FONT COLOR="#000080">ByRef </FONT><FONT COLOR="#ff00ff">array</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">As %Status </FONT><FONT COLOR="#000000">{   </FONT><FONT COLOR="#0000ff">i </FONT><FONT COLOR="#800000">value</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"" </FONT><FONT COLOR="#800080">{     </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(0)=</FONT><FONT COLOR="#800000">value   </FONT><FONT COLOR="#800080">}</FONT><FONT COLOR="#0000ff">else</FONT><FONT COLOR="#800080">{     </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">list</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#0000ff">$lfs</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">value</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">","</FONT><FONT COLOR="#000000">),</FONT><FONT COLOR="#800000">ptr</FONT><FONT COLOR="#000000">=0     </FONT><FONT COLOR="#0000ff">while $listnext</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">list</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">ptr</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">item</FONT><FONT COLOR="#000000">)</FONT><FONT COLOR="#800080">{       </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">ptr</FONT><FONT COLOR="#000000">)=</FONT><FONT COLOR="#800000">item     </FONT><FONT COLOR="#800080">}   }   </FONT><FONT COLOR="#0000ff">q $$$OK </FONT><FONT COLOR="#000000">}

    </FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">Fill() {   </FONT><FONT COLOR="#0000ff">k </FONT><FONT COLOR="#000000">^demo.testD,^demo.testI</FONT>   <FONT COLOR="#800080">&sql(</FONT><FONT COLOR="#0000ff">insert </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#008000">demo</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">phones</FONT><FONT COLOR="#000000">)     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000080">null union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'a' </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'b,a' </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'b,b' </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'a,c,b' </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">',,'   </FONT><FONT COLOR="#800080">)   </FONT><FONT COLOR="#0000ff">zw </FONT><FONT COLOR="#000000">^demo.testD,^demo.testI } }</FONT>

    ターミナルで Fill() メソッドを呼び出します。

    USER><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">demo.test</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">Fill</FONT><FONT COLOR="#000000">()</FONT>
    <FONT COLOR="#000000">^demo.testD=6
    ^demo.testD(1)=</FONT><FONT COLOR="#0000ff">$lb</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">)
    ^demo.testD(2)=</FONT><FONT COLOR="#0000ff">$lb</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"a"</FONT><FONT COLOR="#000000">)
    ^demo.testD(3)=</FONT><FONT COLOR="#0000ff">$lb</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"b,a"</FONT><FONT COLOR="#000000">)
    ^demo.testD(4)=</FONT><FONT COLOR="#0000ff">$lb</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"b,b"</FONT><FONT COLOR="#000000">)
    ^demo.testD(5)=</FONT><FONT COLOR="#0000ff">$lb</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"a,c,b"</FONT><FONT COLOR="#000000">)
    ^demo.testD(6)=</FONT><FONT COLOR="#0000ff">$lb</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">",,"</FONT><FONT COLOR="#000000">)
    ^demo.testI(</FONT><FONT COLOR="#008000">"iPhones"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">" "</FONT><FONT COLOR="#000000">,1)=</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">
    ^demo.testI(</FONT><FONT COLOR="#008000">"iPhones"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">" "</FONT><FONT COLOR="#000000">,6)=</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">
    ^demo.testI(</FONT><FONT COLOR="#008000">"iPhones"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">" A"</FONT><FONT COLOR="#000000">,2)=</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">
    ^demo.testI(</FONT><FONT COLOR="#008000">"iPhones"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">" A"</FONT><FONT COLOR="#000000">,3)=</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">
    ^demo.testI(</FONT><FONT COLOR="#008000">"iPhones"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">" A"</FONT><FONT COLOR="#000000">,5)=</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">
    ^demo.testI(</FONT><FONT COLOR="#008000">"iPhones"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">" B"</FONT><FONT COLOR="#000000">,3)=</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">
    ^demo.testI(</FONT><FONT COLOR="#008000">"iPhones"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">" B"</FONT><FONT COLOR="#000000">,4)=</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">
    ^demo.testI(</FONT><FONT COLOR="#008000">"iPhones"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">" B"</FONT><FONT COLOR="#000000">,5)=</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">
    ^demo.testI(</FONT><FONT COLOR="#008000">"iPhones"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">" C"</FONT><FONT COLOR="#000000">,5)=</FONT><FONT COLOR="#008000">""</FONT>

    ご覧のとおり、インデックスには文字列全体ではなく、その特定の部分だけが含まれています。 従って開発者は好きなように長い文字列を複数の部分文字列に分割できます。 コンマ区切りの文字列以外にも、XML や JSON ファイルなども使えます。

    ID電話番号
    私たちのテーブルには以下が含まれます。
    1 (null)
    2 a
    3 b,a
    4 b,b
    5 a,c,b
    6 ,,

    それでは、「a」を含む部分文字列をすべて見つけましょう。 このためには、'%xxx%'['xxx' といった述語を使うことがルールになっています。

    select * from demo.test where Phones 'a'select * from demo.test where Phones like '%a%'

    しかし、この場合、iPhones インデックスは使用されません。 使用するには、特殊な述語を使う必要があります。

    FOR SOME %ELEMENT(ourfield) (%VALUE elementvalue)

    これを考慮して、以下のようなクエリを使います。

    select * from demo.test where for some %element(Phones) (%value 'a')

    特殊なインデックスを使ったおかげで、このクエリの処理速度は最初のバージョンを使った場合よりも大幅に速くなります。

    もちろん、以下のようにもっと複雑な条件を使うこともできます。(%Value %STARTSWITH 'а')(%Value 'a' and %Value 'b')(%Value in ('c','d'))(%Value is null)

    ここでさっと魔法をかけます。。。


    機密情報を隠す


    通常、BuildValueArray メソッドでは、value を使って array 配列を作成します。

    でも、このルールに従わなかったらどうなるのでしょう?

    次の例を試してみましょう。

    Class demo.test Extends %Persistent
    {
    

    </FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">iLogin On Login(ELEMENTS);

    </FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">Login </FONT><FONT COLOR="#000080">As %String</FONT><FONT COLOR="#000000">;

    </FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">LoginBuildValueArray(   </FONT><FONT COLOR="#ff00ff">value</FONT><FONT COLOR="#000000">,   </FONT><FONT COLOR="#000080">ByRef </FONT><FONT COLOR="#ff00ff">array</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">As %Status </FONT><FONT COLOR="#000000">{   </FONT><FONT COLOR="#0000ff">i </FONT><FONT COLOR="#800000">value</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"Jack" </FONT><FONT COLOR="#800080">{     </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(0)=</FONT><FONT COLOR="#008000">"test1"     </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(1)=</FONT><FONT COLOR="#008000">"test2"     </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(2)=</FONT><FONT COLOR="#008000">"test3"   </FONT><FONT COLOR="#800080">}</FONT><FONT COLOR="#0000ff">elseif </FONT><FONT COLOR="#800000">value</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"Pete" </FONT><FONT COLOR="#800080">{     </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"-"</FONT><FONT COLOR="#000000">)=</FONT><FONT COLOR="#008000">"111"     </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"5.4"</FONT><FONT COLOR="#000000">)=</FONT><FONT COLOR="#008000">"222"     </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"fg"</FONT><FONT COLOR="#000000">)=</FONT><FONT COLOR="#008000">"333"   </FONT><FONT COLOR="#800080">}</FONT><FONT COLOR="#0000ff">else</FONT><FONT COLOR="#800080">{     </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"key"</FONT><FONT COLOR="#000000">)=</FONT><FONT COLOR="#008000">"value"   </FONT><FONT COLOR="#800080">}   </FONT><FONT COLOR="#0000ff">q $$$OK </FONT><FONT COLOR="#000000">}

    </FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">Fill() {   </FONT><FONT COLOR="#0000ff">k </FONT><FONT COLOR="#000000">^demo.testD,^demo.testI</FONT><FONT COLOR="#800080">   &sql(</FONT><FONT COLOR="#0000ff">insert </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#008000">demo</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">login</FONT><FONT COLOR="#000000">)     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'Jack' </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'Jack' </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'Pete' </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'Pete' </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'John' </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'John'   </FONT><FONT COLOR="#800080">)   </FONT><FONT COLOR="#0000ff">zw </FONT><FONT COLOR="#000000">^demo.testD,^demo.testI } }</FONT>

    IDLogin
    配列を作成した後、テーブルの中身は以下のようになります。
    1Jack
    2Jack
    3Pete
    4Pete
    5John
    6John

    そして、肝心なのはここです! 次のクエリを実行してみましょう。

    select * from demo.test where for some %element(Login) (%value '111')
    IDLogin
    実行結果は以下のようになります。
    3Pete
    4Pete

    結果、一部のデータだけがテーブルに表示されています。また、インデックスでは非表示になったデータがいくつかありますが、それでも検索はできます。 これはどのように使えばいいのでしょう?

    例えば、特定のユーザーがアクセスできる一連のパスワードを、インデックスから (1 つだけではなく) すべて非表示にすることができます (通常 1 つだけ非表示にすることはできません)。 また、SQL を使って開かれては困るという機密情報があれば、それをすべて非表示にすることもできます。 もちろん、これは、GRANT column-privilege を実行するなど、他の方法でも行えます。 しかし、その場合は、protected フィールドにアクセスするためのストアドプロシージャを使う必要があります。


    機密情報を隠す(続き)


    私たちのテーブルのデータとインデックスを含むグローバルを見れば、「5.4」や「fg」といったキーの値が表示されていないのが分かります。

    ^demo.testD=6
    ^demo.testD(1)=$lb("","Jack")
    ^demo.testD(2)=$lb("","Jack")
    ^demo.testD(3)=$lb("","Pete")
    ^demo.testD(4)=$lb("","Pete")
    ^demo.testD(5)=$lb("","John")
    ^demo.testD(6)=$lb("","John")
    ^demo.testI("iLogin"," 111",3)=""
    ^demo.testI("iLogin"," 111",4)=""
    ^demo.testI("iLogin"," 222",3)=""
    ^demo.testI("iLogin"," 222",4)=""
    ^demo.testI("iLogin"," 333",3)=""
    ^demo.testI("iLogin"," 333",4)=""
    ^demo.testI("iLogin"," TEST1",1)=""
    ^demo.testI("iLogin"," TEST1",2)=""
    ^demo.testI("iLogin"," TEST2",1)=""
    ^demo.testI("iLogin"," TEST2",2)=""
    ^demo.testI("iLogin"," TEST3",1)=""
    ^demo.testI("iLogin"," TEST3",2)=""
    ^demo.testI("iLogin"," VALUE",5)=""
    ^demo.testI("iLogin"," VALUE",6)=""

    そもそも、なぜこれらを定義しているのか?

    その疑問に答えるには、インデックスを少し変更して、テーブルを作成しなおします。

    Index iLogin On (Login(KEYS), Login(ELEMENTS));

    これで、グローバルは見た目が変わります (インデックス付きのグローバルのみ表示しています)。

    ^demo.testI("iLogin"," -"," 111",3)=""
    ^demo.testI("iLogin"," -"," 111",4)=""
    ^demo.testI("iLogin"," 0"," TEST1",1)=""
    ^demo.testI("iLogin"," 0"," TEST1",2)=""
    ^demo.testI("iLogin"," 1"," TEST2",1)=""
    ^demo.testI("iLogin"," 1"," TEST2",2)=""
    ^demo.testI("iLogin"," 2"," TEST3",1)=""
    ^demo.testI("iLogin"," 2"," TEST3",2)=""
    ^demo.testI("iLogin"," 5.4"," 222",3)=""
    ^demo.testI("iLogin"," 5.4"," 222",4)=""
    ^demo.testI("iLogin"," FG"," 333",3)=""
    ^demo.testI("iLogin"," FG"," 333",4)=""
    ^demo.testI("iLogin"," KEY"," VALUE",5)=""
    ^demo.testI("iLogin"," KEY"," VALUE",6)=""

    これで、見事にキーの値と要素の値の両方が格納されています。 これは、今後どのように活用できるのでしょう?

    例えば、先ほどのパスワードを使った例では、パスワードをその有効期限や他の情報と一緒に保存することができます。 クエリでは、次のようにして行います。

    select * from demo.test where for some %element(Login) (%key='-' and %value '111')

    データをどこに保存するかは、あなた次第です。但し、キーは一意ですが、値はそうでないことを覚えておきましょう。

    また、「コレクション」インデックスは、普通のインデックスと同様、追加データを保存するのに使用できます。

    Index iLogin On (Login(KEYS), Login(ELEMENTS)) [ Data = (Login, Login(ELEMENTS)) ];

    この場合、上のクエリはデータにアクセスせずに、インデックスからすべてのデータを取得してくれるので、時間を節約できます。


    日付 (時刻など)


    日付はコレクションにどう関連しているのか、と疑問に思う方がいると思います。 答えはズバリ「ダイレクトに関連している」です。それは、日付や月、年だけによって検索する必要のあることが頻繁にあるためです。 通常の検索では効果はありません。ここで必要なのは、まさに「コレクションベース」の検索なのです。

    ここで、次の例を見てみましょう。

    Class demo.test Extends %Persistent
    {
    

    </FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">iBirthDay On (BirthDay(KEYS), BirthDay(ELEMENTS));

    </FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">BirthDay </FONT><FONT COLOR="#000080">As %Date</FONT><FONT COLOR="#000000">;

    </FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">BirthDayBuildValueArray(   </FONT><FONT COLOR="#ff00ff">value</FONT><FONT COLOR="#000000">,   </FONT><FONT COLOR="#000080">ByRef </FONT><FONT COLOR="#ff00ff">array</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">As %Status </FONT><FONT COLOR="#000000">{   </FONT><FONT COLOR="#0000ff">i </FONT><FONT COLOR="#800000">value</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"" </FONT><FONT COLOR="#800080">{     </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(0)=</FONT><FONT COLOR="#800000">value   </FONT><FONT COLOR="#800080">}</FONT><FONT COLOR="#0000ff">else</FONT><FONT COLOR="#800080">{     </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">d</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#0000ff">$zd</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">value</FONT><FONT COLOR="#000000">,3)     </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"yy"</FONT><FONT COLOR="#000000">)=+</FONT><FONT COLOR="#0000ff">$p</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">d</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"-"</FONT><FONT COLOR="#000000">,1)     </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"mm"</FONT><FONT COLOR="#000000">)=+</FONT><FONT COLOR="#0000ff">$p</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">d</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"-"</FONT><FONT COLOR="#000000">,2)     </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"dd"</FONT><FONT COLOR="#000000">)=+</FONT><FONT COLOR="#0000ff">$p</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">d</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"-"</FONT><FONT COLOR="#000000">,3)   </FONT><FONT COLOR="#800080">}   </FONT><FONT COLOR="#0000ff">q $$$OK </FONT><FONT COLOR="#000000">}

    </FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">Fill() {   </FONT><FONT COLOR="#0000ff">k </FONT><FONT COLOR="#000000">^demo.testD,^demo.testI</FONT><FONT COLOR="#800080">   &sql(</FONT><FONT COLOR="#0000ff">insert </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#008000">demo</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">birthday</FONT><FONT COLOR="#000000">)     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">{</FONT><FONT COLOR="#000080">d </FONT><FONT COLOR="#008080">'2000-01-01'</FONT><FONT COLOR="#000000">} </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">{</FONT><FONT COLOR="#000080">d </FONT><FONT COLOR="#008080">'2000-01-02'</FONT><FONT COLOR="#000000">} </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">{</FONT><FONT COLOR="#000080">d </FONT><FONT COLOR="#008080">'2000-02-01'</FONT><FONT COLOR="#000000">} </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">{</FONT><FONT COLOR="#000080">d </FONT><FONT COLOR="#008080">'2001-01-01'</FONT><FONT COLOR="#000000">} </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">{</FONT><FONT COLOR="#000080">d </FONT><FONT COLOR="#008080">'2001-01-02'</FONT><FONT COLOR="#000000">} </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">{</FONT><FONT COLOR="#000080">d </FONT><FONT COLOR="#008080">'2001-02-01'</FONT><FONT COLOR="#000000">}   </FONT><FONT COLOR="#800080">)   </FONT><FONT COLOR="#0000ff">zw </FONT><FONT COLOR="#000000">^demo.testD,^demo.testI } }</FONT>

    IDBirthDay
    配列を作成した後、テーブルの中身は以下のようになります。
    101.01.2000
    202.01.2000
    301.02.2000
    401.01.2001
    502.01.2001
    601.02.2001

    これで、簡単に、とてもスピーディに 日付の特定の部分を検索できます。 例えば、以下のようにすれば 2 月生まれの人を全員選択できます。

    select * from demo.test where for some %element(BirthDay) (%key='mm' and %value = 2)
    IDBirthDay
    結果
    301.02.2000
    601.02.2001

    単純なリスト


    Caché DBMS では、単純なリストを対象とした特殊なデータ型 (%List) が備えられています。どの区切り記号を使うかを決められなくて困っている開発者は、文字列の代わりに使えるので便利です。

    このフィールドの使い方は、文字列の使い方にとても似ています。

    それでは、簡単な例を見てみましょう。

    Class demo.test Extends %Persistent
    {
    

    </FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">iList On List(ELEMENTS);

    </FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">List </FONT><FONT COLOR="#000080">As %List</FONT><FONT COLOR="#000000">;

    </FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">ListBuildValueArray(   </FONT><FONT COLOR="#ff00ff">value</FONT><FONT COLOR="#000000">,   </FONT><FONT COLOR="#000080">ByRef </FONT><FONT COLOR="#ff00ff">array</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">As %Status </FONT><FONT COLOR="#000000">{   </FONT><FONT COLOR="#0000ff">i </FONT><FONT COLOR="#800000">value</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"" </FONT><FONT COLOR="#800080">{     </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(0)=</FONT><FONT COLOR="#800000">value   </FONT><FONT COLOR="#800080">}</FONT><FONT COLOR="#0000ff">else</FONT><FONT COLOR="#800080">{     </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">ptr</FONT><FONT COLOR="#000000">=0     </FONT><FONT COLOR="#0000ff">while $listnext</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">value</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">ptr</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">item</FONT><FONT COLOR="#000000">)</FONT><FONT COLOR="#800080">{       </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">ptr</FONT><FONT COLOR="#000000">)=</FONT><FONT COLOR="#800000">item     </FONT><FONT COLOR="#800080">}   }   </FONT><FONT COLOR="#0000ff">q $$$OK </FONT><FONT COLOR="#000000">}

    </FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">Fill() {   </FONT><FONT COLOR="#0000ff">k </FONT><FONT COLOR="#000000">^demo.testD,^demo.testI</FONT><FONT COLOR="#800080">   &sql(</FONT><FONT COLOR="#0000ff">insert </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#008000">demo</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">list</FONT><FONT COLOR="#000000">)     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000080">null union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#808000">$LISTBUILD</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'a'</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#808000">$LISTBUILD</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'b'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'a'</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#808000">$LISTBUILD</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'b'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'b'</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#808000">$LISTBUILD</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'a'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'c'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'b'</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#808000">$LISTBUILD</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'a,,'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#000080">null</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#000080">null</FONT><FONT COLOR="#000000">)   </FONT><FONT COLOR="#800080">)   </FONT><FONT COLOR="#0000ff">zw </FONT><FONT COLOR="#000000">^demo.testD,^demo.testI } }</FONT>

    IDList
    テーブルの中身は、ODBC のディスプレイモードで表示すると以下のようになります。
    1 (null)
    2 a
    3 b,a
    4 b,b
    5 a,c,b
    6 "a,,",,
    注意
     Caché では、論理、ODBC、ディスプレイモード (まとめてデータディスプレイオプション) の 3 種類のデータプレゼンテーションモードがサポートされています。

    ここでは、要素の区切り記号が使われていないため、要素の中では好きな文字を使うことができます。

    ODBC モードで %List 型のフィールドを表示するときは、ODBCDELIMITER パラメーターが区切り記号として使用されます (デフォルトで「,」と同じ)。

    例えば、そのようなフィールドを表示する場合

    Property List As %List(ODBCDELIMITER "^");
    IDList
    を実行すると、テーブルは以下のようになります。
    1 (null)
    2 a
    3 b^a
    4 b^b
    5 a^c^b
    6 a,,^^

    要素の検索は、コンマ区切りの文字列を検索する場合と同じです。

    select * from demo.test where for some %element(List) (%value 'a,,')
    IDList
    6 a,,^^

    %INLIST のオプションは、未だ「コレクション」インデックスを使用できないため、上に紹介した例よりも処理速度が遅くなります。

    select * from demo.test where 'a,,' %inlist List

    コレクション


    それでは、上の例を書き直しましょう。単純なリストの代わりにコレクションのリストを使います。

    Class demo.test Extends %Persistent
    {
    

    </FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">iListStr On ListStr(ELEMENTS);

    </FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">ListStr </FONT><FONT COLOR="#000080">As list Of %String</FONT><FONT COLOR="#000000">;

    </FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">Fill() {   </FONT><FONT COLOR="#0000ff">k </FONT><FONT COLOR="#000000">^demo.testD,^demo.testI</FONT><FONT COLOR="#800080">   &sql(</FONT><FONT COLOR="#0000ff">insert </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#008000">demo</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">liststr</FONT><FONT COLOR="#000000">)     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000080">null union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#808000">$LISTBUILD</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'a'</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#808000">$LISTBUILD</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'b'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'a'</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#808000">$LISTBUILD</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'b'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'b'</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#808000">$LISTBUILD</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'a'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'c'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'b'</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#808000">$LISTBUILD</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'a,,'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#000080">null</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#000080">null</FONT><FONT COLOR="#000000">)   </FONT><FONT COLOR="#800080">)   </FONT><FONT COLOR="#0000ff">zw </FONT><FONT COLOR="#000000">^demo.testD,^demo.testI } }</FONT>

    この例は、中身はほぼ同じですが、微妙に違う点がいくつかあります。 以下にご注意ください。

    • フィールドの COLLATION 値や array のキーおよびインデックス値は、グローバルに保存される前に適切な形に変換されます。 両方の例のグローバルインデックスの値、特に、NULL 値の表記を見比べてください。
    • BuildValueArray メソッドがないため、要素の値しか使えません (キーは使用できない)。
    • フィールドの型に特殊なコレクションクラス (%ListOfDataTypes) が使われている。

    配列コレクション


    上述のとおり、リストではキーを使用できませんが、 配列を使えば、この欠点を解決できます。

    では、次のクラスを作成しましょう。

    Class demo.test Extends %Persistent
    {
    

    </FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">iArrayStr On (ArrayStr(KEYS), ArrayStr(ELEMENTS));

    </FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">str </FONT><FONT COLOR="#000080">As %String</FONT><FONT COLOR="#000000">;

    </FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">ArrayStr </FONT><FONT COLOR="#000080">As array Of %String</FONT><FONT COLOR="#000000">;

    </FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">Fill() {   </FONT><FONT COLOR="#0000ff">k </FONT><FONT COLOR="#000000">^demo.testD,^demo.testI</FONT><FONT COLOR="#800080">   &sql(</FONT><FONT COLOR="#0000ff">insert </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#008000">demo</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">str</FONT><FONT COLOR="#000000">)   </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000080">null union all   </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'aaa' </FONT><FONT COLOR="#000080">union all   </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'bbb' </FONT><FONT COLOR="#000080">union all   </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'bbb' </FONT><FONT COLOR="#000080">union all   </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'ccc' </FONT><FONT COLOR="#000080">union all   </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000080">null   </FONT><FONT COLOR="#800080">)   &sql(</FONT><FONT COLOR="#0000ff">insert </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#008000">demo</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test_ArrayStr</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">test</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">element_key</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">arraystr</FONT><FONT COLOR="#000000">)     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">1,</FONT><FONT COLOR="#008080">'0'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'test1' </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">1,</FONT><FONT COLOR="#008080">'1'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'test2' </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">1,</FONT><FONT COLOR="#008080">'2'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'test3' </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">2,</FONT><FONT COLOR="#008080">'0'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'test1' </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">2,</FONT><FONT COLOR="#008080">'1'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'test2' </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">2,</FONT><FONT COLOR="#008080">'2'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'test3' </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">3,</FONT><FONT COLOR="#008080">'-'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'111' </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">3,</FONT><FONT COLOR="#008080">'5.4'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'222' </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">3,</FONT><FONT COLOR="#008080">'fg'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'333' </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">4,</FONT><FONT COLOR="#008080">'-'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'111' </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">4,</FONT><FONT COLOR="#008080">'5.4'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'222' </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">4,</FONT><FONT COLOR="#008080">'fg'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'333' </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">5,</FONT><FONT COLOR="#008080">'key'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'value' </FONT><FONT COLOR="#000080">union all     </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">6,</FONT><FONT COLOR="#008080">'key'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'value'   </FONT><FONT COLOR="#800080">)   </FONT><FONT COLOR="#0000ff">zw </FONT><FONT COLOR="#000000">^demo.testD,^demo.testI } }</FONT>

    説明が必要と思われる点を列挙しておきます。

    • データは、以前と同じように classD の ^name (データ) と classI の ^name (インデックス) という 2 つのグローバルに格納されています。
    • クラスは 1 つですが、テーブルは既に 2 つあります (お馴染みの demo.test と追加でもう 1 つ demo.test_ArrayStr)。
    • demo.test_ArrayStr では、SQL を使って簡単に配列データにアクセスできるほか、以下のフィールドが設けられています。それぞれの名前は一部事前定義されています。 element_key – キーの値 (事前定義されているフィールド名)。 ArrayStr – 要素の値。 test – 親テーブル demo.test へのリンク。 IDtest||element_key 形式のサービスプライマリキー (事前定義されているフィールド名)。
    • フィールドの型には特殊なコレクションクラス (%ArrayOfDataTypes) が使われています。

    以上を踏まえ、Fill() メソッドを実行すると、テーブルの中身は以下のとおりになります。

    IDstr
    demo.test テーブル
    1 (null)
    2 aaa
    3 bbb
    4 bbb
    5 ccc
    6 (null)
    IDtestelement_keyArrayStr
    demo.test_ArrayStr テーブル
    1||010test1
    1||111test2
    1||212test3
    2||020test1
    2||121test2
    2||222test3
    3||5.435.4222
    3||-3-111
    3||fg3fg333
    4||5.445.4222
    4||-4-111
    4||fg4fg333
    5||key5keyvalue
    6||key6keyvalue

    テーブルが 1 つから 2 になった結果、テーブル間で JOIN を使うことを余儀なくされているように思えますが、そうではありません。

    Caché DBMS が提供する SQL のオブジェクト拡張機能について考慮すると、

    demo.test テーブルの str フィールドにある文字列のうち、キーが「-」で要素の値が「111」であるものを表すテストクエリは、以下のようになります。

    <FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008000">test ID</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">test</FONT><FONT COLOR="#000000">-></FONT><FONT COLOR="#008000">str </FONT><FONT COLOR="#000080">from </FONT><FONT COLOR="#008000">demo</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test_ArrayStr </FONT><FONT COLOR="#000080">where </FONT><FONT COLOR="#008000">element_key</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008080">'-' </FONT><FONT COLOR="#000000">and </FONT><FONT COLOR="#008000">arraystr</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008080">'111'</FONT>
    もしくはこちら
    <FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008000">%ID</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008000">str </FONT><FONT COLOR="#000080">from </FONT><FONT COLOR="#008000">demo</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test </FONT><FONT COLOR="#000080">where </FONT><FONT COLOR="#008000">test_ArrayStr</FONT><FONT COLOR="#000000">-></FONT><FONT COLOR="#008000">element_key</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008080">'-' </FONT><FONT COLOR="#000000">and </FONT><FONT COLOR="#008000">test_ArrayStr</FONT><FONT COLOR="#000000">-></FONT><FONT COLOR="#008000">arraystr</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008080">'111'</FONT>
    IDstr
    結果
    3bbb
    4bbb

    ご覧のとおり、ここは至って単純で、JOIN も使われていません。これは、すべてのデータが 1 つのグローバルに格納され、Caché がこれらのテーブルの「関係」を把握しているためです。

    これらのフィールドを両方のテーブルから参照できるのは、まさにこれが理由です。 実際、demo.test テーブルに test_ArrayStr フィールドはありませんが、それを使って関連するテーブルにアクセスすることができます。


    結論


    この記事で説明したインデックス作成メカニズムは、一部のシステムクラスで広範に使用されています。それには、テキストストリームのインデックスを作成し、SQL による検索を可能にする %Stream.GlobalCharacterSearchable クラスなどが含まれます。 クラスコレクションのインデックス作成に関するトピックは、非常に多岐にわたるため、意図的に割愛しています (組み込み、格納、ストリーム、ユーザー定義、コレクションのコレクションなど)。 また、SQL による操作が不便な場合もあります。これを踏まえ、著者はそのようなコレクションが必要になることは、一部の極めて稀な状況を除き、ほぼないであろうと判断しました。 フルテキスト検索もまた別のトピックであり、SQL を使う場合には独自のインデックスと操作法を伴うものであるため、本記事ではカバーしておりません。 最後に、著者は、SqlListTypeSqlListDelimiter といったプロパティパラメーターの使用例も省いておりますが、好奇心旺盛の方は実際に試す方法を見い出していただけると思います。

    役に立つリンク


    これは、こちらの記事を英訳したものです。 [@Evgeny Shvarov]、翻訳作業にご協力いただきありがとうございました。

    この記事は、Habrahabr でもお読みいただけます。

    1
    0 170
    記事 Mihoko Iijima · 4月 9, 2021 1m read

    これは InterSystems FAQ サイトの記事です。

    INFORMATION_SCHEMA スキーマを使用して取得できます。

    INFORMATION_SCHEMA はシステム用スキーマのため、デフォルトでは管理ポータルのSQLメニューに表示されません。
    表示させる方法は以下の通りです。

    1. 管理ポータル→システムエクスプローラ→SQL メニューを開きます。
    2. スキーマのプルダウン左にある「システム」をチェックします。
    3. スキーマのプルダウンから INFORMATION_SCHEMA を選択します。

    指定のテーブル(ISJ.Class1)に対するID、フィールド名(COLUMN_NAME)、データタイプ(DATA_TYPE)、説明(DESCRIPTION)を取得するSQLは以下の通りです。

    SELECT ID,COLUMN_NAME,DATA_TYPE,DESCRIPTION
    FROM INFORMATION_SCHEMA.COLUMNS
    where TABLE_SCHEMA='ISJ' AND TABLE_NAME='Class1'
    0
    0 603