Snowflake「Stage」のユースケースと「バルクロード」の解説 | Snowflake Advent Calendar 2019 #SnowflakeDB
Snowflakeでは、ステージングエリアにファイルを配置した後、データをバルクロードします。本日は、このステージングエリアである『Stage』の種類とユースケース、一括登録する『バルクロード』について解説します。
データロードの流れ
Snowflakeへのデータロードは、以下の手順で行います。
- 分析したいデータをファイルに出力する
- ファイルをクラウドストレージにアップロードする
- クラウドストレージからSnowflakeにデータをロードする
データロードの方法は、BULK（COPYコマンドを用いたバルクロード）とCONTINUOUS（Snowpipeを用いた連続的なロード）に分類されます。以降では前者のバルクロードの方法を解説します。
データローディングの際には、クラウドサービスレイヤでメタデータを作成します。メタデータはテーブル、テーブル、カラム、およびファイルの統計などです。その後、データベースとテーブルの実際のデータをロードする際にSnowflakeが管理するクラウドストレージに最適化された独自のファイル形式（Micro Partition）に保存します。
データロードの用語の解説
まずは、この後に用いる用語を簡単に解説します。各用語の詳細やユースケースについては後で詳しく説明しますので、ここではザックリとイメージを掴んでください。
Stage
クラウドファイルリポジトリ、つまりデータファイルの保管場所です。
- 一括ロードおよびアンロードを簡素化および合理化
- InternalまたはExternalのいずれか
- Internal Stage：Snowflakeに内部的に保存
- External Stage：Amazon S3のような外部のクラウドストレージにに保存
- ベストプラクティス：
- 取り込みワークロードを管理する「Stage」オブジェクトを作成する
※ Stageについては後ほど改めて解説します。
File Format
ロードするファイルフォーマットの定義オブジェクトです。「ファイルの形式はCSV、区切り文字はカンマ、...」といった定義情報です。
Database / Schema / Table
データベース / スキーマ / テーブルの順に管理します。また、データベース / スキーマ / テーブルの数に制限はありません。
Pipe
Snowpipeという機能で、IoTデバイスの情報やクリックストリームの用に絶え間なく連携されるデータをロードする際に用います。
Stageの種類とユースケース
Stageは、InternalまたはExternalの２種類あります。
Internal Stage
Snowflakeが内部的に保存するオブジェクトストレージです。更にInternal Stageは、以下の３つに分類されます。
- Table Stage
@%[TABLE_NAME]- テーブルに毎にファイル保存領域
- テーブルを作成すると自動的に作成される
- このテーブルに格納するデータを一時的に保存する領域です。temporaryやtransentなテーブルでもこの領域の利用が可能
- User Stage
@~- ユーザー毎に割り当てられるプライベートなファイル保存領域
- Named Stage
@[STAGE_NAME]- 任意の名前を付けて利用者間で共有するファイル保存領域
External Stage
Snowflakeから、Amazon S3のような外部のクラウドストレージのデータを読み込む際に用います。External Stageは、Named Stageのみで用途は同様です。
Internal Stage と External Stageの使い分け
Table StageやUser Stageを利用したい場合は、Internal Stageとなります。Named Stageはどちらでも構いませんが、Internal Stageはファイルオブジェクトをフラットに管理するので、フォルダを用いて階層的に管理したい場合はExternal Stageを用いることになります。また、External Stageに保存されているデータオブジェクトは、Snowflakeとは別の場所なので、レイテンシを気にする方は、Snowflakeを配置したクラウドサービスの同じリージェンのオブジェクトストレージを利用することをおすすめします。
Stage へファイルアップロード、一覧表示
Internal Stageを例に、ファイルをアップロードする例をご紹介します。
Table Stageへのアップロード
ISHIKAWA#COMPUTE_WH@ISHIKAWA_DB.PUBLIC>put file:///tmp/contacts*.csv @my_csv_stage auto_compress=true; contacts5.csv_c.gz(0.00MB): [##########] 100.00% Done (0.966s, 0.00MB/s). contacts4.csv_c.gz(0.00MB): [##########] 100.00% Done (1.010s, 0.00MB/s). contacts2.csv_c.gz(0.00MB): [##########] 100.00% Done (1.002s, 0.00MB/s). contacts1.csv_c.gz(0.00MB): [##########] 100.00% Done (1.006s, 0.00MB/s). contacts3.csv_c.gz(0.00MB): [##########] 100.00% Done (0.489s, 0.00MB/s). +---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+ | source | target | source_size | target_size | source_compression | target_compression | status | message | |---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------| | contacts1.csv | contacts1.csv.gz | 694 | 506 | NONE | GZIP | UPLOADED | | | contacts2.csv | contacts2.csv.gz | 763 | 565 | NONE | GZIP | UPLOADED | | | contacts3.csv | contacts3.csv.gz | 771 | 567 | NONE | GZIP | UPLOADED | | | contacts4.csv | contacts4.csv.gz | 750 | 561 | NONE | GZIP | UPLOADED | | | contacts5.csv | contacts5.csv.gz | 887 | 621 | NONE | GZIP | UPLOADED | | +---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+ 5 Row(s) produced. Time Elapsed: 4.577s
Table Stageへのアップロード
ISHIKAWA#COMPUTE_WH@ISHIKAWA_DB.PUBLIC>put file:///tmp/contacts2.csv @%mycsvtable auto_compress=true; contacts2.csv_c.gz(0.00MB): [##########] 100.00% Done (1.096s, 0.00MB/s). +---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+ | source | target | source_size | target_size | source_compression | target_compression | status | message | |---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------| | contacts2.csv | contacts2.csv.gz | 763 | 565 | NONE | GZIP | UPLOADED | | +---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+ 1 Row(s) produced. Time Elapsed: 3.307s
User Stageへのアップロード
ISHIKAWA#COMPUTE_WH@ISHIKAWA_DB.PUBLIC>put file:///tmp/contacts1.csv @~ auto_compress=true; contacts1.csv_c.gz(0.00MB): [##########] 100.00% Done (1.121s, 0.00MB/s). +---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+ | source | target | source_size | target_size | source_compression | target_compression | status | message | |---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------| | contacts1.csv | contacts1.csv.gz | 694 | 506 | NONE | GZIP | UPLOADED | | +---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+ 1 Row(s) produced. Time Elapsed: 3.895s
FILE FORMAT
データのバルクロード/アンロード時にファイルをパースするための情報を保存する名前付きオブジェクトです。ファイルタイプには、CSV、JSON、XML、PARQUET、ORC、AVROがあります。最終的にはCOPY/UNLOADコマンド内で指定するFILE FORMATオブジェクトを作成します。
CREATE FILE FORMAT DEMO_FF TYPE = 'CSV' FIELD_DELIMITER = ',' RECORD_DELIMITER = '\n' SKIP_HEADER = 1; FILE FORMAT
ローカルストレージ（Internal Stage）からのバルクロード
最後にこれらを用いたローカルストレージ（Internal Stage）からのバルクロードの例を紹介します。
1. File Formatの作成
ISHIKAWA#COMPUTE_WH@ISHIKAWA_DB.PUBLIC>create or replace file format mycsvformat
type = 'CSV'
field_delimiter = '|'
skip_header = 1;
+-----------------------------------------------+
| status |
|-----------------------------------------------|
| File format MYCSVFORMAT successfully created. |
+-----------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.293s
2. Stageの作成
ISHIKAWA#COMPUTE_WH@ISHIKAWA_DB.PUBLIC>create or replace stage my_csv_stage
file_format = mycsvformat;
+-----------------------------------------------+
| status |
|-----------------------------------------------|
| Stage area MY_CSV_STAGE successfully created. |
+-----------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.417s
3. データファイルのアップロード
ISHIKAWA#COMPUTE_WH@ISHIKAWA_DB.PUBLIC>put file:///tmp/contacts*.csv @my_csv_stage auto_compress=true; contacts5.csv_c.gz(0.00MB): [##########] 100.00% Done (0.966s, 0.00MB/s). contacts4.csv_c.gz(0.00MB): [##########] 100.00% Done (1.010s, 0.00MB/s). contacts2.csv_c.gz(0.00MB): [##########] 100.00% Done (1.002s, 0.00MB/s). contacts1.csv_c.gz(0.00MB): [##########] 100.00% Done (1.006s, 0.00MB/s). contacts3.csv_c.gz(0.00MB): [##########] 100.00% Done (0.489s, 0.00MB/s). +---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+ | source | target | source_size | target_size | source_compression | target_compression | status | message | |---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------| | contacts1.csv | contacts1.csv.gz | 694 | 506 | NONE | GZIP | UPLOADED | | | contacts2.csv | contacts2.csv.gz | 763 | 565 | NONE | GZIP | UPLOADED | | | contacts3.csv | contacts3.csv.gz | 771 | 567 | NONE | GZIP | UPLOADED | | | contacts4.csv | contacts4.csv.gz | 750 | 561 | NONE | GZIP | UPLOADED | | | contacts5.csv | contacts5.csv.gz | 887 | 621 | NONE | GZIP | UPLOADED | | +---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+ 5 Row(s) produced. Time Elapsed: 4.577s
4. 一覧表示
ISHIKAWA#COMPUTE_WH@ISHIKAWA_DB.PUBLIC>list @my_csv_stage; +-------------------------------+------+----------------------------------+-------------------------------+ | name | size | md5 | last_modified | |-------------------------------+------+----------------------------------+-------------------------------| | my_csv_stage/contacts1.csv.gz | 512 | dc569330043af1b700023315a864731e | Wed, 27 Nov 2019 04:14:57 GMT | | my_csv_stage/contacts2.csv.gz | 576 | f9a22667a185e648e848df8727215a6b | Wed, 27 Nov 2019 04:14:57 GMT | | my_csv_stage/contacts3.csv.gz | 576 | 963dbd34c02b5c8d9934d890691f36f6 | Wed, 27 Nov 2019 04:14:58 GMT | | my_csv_stage/contacts4.csv.gz | 576 | 2d7f0db71dcfdd53c87d331fce0772f6 | Wed, 27 Nov 2019 04:14:57 GMT | | my_csv_stage/contacts5.csv.gz | 624 | d4ab2e43aa3687b37ac5bdf5c87cec38 | Wed, 27 Nov 2019 04:14:57 GMT | +-------------------------------+------+----------------------------------+-------------------------------+ 5 Row(s) produced. Time Elapsed: 0.291s
5. COPYコマンドでテーブルにデータをロード
ISHIKAWA#COMPUTE_WH@ISHIKAWA_DB.PUBLIC>copy into mycsvtable
from @my_csv_stage/contacts1.csv.gz
file_format = (format_name = mycsvformat)
on_error = 'skip_file';
+---------------------------------------+
| status |
|---------------------------------------|
| Copy executed with 0 files processed. |
+---------------------------------------+
1 Row(s) produced. Time Elapsed: 1.564s
ファイルサイズとファイル数のチューニング
ファイルサイズとファイル数はデータロードのパフォーマンスに大きく影響します。
- Snowflakeにロードする前に事前にファイルを分割する
- ファイル分割数はWarehouseのサイズに応じて決める
- XSが８、Sは１６、Mは３２、Lは64、XLは１２８
- ファイルサイズ
- 10MBから100MBの範囲かつ均等にする
- ファイルは圧縮する
- ファイル分割数はWarehouseのサイズに応じて決める
最後に
初めてDWHを利用するときにデータロードが最初のハードルになります。その少しでもハードルを下げるのに役立てば良いと思い、Snowflake特有のStageやロードの方法について解説しました。
Internal Stageはファイルオブジェクトをフラットに管理するので、実際の運用ではフォルダを用いて階層的に管理できるExternal Stageを用いることが一般的ではないかと考えられます。しかし、External Stageに保存されているデータオブジェクトは、Snowflakeとは別の場所なので、物理的・ネットワーク的に近い、Snowflakeを配置したクラウドサービスの同じリージェンのオブジェクトストレージを利用することをおすすめします。
![[新機能]dbtのdata testで任意の設定をconfigで指定できるようになり、各data testでSnowflakeのウェアハウスを指定できるようになりました](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-f846b6052d1f7b983172226c6b1ed44b/6b4729aabfb5b7a3359acc27dc6b5453/dbt-1200x630-1.jpg)
