Snowflake「Stage」のユースケースと「バルクロード」の解説 | Snowflake Advent Calendar 2019 #SnowflakeDB

Snowflakeでは、ステージングエリアにファイルを配置した後、データをバルクロードします。本日は、このステージングエリアである『Stage』の種類とユースケース、一括登録する『バルクロード』について解説します。

データロードの流れ

Snowflakeへのデータロードは、以下の手順で行います。

  1. 分析したいデータをファイルに出力する
  2. ファイルをクラウドストレージにアップロードする
  3. クラウドストレージから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の2種類あります。

Internal Stage

Snowflakeが内部的に保存するオブジェクトストレージです。更にInternal Stageは、以下の3つに分類されます。

  • 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が8、Sは16、Mは32、Lは64、XLは128
    • ファイルサイズ
      • 10MBから100MBの範囲かつ均等にする
    • ファイルは圧縮する

最後に

初めてDWHを利用するときにデータロードが最初のハードルになります。その少しでもハードルを下げるのに役立てば良いと思い、Snowflake特有のStageやロードの方法について解説しました。

Internal Stageはファイルオブジェクトをフラットに管理するので、実際の運用ではフォルダを用いて階層的に管理できるExternal Stageを用いることが一般的ではないかと考えられます。しかし、External Stageに保存されているデータオブジェクトは、Snowflakeとは別の場所なので、物理的・ネットワーク的に近い、Snowflakeを配置したクラウドサービスの同じリージェンのオブジェクトストレージを利用することをおすすめします。