バインド変数¶

バインド変数の概要¶

バインド変数を使うと、SQL ステートメントのリテラルをプレースホルダーに置き換えることができます。たとえば、次の SQL ステートメントでは、挿入値にリテラルを使用しています。

INSERT INTO t (c1, c2) VALUES (1, 'Test string');

次の SQL ステートメントでは、挿入値にプレースホルダーを使用しています。

INSERT INTO t (c1, c2) VALUES (?, ?);

アプリケーションコードは、SQL ステートメント内の各プレースホルダーとデータをバインドします。プレースホルダーでデータをバインドする手法は、プログラミング言語によって異なります。プレースホルダーの構文もプログラミング言語によって異なります。?、 :varname、または %varname のどちらかになります。

Javascriptストアドプロシージャでバインド変数を使用する¶

Javascript を使用して、SQL を実行するストアドプロシージャを作成できます。

Javascriptコードでバインド変数を指定するには、 ? プレースホルダーを使用します。たとえば、次の INSERT ステートメントは、テーブル行に挿入される値のバインド変数を指定します。

INSERT INTO t (col1, col2) VALUES (?, ?)

Javascriptコードでは、大半の SQL ステートメントの値にバインド変数を使うことができます。制限に関する情報については、 バインド変数の制限事項 をご参照ください。

Javascriptでのバインド変数の使用に関する詳細情報については、 変数のバインド をご参照ください。

Snowflakeスクリプトでバインド変数を使用する¶

Snowflake Scripting を使用して、コードブロックやストアドプロシージャなどの SQL を実行する手続き型コードを作成できます。Snowflake Scriptingコードでバインド変数を指定するには、変数名のプレフィックスにコロンを付けます。たとえば、次の INSERT ステートメントは variable1 という名前のバインド変数を指定します。

INSERT INTO t (c1) VALUES (:variable1)

EXECUTE IMMEDIATE コマンドまたは :ref:`カーソルの OPEN コマンド <label-snowscript_cursors_open>`で SQL を実行すると、USING 句で変数をバインドできます。

この例では、EXECUTE IMMEDIATE コマンドの変数を USING 句でバインドしています。

EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price);

このコードを含む完全な例については、 バインド変数を含むステートメントの実行 をご覧ください。

カーソルを宣言するときに、バインドパラメーター（? 文字）をSELECTステートメントに指定できます。これらのパラメーターは、カーソルを開くときにUSING句で変数にバインドできます。

次の例では、カーソルを宣言し、バインドパラメーターを指定してから、USING 句でカーソルを開いています。

LET c1 CURSOR FOR SELECT id FROM invoices WHERE price > ? AND price < ?;
OPEN c1 USING (minimum_price, maximum_price);

Snowflake Scriptingでは、バインド変数の位置による番号付けや、SQL ステートメント内でのバインド変数の再利用もサポートしています。番号付きバインド変数の場合、各変数宣言にインデックスが割り当てられ、 :n でn番目に宣言された変数をリファレンスできます。例えば、次のSnowflake Scriptingブロックでは、 i 変数にバインド変数 :1 を、 v 変数にバインド変数 :2 を指定し、 :1 バインド変数を SQL ステートメントで再利用しています。

EXECUTE IMMEDIATE $$
DECLARE
  i INTEGER DEFAULT 1;
  v VARCHAR DEFAULT 'SnowFlake';
  r RESULTSET;
BEGIN
  CREATE OR REPLACE TABLE snowflake_scripting_bind_demo (id INTEGER, value VARCHAR);
  EXECUTE IMMEDIATE 'INSERT INTO snowflake_scripting_bind_demo (id, value)
    SELECT :1, (:2 || :1)' USING (i, v);
  r := (SELECT * FROM snowflake_scripting_bind_demo);
  RETURN TABLE(r);
END;
$$
;

+----+------------+
| ID | VALUE      |
|----+------------|
|  1 | SnowFlake1 |
+----+------------+

Snowflake Scriptingコードでは、大半の SQL ステートメントの値にバインド変数を使用できます。制限に関する情報については、 バインド変数の制限事項 をご参照ください。

Snowflake Scriptingでのバインド変数の使用に関する詳細については、 SQL ステートメントでの変数の使用（バインド） および SQL ステートメントでの引数の使用（バインド） をご参照ください。

SQL API でバインド変数を使用する¶

Snowflake SQL API を使用して、Snowflakeデータベースのデータにアクセスして更新することができます。SQLAPI を使用するアプリケーションを作成して、 SQL ステートメントを送信し、展開を管理することができます。

SQL ステートメントを実行するリクエストを送信する場合、ステートメント内の値にバインド変数を使用できます。詳細については、 ステートメントでのバインド変数の使用 をご参照ください。

ドライバーでバインド変数を使用する¶

Snowflake ドライバー を使用すると、Snowflake上で操作を実行するアプリケーションを記述できます。ドライバーはGo、Java、Pythonなどのプログラミング言語をサポートしています。特定のドライバーのアプリケーションでバインド変数を使用する方法については、そのドライバーのリンクからご覧ください。

• Go

• JDBC

• .NET

• Node.js

• ODBC

• Python

バインド変数と値の配列を使用する¶

SQL ステートメントでは、値の配列を変数にバインドできます。この手法を使用すると、1つのバッチで複数行を挿入することで、ネットワークのラウンドトリップやコンパイルを回避し、パフォーマンスを向上させることができます。配列バインドの使用は、「バルクインサート」または「バッチインサート」とも呼ばれます。

以下は、Pythonコードでの配列バインドの例です。

conn = snowflake.connector.connect( ... )
rows_to_insert = [('milk', 2), ('apple', 3), ('egg', 2)]
conn.cursor().executemany(
            "insert into grocery (item, quantity) values (?, ?)",
            rows_to_insert)

この例では、次のバインドリストを指定します: [('milk', 2), ('apple', 3), ('egg', 2)]。アプリケーションがバインドリストを指定する方法は、プログラミング言語によって異なります。

このコードは、テーブルに3行を挿入します。

+-------+----+
| C1    | C2 |
|-------+----|
| milk  |  2 |
| apple |  3 |
| egg   |  2 |
+-------+----+

特定のドライバーのアプリケーションで配列バインドを使用する方法については、そのドライバーのリンクからご覧ください。

• Go

• JDBC

• .NET

• Node.js

• ODBC

• Python

INSERT INTO t VALUES (?,?), (?,?);

半構造化データでバインド変数を使用する¶

半構造化データを変数にバインドするには、変数を文字列型としてバインドし、 PARSE_JSON や ARRAY_CONSTRUCT のような関数を使用します。

次の例では、 VARIANT 列を1つ持つテーブルを作成し、 PARSE_JSON 関数を呼び出してバインド変数で半構造化データをテーブルに挿入します。

CREATE TABLE t (a VARIANT);
-- Code that supplies a bind value for ? of '{'a': 'abc', 'x': 'xyz'}'
INSERT INTO t SELECT PARSE_JSON(a) FROM VALUES (?);

次の例では、テーブルにクエリを行います。

SELECT * FROM t;

クエリは次の出力を返します。

+---------------+
| A             |
|---------------|
| {             |
|   "a": "abc", |
|   "x": "xyz"  |
| }             |
+---------------+

次のステートメントは、 ARRAY_CONSTRUCT 関数を呼び出して、バインド変数を持つ VARIANT 列に半構造化データの配列を挿入します。

INSERT INTO t SELECT ARRAY_CONSTRUCT(column1) FROM VALUES (?);

これらの例はどちらも単一行を挿入することができますが、配列バインドを使用して複数行を1つのバッチで挿入することもできます。この手法を使って、 VARIANT 列に有効な任意のタイプの半構造化データを挿入することができます。

バインド変数の値を取得する¶

実行されたクエリのバインド変数の値を取得するには、INFORMATION_SCHEMA スキーマで BIND_VALUES テーブル関数を使用します。この関数で、JavascriptやSnowflakeスクリプトコードなど、バインド変数をサポートするコードからバインド変数値を取得できます。

また、これらのバインド変数値には、QUERY_HISTORYアカウント使用状況ビュー や QUERY_HISTORY 組織の使用状況ビュー の出力の bind_values 列から、あるいは QUERY_HISTORY 関数 でアクセスすることもできます。

これらのメソッドのいずれかを使用してバインド変数の値を取得するには、ALLOW_BIND_VALUES_ACCESS アカウントレベルパラメーターを TRUE に設定する必要があります。

次のような場合に、バインド変数の値を取得することができます。

• クエリのトラブルシューティング - クエリで使用された正確なバインド値がわかっていると、クエリを最適化し、次のタイプの問題をデバッグすることが容易になります。

  • クエリの実行に失敗する。

  • クエリのパフォーマンスが低下する。

  • クエリがキャッシュや想定される実行プランを使用していない。

• テスト用クエリの再作成 - 開発者 および DBAs は、バインド変数値を使用してユーザーが生成したクエリを再作成し、問題を複製することや、負荷テストを行うことができます。

• 監査とコンプライアンス - セキュリティとコンプライアンスのために、組織はユーザーがアクセスするデータを監査する必要があります。バインド変数の値を使用して、ユーザーが取得した正確なデータを特定できます。

SELECT * FROM TABLE(
  INFORMATION_SCHEMA.BIND_VALUES('<query_id_value>'));

SELECT bind_values
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
  WHERE query_id = '<query_id_value>';

DECLARE
  name STRING;
  temperature FLOAT;
  res RESULTSET;
BEGIN
  name := 'Snowman';
  temperature := -20.14;
  res := (
    SELECT
      CONCAT('Hello ', :NAME, '!') as greeting,
      CONCAT('It is ', :TEMPERATURE, 'deg C today.') as weather
  );
  RETURN LAST_QUERY_ID();
END;

EXECUTE IMMEDIATE
$$
DECLARE
  name STRING;
  temperature FLOAT;
  res RESULTSET;
BEGIN
  name := 'Snowman';
  temperature := -20.14;
  res := (
    SELECT
      CONCAT('Hello ', :NAME, '!') as greeting,
      CONCAT('It is ', :TEMPERATURE, 'deg C today.') as weather
  );
  RETURN LAST_QUERY_ID();
END;
$$
;

+--------------------------------------+
| anonymous block                      |
|--------------------------------------|
| 01bbe3d6-0109-0863-0000-a99502ffa062 |
+--------------------------------------+

SELECT * FROM TABLE(
  INFORMATION_SCHEMA.BIND_VALUES('01bbe3d6-0109-0863-0000-a99502ffa062'));

+--------------------------------------+----------+-------------+------+---------+
| QUERY_ID                             | POSITION | NAME        | TYPE | VALUE   |
|--------------------------------------+----------+-------------+------+---------|
| 01bbe3d6-0109-0863-0000-a99502ffa062 |     NULL | TEMPERATURE | REAL | -20.14  |
| 01bbe3d6-0109-0863-0000-a99502ffa062 |     NULL | NAME        | TEXT | Snowman |
+--------------------------------------+----------+-------------+------+---------+

cursor = conn.cursor()
print(cursor.execute(
          """
          SELECT
              CONCAT('Hello ', ?, '!') as greeting,
              CONCAT('It is ', ?, 'deg C today.') as weather
          """,
          params=["Snowman", -20.14],
      ).fetch_pandas_all())

query_id = cursor.sfqid
print(f"Bind values for query {query_id} are:")
print(cursor.execute("SELECT * FROM TABLE(INFORMATION_SCHEMA.BIND_VALUES(?))", params=[query_id]).fetch_pandas_all())

        GREETING                   WEATHER
0  Hello Snowman!  It is -20.14deg C today.

Bind values for query 01bbe918-0200-0001-0000-000000101145 are:

                               QUERY_ID POSITION  NAME  TYPE    VALUE
0  01bbe918-0200-0001-0000-000000101145        1  None  TEXT  Snowman
1  01bbe918-0200-0001-0000-000000101145        2  None  REAL   -20.14

バインド変数の制限事項¶

バインド変数には以下の制限があります。

• SELECT ステートメントの制限。

  • バインド変数により、データ型定義（例: NUMBER(?)）や 照合順序指定 （例: COLLATE ?）の一部である数値を置き換えることはできません。

  • バインド変数は、ステージ上のファイルをクエリする SELECT ステートメントのソースには使用できません。

• DDL コマンドの制限事項。

  • バインド変数は、以下の DDL コマンドでは使用できません。

    • CREATE/ALTER INTEGRATION

    • CREATE/ALTER REPLICATION GROUP

    • CREATE/ALTER PIPE

    • CREATE TABLE ... USING TEMPLATE

  • バインド変数は、以下の句では使用できません。

    • ALTER COLUMN

    • COMMENT ON CONSTRAINT

  • CREATE/ALTER コマンドでは、以下のパラメーターの値にバインド変数を使用することはできません。

    • CREDENTIALS

    • DIRECTORY

    • ENCRYPTION

    • IMPORTS

    • PACKAGES

    • REFRESH

    • TAG

    • 外部テーブル固有のパラメーター

  • バインド変数は、 FILE FORMAT 値の一部であるプロパティには使用できません。

• COPY INTO コマンドでは、バインド変数は以下のパラメーターの値には使用できません。

  • CREDENTIALS

  • ENCRYPTION

  • FILE_FORMAT

• SHOW コマンドでは、STARTS WITH パラメーターにバインド変数を使用することはできません。

• バインド変数は、 EXECUTE IMMEDIATE FROM コマンドでは使用できません。

• 以下でバインド変数を使用する場合、バインド変数の値はあるデータ型から別のデータ型に自動では変換されません。

  • データ型を明示的に指定するSnowflake Scriptingコード

  • DDL ステートメント

  • ステージ名

バインド変数のセキュリティに関する考慮事項¶

バインド変数は、すべてのケースで機密データをマスキングするわけではありません。たとえば、バインド変数の値はエラーメッセージやその他のアーティファクトに表示される可能性があります。

バインド変数は、 SQL ステートメントをユーザー入力で作成する際に、 SQL インジェクション攻撃を防ぐのに役立ちます。しかし、バインド変数には潜在的なセキュリティリスクがあります。SQL ステートメントへのインプットが外部ソースからのものである場合、それらが検証済みであることを確認します。詳細については、 SQL インジェクション をご参照ください。