ロード中のデータの変換¶
Snowflakeは、 COPY INTO <テーブル> コマンドを使用してデータをテーブルにロードする際のデータの変換をサポートし、基本的な変換のための ETL パイプラインを劇的に簡素化します。この機能により、データのロード中に列を並べ替える際に、一時テーブルを使用して事前に変換されたデータを保存する必要がなくなります。
COPY コマンドは以下をサポートします。
SELECT ステートメントを使用した列の並べ替え、列の省略、およびキャスト。データファイルに、ターゲットテーブルと同じ数と列の順序を持たせる必要はありません。
ENFORCE_LENGTH | TRUNCATECOLUMNS オプションは、ターゲット列の長さを超えるテキスト文字列を切り捨てることができます。
ステージングされたデータファイルのクエリに関する一般情報については、 ステージングされたファイルのデータのクエリ をご参照ください。
このトピックの内容：
使用上の注意¶
このセクションでは、ロード中にステージングされたデータファイルを変換するための使用情報を提供します。
サポートされているステージ¶
COPY 変換では、名前付きステージ（内部または外部）とユーザーステージのみがサポートされています。
サポートされているファイル形式¶
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エラーチェックを活用するには、フォーマットタイプとして CSV を設定します（デフォルト値）。CSV と同様に、ndjson準拠のデータでは、各行は個別のレコードです。Snowflakeは、各行を有効な JSON オブジェクトまたは配列として解析します。
次の形式タイプとオプションを指定します。
type = 'csv' field_delimiter = none record_delimiter = '\\n'
形式タイプとして JSON を指定できますが、 ON_ERROR オプションを設定して続行、またはファイルをスキップしても、変換のエラーによりCOPY 操作は停止します。
- 他のすべてのファイル形式タイプ
データファイルに一致する形式のタイプとオプションを指定します。
ファイル形式オプションを明示的に指定するには、次のいずれかの方法で設定します。
SELECT ステートメントを使用したステージングデータファイルのクエリ： |
|
COPY INTO** <テーブル> ステートメント： を使用した、ステージングされたデータファイルからの列のロード |
|
サポートされている関数¶
Snowflakeは現在、 COPY 変換用に次の関数サブセットをサポートしています。
-
この関数を使用して値を明示的にキャストする場合は、 DATE_FORMAT ファイル形式オプションも DATE_INPUT_FORMAT パラメーターも適用されないことに注意してください。
-
この関数を使用して値を明示的にキャストする場合は、 TIME_FORMAT ファイル形式オプションも TIME_INPUT_FORMAT パラメーターも適用されないことに注意してください。
-
この関数を使用して値を明示的にキャストする場合は、 TIMESTAMP_FORMAT ファイル形式オプションも TIMESTAMP_INPUT_FORMAT パラメーターも適用されないことに注意してください。
-
COPY INTO <テーブル> コマンドは、この関数のオプションである
形式引数をサポートしていないことに注意してください。 -
COPY INTO <テーブル> コマンドは、この関数のオプションである
形式引数をサポートしていないことに注意してください。 TRY_TO_TIMESTAMP / TRY_TO_TIMESTAMP_*
COPY INTO <テーブル> コマンドは、この関数のオプションである
形式引数をサポートしていないことに注意してください。
VALIDATE 関数のサポートがないことに特に注意してください。
COPY 変換は、 FLATTEN 関数、または JOIN、 または GROUP BY （集計）構文をサポート しません。
サポートされる機能のリストは、時間の経過とともに拡大する可能性があります。
次のカテゴリの関数もサポートされています。
Scalar SQLUDFs。
結果のフィルタリング¶
WHERE 句を使用した FROM 句の結果のフィルタリングはサポートされていません。SELECT ステートメントの LIMIT、 FETCH、 TOP キーワードもサポートされていません。
SELECT ステートメントの DISTINCT キーワードは完全にはサポートされていません。キーワードを指定すると、一貫性のない、または予期しない ON_ERROR コピーオプションの動作が発生する可能性があります。
VALIDATION_MODE パラメーター¶
VALIDATION_MODE パラメーターは、ロード中にデータを変換する COPY ステートメントをサポートしません。
CURRENT_TIME、 CURRENT_TIMESTAMP デフォルトの列値¶
デフォルト値が CURRENT_TIME（） または CURRENT_TIMESTAMP（） である列のロード時間をキャプチャするテーブルにデータをロードする場合、特定の COPY ステートメントを使用してロードされるすべての行には、同じタイムスタンプ値があります。その値には、 COPY ステートメントが開始された時間が記録されます。
例：
create or replace table mytable(
c1 timestamp DEFAULT current_timestamp(),
c2 number
);
copy into mytable(c2)
from (select t.$1 from @mystage/myfile.csv.gz t);
+-------------------------------+----------+
| C1 | C2 |
|-------------------------------+----------+
| 2018-09-05 08:58:28.718 -0700 | 1 |
...
| 2018-09-05 08:58:28.718 -0700 | 500 |
+-------------------------------+----------+
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 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;
-- 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": "2016-04-25", |
| "type": "Residential" |
| } |
| { |
| "dimensions": { |
| "sq_ft": "1103" |
| }, |
| "location": { |
| "city": "Belmont", |
| "zip": "02478" |
| }, |
| "price": "92567", |
| "sale_date": "2016-06-18", |
| "type": "Residential" |
| } |
| { |
| "dimensions": { |
| "sq_ft": "1122" |
| }, |
| "location": { |
| "city": "Winchester", |
| "zip": "01890" |
| }, |
| "price": "89921", |
| "sale_date": "2016-01-31", |
| "type": "Condo" |
| } |
+------------------------------+
-- Create a target table for the data
copy into home_sales(location, sq_ft, sale_date, price)
from (select
$1:location::varchar,
$1:dimensions.sq_ft::number,
$1:sale_date::date,
$1:price::varchar
from @mystage/sales.json.gz t);
-- Copy elements from the staged file into the target table
-- Note that the semi-structured data is stored in a single column ($1)
copy into home_sales(location, sq_ft, sale_date, price)
from (select $1:location, $1:dimensions.sq_ft, $1:sale_date, $1:price
from @mystage/sales.json.gz t);
-- Query the target table
SELECT * from home_sales;
+-------------------------+-------+-------------+--------------+-------+
| LOCATION | SQ_FT | TYPE | SALE_DATE | PRICE |
|-------------------------+-------+-------------+--------------+-------|
| { | 1000 | Residential | 2016-04-25 | 75836 |
| "city": "Lexington", | | | | |
| "zip": "40503" | | | | |
| } | | | | |
| { | 1103 | Residential | 2016-06-18 | 92567 |
| "city": "Belmont", | | | | |
| "zip": "02478" | | | | |
| } | | | | |
| { | 1122 | Residential | 2016-01-31 | 89921 |
| "city": "Winchester", | | | | |
| "zip": "01890" | | | | |
| } | | | | |
+-------------------------+-------+-------------+--------------+-------+
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" | | ] | ] | +----------+----------+