SQL 変数

このトピックでは、Snowflakeのセッションで SQL 変数を定義して使用する方法について説明します。

このトピックの内容:

概要

Snowflakeは、ユーザーが宣言した SQL 変数をサポートしています。アプリケーション固有の環境設定を保存するなど、多くの用途があります。

変数識別子

SQL 変数は、大文字と小文字を区別しない名前を使用してグローバルに識別されます。

変数 DDL

Snowflakeは、 SQL 変数を使用するための次の DDL コマンドを提供します。

変数の初期化

変数を設定するには、 SQL ステートメント SET を実行するか、Snowflakeに接続する際、接続文字列に変数を設定します。

文字列変数またはバイナリ変数のサイズは256バイトに制限されています。

SQL を使用してセッション内の変数を初期化する

SET コマンドを使用して、変数を SQL で初期化できます。変数のデータ型は、評価された式の結果のデータ型から派生します。

SET MY_VARIABLE=10;
SET MY_VARIABLE='example';

同じステートメントで複数の変数を初期化できるため、サーバーとの往復通信の回数を減らすことができます。

SET (VAR1, VAR2, VAR3)=(10, 20, 30);
SET (VAR1, VAR2, VAR3)=(SELECT 10, 20, 30);

接続での変数の設定

SET を使用してセッション内で変数を設定することに加えて、Snowflakeでセッションを初期化するために使用される接続文字列の引数として変数を渡すことができます。これは、接続文字列の指定のみがカスタマイズ可能な場合にツールを使用する際に特に便利です。

例えば、Snowflake JDBC ドライバーを使用して、パラメーターとして解釈される追加の接続プロパティを設定できます。JDBC API では、 SQL 変数が文字列である必要があります。

// build connection properties
Properties properties = new Properties();

// Required connection properties
properties.put("user"    ,  "jsmith"      );
properties.put("password",  "mypassword");
properties.put("account" ,  "myaccount");

// Set some additional variables.
properties.put("$variable_1", "some example");
properties.put("$variable_2", "1"           );

// create a new connection
String connectStr = "jdbc:snowflake://localhost:8080";

// Open a connection under the snowflake account and enable variable support
Connection con = DriverManager.getConnection(connectStr, properties);

SQL での変数の使用

Snowflakeでは、リテラル定数が許可されている場所であればどこでも変数を使用できます。それらをバインド値および列名と区別するには、すべての変数の前に $ 記号を付ける必要があります。

例:

SET (MIN, MAX)=(40, 70);

SELECT $MIN;

SELECT AVG(SALARY) FROM EMP WHERE AGE BETWEEN $MIN AND $MAX;

注釈

$ 記号は、SQL ステートメントで変数を識別するために使用されるプレフィックスであるため、識別子で使用される場合は特殊文字として扱われます。識別子(データベース名、テーブル名、列名など)は、名前全体を二重引用符で囲まない限り、特殊文字で始めることはできません。詳細については、 オブジェクト識別子 をご参照ください。

変数には、テーブル名などの識別子名を含めることもできます。変数を識別子として使用するには、 IDENTIFIER($MY_VARIABLE) などの IDENTIFIER() 内に変数をラップする必要があります。以下に例を示します。

CREATE TABLE IDENTIFIER($MY_TABLE_NAME) (i INTEGER);
INSERT INTO IDENTIFIER($MY_TABLE_NAME) (i) VALUES (42);
SELECT * FROM IDENTIFIER($MY_TABLE_NAME);
DROP TABLE IDENTIFIER($MY_TABLE_NAME);

FROM 句のコンテキストでは、次のように変数名を TABLE() でラップできます。

SELECT * FROM TABLE($MY_TABLE_NAME);
+----+
|  I |
|----|
| 42 |
+----+

IDENTIFIER()の詳細については、 文字列リテラル/セッション変数/識別子としてのバインド変数 をご参照ください。

セッションの変数の表示

現在のセッションで定義されているすべての変数を表示するには、 SHOW VARIABLES コマンドを使用します。

SET (MIN, MAX)=(40, 70);

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

SHOW VARIABLES;

+-------------+---------------------------------+---------------------------------+------+-------+-------+---------+
|  session_id | created_on                      | updated_on                      | name | value | type  | comment |
|-------------+---------------------------------+---------------------------------+------+-------+-------+---------|
| 34359992326 | Fri, 21 Apr 2017 11:20:32 -0700 | Fri, 21 Apr 2017 11:20:32 -0700 | MAX  | 70    | fixed |         |
| 34359992326 | Fri, 21 Apr 2017 11:20:32 -0700 | Fri, 21 Apr 2017 11:20:32 -0700 | MIN  | 40    | fixed |         |
+-------------+---------------------------------+---------------------------------+------+-------+-------+---------+

セッション変数関数

セッション変数を操作して他のデータベースシステムとの互換性をサポートし、変数にアクセスするための $ 構文をサポートしないツールを介して SQL を発行するために、次の便利な関数が提供されます。これらの関数はすべて、セッション変数値を文字列として受け入れて返します。

  • SYS_CONTEXT および SET_SYS_CONTEXT

  • SESSION_CONTEXT および SET_SESSION_CONTEXT

  • GETVARIABLE および SETVARIABLE

次は GETVARIABLE()の使用例です。

set var_artist_name ='Jackson Browne';
-- Variable names are forced to uppercase by default, so output is NULL.
select getvariable('var_artist_name');
-- Output is "Jackson Browne".
select getvariable('VAR_ARTIST_NAME');

-- You can use the variable name in a WHERE clause, for example:
select album_title from albums where artist = $VAR_ARTIST_NAME;

変数の削除/消去

SQL 変数はセッション専用です。Snowflakeのセッションが閉じられると、セッション中に作成されたすべての変数が削除されます。これは、他のセッションで設定されたユーザー定義変数には誰もアクセスできず、セッションが閉じられると、これらの変数が期限切れになることを意味します。

さらに、変数はいつでも UNSET コマンドを使用して明示的に破棄できます。

例:

UNSET MY_VARIABLE;