ロード中のデータの変換¶
Snowflakeは、 COPY INTO <テーブル> コマンドを使用してデータをテーブルにロードする際のデータの変換をサポートし、基本的な変換のための ETL パイプラインを劇的に簡素化します。この機能により、データのロード中に列を並べ替える際に、一時テーブルを使用して事前に変換されたデータを保存する必要がなくなります。この機能は、一括ロードとSnowpipeの両方に適用されます。
COPY コマンドは以下をサポートします。
SELECT ステートメントを使用した列の並べ替え、列の省略、およびキャスト。データファイルに、ターゲットテーブルと同じ数と列の順序を持たせる必要はありません。
ENFORCE_LENGTH | TRUNCATECOLUMNS オプションは、ターゲット列の長さを超えるテキスト文字列を切り捨てることができます。
ステージングされたデータファイルのクエリに関する一般情報については、 ステージングされたファイルのデータのクエリ をご参照ください。
このトピックの内容:
使用上の注意¶
このセクションでは、ロード中にステージングされたデータファイルを変換するための使用情報を提供します。
サポートされているファイル形式¶
次のファイル形式タイプは、 COPY 変換でサポートされています。
CSV
JSON
Avro
ORC
Parquet
XML
ステージングされたデータファイルを解析するには、そのファイル形式を記述する必要があります。
- CSV:
デフォルトの形式は、文字区切りの UTF -8テキストです。デフォルトのフィールド区切り文字はコンマ文字（
,）です。デフォルトの記録区切り文字は改行文字です。ソースデータが別の形式の場合は、ファイル形式のタイプとオプションを指定します。ステージングされたデータファイルをクエリする場合、
ERROR_ON_COLUMN_COUNT_MISMATCHオプションは無視されます。データファイルは、ターゲットテーブルと同数および同順序の列である必要はありません。- JSON:
ロード操作中に JSON データを変換するには、データファイルを NDJSON （「改行区切り JSON」）標準形式で構成する必要があります。そうしないと、次のエラーが発生する場合があります。
Error parsing JSON: more than one document in the input- 他のすべてのファイル形式タイプ:
データファイルに一致する形式タイプとオプションを指定します。
ファイル形式のオプションを明示的に指定するには、次のいずれかの方法で設定します。
SELECT ステートメントを使用したステージングデータファイルのクエリ： |
|
COPY INTO <テーブル> ステートメント： を使用した、ステージングされたデータファイルからの列のロード |
|
サポートされている関数¶
Snowflakeは現在、 COPY 変換用に次の関数サブセットをサポートしています。
-
この関数を使用して値を明示的にキャストする場合は、 DATE_FORMAT ファイル形式オプションも DATE_INPUT_FORMAT パラメーターも適用されないことに注意してください。
-
この関数を使用して値を明示的にキャストする場合は、 TIME_FORMAT ファイル形式オプションも TIME_INPUT_FORMAT パラメーターも適用されないことに注意してください。
-
この関数を使用して値を明示的にキャストする場合は、 TIMESTAMP_FORMAT ファイル形式オプションも TIMESTAMP_INPUT_FORMAT パラメーターも適用されないことに注意してください。
-
COPY INTO <テーブル> コマンドは、この関数のオプションである
format引数をサポートしていないことに注意してください。 -
COPY INTO <テーブル> コマンドは、この関数のオプションである
format引数をサポートしていないことに注意してください。 TRY_TO_TIMESTAMP / TRY_TO_TIMESTAMP_*
COPY INTO <テーブル> コマンドは、この関数のオプションである
format引数をサポートしていないことに注意してください。
特に、 VALIDATE 関数は COPY INTO <テーブル> ステートメントの SELECT リストを無視することに注意してください。この関数は、ステートメントで参照されているファイルを解析し、解析エラーを返します。関数が COPY INTO <テーブル> 式のコンテキストでファイルを評価することを期待している場合、この動作は驚くべきものになる可能性があります。
COPY 変換は、 FLATTEN 関数、または JOIN、 または GROUP BY （集計）構文をサポート しません。
サポートされる関数のリストは、時間の経過とともに拡大する可能性があります。
次のカテゴリの関数もサポートされています。
Scalar SQLUDFs。
注釈
Scalar SQL UDFs の場合、Snowflakeでは変換エラー処理のサポートが限られており、一貫性のない、または予期しない ON_ERROR コピーオプションの動作に遭遇する可能性があります。
結果のフィルタリング¶
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 をクエリして記録の正確なロード時間値を取得することをお勧めします。詳細については、 ステージングされたファイルのメタデータのクエリ をご参照ください。
CSV データの変換¶
テーブルデータのサブセットのロード¶
データのサブセットをテーブルにロードします。不足している列については、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 | +------+------+------+
半構造化データの変換¶
このセクションの例は、特に断りのない限り、半構造化データ型に適用されます。
半構造化データの個別の列へのロード¶
次の例では、ステージングされた半構造化ファイルから異なるデータ型の個別のテーブル列に、繰り返しの要素をロードします。
この例では、次の半構造化データをリレーショナルテーブルの個別の列にロードし、 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" | | ] | ] | +----------+----------+