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() 内に変数をラップする必要があります。以下に例を示します。
SET my_table_name='table1';
CREATE TABLE IDENTIFIER($my_table_name) (i INTEGER);
INSERT INTO IDENTIFIER($my_table_name) (i) VALUES (42);
SELECT * FROM IDENTIFIER($my_table_name);
+----+
| I |
|----|
| 42 |
+----+
FROM 句のコンテキストでは、次のように変数名を TABLE() でラップできます。
SELECT * FROM TABLE($my_table_name);
+----+
| I |
|----|
| 42 |
+----+
DROP TABLE IDENTIFIER($my_table_name);
IDENTIFIER() の詳細については、 IDENTIFIER()構文による識別子としてのリテラルと変数 をご参照ください。
セッションの変数の表示¶
現在のセッションで定義されているすべての変数を表示するには、 SHOW VARIABLES コマンドを使用します。
SET (min, max)=(40, 70);
+----------------------------------+
| status |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
SHOW VARIABLES;
+----------------+-------------------------------+-------------------------------+------+-------+-------+---------+
| session_id | created_on | updated_on | name | value | type | comment |
|----------------+-------------------------------+-------------------------------+------+-------+-------+---------|
| 10363773891062 | 2024-06-28 10:09:57.990 -0700 | 2024-06-28 10:09:58.032 -0700 | MAX | 70 | fixed | |
| 10363773891062 | 2024-06-28 10:09:57.990 -0700 | 2024-06-28 10:09:58.021 -0700 | MIN | 40 | fixed | |
+----------------+-------------------------------+-------------------------------+------+-------+-------+---------+
セッション変数関数¶
セッション変数を操作して他のデータベースシステムとの互換性をサポートし、変数にアクセスするための $ 構文をサポートしないツールを介して SQL を発行するために、次の便利な関数が提供されます。これらの関数はすべて、セッション変数値を文字列として受け入れて返します。
SYS_CONTEXT および SET_SYS_CONTEXT
SESSION_CONTEXT および SET_SESSION_CONTEXT
GETVARIABLE および SETVARIABLE
次は GETVARIABLE の使用例です。まず、 SET を使って変数を定義します。
SET var_artist_name = 'Jackson Browne';
+----------------------------------+
| status |
+----------------------------------+
| Statement executed successfully. |
+----------------------------------+
変数の値を返します。
SELECT GETVARIABLE('var_artist_name');
この例では、Snowflakeは変数をすべて大文字で保存するため、出力は NULL になります。
大文字小文字を更新します。
SELECT GETVARIABLE('VAR_ARTIST_NAME');
+--------------------------------+
| GETVARIABLE('VAR_ARTIST_NAME') |
+--------------------------------+
| Jackson Browne |
+--------------------------------+
WHERE 句の中で変数名を使うことができます。たとえば、
SELECT album_title
FROM albums
WHERE artist = $var_artist_name;
変数の削除¶
SQL 変数はセッション専用です。Snowflakeのセッションが閉じられると、セッション中に作成されたすべての変数が削除されます。これは、他のセッションで設定されたユーザー定義変数には誰もアクセスできず、セッションが閉じられると、これらの変数が期限切れになることを意味します。
さらに、変数はいつでも UNSET コマンドを使用して明示的に破棄できます。
例:
UNSET my_variable;