Transform data during a load

Snowflakeは、 COPY INTO <テーブル> コマンドを使用してデータをテーブルにロードする際のデータの変換をサポートし、基本的な変換のための ETL パイプラインを劇的に簡素化します。この機能により、データのロード中に列を並べ替える際に、一時テーブルを使用して事前に変換されたデータを保存する必要がなくなります。この機能は、一括ロードとSnowpipeの両方に適用されます。

COPY コマンドは以下をサポートします。

  • SELECT ステートメントを使用した列の並べ替え、列の省略、およびキャスト。データファイルに、ターゲットテーブルと同じ数と列の順序を持たせる必要はありません。

  • ENFORCE_LENGTH | TRUNCATECOLUMNS オプションは、ターゲット列の長さを超えるテキスト文字列を切り捨てることができます。

ステージングされたデータファイルのクエリに関する一般情報については、 Query data in staged files をご参照ください。

使用上の注意

このセクションでは、ロード中にステージングされたデータファイルを変換するための使用情報を提供します。

サポートされているファイル形式

次のファイル形式タイプは、 COPY 変換でサポートされています。

  • CSV

  • JSON

  • Avro

  • ORC

  • Parquet

  • XML

ステージングされたデータファイルを解析するには、そのファイル形式を記述する必要があります。

CSV:

デフォルトの形式は、文字区切りの UTF -8テキストです。デフォルトのフィールド区切り文字はコンマ文字（,）です。デフォルトのレコード区切り文字は改行文字です。ソースデータが別の形式の場合は、ファイル形式のタイプとオプションを指定します。

ステージングされたデータファイルをクエリする場合、 ERROR_ON_COLUMN_COUNT_MISMATCH オプションは無視されます。データファイルは、ターゲットテーブルと同数および同順序の列である必要はありません。

他のすべてのファイル形式タイプ:

データファイルに一致する形式タイプとオプションを指定します。

ファイル形式のオプションを明示的に指定するには、次のいずれかの方法で設定します。

SELECT ステートメントを使用したステージングデータファイルのクエリ：

  • 名前付きファイル形式またはステージオブジェクトに指定されたファイル形式のオプションとして。名前付きファイル形式/ステージオブジェクトを SELECT ステートメントで参照できるようになります。

COPY INTO <テーブル> ステートメント： を使用した、ステージングされたデータファイルからの列のロード

  • COPYINTO <table> ステートメントで、直接指定されたファイル形式のオプションとして。

  • 名前付きファイル形式またはステージオブジェクトに指定されたファイル形式のオプションとして。名前付きファイル形式/ステージオブジェクトは、 COPY INTO <テーブル> ステートメントで参照できるようになります。

サポートされている関数

Snowflakeは現在、 COPY 変換用に次の関数サブセットをサポートしています。

特に、 VALIDATE 関数は COPY INTO <テーブル> ステートメントの SELECT リストを無視することに注意してください。この関数は、ステートメントで参照されているファイルを解析し、解析エラーを返します。関数が COPY INTO <テーブル> 式のコンテキストでファイルを評価することを期待している場合、この動作は驚くべきものになる可能性があります。

COPY 変換は、 FLATTEN 関数、または JOIN、 または GROUP BY （集計）構文をサポート しません。

サポートされる関数のリストは、時間の経過とともに拡大する可能性があります。

次のカテゴリの関数もサポートされています。

注釈

Scalar SQL UDFs の場合、Snowflakeでは変換エラー処理のサポートが限られており、一貫性のない、または予期しない ON_ERROR コピーオプションの動作に遭遇する可能性があります。

Filter results

WHERE 句を使用した FROM 句の結果のフィルタリングはサポートされていません。SELECT ステートメントの ORDER BY、 LIMIT、 FETCH、 TOP キーワードもサポートされていません。

SELECT ステートメントの DISTINCT キーワードは完全にはサポートされていません。キーワードを指定すると、一貫性のない、または予期しない ON_ERROR コピーオプションの動作が発生する可能性があります。

VALIDATION_MODE パラメーター

VALIDATION_MODE パラメーターは、ロード中にデータを変換する COPY ステートメントをサポートしません。

CURRENT_TIME、 CURRENT_TIMESTAMP デフォルトの列値

ロード時間をキャプチャするために CURRENT_TIME、CURRENT_TIMESTAMP のデフォルトの列値を使用する代わりに、 METADATA$START_SCAN_TIME をクエリして記録の正確なロード時間値を取得することをお勧めします。詳細については、 Query metadata for staged files をご参照ください。

MATCH_BY_COLUMN_NAME copy option

ロード中のデータ変換に MATCH_BY_COLUMN_NAME コピーオプションと SELECT ステートメントを合わせて使用することは、すべての場合において許可されていません。この2つのオプションを個別に使用することはできますが、併用することはできません。これを実行しようとすると、エラー、 SQL compilation error: match_by_column_name is not supported with copy transform が発生します.

Transform CSV data

テーブルデータのサブセットのロード

データのサブセットをテーブルにロードします。不足している列については、Snowflakeはデフォルト値を挿入します。次の例では、ステージングされた　CSV　ファイルの列1、2、6、および7からデータをロードします。

copy into home_sales(city, zip, sale_date, price)
   from (select t.$1, t.$2, t.$6, t.$7 from @mystage/sales.csv.gz t)
   FILE_FORMAT = (FORMAT_NAME = mycsvformat);

ロード中の CSV 列の並べ替え

次の例では、テーブルにロードする前に、ステージングされた CSV ファイルの列データを並べ替えます。さらに、 COPY ステートメントは SUBSTR , SUBSTRING 関数を使用して、文字列を挿入する前に最初の数文字を削除します。

copy into home_sales(city, zip, sale_date, price)
   from (select SUBSTR(t.$2,4), t.$1, t.$5, t.$4 from @mystage t)
   FILE_FORMAT = (FORMAT_NAME = mycsvformat);

ロード中のデータ型の変換

データのロード中にステージングされたデータを他のデータ型に変換します。すべての 変換関数 がサポートされています。

たとえば、それぞれ TO_BINARY、 TO_DECIMAL , TO_NUMBER , TO_NUMERIC、および TO_TIMESTAMP / TO_TIMESTAMP_* 関数を使用して、文字列をバイナリ値、10進数、またはタイムスタンプとして変換します。

サンプル CSV ファイル:

snowflake,2.8,2016-10-5
warehouse,-12.3,2017-01-23

SQL ステートメント:

-- Stage a data file in the internal user stage
PUT file:///tmp/datafile.csv @~;

-- Query the staged data file
select t.$1,t.$2,t.$3 from @~/datafile.csv.gz t;

-- Create the target table
create or replace table casttb (
  col1 binary,
  col2 decimal,
  col3 timestamp_ntz
  );

-- Convert the staged CSV column data to the specified data types before loading it into the destination table
copy into casttb(col1, col2, col3)
from (
  select to_binary(t.$1, 'utf-8'),to_decimal(t.$2, '99.9', 9, 5),to_timestamp_ntz(t.$3)
  from @~/datafile.csv.gz t
)
file_format = (type = csv);

-- Query the target table
select * from casttb;

+--------------------+------+-------------------------+
| COL1               | COL2 | COL3                    |
|--------------------+------+-------------------------|
| 736E6F77666C616B65 |    3 | 2016-10-05 00:00:00.000 |
| 77617265686F757365 |  -12 | 2017-01-23 00:00:00.000 |
+--------------------+------+-------------------------+

ロードされたデータへのシーケンス列の追加

CREATE SEQUENCE を使用してシーケンスオブジェクトを作成します。COPY コマンドを使用してデータをテーブルにロードする場合、 NEXTVAL 式を使用してオブジェクトにアクセスし、ターゲット番号列のデータをシーケンスします。クエリでのシーケンスの使用の詳細については、 シーケンスの使用 をご参照ください。

-- Create a sequence
create sequence seq1;

-- Create the target table
create or replace table mytable (
  col1 number default seq1.nextval,
  col2 varchar,
  col3 varchar
  );

-- Stage a data file in the internal user stage
PUT file:///tmp/myfile.csv @~;

-- Query the staged data file
select $1, $2 from @~/myfile.csv.gz t;

+-----+-----+
| $1  | $2  |
|-----+-----|
| abc | def |
| ghi | jkl |
| mno | pqr |
| stu | vwx |
+-----+-----+

-- Include the sequence nextval expression in the COPY statement
copy into mytable (col1, col2, col3)
from (
  select seq1.nextval, $1, $2
  from @~/myfile.csv.gz t
)
;

select * from mytable;

+------+------+------+
| COL1 | COL2 | COL3 |
|------+------+------|
|    1 | abc  | def  |
|    2 | ghi  | jkl  |
|    3 | mno  | pqr  |
|    4 | stu  | vwx  |
+------+------+------+

ロードされたデータへのAUTOINCREMENT / IDENTITY列の追加

数値列の AUTOINCREMENT または IDENTITY のデフォルト値を設定します。COPY コマンドを使用してテーブルにデータをロードする場合、 SELECT ステートメントの列を省略します。このステートメントは自動的にデータを列に取り込みます。

-- Create the target table
create or replace table mytable (
  col1 number autoincrement start 1 increment 1,
  col2 varchar,
  col3 varchar
  );

-- Stage a data file in the internal user stage
PUT file:///tmp/myfile.csv @~;

-- Query the staged data file
select $1, $2 from @~/myfile.csv.gz t;

+-----+-----+
| $1  | $2  |
|-----+-----|
| abc | def |
| ghi | jkl |
| mno | pqr |
| stu | vwx |
+-----+-----+

-- Omit the sequence column in the COPY statement
copy into mytable (col2, col3)
from (
  select $1, $2
  from @~/myfile.csv.gz t
)
;

select * from mytable;

+------+------+------+
| COL1 | COL2 | COL3 |
|------+------+------|
|    1 | abc  | def  |
|    2 | ghi  | jkl  |
|    3 | mno  | pqr  |
|    4 | stu  | vwx  |
+------+------+------+

Transform semi-structured data

このセクションの例は、特に断りのない限り、半構造化データ型に適用されます。

半構造化データの個別の列へのロード

次の例では、ステージングされた半構造化ファイルから異なるデータ型の個別のテーブル列に、繰り返しの要素をロードします。

この例では、次の半構造化データをリレーショナルテーブルの個別の列にロードし、 location オブジェクト値を VARIANT 列にロードし、残りの値をリレーショナル列にロードします。

-- Sample data:
{"location": {"city": "Lexington","zip": "40503"},"dimensions": {"sq_ft": "1000"},"type": "Residential","sale_date": "4-25-16","price": "75836"},
{"location": {"city": "Belmont","zip": "02478"},"dimensions": {"sq_ft": "1103"},"type": "Residential","sale_date": "6-18-16","price": "92567"},
{"location": {"city": "Winchester","zip": "01890"},"dimensions": {"sq_ft": "1122"},"type": "Condo","sale_date": "1-31-16","price": "89921"}

次の SQL ステートメントは、内部ステージ mystage からファイル sales.json をロードします。

注釈

この例では JSON データをロードしますが、他の型（例: Avro、 ORC）の半構造化データをロードする場合の SQL ステートメントも同様です。

Parquetデータを使用する追加の例については、 Parquetデータを個別の列にロードする （このトピック内）をご参照ください。

 -- Create an internal stage with the file type set as JSON.
 CREATE OR REPLACE STAGE mystage
   FILE_FORMAT = (TYPE = 'json');

 -- Stage a JSON data file in the internal stage.
 PUT file:///tmp/sales.json @mystage;

 -- Query the staged data. The data file comprises three objects in NDJSON format.
 SELECT t.$1 FROM @mystage/sales.json.gz t;

 +------------------------------+
 | $1                           |
 |------------------------------|
 | {                            |
 |   "dimensions": {            |
 |     "sq_ft": "1000"          |
 |   },                         |
 |   "location": {              |
 |     "city": "Lexington",     |
 |     "zip": "40503"           |
 |   },                         |
 |   "price": "75836",          |
 |   "sale_date": "2022-08-25", |
 |   "type": "Residential"      |
 | }                            |
 | {                            |
 |   "dimensions": {            |
 |     "sq_ft": "1103"          |
 |   },                         |
 |   "location": {              |
 |     "city": "Belmont",       |
 |     "zip": "02478"           |
 |   },                         |
 |   "price": "92567",          |
 |   "sale_date": "2022-09-18", |
 |   "type": "Residential"      |
 | }                            |
 | {                            |
 |   "dimensions": {            |
 |     "sq_ft": "1122"          |
 |   },                         |
 |   "location": {              |
 |     "city": "Winchester",    |
 |     "zip": "01890"           |
 |   },                         |
 |   "price": "89921",          |
 |   "sale_date": "2022-09-23", |
 |   "type": "Condo"            |
 | }                            |
 +------------------------------+

 -- Create a target table for the data.
 CREATE OR REPLACE TABLE home_sales (
   CITY VARCHAR,
   POSTAL_CODE VARCHAR,
   SQ_FT NUMBER,
   SALE_DATE DATE,
   PRICE NUMBER
 );

 -- Copy elements from the staged file into the target table.
 COPY INTO home_sales(city, postal_code, sq_ft, sale_date, price)
 FROM (select
 $1:location.city::varchar,
 $1:location.zip::varchar,
 $1:dimensions.sq_ft::number,
 $1:sale_date::date,
 $1:price::number
 FROM @mystage/sales.json.gz t);

 -- Query the target table.
 SELECT * from home_sales;

+------------+-------------+-------+------------+-------+
| CITY       | POSTAL_CODE | SQ_FT | SALE_DATE  | PRICE |
|------------+-------------+-------+------------+-------|
| Lexington  | 40503       |  1000 | 2022-08-25 | 75836 |
| Belmont    | 02478       |  1103 | 2022-09-18 | 92567 |
| Winchester | 01890       |  1122 | 2022-09-23 | 89921 |
+------------+-------------+-------+------------+-------+

Parquetデータの個別列へのロード

前の例と同様ですが、Parquet形式のファイルから半構造化データをロードします。この例は、Apache Parquetに精通しているユーザー向けに提供されています。

-- Create a file format object that sets the file format type. Accept the default options.
create or replace file format my_parquet_format
  type = 'parquet';

-- Create an internal stage and specify the new file format
create or replace temporary stage mystage
  file_format = my_parquet_format;

-- Create a target table for the data.
create or replace table parquet_col (
  custKey number default NULL,
  orderDate date default NULL,
  orderStatus varchar(100) default NULL,
  price varchar(255)
);

-- Stage a data file in the internal stage
put file:///tmp/mydata.parquet @mystage;

-- Copy data from elements in the staged Parquet file into separate columns
-- in the target table.
-- Note that all Parquet data is stored in a single column ($1)
-- SELECT list items correspond to element names in the Parquet file
-- Cast element values to the target column data type
copy into parquet_col
  from (select
  $1:o_custkey::number,
  $1:o_orderdate::date,
  $1:o_orderstatus::varchar,
  $1:o_totalprice::varchar
  from @mystage/mydata.parquet);

-- Query the target table
SELECT * from parquet_col;

+---------+------------+-------------+-----------+
| CUSTKEY | ORDERDATE  | ORDERSTATUS | PRICE     |
|---------+------------+-------------+-----------|
|   27676 | 1996-09-04 | O           | 83243.94  |
|  140252 | 1994-01-09 | F           | 198402.97 |
...
+---------+------------+-------------+-----------+

半構造化データのフラット化

FLATTEN は、 VARIANT、 OBJECT、または ARRAY 列の側面ビューを生成するテーブル関数です。 半構造化データの個別の列へのロード のサンプルデータを使用して、オブジェクトの各要素に個別の行を持つテーブルを作成します。

-- Create an internal stage with the file delimiter set as none and the record delimiter set as the new line character
create or replace stage mystage
  file_format = (type = 'json');

-- Stage a JSON data file in the internal stage with the default values
put file:///tmp/sales.json @mystage;

-- Create a table composed of the output from the FLATTEN function
create or replace table flattened_source
(seq string, key string, path string, index string, value variant, element variant)
as
  select
    seq::string
  , key::string
  , path::string
  , index::string
  , value::variant
  , this::variant
  from @mystage/sales.json.gz
    , table(flatten(input => parse_json($1)));

  select * from flattened_source;

+-----+-----------+-----------+-------+-------------------------+-----------------------------+
| SEQ | KEY       | PATH      | INDEX | VALUE                   | ELEMENT                     |
|-----+-----------+-----------+-------+-------------------------+-----------------------------|
| 1   | location  | location  | NULL  | {                       | {                           |
|     |           |           |       |   "city": "Lexington",  |   "location": {             |
|     |           |           |       |   "zip": "40503"        |     "city": "Lexington",    |
|     |           |           |       | }                       |     "zip": "40503"          |
|     |           |           |       |                         |   },                        |
|     |           |           |       |                         |   "price": "75836",         |
|     |           |           |       |                         |   "sale_date": "2017-3-5",  |
|     |           |           |       |                         |   "sq__ft": "1000",         |
|     |           |           |       |                         |   "type": "Residential"     |
|     |           |           |       |                         | }                           |
...
| 3   | type      | type      | NULL  | "Condo"                 | {                           |
|     |           |           |       |                         |   "location": {             |
|     |           |           |       |                         |     "city": "Winchester",   |
|     |           |           |       |                         |     "zip": "01890"          |
|     |           |           |       |                         |   },                        |
|     |           |           |       |                         |   "price": "89921",         |
|     |           |           |       |                         |   "sale_date": "2017-3-21", |
|     |           |           |       |                         |   "sq__ft": "1122",         |
|     |           |           |       |                         |   "type": "Condo"           |
|     |           |           |       |                         | }                           |
+-----+-----------+-----------+-------+-------------------------+-----------------------------+

半構造化要素の分割と VARIANT 値としての個別の列へのロード

半構造化データの個別の列へのロード の手順に従って、半構造化データの個々の要素をターゲットテーブルの異なる列にロードできます。さらに、 SPLIT 関数を使用して、セパレーターを含む要素値を分割し、それらを配列としてロードできます。

たとえば、繰り返し要素のドット区切りで IP アドレスを分割します。IP アドレスを個別の列の配列としてロードします。

-- Create an internal stage with the file delimiter set as none and the record delimiter set as the new line character
create or replace stage mystage
  file_format = (type = 'json');

-- Stage a semi-structured data file in the internal stage
put file:///tmp/ipaddress.json @mystage auto_compress=true;

-- Query the staged data
select t.$1 from @mystage/ipaddress.json.gz t;

+----------------------------------------------------------------------+
| $1                                                                   |
|----------------------------------------------------------------------|
| {"ip_address": {"router1": "192.168.1.1","router2": "192.168.0.1"}}, |
| {"ip_address": {"router1": "192.168.2.1","router2": "192.168.3.1"}}  |
+----------------------------------------------------------------------+

-- Create a target table for the semi-structured data
create or replace table splitjson (
  col1 array,
  col2 array
  );

-- Split the elements into individual arrays using the SPLIT function and load them into separate columns
-- Note that all JSON data is stored in a single column ($1)
copy into splitjson(col1, col2)
from (
  select split($1:ip_address.router1, '.'),split($1:ip_address.router2, '.')
  from @mystage/ipaddress.json.gz t
);

-- Query the target table
select * from splitjson;

+----------+----------+
| COL1     | COL2     |
|----------+----------|
| [        | [        |
|   "192", |   "192", |
|   "168", |   "168", |
|   "1",   |   "0",   |
|   "1"    |   "1"    |
| ]        | ]        |
| [        | [        |
|   "192", |   "192", |
|   "168", |   "168", |
|   "2",   |   "3",   |
|   "1"    |   "1"    |
| ]        | ]        |
+----------+----------+