SnowflakeのCOPYコマンドでステージのファイルにデータをアンロードしてみた

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

Snowflakeでは、COPYコマンドでステージにあるファイルからテーブルにデータをロードできますが、
その逆、テーブルからステージにあるファイルにアンロード（エクスポート）することもできます。

今回は、COPYコマンドでアンロードを試してみました。

準備

テーブルの作成

アンロード元のテーブルを作成します。

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

準備が完了すると、以下が作成された状態になります。

• データベース：CITIBIKE
• テーブル：TRIPS
• ステージ：CITIBIKE_TRIPS　※今回使用しません
• ファイル形式：CSV

ステージの作成

アンロード先のステージを作成します。

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

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

続いて、CRATE STAGEコマンドを実行します。
今回は名前付きステージを作成して検証します。

create or replace stage TRIPS_UNLOAD;

list @TRIPS_UNLOADを実行してみましょう。

ステージングされたファイルについて、
ファイル名、サイズ、MD5ハッシュ、最終更新日の情報が取得できます。

ステージを作成した直後なので、ステージングされたファイルはありませんね。

アンロード

それでは、作成したステージにアンロードしてみましょう。

アンロードするデータのソースは、テーブルまたはクエリを指定できます。

テーブルから

FROMにテーブルを指定してアンロードすることができます。
次のSQL文を実行してみましょう。

copy into @TRIPS_UNLOAD
  from TRIPS
  file_format = (format_name = CSV compression = 'GZIP');

• フィールドを正しく解析するために、データ準備時に定義したCSVを指定しています。
• 圧縮アルゴリズムにGZIPを指定しています。

アンロードが完了しました。
アンロードされた行数、入力バイト数、出力バイト数が表示されていますね。

list @TRIPS_UNLOADコマンドでアンロードされたファイルを確認します。

アンロードされたファイルのリストが取得できました。
124個のファイルがアンロードされたようです。
ファイル名はdataに連番が付与される形になっていますね。

クエリから

FROMにクエリを指定することも可能です。

次のSQL文で、クエリを指定してアンロードしてみましょう。
stoptimeカラムが2013年のデータに絞込んでみます。
ステージのファイルパスも、@TRIPS_UNLOAD/2013にします。

copy into @TRIPS_UNLOAD/2013
  from (select * from TRIPS where date_part(year, stoptime) = 2013)
  file_format = (format_name = CSV compression = 'GZIP');

list @TRIPS_UNLOAD/2013コマンドでアンロードされたファイルを確認します。

14個のファイルが取得できました。

stoptimeカラムが2013年のデータに絞り込んだのでファイル数が減っています。
ファイル名も２０１３に連番が付与される形に変わっていますね。

オプション

COPYコマンドには便利なオプションが用意されています。
いくつか試してみましょう。

OVERWRITE

上書きオプションです。
一致する名前を持つ既存のファイルを上書きするかどうかをブール値で指定します。

先ほどstoptimeが2013年のデータに絞り込みましたが、limit 1000でさらに絞り込みます。

copy into @TRIPS_UNLOAD/2013
  from (select * from TRIPS where date_part(year, stoptime) = 2013 limit 1000)
  file_format = (format_name = CSV compression = 'GZIP')
  OVERWRITE = TRUE;

list @TRIPS_UNLOAD/2013コマンドでアンロードされたファイルを確認します。

14個のファイルが取得できました。

アンロードされたのは赤枠で囲った2013_0_0_0.csv.gzです。
limit 1000で絞り込んだのでサイズの小さいファイルが1個アンロードされたようです。

タイムスタンプが他のファイルより後になっているので、
2013_0_0_0.csv.gzが上書きされたことが分かりますね。

MAX_FILE_SIZE

上限サイズ指定オプションです。バイト単位で指定します。
スレッドごとに並列に生成される各ファイルの上限サイズを指定します。

デフォルトは、16 MBなので、半分の 8 MBを指定してみます。
OVERWRITEオプションはTRUEのままとします。

copy into @TRIPS_UNLOAD/2013
  from (select * from TRIPS where date_part(year, stoptime) = 2013)
  file_format = (format_name = CSV compression = 'GZIP')
  OVERWRITE = TRUE
  MAX_FILE_SIZE = 8000000;

list @TRIPS_UNLOAD/2013コマンドでアンロードされたファイルを確認します。

24個のファイルがアンロードされたようです。

上限サイズをデフォルトの半分にしたのでファイル数が増えています。
タイムタンプが更新されているのでファイルが上書きされたことも確認できます。

ファイルサイズも指定した上限サイズ 8 MB（= 8,388,608 B）内に収まっていますね。

SINGLE

単一ファイル生成オプションです。
単一のファイルを生成するか複数のファイルを生成するかをブール値で指定します。

デフォルトはFALSEなので、TRUEを指定して単一ファイルを生成してみます。
ステージのファイルパスを、拡張子まで含めて2013-12.csv.gzで指定します。
ファイルの上限サイズを超えないように、stoptimeが2013年12月のデータに絞込みます。

copy into @TRIPS_UNLOAD/2013-12.csv.gz
  from (select * from TRIPS where date_part(year, stoptime) = 2013 and date_part(month, stoptime) = 12)
  file_format = (format_name = CSV compression = 'GZIP')
  SINGLE = TRUE;

list @TRIPS_UNLOAD/2013コマンドでアンロードされたファイルを確認します。

指定したファイルパスでアンロードされていますね。

INCLUDE_QUERY_ID

UUID追加オプションです。
アンロードされたファイルを一意に識別するかどうかをブール値で指定します。

デフォルトはFALSEなので、TRUEを指定してファイルの名前にUUID追加してみます。
UUIDは、データファイルをアンロードするために使用されるCOPYステートメントのクエリIDです。

copy into @TRIPS_UNLOAD/2013
  from (select * from TRIPS where date_part(year, stoptime) = 2013 and date_part(month, stoptime) = 12)
  file_format = (format_name = CSV compression = 'GZIP')
  INCLUDE_QUERY_ID = TRUE;

list @TRIPS_UNLOAD/2013コマンドでアンロードされたファイルを確認します。

ファイル名にUUIDが追加されていますね。

DETAILED_OUTPUT

ファイル情報出力オプションです。
アンロードされた個々のファイル情報を出力するかをブール値で指定します。

デフォルトはFALSEなので、TRUEを指定してファイルの情報を出力してみます。
INCLUDE_QUERY_IDオプションはTRUEのままとします。

copy into @TRIPS_UNLOAD/2013
  from (select * from TRIPS where date_part(year, stoptime) = 2013 and date_part(month, stoptime) = 12)
  file_format = (format_name = CSV compression = 'GZIP')
  INCLUDE_QUERY_ID = TRUE
  DETAILED_OUTPUT = TRUE;

各ファイルのパスと名前、サイズ、ファイルにアンロードされた行数が表示されましたね。

list @TRIPS_UNLOAD/2013コマンドでステージにあるファイルを確認します。

青枠で囲ったファイルが今回のクエリでアンロードされたファイル、
赤枠で囲ったファイルが1つ前のクエリでアンロードされたファイルです。

INCLUDE_QUERY_IDオプションはTRUEのままだったので、同じクエリでもファイル名が異なっています。
ファイル名が一意になるので、アンロードされたファイルを誤って上書きしないようにするのに役立ちます。

PARTITION BY

ファイル分割オプションです。
アンロードされたテーブルの行を個別のファイルに分割するために使用される式を指定します。

stoptimeをYYYYMMMDD形式に変換した値で分割してみます。

copy into @TRIPS_UNLOAD
  from (select * from TRIPS where date_part(year, stoptime) = 2013 and date_part(month, stoptime) = 12)
  PARTITION BY (to_varchar(stoptime, 'YYYY/MM/DD'))
  file_format = (format_name = CSV compression='GZIP');

list @TRIPS_UNLOAD/2013コマンドでアンロードされたファイルを確認します。

ファイルがYYYYMMMDD形式で分割されていますね。

まとめ

以上、COPYコマンドでデータのアンロードを試してみました。

今回試したオプションを利用して、
一旦データをテーブルに取り込んで必要な形式に変換してファイルに出力
といった使い方もできそうですね。

今回試したオプション以外にも、
列見出しの有無、フィールド区切り文字の指定などファイルの中身に関するオプションもあります。
詳しくはドキュメントを参照ください。

それでは！

参考

• COPY INTO <場所> — Snowflake Documentation
• LIST — Snowflake Documentation
• CREATE STAGE — Snowflake Documentation