RESULTSETs の操作

このトピックでは、Snowflakeスクリプトで RESULTSET を使用する方法について説明します。

概要

Snowflakeスクリプトでは、 RESULTSET はクエリの結果セットをポイントする SQL データ型です。

RESULTSET は結果への単なるポインターであるため、 RESULTSET を介して結果にアクセスするには、次のいずれかを実行する必要があります。

  • TABLE(...) 構文を使用して、結果をテーブルとして取得します。

  • カーソル を使用して RESULTSET を反復処理します。

これらの両方の例を以下に示します。

カーソルと RESULTSET の違いを理解する

RESULTSET と カーソル はどちらも、クエリの結果セットへのアクセスを提供します。ただし、これらのオブジェクトは次の点で異なります。

  • クエリが実行された時点。

    • カーソルの場合、カーソルに対して OPEN コマンドを実行すると、クエリが実行されます。

    • RESULTSET の場合は、クエリを RESULTSET に割り当てるとクエリが実行されます（DECLARE セクション内または BEGIN ... END ブロック内のいずれか）。

  • OPEN コマンドでのバインドのサポート。

    • カーソルを宣言するときに、バインドパラメーター（? 文字）を指定できます。後で OPEN コマンドを実行すると、 USING 句で変数をそれらのパラメーターにバインドできます。

    • RESULTSET は、 OPEN コマンドをサポートしていません。ただし、結果セットを返す前に、 SQL コマンドで変数をバインドできます。

一般に、クエリの結果セットを含むテーブルを返す場合は、 RESULTSET を使用する方が簡単です。ただし、カーソルを使用してSnowflakeスクリプトブロックからテーブルを返すこともできます。そのためには、カーソルを RESULTSET_FROM_CURSOR(cursor) に渡して RESULTSET を返し、その RESULTSET を TABLE(...) に渡します。 カーソルのテーブルを返す をご参照ください。

RESULTSET の宣言

ブロックの DECLARE セクションまたはブロックの BEGIN ... END セクションで RESULTSET を宣言できます。

  • DECLARE セクション内において、 RESULTSET 宣言構文 で説明されている構文を使用します。例:

    DECLARE
  ...
  res RESULTSET DEFAULT (SELECT col1 FROM mytable ORDER BY col1);
    Copy

  • BEGIN ... END ブロック内において、 RESULTSET 割り当て構文 で説明されている構文を使用します。例:

    BEGIN
  ...
  LET res RESULTSET := (SELECT col1 FROM mytable ORDER BY col1);
    Copy

宣言された RESULTSET へのクエリの割り当て

すでに宣言されている RESULTSET にクエリの結果を割り当てるには、次の構文を使用します。

<resultset_name> := [ ASYNC ] ( <query> ) ;
Copy

条件:

resultset_name

RESULTSET の名前。

名前は、現在のスコープ内で一意である必要があります。

名前は、 オブジェクト識別子 の名前付け規則に従う必要があります。

ASYNC

Snowflakeのロゴ（黒）（文字なし） プレビュー機能 --- オープン

すべてのアカウントで利用可能です。

クエリを非同期の子ジョブとして実行します。

クエリには、 SELECT ステートメントや DML ステートメント（INSERT や UPDATE など）を含む、有効な SQL ステートメントを指定できます。

このキーワードを省略すると、ストアドプロシージャは子ジョブを順次実行し、各子ジョブは実行中の子ジョブの終了を待ってから開始します。

このキーワードを使用すると、複数の子ジョブを同時に実行できるため、効率が向上し、全体の実行時間が短縮されます。

AWAIT および CANCEL ステートメントを使用して、 RESULTSET の非同期の子ジョブを管理できます。

query

RESULTSET に割り当てるクエリ。

クエリを RESULTSET に割り当てる方法：

DECLARE
  res RESULTSET;
BEGIN
  res := (SELECT col1 FROM mytable ORDER BY col1);
  ...
Copy

クエリを RESULTSET に割り当て、非同期の子ジョブとしてクエリを実行する方法：

DECLARE
  res RESULTSET;
BEGIN
  res := ASYNC (SELECT col1 FROM mytable ORDER BY col1);
  ...
Copy

クエリ用に SQL 文字列を動的に構築するには、 query を (EXECUTE IMMEDIATE string_of_sql) にセットします。例:

DECLARE
  res RESULTSET;
  col_name VARCHAR;
  select_statement VARCHAR;
BEGIN
  col_name := 'col1';
  select_statement := 'SELECT ' || col_name || ' FROM mytable';
  res := (EXECUTE IMMEDIATE :select_statement);
  RETURN TABLE(res);
END;
Copy

RESULTSET に対して EXECUTE IMMEDIATE ステートメントに query をセットすることはできますが、カーソルに対してこれを行うことはできません。

RESULTSET の使用

RESULTSET のクエリは、オブジェクトがそのクエリに関連付けられているときに実行されます。例:

  • RESULTSET を宣言し、 DEFAULT 句をクエリに設定すると、その時点でクエリが実行されます。

  • := 演算子を使用してクエリを RESULTSET に割り当てると、その時点でクエリが実行されます。

注釈

RESULTSET はクエリの結果セットをポイントしているため（クエリの結果セットは含まれていません）、 RESULTSET はクエリ結果がキャッシュされている間（通常は24時間）のみ有効です。クエリ結果のキャッシュの詳細については、 保存済みのクエリ結果の使用 をご参照ください。

クエリが実行されると、カーソルを使用して結果にアクセスできます。ストアドプロシージャから結果をテーブルとして返すこともできます。

カーソルの使用による RESULTSET からのデータへのアクセス

カーソルを使用して RESULTSET からデータにアクセスするには、オブジェクト上で カーソルを宣言 します。例:

DECLARE
  ...
  res RESULTSET DEFAULT (SELECT col1 FROM mytable ORDER BY col1);
  c1 CURSOR FOR res;
Copy

RESULTSET でカーソルを宣言すると、カーソルはすでに RESULTSET にあるデータにアクセスできます。カーソルで OPEN コマンドを実行しても、 RESULTSET のクエリは再度実行されません。

次に、 カーソルを開き、カーソルを使用して データをフェッチ します。

注釈

結果に GEOGRAPHY 値が含まれる場合は、 GEOGRAPHY 入力値を期待する関数に値を渡す前に、値を GEOGRAPHY 型にキャストする必要があります。 カーソルの使用による GEOGRAPHY 値の取得 をご参照ください。

RESULTSET をテーブルとして返す

RESULTSET がポイントする結果を返す場合は、 RESULTSET を TABLE(...) に渡します。例:

CREATE PROCEDURE f()
  RETURNS TABLE(column_1 INTEGER, column_2 VARCHAR)
  ...
    RETURN TABLE(my_resultset_1);
  ...
Copy

これは、 TABLE(...) が テーブル関数 （RESULT_SCAN など）で使用される方法と似ています。

例に示すように、テーブルを返すストアドプロシージャを作成する場合は、テーブルを返すものとしてストアドプロシージャを宣言する必要があります。

注釈

現在、 TABLE(resultset_name) 構文は、 RETURN ステートメントでのみサポートされています。

カーソルを使用して RESULTSET から行をフェッチ した場合でも、 TABLE(resultset_name) によって返されるテーブルには、（カーソルの内部行ポインターから始まる行だけでなく）すべての行が含まれています。

RESULTSET データ型の制限

RESULTSET はデータ型ですが、Snowflakeはまだ以下をサポートしていません。

  • 型 RESULTSET の列を宣言する。

  • 型 RESULTSET のパラメーターを宣言する。

  • ストアドプロシージャの戻り型を RESULTSET として宣言する。

Snowflakeは、Snowflakeスクリプト内でのみ RESULTSET をサポートします。

また、 RESULTSET を直接テーブルとして使用することはできません。たとえば、次は無効です。

SELECT * FROM my_result_set;
Copy

RESULTSET の使用例

次のセクションでは、 RESULTSET の使用例を示します。

例に対するデータの設定

以下の例の多くでは、以下に示すテーブルとデータを使用しています。

CREATE OR REPLACE TABLE t001 (a INTEGER, b VARCHAR);
INSERT INTO t001 (a, b) VALUES
  (1, 'row1'),
  (2, 'row2');
Copy

例: ストアドプロシージャからテーブルを返す

次のコードは、 RESULTSET を宣言し、 RESULTSET がポイントする結果を返す方法を示しています。CREATE PROCEDURE コマンドの RETURNS 句は、ストアドプロシージャが INTEGER 型の1つの列を含むテーブルを返すことを宣言しています。

ブロック内の RETURN ステートメントは、 TABLE(...) 構文を使用して結果をテーブルとして返します。

ストアドプロシージャを作成します。

CREATE OR REPLACE PROCEDURE test_sp()
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
  DECLARE
    res RESULTSET DEFAULT (SELECT a FROM t001 ORDER BY a);
  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 test_sp()
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
$$
  DECLARE
      res RESULTSET default (SELECT a FROM t001 ORDER BY a);
  BEGIN
      RETURN TABLE(res);
  END;
$$;
Copy

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

CALL test_sp();
Copy
+---+
| A |
|---|
| 1 |
| 2 |
+---+

RESULT_SCAN 関数を使用して、ストアドプロシージャ呼び出しの結果を処理することもできます。

SELECT *
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
  ORDER BY 1;
Copy
+---+
| A |
|---|
| 1 |
| 2 |
+---+

例: SQL ステートメントを動的に作成する

SQL を動的に構築できます。以下は、前のストアドプロシージャと同じクエリを実行しますが、動的に構築される SQL ステートメントを使用する例です。

CREATE OR REPLACE PROCEDURE test_sp_dynamic(table_name VARCHAR)
  RETURNS TABLE(a INTEGER)
  LANGUAGE SQL
AS
DECLARE
  res RESULTSET;
  query VARCHAR DEFAULT 'SELECT a FROM IDENTIFIER(?) ORDER BY a;';
BEGIN
  res := (EXECUTE IMMEDIATE :query USING(table_name));
  RETURN TABLE(res);
END;
Copy

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

CREATE OR REPLACE PROCEDURE test_sp_dynamic(table_name VARCHAR)
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
$$
  DECLARE
    res RESULTSET;
    query VARCHAR DEFAULT 'SELECT a FROM IDENTIFIER(?) ORDER BY a;';
  BEGIN
    res := (EXECUTE IMMEDIATE :query USING(table_name));
    RETURN TABLE(res);
  END
$$
;
Copy

例を実行するには、ストアドプロシージャを呼び出し、テーブル名を渡します。

CALL test_sp_dynamic('t001');
Copy
+---+
| A |
|---|
| 1 |
| 2 |
+---+

例: DEFAULT 句なしで RESULTSET 変数を宣言する

次のコードは、 DEFAULT 句なしで（つまり、クエリを RESULTSET に関連付けずに） RESULTSET を宣言し、後で RESULTSET をクエリに関連付ける方法を示しています。

CREATE OR REPLACE PROCEDURE test_sp_02()
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
  DECLARE
    res RESULTSET;
  BEGIN
    res := (SELECT a FROM t001 ORDER BY a);
    RETURN TABLE(res);
  END;
Copy

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

CREATE OR REPLACE PROCEDURE test_sp_02()
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
$$
  DECLARE
      res RESULTSET;
  BEGIN
      res := (SELECT a FROM t001 ORDER BY a);
      RETURN TABLE(res);
  END;
$$;
Copy

例を実行するには、ストアドプロシージャを呼び出します。

CALL test_sp_02();
Copy
+---+
| A |
|---|
| 1 |
| 2 |
+---+

例: CURSOR を RESULTSET で使用する

次のコードは、 カーソル を使用して RESULTSET の行を反復処理する方法を示しています。

ストアドプロシージャを作成します。

CREATE OR REPLACE PROCEDURE test_sp_03()
RETURNS VARCHAR
LANGUAGE SQL
AS

DECLARE
  accumulator INTEGER DEFAULT 0;
  res1 RESULTSET DEFAULT (SELECT a FROM t001 ORDER BY a);
  cur1 CURSOR FOR res1;
BEGIN
  FOR row_variable IN cur1 DO
    accumulator := accumulator + row_variable.a;
  END FOR;
  RETURN accumulator::VARCHAR;
END;
Copy

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

CREATE OR REPLACE PROCEDURE test_sp_03()
RETURNS INTEGER
LANGUAGE SQL
AS
$$
  DECLARE
    accumulator INTEGER DEFAULT 0;
    res1 RESULTSET DEFAULT (SELECT a FROM t001 ORDER BY a);
    cur1 CURSOR FOR res1;
  BEGIN
    FOR row_variable IN cur1 DO
        accumulator := accumulator + row_variable.a;
    END FOR;
    RETURN accumulator;
  END;
$$;
Copy

ストアドプロシージャを呼び出すと、結果にテーブル内の a の値が追加されます（1 + 2）。

CALL test_sp_03();
Copy
+------------+
| TEST_SP_03 |
|------------|
| 3          |
+------------+

例：テーブルを同時にクエリする子ジョブの実行

Snowflakeのロゴ（黒）（文字なし） プレビュー機能 --- オープン

すべてのアカウントで利用可能です。

以下のコードは、 ASYNC キーワードを使用して、テーブルをクエリする複数の子ジョブを同時に実行する方法を示しています。

この例では、以下の表のデータを使用します。

CREATE OR REPLACE TABLE orders_q1_2024 (
  order_id INT,
  order_amount NUMBER(12,2));

INSERT INTO orders_q1_2024 VALUES (1, 500.00);
INSERT INTO orders_q1_2024 VALUES (2, 225.00);
INSERT INTO orders_q1_2024 VALUES (3, 725.00);
INSERT INTO orders_q1_2024 VALUES (4, 150.00);
INSERT INTO orders_q1_2024 VALUES (5, 900.00);

CREATE OR REPLACE TABLE orders_q2_2024 (
  order_id INT,
  order_amount NUMBER(12,2));

INSERT INTO orders_q2_2024 VALUES (1, 100.00);
INSERT INTO orders_q2_2024 VALUES (2, 645.00);
INSERT INTO orders_q2_2024 VALUES (3, 275.00);
INSERT INTO orders_q2_2024 VALUES (4, 800.00);
INSERT INTO orders_q2_2024 VALUES (5, 250.00);
Copy

以下のストアドプロシージャは、以下のアクションを実行します。

  • すべての行の order_amount 値を両方のテーブルにクエリし、その結果を異なる RESULTSETs (各テーブルに 1 つずつ) に返します。

  • ASYNC キーワードを使用して、クエリが並行子ジョブとして実行されるように指定します。

  • RESULTSET ごとに AWAIT ステートメントを実行し、プロシージャがクエリの終了を待ってから処理を進めるようにします。AWAIT が RESULTSET に対して実行されるまで、 RESULTSET のクエリ結果にアクセスすることはできません。

  • カーソルを使用して、各テーブルの order_amount 行の合計を計算します。

  • テーブルの合計を加算し、値を返します。

CREATE OR REPLACE PROCEDURE test_sp_async_child_jobs_query()
RETURNS INTEGER
LANGUAGE SQL
AS
DECLARE
  accumulator1 INTEGER DEFAULT 0;
  accumulator2 INTEGER DEFAULT 0;
  res1 RESULTSET DEFAULT ASYNC (SELECT order_amount FROM orders_q1_2024);
  res2 RESULTSET DEFAULT ASYNC (SELECT order_amount FROM orders_q2_2024);
BEGIN
  AWAIT res1;
  LET cur1 CURSOR FOR res1;
  OPEN cur1;
  AWAIT res2;
  LET cur2 CURSOR FOR res2;
  OPEN cur2;
  FOR row_variable IN cur1 DO
      accumulator1 := accumulator1 + row_variable.order_amount;
  END FOR;
  FOR row_variable IN cur2 DO
      accumulator2 := accumulator2 + row_variable.order_amount;
  END FOR;
  RETURN accumulator1 + accumulator2;
END;
Copy

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

CREATE OR REPLACE PROCEDURE test_sp_async_child_jobs_query()
RETURNS INTEGER
LANGUAGE SQL
AS
$$
  DECLARE
    accumulator1 INTEGER DEFAULT 0;
    accumulator2 INTEGER DEFAULT 0;
    res1 RESULTSET DEFAULT ASYNC (SELECT order_amount FROM orders_q1_2024);
    res2 RESULTSET DEFAULT ASYNC (SELECT order_amount FROM orders_q2_2024);
  BEGIN
    AWAIT res1;
    LET cur1 CURSOR FOR res1;
    OPEN cur1;
    AWAIT res2;
    LET cur2 CURSOR FOR res2;
    OPEN cur2;
    FOR row_variable IN cur1 DO
        accumulator1 := accumulator1 + row_variable.order_amount;
    END FOR;
    FOR row_variable IN cur2 DO
        accumulator2 := accumulator2 + row_variable.order_amount;
    END FOR;
    RETURN accumulator1 + accumulator2;
  END;
$$;
Copy

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

CALL test_sp_async_child_jobs_query();
Copy
+--------------------------------+
| TEST_SP_ASYNC_CHILD_JOBS_QUERY |
|--------------------------------|
|                           4570 |
+--------------------------------+

例：テーブルに行を挿入する子ジョブの同時実行

Snowflakeのロゴ（黒）（文字なし） プレビュー機能 --- オープン

すべてのアカウントで利用可能です。

以下のコードは、 ASYNC キーワードを使用して、テーブルに行を挿入する複数の子ジョブを同時に実行する方法を示しています。

以下のストアドプロシージャは、以下のアクションを実行します。

  • orders_q3_2024 テーブルが存在しない場合は作成します。

  • テーブルへの挿入結果を保持する2つの RESULTSETs、 insert_1 と insert_2 を作成します。ストアドプロシージャの引数は、テーブルに挿入される値を指定します。

  • ASYNC キーワードを使用して、挿入が同時実行子ジョブとして実行されるように指定します。

  • RESULTSET ごとに AWAIT ステートメントを実行し、プロシージャが挿入の終了を待ってから処理を進めるようにします。AWAIT が RESULTSET に対して実行されるまで、 RESULTSET の結果にはアクセスできません。

  • orders_q3_2024 テーブルのクエリ結果を保持する新しい RESULTSET res を作成します。

  • クエリの結果を返します。

CREATE OR REPLACE PROCEDURE test_sp_async_child_jobs_insert(
  arg1 INT,
  arg2 NUMBER(12,2),
  arg3 INT,
  arg4 NUMBER(12,2))
RETURNS TABLE()
LANGUAGE SQL
AS
  BEGIN
   CREATE TABLE IF NOT EXISTS orders_q3_2024 (
      order_id INT,
      order_amount NUMBER(12,2));
    LET insert_1 RESULTSET := ASYNC (INSERT INTO orders_q3_2024 SELECT :arg1, :arg2);
    LET insert_2 RESULTSET := ASYNC (INSERT INTO orders_q3_2024 SELECT :arg3, :arg4);
    AWAIT insert_1;
    AWAIT insert_2;
    LET res RESULTSET := (SELECT * FROM orders_q3_2024 ORDER BY order_id);
    RETURN TABLE(res);
  END;
Copy

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

CREATE OR REPLACE PROCEDURE test_sp_async_child_jobs_insert(
  arg1 INT,
  arg2 NUMBER(12,2),
  arg3 INT,
  arg4 NUMBER(12,2))
RETURNS TABLE()
LANGUAGE SQL
AS
$$
  BEGIN
   CREATE TABLE IF NOT EXISTS orders_q3_2024 (
      order_id INT,
      order_amount NUMBER(12,2));
    LET insert_1 RESULTSET := ASYNC (INSERT INTO orders_q3_2024 SELECT :arg1, :arg2);
    LET insert_2 RESULTSET := ASYNC (INSERT INTO orders_q3_2024 SELECT :arg3, :arg4);
    AWAIT insert_1;
    AWAIT insert_2;
    LET res RESULTSET := (SELECT * FROM orders_q3_2024 ORDER BY order_id);
    RETURN TABLE(res);
  END;
$$;
Copy

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

CALL test_sp_async_child_jobs_insert(1, 325, 2, 241);
Copy
+----------+--------------+
| ORDER_ID | ORDER_AMOUNT |
|----------+--------------|
|        1 |       325.00 |
|        2 |       241.00 |
+----------+--------------+

RESULTSET を使用するその他の例

以下は RESULTSET を使った例です。

言語: 日本語