データベースのベストプラクティスと役立つ知識
Altair RapidMinerとSQLデータベース
Altair AI StudioおよびAltair AI Hubは、JDBCドライバ標準を使用してリレーショナル(SQL)データベースにアクセスします。JDBCドライバを備えたデータベースシステムであれば、Altair RapidMinerで使用することができます。このページに記載されている情報は、明記しない限り、AI StudioとAI Hubについて言及しています。
他にも「NoSQL」データベースと呼ばれるデータベースタイプもあります。これらのデータベースの中にはAltair RapidMinerでサポートされているものもありますが、このページはこれらのデータベースには関係ありません。
目次
リレーショナルデータベースと標準SQL
標準SQL(Structured Query Language)は存在しますが、データベースシステム間には多くの違いがあります。データベースの切り替えは必ずしも容易ではありませんが、Altair RapidMinerは多くのオペレータに標準SQLのみを使用することで、違いを抽象化しようとしています。しかし、手書きのSQLクエリや特殊なデータ型、高度な機能では移植性に限界があります。
関心のある分野は以下の通りです。
- オブジェクトの命名規則 – データベースシステムによっては、オブジェクト(テーブル、列など)の名前を大文字または小文字に変換しますが、多くの場合、二重引用符で囲まれたオブジェクト名を受け入れ、大文字小文字を保持します。これはAltair RapidMinerがデータベースに書き込むSQLステートメントを作成する際などに行います。
- データ型 – データベースシステムによっては、特殊なデータ型(「VARCHAR2」など)の長い文字列のみをサポートするものもあれば、透過的にサポートするものもあります。大文字と小文字を区別せずに文字列を比較するものもあれば、完全一致を期待するものもあります。タイムゾーン情報の有無にかかわらずタイムスタンプをサポートするものもあります。XMLやJSONドキュメントをネイティブにサポートするものもあります。
- ソート – 数値のソートは明確に定義する必要がありますが、文字列のソートはそうではありません。多くのデータベースシステムは、文字列のソートにオペレーティングシステムを使用し、現在のロケール設定を使用します。また、データベース内部で指定できるソートルールをサポートし、テーブルや単一のクエリごとに異なるルールを指定することもできます。異なるデータベースが同じデータを同じようにソートするとは思わないでください。
- 関数 – これはデータベースシステム間で最も大きな違いがある部分です。標準SQLは多くの共通機能を提供していますが、多くのデータベースシステムは、より使いやすく、より高度な機能を実行できるを追加の関数を実装しています。標準以外の関数を使用すると、データベース間の移植性に影響します。標準以外の関数を使用すると、データベース間の移植性に影響します。
データベースへの接続
すべてのリポジトリまたはプロジェクトにもConnectionsというフォルダがあります。ユーザーはこのフォルダに接続オブジェクトを作成します。各接続オブジェクトには、設定情報とJDBCドライバ全体が含まれています。これにより、共通のJDBCドライバセットを維持する必要がなく、システム間で接続を移植することができます。
Altair RapidMinerには、AI Studioのインストールパスのlib/jdbcサブフォルダーに、無料で利用でき再配布可能なJDBCドライバが多数含まれています。追加のドライバをベンダーのウェブサイトからjarファイルとしてダウンロードし、接続を指定することもできます。ダウンロードしたJDBCドライバファイル用の共通フォルダを用意することをお勧めします。
組み込みデータベース
企業のIT部門などが提供する外部データベースサーバーにアクセスできないユーザーは、Altair RapidMiner内で独自のデータベースを実行することができます。これらのシステムのうち2つはAI Studioのデフォルトインストールで利用可能です。その他のシステムはダウンロードしてAI Studioで使用できます。
| データベース | 取得方法 | 備考 |
|---|---|---|
| HyperSQL (HSQLDB) | AI Studioに含まれています。HSQLDBタイプで新規接続を追加します。 | ユーザー名: SA
カスタム接続文字列: jdbc:hsqldb:file:/path/to/file |
| H2 | AI Studioインストールパス内の「lib」にあるJDBCドライバ。カスタムデータベース接続を追加し、ファイルブラウザでドライバを選択します。 | カスタム接続文字列:jdbc:h2:file:/path/to/file |
| SQLite | ドライバJARファイルをダウンロードし、カスタムデータベース接続を追加します。 | カスタム接続文字列:jdbc:sqlite:/path/to/file |
データベースへの初回接続時に、データベースファイルが自動的に作成されます。データは1つまたはいくつかのファイルに保存され、他のユーザーと自由に共有することができます。
チーム用のデータベースサーバーを探していて、そのようなサービスを維持するリソースがある場合、PostgreSQLは分析用の高度な機能を備え、サポートが充実したオープンソースデータベースとして、無料の優れた選択です。
データベースアクセスのためのオペレータ
Read Database
Read Databaseオペレータはデータベースに接続してSQLクエリを実行し、表形式のデータを返します。SQLクエリはAI Studioで対話的に指定することも、ファイルから読み込むことも、テーブル名を指定してAltair RapidMinerで作成することもできます(データベースシステムによっては、ビュー、マテリアライズドビュー、その他の表形式のオブジェクトタイプも、この文脈では「テーブル」または「リレーション」です)。
Build SQL Queryダイアログには、選択したデータベースのテーブルとビュー、および選択したオブジェクトの属性が一覧表示されるため、SQLクエリの作成が容易になります。
SQLインジェクションは悪意のある人々にソフトウェアを攻撃される要因の1つです。Altair RapidMinerプロジェクトに対して頻繁に起こる脅威ではありませんが、このような問題はプロセスを混乱させ、データ処理に問題を引き起こす可能性があります。
次のクエリはSQLインジェクションの可能性を示しています。
SELECT * FROM Customers WHERE Lastname = '<query parameter>'
これは、指定された姓を持つ顧客を検索します。クエリパラメータはデータベースに送信されるステートメントに直接挿入されます。悪意がなくても、O’Connorという顧客(または一重引用符を含むその他の姓)を検索するだけで、クエリパラメータを使用してこのSQLステートメントを呼び出すと構文エラーになります。
これを避けるのは簡単です。Read Databaseにはprepare statementというパラメータがあります(表示されない場合はShow advanced parametersを有効にしてください)。これによりパラメータダイアログが表示され、パラメータを追加しそのタイプを指定することができます。SQLステートメントにパラメータを渡すこの方法は、データベースでの予期しない構文エラーを回避し、予期しない形式(数値ではなく文字列など)のデータの問題を特定するのに役立ちます。
準備されたステートメントでは、クエリは以下のように記述されます。
SELECT * FROM Customers WHERE Lastname = ?
クエスチョンマークはダイアログに入力された最初のパラメータを指し、2番目は2番目のパラメータを指します。
Read Databaseには、他のデータベースオペレータと同様に、オプションの「con」(接続)入力と出力があります。複数のデータベースオペレータを使用するプロセスの中には、最初のステップで接続エントリを取得し、それをデータベースオペレータ間で受け渡し共有することで簡素化できるものがあります。これは、外部から接続を渡したりパラメータを使用したりすることで、異なるデータベース環境(開発環境、テスト環境、本番環境など)で動作するプロセスを作成する良い方法でもあります。
Write Database
Write Databaseオペレータは、表形式のデータを取得してデータベースに保存します。テーブルスキーマとテーブル名を指定することができます。
overwrite modeパラメータの正しい設定を考慮することが重要です。デフォルトでは、オペレータは必要に応じてターゲットテーブルを作成し、入力データに合わせてその構造を変更します。これは、データサイエンティストが一人でデータベースを操作する場合に想定される動作ですが、他のシナリオでは決して理想的ではありません。多くの場合、データベースの構造は固定されており、Altair RapidMinerのユーザーが変更することはできません。
このような状況では、追記が正しいoverwrite modeです。書き込む前にテーブルを空にする必要がある場合、Write Databaseの前にDELETE FROM tablenameなどのステートメントでExecute SQLを使用できます。
多くのデータベーステーブルでは、新規レコードに対してデータベースシステムが生成する数値ID値などの生成された主キーを使用しています。生成された主キーの追加パラメータは、挿入操作の後にこれらの値を取得し、テーブルに追加します。
データベースの種類によっては、Altair RapidMinerによってテーブル構造が作成または更新される場合に、デフォルトのvarcharの長さを設定する必要があります。デフォルトでは、Altair RapidMinerはデータベースシステムにそのようなデータ型が存在する場合、長い長さのテキストデータ型を使用しようとします。
プロセス開発の初期段階で、データベースにフルアクセスできる場合、入力データと一致する構造を持つテーブルを常に再作成する標準的な動作が役立ちます。その後、テーブル構造を凍結し、より高速なクエリのためにインデックスを追加し、追記上書きモードを使用した方が良い場合があります。
バッチサイズパラメータを使用すると、Write Databaseの書き込み速度を向上させることができます。Altair RapidMinerは1つのステップで挿入するレコードをバッチサイズにグループ化し、1つのトランザクションでまとめて送信します。エンタープライズデータベースでは、デフォルト設定の100よりもはるかに高い設定値(500や1000など)でデータを高速に書き込む可能性があります。
Update Database
Update Databaseオペレータはデータテーブルを取得し、入力行に一致するデータをデータベースから見つけ、更新または追加します。
更新する行を特定するためにすべての属性値を使用するというデフォルト設定は、ほとんどの場合適切ではありません。通常は、ID属性(属性フィルタタイプ = 単一)か、属性の組み合わせを選択することになります。
ルックアップは行ごとに行われます。これはパフォーマンスに大きな影響を与えます。
大きなデータベーステーブルでは、テーブルに適切なインデックスを付けることが重要です。Write Databaseで作成されたテーブルには通常インデックスがないため、使用しているデータベースシステムのドキュメントを参照して、インデックスを追加することをお勧めします。
既存のインデックスがAltair RapidMinerで使用されず、アクセスに時間がかかる場合があります。その理由は、Altair RapidMinerがID列を浮動小数点数値として識別しているにもかかわらず、インデックスが整数値であるためです。データベースシステムによっては、これらの表現が自動的に変換されず、基準に一致するインデックスが見つからないため、フルテーブルスキャンに戻ってしまうことがあります。Altair RapidMinerでは、データ型を明示的にIntegerに変更するか、データベースで一致するインデックスを作成することで解決できます。
Update Databaseが使用する行ごとのアクセスパターンでは、ネットワーク遅延が重要なトピックになります。異なるコンピュータ間のレイテンシ(待ち時間)は、リクエストを含むネットワークパケットとリクエストに対するリプライ間の往復時間です。ローカルネットワークではレイテンシは 1 ミリ秒未満ですが、インターネットやクラウドサービスでは数百ミリ秒に達することもあります。
つまり、Update Databaseでは、データベースのロケーションが変わっただけで、同じプロセスに長い時間がかかる可能性があります。レイテンシに応じて、異なるネットワークロケーション間では、1秒間に数回から30回程度の操作しかできませんが、ローカルネットワークでは 1 秒あたり数千行で同じアクションを実行できます。もちろん、データベースとAltair RapidMinerのロケーションを自由に選べるとは限りませんが、これは注意すべき点です。
Read DatabaseとWrite Databaseは、Write Databaseのバッチサイズパラメータが低すぎない限り、1対1のアクセスパターンを使用しないため、Update Databaseほどレイテンシの影響を強く受けません。
もしUpdate Databaseのパフォーマンスに問題があり、その理由がレイテンシであると判明している場合、考えられる解決策は、Write Databaseを使用し、データを一時テーブルに入れ、SQLステートメントを使用してターゲットテーブルに新規データを挿入し、既存データを更新する方法です。もちろん、これにはある程度のSQLの知識が必要ですが、多くの場合、エンタープライズ設定では利用可能です。
Execute SQL
Execute SQLオペレータは、任意のSQLステートメントをデータベースに送信するために使用できます。他のスクリプトオペレータと同様、SQLと現在のデータベースで使用されている方言についての知識が必要です。
対象となるデータベースがサポートしていれば、複数のステートメントを一度にまとめ送信することができます。ステートメントはセミコロンで区切る必要があります。これが機能しない場合は、データベースシステムのドキュメントを参照してください。
Execute SQLの使用例としては、テーブルやインデックスの作成、テーブルからデータを削除してから再入力すること、一括更新、その他経験豊富なユーザーがデータベースで行うことができるその他の操作が挙げられます。
Read Databaseと同様、Execute SQLもprepareステートメントでのパラメータ送信をサポートしています。SQLインジェクションを回避することは、データを読み込むときと同様に、SQLを実行するときにも重要です。
In-Database Processingエクステンション
リレーショナルデータベースからの複雑なクエリのために、Altair RapidMinerマーケットプレイスからIn-Database Processingエクステンションを入手することができます。
その目的は、Altair RapidMinerのコンセプトとオペレータを使用して、データベース内のクエリと読み書きパイプラインをグラフィカルにモデリングすることです。基本的には、Altair RapidMinerをデータベースクエリビルダーに拡張します。
基本オペレータはIn Database Nestです。ネスト内では、エクステンションに属する他のオペレータを自由に配置することができます。一般的な方法として、以下のオペレータがあります。
- データベースのテーブルにアクセスするためのRetrieve (In-Database)
- 必要な列だけを選択するためのSelect Attributes (In-Database)
- クエリに条件を追加するためのFilter Examples (In-Database)
- などがあります。結合、置換、属性生成、その他のオペレータも利用可能です。
In Database Nest内のオペレータは直接実行されません。その配置とパラメータは、1つまたは複数のSQLステートメントを生成するために使用され、直接データベースに送信されます。Altair RapidMinerはこのSQLクエリの結果を読み込みます。これにより、Altair RapidMinerの使用経験はあるがSQLを知らないユーザーでも、複雑なクエリを作成し、データベースで効率的に実行することができます。Altair RapidMinerですべてのデータを読み込んで処理(フィルタリング/生成/結合)するよりもはるかに効率的です。
このエクステンションでは、いくつかのデータベースシステムのサポートが拡張され、Generate Attributes (In-Database)にその関数の完全なリストが含まれています。その他のデータベースシステムは標準SQL機能でサポートされています。
ユーザは生成されたSQL文を見たいと思うことがあります。これは生成されたデータセットの注釈として利用できます。結果画面には注釈タブがあり、そこでステートメントを見つけてコピーすることができます。
Altair RapidMinerにおけるデータベースの概念とその応用
トランザクション
リレーショナルデータベースでは、トランザクションはデータベース操作をグループ化し、それらの操作のいずれも成功しないか、すべてが成功しないようにします。トランザクションの必要性を示す典型的な例は銀行です。口座Aからお金を引き、口座Bに追加する処理を考えてみましょう。もし最初の操作だけが成功すれば、誰かがお金を失い、そのまま消えてしまいます。
SQLでは、トランザクションはBEGINキーワードで開始されます。その後、任意の数の他のSQLステートメントを実行できます。それらはCOMMITでデータバンクに永続化されます。トランザクション内で何かが壊れた場合、トランザクションは自動的に終了するか、ROLLBACKでキャンセルすることができます。
Altair RapidMinerはExecute SQLでトランザクションをサポートしています。ユーザーはトランザクションの開始時にBEGIN;、終了時にCOMMIT;と記述するだけです。
Altair RapidMinerのオペレータは別々に、かつ順番に実行されます。これには、プロセス内の各オペレータで個別にデータベース接続を開くことも含まれます。そのため、残念ながら、Execute SQLを使用してトランザクションを開始し、Read DatabaseやWrite Databaseなどの操作を行い、最後にトランザクションをコミットすることはできません。
インデックス
前述したように、インデックスはデータベースのクエリを高速化する優れた方法です。インデックスとは、データベース内の特別な構造のことで、テーブル全体を読むよりも速く情報を見つけることができます。
ほとんどのデータベースシステムはインデックスをサポートしており、使用するケースによって異なるタイプのインデックスもあります。詳細については、データベースシステムのドキュメントを参照してください。
ほとんどのデータベースでは、以下のコマンドを実行すると、デフォルト設定でテーブル列にインデックスが作成されます
CREATE INDEX ix_table_column ON tablename(columnname);
インデックスを作成することを推奨する基準はいくつかありますが、タイミングに対する明確な答えはありません。
- 列が結合またはフィルター (WHERE 条件) で使用される。
- 列がデータベースの更新で使用される(つまり、フィルターで繰り返し使用される)。
- 列の値が1つか2つではなく、さまざまな値が適切に分散される。テーブルに対する選択データの割合が小さいほど、インデックスによる速度向上が大きくなる。
- テーブルが大きい。
- クエリが頻繁に実行される。
実際に必要ないのに、大量のインデックスを作成するのはやてください。ディスクスペースを占有し、データの読み書きが遅くなるだけです。
Altair RapidMinerはクエリのパフォーマンスが向上するとデータベースが判断した場合、自動的にクエリにインデックスを使用します。ほとんどのデータベースシステムでは、インデックスの使用を強制することはできませんし、推奨されません。
セキュリティ
ほとんどの企業データベースシステムは、ユーザー認証、ユーザーグループまたはロ ール、データベースオブジェクトに対するきめ細かなアクセス制御をサポートしています。
多くの場合、そのようなデータベースには、アナリスト用の共通の読み取り専用ユーザーアカウントがあります。しかし他の状況では、データベースは企業認証システムと統合されており、ユーザーは独自のユーザーアカウントで自分自身を認証する必要があります。Altair RapidMinerは、データベース接続を作成する際に入力する挿入パラメータによって、この状況をサポートします。
データベースのセキュリティに関する従来のルールは、データベースシステムへのアクセスを可能な限り制限することです。多くの企業データベースはこの方法でロックダウンされており、特定のネットワークからの接続しか受け入れていません。例えば、ユーザーはオフィスにいる間はデータベースにアクセスできるが、自宅からはアクセスできない場合があります。
つまり、同じデータベース接続でも、ノートパソコンがオフィスのネットワークにあるときは使えるが、そうでないときは使えないということです。このような状況では、AI Hub上でプロセスを実行し、その結果を使って作業すると便利です。
JDBC接続パラメータ
JDBCドライバは、データベース接続の動作を最適化するためのパラメータを提供する場合があります。
例えば、PostgreSQLでは以下のオプションが設定できます。
- stringtype(デフォルト: VARCHAR):「undefined」に設定することを推奨します。RMがWrite DatabaseやUpdate Databaseなどで行っているprepared ステートメントを使用する場合、stringtype=VARCHARに設定すると、多くのデータ型がデータベースによって文字列として解釈されます。これをstringtype=unspecifiedに変更すると、異なるが互換性のあるデータ型を持つデータをデータベースに書き込むことができます。例えば、サンプルセットに文字列として表現された日付やtrue/falseのブール値がある場合、これらはデータベースによって実際の列型に変換されますが、stringtype=unspecifiedの場合のみです。
- defaultRowFetchSize(デフォルト: 0): デフォルトでは、Postgres JDBCドライバはクエリ結果全体を一度に読み込もうとします。これはAltair RapidMinerの動作と一致しますが、100000(一度に読み込む行数)などの値を設定することで、場合によってはメモリ使用量を削減できるかもしれません。(Altair RapidMinerではない)順次読み込みを行うプログラムでは、この設定を適切な数値に変更することでメモリを大幅に節約できる可能性があります。
- currentSchema: PostgreSQLには検索パスという概念があります(オペレーティングシステムのコマンドインタプリタのようなものです)。例えば、スキーマ “public “とスキーマ$username(存在する場合)は自動的に検索パスにあります。スキーマを指定せずに特定のスキーマ(データマイニングなど)のオブジェクトを参照したい場合は、このパラメータにスキーマ名を設定します。
注意：(スキーマを指定せずに)テーブルだけを参照するクエリは、他のシステムで同じ変更を行った場合にのみ移植可能になります。 - ApplicationName: RapidMiner/MyProjectのように設定するのが望ましいです。データベース管理者はアプリケーション名をツールで確認することで、誰が/どのデータベースを使用しているかを知ることができ便利です。
JDBC接続パラメータはデータベースに大きく依存します。これらの設定が他のデータベースでも機能するとは思わないでください。詳細はJDBCドライバのドキュメントを参照してください。
