IDENTIFIER()構文による識別子としてのリテラルと変数

Snowflake SQL ステートメントでは、オブジェクトを名前で参照することに加えて（識別子の要件 を参照）、文字列リテラル、セッション変数、バインド変数、または Snowflakeスクリプト変数 を使用しても、オブジェクトを参照できます。たとえば、 SELECT ステートメントの FROM 句でテーブルの名前に設定されているセッション変数を使用できます。リテラルや変数で指定されたオブジェクト名を使用するには、 IDENTIFIER() 構文を使用します。

IDENTIFIER() を使用してデータベース・オブジェクトを特定することは、コードの再利用性を高め、 SQL インジェクション のリスクを防ぐことができるため、ベスト・プラクティスです。

構文

IDENTIFIER( { string_literal | session_variable | bind_variable | snowflake_scripting_variable } )
Copy
string_literal

オブジェクトの名前を識別する文字列です。

  • 文字列は、一重引用符（'name'）で囲むか、ドル記号（$name）で始まる必要があります。

  • 文字列リテラルには、完全修飾オブジェクト名（ 'db_name.schema_name.object_name' または $db_name.schema_name.object_name など）を使用できます。

session_variable

セッションに設定された SQL 変数 です。

bind_variable

? または :variable の形式の バインド変数 です。バインドをサポートするクライアント/プログラムインターフェイス（JDBC 、 ODBC、Pythonなど）で使用できます。

snowflake_scripting_variable

設定された Snowflakeスクリプト変数。

使用上の注意

  • オブジェクトを名前で識別する必要がある場合（クエリ、 DML、 DDL など）、リテラルや変数（セッションやバインド）を使用できる場合があります。

  • 同じクエリで、オブジェクト識別子にバインド変数を使用し、値にバインド変数を使用できます。

  • FROM 句では、 TABLE( { string_literal | session_variable | bind_variable | snowflake_scripting_variable } ) を IDENTIFIER( { string_literal | session_variable | bind_variable | snowflake_scripting_variable } ) の同義語として使用できます。

  • IDENTIFIER() は関数の構文を使っていますが、これは真の関数ではなく、 SHOW FUNCTIONS のようなコマンドでは返されません。

例

以下の例では、 IDENTIFIER() 構文を使用しています。

文字列リテラルでの IDENTIFIER() 構文の使用

これらの例では、文字列リテラルにオブジェクト識別子が含まれている場合にオブジェクトを参照する方法を示しています。

データベースを作成します。

CREATE OR REPLACE DATABASE IDENTIFIER('my_db');
Copy
+--------------------------------------+
| status                               |
|--------------------------------------|
| Database MY_DB successfully created. |
+--------------------------------------+

スキーマを作成します:

CREATE OR REPLACE SCHEMA IDENTIFIER('my_schema');
Copy
+----------------------------------------+
| status                                 |
|----------------------------------------|
| Schema MY_SCHEMA successfully created. |
+----------------------------------------+

完全修飾名を含む文字列で指定された大文字小文字を区別しないテーブル名を使用してテーブルを作成します:

CREATE OR REPLACE TABLE IDENTIFIER('my_db.my_schema.my_table') (c1 number);
Copy
+--------------------------------------+
| status                               |
|--------------------------------------|
| Table MY_TABLE successfully created. |
+--------------------------------------+

二重引用符で囲まれた文字列で指定された大文字小文字を区別するテーブル名を使用してテーブルを作成します:

CREATE OR REPLACE TABLE IDENTIFIER('"my_table"') (c1 number);
Copy
+--------------------------------------+
| status                               |
|--------------------------------------|
| Table my_table successfully created. |
+--------------------------------------+

スキーマ内のテーブルを表示します:

SHOW TABLES IN SCHEMA IDENTIFIER('my_schema');
Copy
+-------------------------------+----------+---------------+-------------+-------+---------+---------+
| created_on                    | name     | database_name | schema_name | kind  | comment | ...     |
|-------------------------------+----------+---------------+-------------+-------+---------+---------|
| 2024-07-03 08:55:11.992 -0700 | MY_TABLE | MY_DB         | MY_SCHEMA   | TABLE |         | ...     |
| 2024-07-03 08:56:00.604 -0700 | my_table | MY_DB         | MY_SCHEMA   | TABLE |         | ...     |
+-------------------------------+----------+---------------+-------------+-------+---------+---------+

セッション変数での IDENTIFIER() 構文の使用

これらの例では、テーブル名やスキーマ名を持つ セッション変数 の使用方法を示しています。

スキーマ名のセッション変数を設定します：

SET schema_name = 'my_db.my_schema';
Copy

テーブル名のセッション変数を設定します：

SET table_name = 'my_table';
Copy

現在のセッションのスキーマを指定します：

USE SCHEMA IDENTIFIER($schema_name);
Copy

テーブルに値を挿入します：

INSERT INTO IDENTIFIER($table_name) VALUES (1), (2), (3);
Copy

テーブルをクエリします：

SELECT * FROM IDENTIFIER($table_name) ORDER BY 1;
Copy
+----+
| C1 |
|----|
|  1 |
|  2 |
|  3 |
+----+

この例は、関数名を持つセッション変数の使用方法を示しています。

  1. 関数 speed_of_light を作成します：

    CREATE FUNCTION speed_of_light() 
RETURNS INTEGER
AS
  $$
  299792458
  $$;
    Copy

  2. 名前で関数を呼び出します。

    SELECT speed_of_light();
    Copy
    +------------------+
| SPEED_OF_LIGHT() |
|------------------|
|        299792458 |
+------------------+

  3. IDENTIFIER（）構文を使用して関数を呼び出します。

    SET my_function_name = 'speed_of_light';
    Copy
    SELECT IDENTIFIER($my_function_name)();
    Copy
    +---------------------------------+
| IDENTIFIER($MY_FUNCTION_NAME)() |
|---------------------------------|
|                       299792458 |
+---------------------------------+

バインド変数での IDENTIFIER() 構文の使用

これらの例では、 バインド変数 を使ってオブジェクトを識別する方法を示しています。

この例では、 JDBC で関数名をバインドする方法を示しています。関数の名前は speed_of_light です。

String sql_command;

// Create a Statement object to use later.
System.out.println("Create JDBC statement.");
Statement statement = connection.createStatement();
System.out.println("Create function.");
sql_command = "CREATE FUNCTION speed_of_light() RETURNS INTEGER AS $$ 299792458 $$";
statement.execute(sql_command);

System.out.println("Create prepared statement.");
sql_command = "SELECT IDENTIFIER(?)()";
PreparedStatement ps = connection.prepareStatement(sql_command);
// Bind
ps.setString(1, "speed_of_light");
ResultSet rs = ps.executeQuery();
if (rs.next()) {
  System.out.println("Speed of light (m/s) = " + rs.getInt(1));
}
Copy

次の例は、バインディングを使用できるさまざまな SQL ステートメントと、バインドできるさまざまなデータベースオブジェクト（スキーマ名やテーブル名を含む）を示しています。

USE SCHEMA IDENTIFIER(?);

CREATE OR REPLACE TABLE IDENTIFIER(?) (c1 NUMBER);

INSERT INTO IDENTIFIER(?) values (?), (?), (?);

SELECT t2.c1
  FROM IDENTIFIER(?) AS t1,
       IDENTIFIER(?) AS t2
  WHERE t1.c1 = t2.c1 AND t1.c1 > (?);

DROP TABLE IDENTIFIER(?);
Copy

Snowflake Scripting変数での IDENTIFIER() 構文の使用

次の例は、 SELECT ステートメントのテーブル名に Snowflake Scripting 変数 を使用する方法を示しています。

BEGIN
  LET res RESULTSET := (SELECT COUNT(*) AS COUNT FROM IDENTIFIER(:table_name));
  ...
Copy
言語: 日本語