【Snowflake】SQL COPYコマンドを用いてデータをロードする方法

2023年5月26日

当ページには広告が含まれています。

こんにちは、DXCEL WAVEの運営者(@dxcelwave)です！

こんな方におすすめ！

Snowflakeに興味がある
COPY INTOコマンドの基本的な使い方および各種オプションについて詳しく学びたい

Snowflake（スノーフレーク）とは

Snowflake公式：https://www.snowflake.com/ja/

Snowflakeとは、クラウド上で動作するSaas型のデータウェアハウスです。Snowflakeをもとにサイロ化されたデータを一元管理でき、データアクセス性とデータ利活用の双方を効率化することができます。

Snowflakeには次のような特徴があります。

1. クラウドネイティブ

Snowflakeは、クラウド上で動作するデータクラウドです。

Amazon Web Services（AWS）、Microsoft Azure、Google Cloud Platform（GCP）のいずれかのクラウドプロバイダーにホストされています。

2. マルチクラウド

Snowflakeは、AWS、Azure、GCPのいずれかのクラウドプラットフォームでホストされているため、ユーザーは自身のビジネスニーズに応じて異なるプラットフォームが選択可能です。

3. ストレージとコンピューティングの分離

Snowflakeは、ストレージとコンピューティングを分離しています。これにより、必要に応じてストレージとコンピューティングのリソースを独立して拡張できます。

4. ユーザー管理

Snowflakeは、ロールベースのアクセス制御を提供しています。

データ利用のユースケースや会社の部門・チーム単位でのアクセス管理も可能です。

5. パフォーマンス

Snowflakeは、高速かつスケーラブルなパフォーマンスを提供しています。ストレージとコンピューティングの分離により、必要に応じてリソースを拡張できます。そのため、データ処理能力とコストというトレードオフな関係も考慮し、状況に応じたパフォーマンスの最適化も可能です。

6. データセキュリティ

Snowflake上のデータは暗号化され、セキュリティの高い状態で保管されています。

また、指定のユーザー・ロールがのみデータアクセス可能とする機能やデータマスキング機能などセキュリティ対策に特化した機能も多数提供されています。

【参考】Snowflakeの学習におすすめの教材

Sowflakeについて詳しく学びたい方向けに、おすすめの学習講座をご紹介します。現時点で英語版が多いもののUdemyでは多数の優良教材が用意されています。

【Snowflake】COPY INTOコマンドを用いたSQL基本操作

それでは実際にSnowflakeの画面を開き、COPY INTOコマンドを用いたデータロード方法について解説していきます。コマンド解説に際して、次のようなコードを利用します。

ひな型コード

COPY INTO      <テーブル名>                        -- コピー先のテーブル
FROM           <外部ステージ>                      -- コピー元のステージ
FILES       =  (<ファイルパス1>, <ファイルパス2>)    -- データソースとなるファイルパス 
ON_ERROR    =  CONTINUE                          -- コピー時のエラーハンドリング
FILE_FORMAT = <ファイルフォーマット名>;              -- 外部ステージのファイル読込時に指定するフォーマット

ひな型コード（例：Amazon S3上のファイルを対象テーブルにコピー）

例えば、SAMPLE_DB.PUBLIC.TEST_TABLEというテーブルに、AmazonS3バケット(s3://snowflake/)からロードしたtest_data.csvをコピーする場合、次のように記載できます。

COPY INTO SAMPLE_DB.PUBLIC.TEST_TABLE                
FROM          's3://snowflake/'
FILES       = ('test_data.csv')
ON_ERROR    =  CONTINUE           
FILE_FORMAT = (type            = csv 
               field_delimiter = ',' 
               skip_header     = 1
               );

この例は、S3から誰でも見れる公開URLが発行されている場合に有効なコマンドです。

ひな型コード（例：Snowflakeステージにあるファイルを対象テーブルにコピー）

AWSのIAMを通じてSnowflakeとS3間でのアクセス制御を行なっている場合、一度S3のデータをSnowflakeの外部ステージにロードし、それをコピー元データとして利用することが多いでしょう。

例えば、SAMPLE_DB.PUBLIC.TEST_TABLEというテーブルに、外部ステージオブジェクトDATABASE.EXTERNAL_STAGEにあるフォルダcsv_folder内にあるファイルを全てコピーする場合、次のように記載できます。

COPY INTO SAMPLE_DB.PUBLIC.TEST_TABLE                
FROM           @DATABASE.EXTERNAL_STAGE.csv_folder
ON_ERROR    =  CONTINUE           
FILE_FORMAT = (type            = csv 
               field_delimiter = ',' 
               skip_header     = 1
               );

【Snowflake】COPY INTOコマンドのオプション解説

COPY INTOコマンドには様々なオプションも指定できます。以下、１つずつ詳しくみていきましょう。

ON_ERROR

ON_ERRORとは、外部ステージからファイル読込中にエラーが発生した場合、どのようなエラーハンドリングを行うか指定するオプションです。

コード

COPY INTO      <テーブル名>                        
FROM           <外部ステージ>                   
FILES       =  (<ファイルパス1>, <ファイルパス2>)   
ON_ERROR    =  CONTINUE                          -- コピー時のエラーハンドリング
FILE_FORMAT = <ファイルフォーマット名>;

ON_ERROR

値	概要
CONTINUE	エラーが見つかった場合もファイルのロードを続行。一方、エラーが見つかった行はロードされない。
SKIP_FILE	エラーが見つかった場合、そのファイル自体のロードをスキップ。
SKIP_FILE_数値（例 SKIP_FILE_10）	ファイル内で見つかったエラー行の数が指定された数以上の場合、ファイルをスキップ。
SKIP_FILE_数値% （例 SKIP_FILE_10%）	ファイル内で見つかったエラー行の割合が指定された割合を超えた場合、ファイルをスキップ。
ABORT_STATEMENT	データファイルでエラーが見つかった場合、ロード操作中止。

RETURN_FAILED_ONLY

RETURN_FAILED_ONLYとは、ファイルロード時にエラーが発生時、結果を通知するかどうか指定できるオプションです。通知する場合True、通知しない場合Falseを指定します。

COPY INTO      <テーブル名>                        
FROM           <外部ステージ>                   
FILES              =  (<ファイルパス1>, <ファイルパス2>)
FILE_FORMAT        = <ファイルフォーマット名>
RETURN_FAILED_ONLY = TRUE | FALSE;

VALIDATION_MODE

VALIDATION_MODEとは、COPYコマンドにデータファイルを検証するよう指示するオプションです。

COPYコマンドを用いると、通常ファイルをロードしますが、VALIDATION_MODE=Trueに設定することで、ファイルのエラー有無を検証し指定された検証オプションに基づいて結果を返すようになり、ファイルのロードはしないようになります。

コード

COPY INTO      <テーブル名>                        
FROM           <外部ステージ>                   
FILES           =  (<ファイルパス1>, <ファイルパス2>)
FILE_FORMAT     = <ファイルフォーマット名>
VALIDATION_MODE = RETURN_n_ROWS | RETURN_ERRORS;

VALIDATION MODE

検証オプション	概要
RETURN_n_ROWS （例：RETURN_10_ROWS）	エラーが発生しない場合、指定された行数を検証。発生した場合、行で最初に発生したエラー有という結果を返す。
RETURN_ERRORS	すべてのファイルで発生したすべてのエラーを返す。
RETURN_ALL_ERRORS	ロード中に ON_ERRORオプションが CONTINUEに設定の場合、以前のロード中に部分的にロードされたエラーを含む、すべてのファイルの全エラーを返す。

TRUNCATECOLUMNS

TRUNCATECOLUMNSとは、コピー先テーブルの文字列型（CHAR）の項目にデータをコピーする場合に、文字数超過したデータが混入していた場合の対処法を指定するオプションです。

Trueの場合は指定の文字数におさまるように切り捨て処理を実行し、Falseの場合はエラーを返しデータをロードしないようにします。

COPY INTO      <テーブル名>                        
FROM           <外部ステージ>                   
FILES           =  (<ファイルパス1>, <ファイルパス2>)
FILE_FORMAT     = <ファイルフォーマット名>
TRUNCATECOLUMNS = TRUE | FALSE;

FORCE

COPYオプションを対象テーブルに対して複数回実行した場合において、以前ロードしたことのあるデータは、ロードをスキップし、データ重複のないようにしてくれます。

FORCEとは、以前にデータがロードされたかどうかに関係なく、コピー元のすべてのファイルをロードするよう指定できるオプションです。

COPY INTO      <テーブル名>                        
FROM           <外部ステージ>                   
FILES       =  (<ファイルパス1>, <ファイルパス2>)
FILE_FORMAT = <ファイルフォーマット名>
FORCE       =  TRUE | FALSE;

SIZE LIMIT

SIZE LIMITとは、データコピー時に最大サイズを指定できるオプションです。

COPY INTO      <テーブル名>                        
FROM           <外部ステージ>                   
FILES       =  (<ファイルパス1>, <ファイルパス2>)
FILE_FORMAT = <ファイルフォーマット名>
SIZE_LIMIT  = num;