実行時に SQL を構築する

Snowflakeは、実行時に動的に SQL ステートメントの文字列を構築するためのいくつかの異なる技術をサポートしています。これらのテクニックを使うことで、 SQL ステートメントの全文が実行時までわからないようなユースケースに対して、より一般的で柔軟な SQL 文字列を指定することができます。

ストアドプロシージャやアプリケーションは、ユーザー入力を受け付け、その入力を SQL ステートメントで使用できます。例えば、あるテーブルには販売注文に関する情報が格納されているかもしれません。アプリケーションやストアドプロシージャは、注文 ID を入力として受け取り、その特定の注文の結果のみを返すクエリを実行するかもしれません。

開発者は、プレースホルダーを含む SQL ステートメントでストアドプロシージャコードやアプリケーションコードを記述し、コード内のプレースホルダーに変数をバインドすることができます。これらのプレースホルダーは バインド変数 と呼ばれます。開発者は、入力文字列から SQL ステートメントを構築するコードを書くこともできます（たとえば、 SQL コマンド、パラメータ、値を含む文字列を連結します）。

実行時に動的に SQL ステートメントを構築するために、以下のテクニックが利用できます。

  • TO_QUERY 関数 - この関数は、入力として SQL 文字列とオプションのパラメータを受け取ります。

  • 動的 SQL - ストアドプロシージャやアプリケーションのコードが入力を受け取り、その入力を使って動的 SQL ステートメントを構築します。コードは、 Snowflake Scripting または Javascript ストアドプロシージャ、またはSnowflake Scripting匿名ブロックの一部にすることができます。このテクニックは、 Snowflakeドライバー または Snowflake SQL REST API を使用するアプリケーションコードでも使用できます。

注釈

プログラムがユーザー入力で SQL ステートメントを構築する場合、 SQL インジェクションなどの潜在的なセキュリティリスクが存在します。SQL ステートメントへのインプットが外部ソースからのものである場合、それらが検証済みであることを確認します。詳細については、 SQL インジェクション をご参照ください。

TO_QUERY 関数の使用

ストアドプロシージャや、 SQL ステートメントを動的に作成するアプリケーションのコードでは、 TO_QUERY 関数を使用できます。このテーブル関数は、 SQL の文字列を入力として受け取ります。オプションで、 SQL の文字列にパラメータを含めることができ、パラメータに渡す引数をバインド変数として指定することができます。

以下は、この関数を呼び出す簡単な例です。

SELECT COUNT(*) FROM TABLE(TO_QUERY('SELECT 1'));
Copy
+----------+
| COUNT(*) |
|----------|
|        1 |
+----------+

以下の例では、ストアドプロシージャで TO_QUERY 関数を使用しています。

CREATE OR REPLACE PROCEDURE get_num_results_tq(query VARCHAR)
RETURNS TABLE ()
LANGUAGE SQL
AS
DECLARE
  res RESULTSET DEFAULT (SELECT COUNT(*) FROM TABLE(TO_QUERY(:query)));
BEGIN
  RETURN TABLE(res);
END;
Copy

注: SnowSQL、 Classic Console、 execute_stream または execute_string メソッドを Python Connector コードで使用している場合は、代わりにこの例を使用してください（SnowSQL、 Classic Console、Python ConnectorでSnowflakeスクリプトを使用する を参照）。

CREATE OR REPLACE PROCEDURE get_num_results_tq(query VARCHAR)
RETURNS TABLE ()
LANGUAGE SQL
AS
$$
DECLARE
  res RESULTSET DEFAULT (SELECT COUNT(*) FROM TABLE(TO_QUERY(:query)));
BEGIN
  RETURN TABLE(res);
END;
$$
;
Copy

ストアドプロシージャを呼び出します。

CALL get_num_results_tq('SELECT 1');
Copy
+----------+
| COUNT(*) |
|----------|
|        1 |
+----------+

ストアドプロシージャとアプリケーションで動的 SQL を使用する

ユーザー入力を受け付ける SQL ステートメントを作成するには、 Snowflake Scripting または Javascript ストアドプロシージャ内、またはSnowflake Scripting匿名ブロック内で動的 SQL を使用します。また、 Snowflakeドライバー または Snowflake SQL REST API を使用するアプリケーションコードで、動的 SQL を使用することもできます。

この例では、Snowflake Scriptingを使用してストアドプロシージャを作成します。このストアドプロシージャは、 SQL テキストを入力として受け取り、そのテキストを追加して SQL ステートメントを含む文字列を構築します。その後、 EXECUTE IMMEDIATE コマンドを使って動的 SQL を実行します。

CREATE OR REPLACE PROCEDURE get_num_results(query VARCHAR)
RETURNS INTEGER
LANGUAGE SQL
AS
DECLARE
  row_count INTEGER DEFAULT 0;
  stmt VARCHAR DEFAULT 'SELECT COUNT(*) FROM (' || query || ')';
  res RESULTSET DEFAULT (EXECUTE IMMEDIATE :stmt);
  cur CURSOR FOR res;
BEGIN
  OPEN cur;
  FETCH cur INTO row_count;
  RETURN row_count;
END;
Copy

注: SnowSQL、 Classic Console、 execute_stream または execute_string メソッドを Python Connector コードで使用している場合は、代わりにこの例を使用してください（SnowSQL、 Classic Console、Python ConnectorでSnowflakeスクリプトを使用する を参照）。

CREATE OR REPLACE PROCEDURE get_num_results(query VARCHAR)
RETURNS INTEGER
LANGUAGE SQL
AS
$$
DECLARE
  row_count INTEGER DEFAULT 0;
  stmt VARCHAR DEFAULT 'SELECT COUNT(*) FROM (' || query || ')';
  res RESULTSET DEFAULT (EXECUTE IMMEDIATE :stmt);
  cur CURSOR FOR res;
BEGIN
  OPEN cur;
  FETCH cur INTO row_count;
  RETURN row_count;
END;
$$
;
Copy

次の例では、プロシージャを呼び出します。

CALL get_num_results('SELECT 1');
Copy
+-----------------+
| GET_NUM_RESULTS |
|-----------------|
|               1 |
+-----------------+

動的 SQL はバインド変数をサポートしています。次のSnowflake Scriptingの例では、 ? プレースホルダで表されるバインド変数を使用して、 SQL ステートメントを実行時に動的に作成します。このブロックは、以下の invoices テーブルからデータを選択します。

CREATE OR REPLACE TABLE invoices (price NUMBER(12, 2));
INSERT INTO invoices (price) VALUES
  (11.11),
  (22.22);
Copy

匿名ブロックを実行します。

DECLARE
  rs RESULTSET;
  query VARCHAR DEFAULT 'SELECT * FROM invoices WHERE price > ? AND price < ?';
  minimum_price NUMBER(12,2) DEFAULT 20.00;
  maximum_price NUMBER(12,2) DEFAULT 30.00;
BEGIN
  rs := (EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price));
  RETURN TABLE(rs);
END;
Copy

注: SnowSQL、 Classic Console、 execute_stream または execute_string メソッドを Python Connector コードで使用している場合は、代わりにこの例を使用してください（SnowSQL、 Classic Console、Python ConnectorでSnowflakeスクリプトを使用する を参照）。

EXECUTE IMMEDIATE $$
DECLARE
  rs RESULTSET;
  query VARCHAR DEFAULT 'SELECT * FROM invoices WHERE price > ? AND price < ?';
  minimum_price NUMBER(12,2) DEFAULT 20.00;
  maximum_price NUMBER(12,2) DEFAULT 30.00;
BEGIN
  rs := (EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price));
  RETURN TABLE(rs);
END;
$$
;
Copy
+-------+
| PRICE |
|-------|
| 22.22 |
+-------+

SQL を動的に構築するテクニックの比較

次のテーブルは、 SQL を動的に構築するテクニックの長所と短所を説明したものです。

テクニック

メリット

デメリット

TO_QUERY 関数

  • 簡単な構文

  • 組み込みのエラー処理

  • SQL を動的に構築するユースケースに特化したセマンティクス

  • 自動的に決定される結果セット

  • 実行前にクエリの説明や解説はできない

  • SELECT ステートメントの FROM 句でのみ有効

  • Snowflake固有

動的 SQL

  • TO_QUERY 関数よりも一般的で柔軟性がある

  • クエリは実行前に説明や解説することができる

  • TO_QUERY 関数より複雑

  • 手動エラー処理
言語: 日本語