データベースのクエリ

データベースコネクタを使用してデータベース操作を設定する場合、データベース内で実行する SQL ステートメントに変数値を追加する方法がいくつかあります。

入力パラメータを使用したデータベースクエリの保護

RDBMS から情報を取得するには select 操作を使用します。その操作の主な概念は、SQL クエリを提供し、DataWeave を使用してパラメータを提供することです。

<flow name="selectParameterizedQuery">
  <db:select config-ref="dbConfig">
    <db:sql>SELECT * FROM PLANET WHERE name = :name</db:sql>
    <db:input-parameters>
      #[{'name' : payload}]
    </db:input-parameters>
  </db:select>
</flow>

上記の例のように、入力パラメータはキー - 値のペアで提供されます。このペアは、DataWeave スクリプトを埋め込むことで作成できます。このキーをセミコロン文字 (:) と組み合わせて、名前によりパラメータ値を参照します。クエリでパラメータを使用するには、この方法をお勧めします。

バージョン 1.4.0 以降 (セミコロンのエスケープ): SQL クエリでセミコロン文字を (:) を使用する必要がある場合、セミコロン文字の前にバックスラッシュを置くことでセミコロン文字をエスケープできます。これは、たとえば、PostgeSQL 種別のキャストを使用する場合に役立ちます。この場合、キャスト先の種別の前に 2 つのセミコロンが必要です。例: <db:sql>SELECT price\:\:float8 FROM PRODUCT</db:sql>

別の方法として、` <db:sql>SELECT * FROM PLANET WHERE name = #[payload] </db:sql>` を直接記述できますが、これは非常に危険なため、お勧めできません。

入力パラメータを使用して SELECT ステートメントの WHERE 句を設定する利点は次のとおりです。

  • クエリが SQL インジェクション攻撃の影響を受けなくなる。

  • コネクタが他の方法では不可能な最適化を実行でき、アプリケーションの全体的なパフォーマンスが向上する。

操作の入力と出力には DataSense を使用できます。クエリを分析し、SQL ステートメントの射影セクションを分析することで、コネクタはクエリの出力構造を自動的に計算します。同時に、WHERE 句の条件をテーブル構造と比較して、DataSense 入力も生成します。これは、入力パラメータを生成する DataWeave スクリプトを作成するのに役立ちます。

動的クエリ

場合によって、WHERE 句をパラメータ化するだけでなく、クエリ自体の一部をパラメータ化しなければならないことがあります。次のユースケースの例は、条件に応じてオンラインテーブルか履歴テーブルにアクセスする必要があるクエリ、またはプロジェクト列を変更する必要がある複雑なクエリです。

次の例では、テーブルが変数に依存することを記述する文字列 $(vars.table) を構築してクエリを生成するための完全な式を確認できます。重要なことは、一部のクエリテキストは動的な "SELECT * FROM $(vars.table) ですが、WHERE 句では、入力パラメータを使用して WHERE 条件を定義するベストプラクティスが引き続き使用されていることです (この例では WHERE name = :name)。

<set-variable variableName="table" value="PLANET"/>
<db:select config-ref="dbConfig">
    <db:sql>#["SELECT * FROM $(vars.table) WHERE name = :name"]</db:sql>
    <db:input-parameters>
        #[{'name' : payload}]
    </db:input-parameters>
</db:select>
Mule 3 では、選択の概念がパラメータ化クエリと動的クエリに分割されており、この両方を同時に使用できませんでした。動的クエリを使用するか、パラメータの使用から利点を得るか (SQL インジェクションの保護、PreparedStatement の最適化など) のどちらかを選択する必要がありました。また、構文がそれぞれ異なるため、同じことを行う 2 つの異なる方法を学ぶ必要がありました。ただし、Mule 4 のデータベースコネクタがあれば、クエリ内で式を使用してこの両方の方法を同時に使用できるようになりました。

上の例でなぜ動的クエリが必要なのですか? 単にテーブルを別の入力パラメータのように処理できますか? 答えはいいえです。入力パラメータは WHERE 句のパラメータにのみ適用できます。クエリの別の部分を変更するには、DataWeave の補間演算子を使用する必要があります。

大きな結果のストリーミング

データベーステーブルは増大する傾向があります。特にインテグレーションのユースケースを処理している場合、1 つのクエリで数万ものレコードが返されることがあります。この解決策としてストリーミングが優れています。ストリーミングとはどういう意味でしょうか? たとえば、10K 行を返すクエリがあるとします。この行を一度にフェッチすると、次のようになります。

  • ネットワークから大量の取り込みを行うため、パフォーマンスが低下する。

  • すべての情報を RAM に読み込む必要があるため、メモリ不足になるリスクがある。

ストリーミングとは、コネクタが 10K 行を一度にフェッチしないことを意味します。代わりに、コネクタは小さなチャンクをフェッチし、そのチャンクが消費されたら残りをフェッチします。これにより、ネットワークとメモリに対する懸念を軽減できます。

Mule 4 と Mule 3 のストリーミングの比較

Mule 3.x では、ストリーミングはデフォルトで無効になっているため、ストリーミングを明確に有効にする必要がありました。Mule 4 では、ストリーミングは透過的であり、常に有効になっています。ストリーミングについて特に気にする必要はありません。この機能があることを信じるだけです。

Mule 3 からのもう 1 つの改善点は、Mule 4 で新しい反復可能なストリームメカニズムが使用できるようになったことです。つまり、ストリームが反復可能になりました。DataWeave や他のコンポーネントで同じストリームを何度も処理できます。また、並列処理も可能です。反復可能なストリームについての詳細は、Mule 4.0 でのストリーミングを参照してください。

結果の制限

Mule Runtime を使用すると、コネクタでストリーミングを適切に処理できます。ただし、これは、データの大きなチャンクをデータベースから Mule に移動することをお勧めするものではありません。ストリーミングを使用しても、通常の SQL クエリで、各行に大量の情報を含む多くの行が返される可能性はあります。これにより、次の結果が生じます。

  • 大きなネットワークオーバーヘッド

  • データベースサーバの大量のリソース消費量

  • Mule Runtime の大容量メモリおよび要件

Select 操作では、これに役立つパラメータ (fetchSize および maxRows) が提供されます。

<db:select fetchSize="200" maxRows="1000" config-ref="dbConfig">
  <db:sql>select * from some_table</db:sql>
</db:select>

この例の指示では、コネクタは 1000 合計行を超える行を SQL SELECT クエリの結果としてフェッチしませんが、データベースからの結果をデータベースコネクタに一度に 200 件のみストリーミングします。このため、この例では、一致する 1,000 行を取得するのにネットワークを 5 回往復する必要があります。

この組み合わせにより、取得される情報の総量が制限され (maxRows 値)、ネットワーク経由でデータベースから小さいチャンクのデータが返されることが保証されます (fetchSize 値)。

fetchSize は JDBC ドライバへのヒントであり、常に適用する必要はありません。この動作は、JDBC ドライバのプロバイダによって異なります。多くの場合、JDBC ドライバのデフォルトの fetchSize10 です。

クエリタイムアウトの設定

クエリタイムアウトリファレンス

データベースクエリの実行に長い時間がかかる場合があります。多くの場合、次の要因によりクエリの実行が遅延します。

  • 非効率なクエリ (多くの行を反復処理する不適切なインデックスを持つクエリなど)

  • ビジー状態の RDBMS またはネットワーク

  • ロック競合

一般に、クエリにタイムアウトを設定することをお勧めします。タイムアウトを管理するには、queryTimeoutqueryTimeoutUnit を設定します。次の例は、Select 操作のタイムアウトの設定方法を示していますが、タイムアウトの設定はすべての操作でサポートされます。

<db:select queryTimeout="0" queryTimeoutUnit="SECONDS" config-ref="dbConfig">
   <db:sql>select * from some_table</db:sql>
</db:select>

Was this article helpful?

💙 Thanks for your feedback!

Edit on GitHub