COPYコマンドによるテーブルへのデータロード時にバインド変数を使用できるようになりました #SnowflakeDB

モダンデータスタック(MDS)

#Snowflake

安原朋紀

2024.11.03

はじめに

2024年10月のアップデートで、バインド変数のサポートが拡張され、COPY INTO <table> ステートメントのステージ名やその他のパラメーターに対するバインド変数の使用がサポートされました。こちらの機能を試してみましたので、本記事で内容をまとめてみます。

アップデートの概要

Snowflake の SQL ステートメントでは、文字列リテラル、セッション変数やバインド変数などを使用し、オブジェクトを参照できます。

バインド変数とは、SQL などのクエリ内で値を動的に設定できるプレースホルダのことで、SQL ステートメント内に値を直接書き込む代わりに、変数を使うことで実行時に値を割り当てることができます。

これまでは Snowflake の SQL ステートメント内でステージやステージパス（@stage/path）は、変数識別子として使用することができませんでした。

そのため、例えば日次でのデータロード時に、日付などでステージのパスが構成されたり、ロード対象のファイル名が動的に変更されるような場合、何らかの対応が必要となります。
SQL を使用する場合の対応案の一つとして、Snowflake では文字列で定義されたSQLをEXECUTE IMMEDIATEコマンドで実行できるため、実行したい COPY コマンドの文字列を動的に生成し、そのコマンドを実行する、などの手順が必要でした。

しかし、今回のアップデートによって、パスやファイル名を含むステージ名やCOPYコマンド実行時のパラメータにもバインド変数が使えるようになったため、ストアドプロシージャのクエリ内にプレースホルダを埋め込み、パラメータとして指定することができるようになりました。

試してみる

本機能に関しては以下に記載がありますので、こちらを参考に進めます。また、ここではSnowflakeスクリプトを使用します。

事前準備

はじめに検証用のデータベース・スキーマ内に、ここでは内部ステージを作成しました。

--内部ステージを作成
CREATE OR REPLACE STAGE my_int_stage
    ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')
    DIRECTORY = (ENABLE = TRUE);

作成したステージに対して、ここでは年月日でパスが区切られる設定とし、以下のコマンドでファイルをアップロードしました。また、ファイル名にも年月日を含めてみています。

PUT file://C:/temp/data/sample_data_20241102.csv @my_int_stage/2024/11/02 AUTO_COMPRESS=TRUE;
PUT file://C:/temp/data/sample_data_20241103.csv @my_int_stage/2024/11/03 AUTO_COMPRESS=TRUE;
PUT file://C:/temp/data/sample_data_20241104.csv @my_int_stage/2024/11/04 AUTO_COMPRESS=TRUE;

ステージを確認

>ls @my_int_stage;
+-----------------------------------------------------+------+----------------------------------+------------------------------+
| name                                                | size | md5                              | last_modified                |
|-----------------------------------------------------+------+----------------------------------+------------------------------|
| my_int_stage/2024/11/02/sample_data_20241102.csv.gz |  193 | 1489717b4d633f8df7cfee85e719b811 | Sat, 2 Nov 2024 11:18:07 GMT |
| my_int_stage/2024/11/03/sample_data_20241103.csv.gz |  192 | 7cab0844061797b556c68d1d2c8d12ed | Sat, 2 Nov 2024 11:18:09 GMT |
| my_int_stage/2024/11/04/sample_data_20241104.csv.gz |  191 | 2619759dfbfe3aac3243128741bb7ed3 | Sat, 2 Nov 2024 11:18:09 GMT |
+-----------------------------------------------------+------+----------------------------------+------------------------------+
3 Row(s) produced. Time Elapsed: 0.224s

データロードに必要なファイルフォーマットとデータのロード先となるテーブルを以下の手順で定義しました。

--ファイルフォーマットを作成
CREATE OR REPLACE FILE FORMAT my_csv_format
  TYPE = CSV
  FIELD_DELIMITER = ','
  PARSE_HEADER = TRUE
  EMPTY_FIELD_AS_NULL = true
  COMPRESSION = gzip;

--スキーマ検出機能によりテーブルを定義
CREATE OR REPLACE TABLE mytable
    USING TEMPLATE (
        SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
        FROM TABLE(
            INFER_SCHEMA(
                LOCATION=>'@my_int_stage/',
                FILE_FORMAT=>'my_csv_format'
                )
            )
        );

作成されたテーブル定義は以下のようになっています。こちらにデータをロードしていきます。

>DESC TABLE mytable;
+----------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+
| name     | type              | kind   | null? | default | primary key | unique key | check | expression | comment | policy name | privacy domain |
|----------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------|
| Val      | NUMBER(10,9)      | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| Date     | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| Category | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| ID       | NUMBER(2,0)       | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
+----------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+
4 Row(s) produced. Time Elapsed: 0.094s

Snowflakeスクリプトを使用するストアドプロシージャを作成

年月日でパス・ファイル名が異なる各ファイルをロードするために、ここでは以下のストアドプロシージャを定義しました。

CREATE OR REPLACE PROCEDURE test_copy(target_path VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
    COPY INTO mytable
        FROM :target_path
        FILE_FORMAT=(FORMAT_NAME = 'my_csv_format')
        MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE ;
    RETURN 'Completed!';
EXCEPTION
    WHEN OTHER THEN RAISE;
END;
$$
;

ポイントは COPY コマンド内のFROM :target_path です。以下に記載がありますが、Snowflakeスクリプト内で引数を使用する際は、引数名の前にコロン（:）を記述します。
上記では COPY コマンドの FROM 句以降を変数とし、引数のtarget_path にロード対象のファイルパスを指定することで、ストアドプロシージャ呼び出し時に指定される値でファイルをロードします。

変数を定義しロード

ストアドプロシージャを定義したので、引数に指定する値を変数として以下のように定義しました。

SET file_path = (SELECT '@my_int_stage/' || LOWER(TO_VARCHAR(CURRENT_DATE(), 'yyyy/mm/dd/sample_data_yyyymmdd.csv.gz')));

中身は以下のようになっており、コマンド実行時の年月日からなるパス・ファイル名から構成されます。

>SELECT $file_path;
+------------------------------------------------------+
| $FILE_PATH                                           |
|------------------------------------------------------|
| @my_int_stage/2024/11/02/sample_data_20241102.csv.gz |
+------------------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.088s

定義した変数を引数にストアドプロシージャを呼び出します。

>CALL test_copy($file_path);
+------------+
| TEST_COPY  |
|------------|
| Completed! |
+------------+
1 Row(s) produced. Time Elapsed: 1.471s

結果を確認します。

テーブル

>SELECT * FROM MYTABLE;
+--------------+----+----------+------------+
|          Val | ID | Category | Date       |
|--------------+----+----------+------------|
|  1.114388475 |  1 | A        | 2024/11/02 |
| -0.617730236 |  2 | B        | 2024/11/02 |
| -1.226039849 |  3 | A        | 2024/11/02 |
| -1.249994022 |  4 | A        | 2024/11/02 |
|  0.775035178 |  5 | A        | 2024/11/02 |
|  0.442294860 |  6 | C        | 2024/11/02 |
|  0.819239543 |  7 | A        | 2024/11/02 |
| -0.921003054 |  8 | B        | 2024/11/02 |
| -0.288820276 |  9 | B        | 2024/11/02 |
|  0.752006217 | 10 | C        | 2024/11/02 |
+--------------+----+----------+------------+
10 Row(s) produced. Time Elapsed: 0.344s

コピー履歴

>SELECT FILE_NAME,STAGE_LOCATION,LAST_LOAD_TIME,ROW_COUNT,ROW_PARSED FROM TABLE(information_schema.copy_history(TABLE_NAME=>'mytable', START_TIME=> DATEADD(hours, -1, CURRENT_TIMESTAMP())));
+----------------------------------------+----------------------------------------------+-------------------------------+-----------+------------+
| FILE_NAME                              | STAGE_LOCATION                               | LAST_LOAD_TIME                | ROW_COUNT | ROW_PARSED |
|----------------------------------------+----------------------------------------------+-------------------------------+-----------+------------|
| 2024/11/02/sample_data_20241102.csv.gz | stages/9ed0fe1c-0ddd-4baf-b108-392a5a455ff6/ | 2024-11-02 20:40:15.827 +0900 |        10 |         10 |
+----------------------------------------+----------------------------------------------+-------------------------------+-----------+------------+
1 Row(s) produced. Time Elapsed: 0.356s

問題なくロードできました。

引数にサブクエリを使用する

ストアドプロシージャの引数にはサブクエリを使用できます。サンプルとして次の日付のファイルもステージにアップロードしているので、以下の通りストアドプロシージャを呼び出してみます。

>CALL test_copy('@my_int_stage/' || LOWER(TO_VARCHAR(CURRENT_DATE() + 1, 'yyyy/mm/dd/sample_data_yyyymmdd.csv.gz')));
+------------+
| TEST_COPY  |
|------------|
| Completed! |
+------------+
1 Row(s) produced. Time Elapsed: 0.753s

結果は以下の通りとなっており、指定（ここでは翌日の日付）のファイルをロードできました。

テーブル

>SELECT * FROM MYTABLE;
+--------------+----+----------+------------+
|          Val | ID | Category | Date       |
|--------------+----+----------+------------|
|  1.114388475 |  1 | A        | 2024/11/02 |
| -0.617730236 |  2 | B        | 2024/11/02 |
| -1.226039849 |  3 | A        | 2024/11/02 |
| -1.249994022 |  4 | A        | 2024/11/02 |
|  0.775035178 |  5 | A        | 2024/11/02 |
|  0.442294860 |  6 | C        | 2024/11/02 |
|  0.819239543 |  7 | A        | 2024/11/02 |
| -0.921003054 |  8 | B        | 2024/11/02 |
| -0.288820276 |  9 | B        | 2024/11/02 |
|  0.752006217 | 10 | C        | 2024/11/02 |
| -0.359878994 |  1 | A        | 2024/11/03 |
| -2.681319922 |  2 | B        | 2024/11/03 |
| -0.929509687 |  3 | B        | 2024/11/03 |
| -0.682002319 |  4 | A        | 2024/11/03 |
| -1.241710994 |  5 | C        | 2024/11/03 |
|  0.769089496 |  6 | B        | 2024/11/03 |
| -0.151905356 |  7 | B        | 2024/11/03 |
|  0.462443169 |  8 | C        | 2024/11/03 |
| -0.453144568 |  9 | A        | 2024/11/03 |
|  0.582974854 | 10 | B        | 2024/11/03 |
+--------------+----+----------+------------+
20 Row(s) produced. Time Elapsed: 0.144s

コピー履歴

>SELECT FILE_NAME,STAGE_LOCATION,LAST_LOAD_TIME,ROW_COUNT,ROW_PARSED FROM TABLE(information_schema.copy_history(TABLE_NAME=>'mytable', START_TIME=> DATEADD 
                                      (hours, -1, CURRENT_TIMESTAMP())));

+----------------------------------------+----------------------------------------------+-------------------------------+-----------+------------+
| FILE_NAME                              | STAGE_LOCATION                               | LAST_LOAD_TIME                | ROW_COUNT | ROW_PARSED |
|----------------------------------------+----------------------------------------------+-------------------------------+-----------+------------|
| 2024/11/02/sample_data_20241102.csv.gz | stages/9ed0fe1c-0ddd-4baf-b108-392a5a455ff6/ | 2024-11-02 20:40:15.827 +0900 |        10 |         10 |
| 2024/11/03/sample_data_20241103.csv.gz | stages/9ed0fe1c-0ddd-4baf-b108-392a5a455ff6/ | 2024-11-02 20:50:38.737 +0900 |        10 |         10 |
+----------------------------------------+----------------------------------------------+-------------------------------+-----------+------------+
2 Row(s) produced. Time Elapsed: 0.347s

もちろん直接テキストを指定しての実行も可能です。

>CALL test_copy('@my_int_stage/2024/11/03/sample_data_20241103.csv.gz');

+------------+
| TEST_COPY  |
|------------|
| Completed! |
+------------+
1 Row(s) produced. Time Elapsed: 0.567s

今回の設定では特に指定していませんが、制約に記載の COPY コマンド実行時の一部のオプションを除いて、同様に変数として指定できます。こちらについては、公式ドキュメントのサンプルクエリをご参照ください。

クエリ文字列を生成する場合

検証時と同じ設定で、バインド変数が使用できない場合は、例えば以下のようにクエリテキストを生成し、実行する必要がありました。パラメータが多い際は、エスケープなども必要な場合があり、手間となり得ました。

--クエリ文字列を作る場合
SET file_path = (SELECT '@my_int_stage/' || LOWER(TO_VARCHAR(CURRENT_DATE(), 'yyyy/mm/dd/sample_data_yyyymmdd.csv.gz')));
SELECT $file_path;

--COPYコマンド実行用のクエリを作成
SET query = 
'COPY INTO mytable' ||
' FROM ' || $file_path ||
' FILE_FORMAT = (
  FORMAT_NAME = \'my_csv_format\'
)
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;';

クエリを確認

>SELECT $query;
+---------------------------------------------------------------------------------------------+
| $QUERY                                                                                      |
|---------------------------------------------------------------------------------------------|
| COPY INTO mytable FROM @my_int_stage/2024/11/02/sample_data_20241102.csv.gz FILE_FORMAT = ( |
|   FORMAT_NAME = 'my_csv_format'                                                             |
| )                                                                                           |
| MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;                                                    |
+---------------------------------------------------------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.072s

実行

>EXECUTE IMMEDIATE $query;
+-----------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file                                                | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|-----------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| my_int_stage/2024/11/02/sample_data_20241102.csv.gz | LOADED |          10 |          10 |           1 |           0 | NULL        |             NULL |                  NULL | NULL           
+-----------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
1 Row(s) produced. Time Elapsed: 0.987s

制約

バインド変数の制限

バインド変数の制限については以下に記載があります。こちらによると、COPY INTO コマンドの以下のパラメータの値に対するバインド変数の使用できません。

CREDENTIALS
ENCRYPTION
FILE_FORMAT

アンロード

同様にアンロード時も可能か試してみましたが、例えば以下のようにパラメータのみ変数とする場合でもエラーとなり定義できませんでした。リリースノートでもCOPY INTO <table> statementsについてサポートとあるため、こちらは少なくとも執筆時点（2024年11月2日）ではサポートされていないのかと思います。

--アンロード用のストアドプロシージャを定義
CREATE OR REPLACE PROCEDURE test_unload(
    single BOOLEAN)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
    COPY INTO @my_int_stage/unload/
        FROM mytable 
        FILE_FORMAT = (FORMAT_NAME ='my_csv_format' COMPRESSION='NONE')
        SINGLE = :single ;
END
$$
;