SnowflakeのCOPYコマンド実行時にロードするファイルを指定してみた

「カスタマーストーリー」を支えるツール特集

#Snowflake

#ビジネス・アナリティクス

#ビッグデータ

エノカワ

2021.06.17

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは！エノカワです。

SnowflakeのCOPYコマンド実行時のオプションとして、FILESとPATTERNがあります。
いずれもロードするファイルを指定するオプションですが、指定方法が異なります。

FILES：ファイル名のリストを指定する
PATTERN：ファイル名やパスを正規表現パターン文字列で指定する

今回は、この2つのオプションを実際に試してみました。

使用するデータ

Citi Bikeというシェアサイクリングサービスの利用者に関するデータを使用します。
以下の記事を参考にロードするデータの準備を行います。

ロードするデータの準備が完了すると、以下が作成された状態になります。

データベース：CITIBIKE
テーブル：TRIPS
ステージ：CITIBIKE_TRIPS
ファイル形式：CSV

全てのファイルをロード

まずは全てのファイルをロードしてみましょう。

ワークシート上で下記SQLを実行し、コンテキストを設定します。

// コンテキスト設定
USE ROLE SYSADMIN;
USE WAREHOUSE COMPUTE_WH;
USE CITIBIKE;
USE SCHEMA PUBLIC;

実際にロードを行う前にLISTコマンドでステージにあるファイルを確認します。

// ステージングされたファイルを確認
list @CITIBIKE_TRIPS;

CITIBIKE_TRIPSステージに376個のファイルがあることが確認できました。

それでは、COPYコマンドでTRIPS_FILESテーブルに全てのファイルをロードしてみましょう。
FILESやPATTERNオプションは指定しません。

// 全てのファイルをロード
copy into trips from @citibike_trips
file_format=CSV;

376個のファイルがロードされました。
ステージにある全てのファイルがロードされたことになります。

次は、オプションを使用してロードするファイルを指定してみましょう。

FILESオプションによるファイル指定

まずは、FILESオプションです。

先ほどロードしたTRIPSテーブルと区別するために、TRIPS_FILESテーブルを作成します。

// FILESオプション検証用テーブル作成
create table trips_files like trips;

それでは、COPYコマンドでTRIPS_FILESテーブルにロードしてみましょう。
FILESオプションを指定して、2個のファイルだけロードします。

// FILESオプションでファイルを指定してロード
copy into trips_files from @citibike_trips
  file_format = CSV
  files = ('/trips_2013_0_0_0.csv.gz', '/trips_2013_0_1_0.csv.gz');

FILESオプションで指定した2個のファイルだけロードされました。

PATTERNオプションによるファイル指定

次に、PATTERNオプションです。

他のテーブルと区別するためにTRIPS_PATTERNテーブルを作成します。

// PATTERNオプション検証用テーブル作成
create table trips_pattern like trips;

実際にロードを行う前に正規表現パターン文字列にマッチするファイルを確認します。

LISTコマンドにもPATTERNオプションがあるので、
試しにcitibike-trips/trips_2013_0_のパスで始まるファイルを確認してみます。

// LISTコマンドで対象となるファイルを確認
list @citibike_trips
  pattern = 'citibike-trips/trips_2013_0_.*';

8個のファイルがマッチしました。

それでは、COPYコマンドでTRIPS_PATTERNテーブルにロードしてみましょう。
PATTERNオプションでcitibike-trips/trips_2013_0_のパスで始まるファイルを指定します。

// PATTERNオプションでファイルを指定してロード
copy into trips_pattern from @citibike_trips
  file_format = CSV
  pattern = 'citibike-trips/trips_2013_0_.*';

正規表現パターン文字列にマッチする8個のファイルだけロードされました。
LSITコマンドで確認したファイルと同じであることが確認できます。

【応用】ファイルパスごとにファイル指定

最後に、応用としてファイルパスごとにロードするファイルを指定してみましょう。

ファイル名のパターンを確認する

CITIBIKE_TRIPSステージにあるファイル名には西暦年の文字列が含まれています。
LISTコマンドでPATTERNオプションを指定して、2014年と2015年のファイルを確認してみましょう。

// 2014年のファイル一覧
list @citibike_trips
  pattern = 'citibike-trips/trips_2014.*';

// 2015年のファイル一覧
list @citibike_trips
  pattern = 'citibike-trips/trips_2015.*';

それぞれ64個のファイルが該当しました。

西暦年ごとに正規表現パターン文字列を変える

ここで、以下のシーンを想定してみましょう。

2014年：.*_0_[0-3]_0.csv.gzにマッチするファイルだけロードする
→上図の青枠で囲った4個のファイルが該当
2015年：.*_0_[4-7]_0.csv.gzにマッチするファイルだけロードする
→上図のオレンジ枠で囲った4個のファイルが該当

西暦ごとにPATTERNオプションに指定する正規表現パターン文字列を変えることで実現できそうです。

早速試してみましょう。

まず、検証用に2014年と2015年それぞれのテーブルを作成します。

// 2014年のファイルロード用のテーブル作成
create table trips_2014 like trips;

// 2015年のファイルロード用のテーブル作成
create table trips_2015 like trips;

次に、西暦年を区別するためにひと手間加えます。

ファイル名に西暦年が含まれることに着目し、西暦年ごとにステージを分けることを考えます。

ステージを作成する際に以下の設定を行います。

ステージ名：末尾に西暦年を加える
URL：西暦年まで含めたパスを指定する

こうすることで、ステージごとに対象となる西暦年のファイルに絞り込むことが狙いです。

// 2014年のファイルロード用のステージ作成
create stage citibike_trips_2014
  url = 's3://snowflake-workshop-lab/citibike-trips/trips_2014';

// 2015年のファイルロード用のステージ作成
create stage citibike_trips_2015
  url = 's3://snowflake-workshop-lab/citibike-trips/trips_2015';

それでは、COPYコマンドでそれぞれのテーブルにロードしてみましょう。
西暦年ごとにステージ名と正規表現パターン文字列を変えています。

// 各テーブルに指定ファイルをロード
copy into trips_2014 from @citibike_trips_2014
  file_format = CSV
  pattern = '.*_0_[0-3]_0.csv.gz';

copy into trips_2015 from @citibike_trips_2015
  file_format = CSV
  pattern = '.*_0_[4-7]_0.csv.gz';