CI 部の宮本です。今回はローカルの CSV ファイルを Snowflake にロードしてみたいと思います。
やり方を調べていたところ、公式ドキュメントにぴったりのチュートリアルがあったので、以下を参考に進めてみます。
SnowSQL で Snowflake にログインする
今回のチュートリアルは CLI ツールである SnowSQL を使用します。インストール方法は以下の記事を参考にしてください。
以下コマンドでログインします。
$ snowsql -a <account_name> -u <user_name>
<account_name>
、<user_name>
はご自身のものに置き換えてくださいね。
因みに<account_name>
は XXXXXXX.ap-northeast-1.aws
のような形式のものです。(AWS の東京リージョンで Snowflake を構築した場合)
データベース、テーブルの作成
CSV をロードする為のデータベース、テーブルを作成します。チュートリアルでは仮想ウェアハウスも作成していますが、初期で用意されているCOMPUTE_WH
でも事足りそうなのでスキップします。
# データベースの作成 miyamoto#COMPUTE_WH@(no database).(no schema)>create or replace database sf_tuts; +----------------------------------------+ | status | |----------------------------------------| | Database SF_TUTS successfully created. | +----------------------------------------+ 1 Row(s) produced. Time Elapsed: 0.673s # テーブルの作成 miyamoto#COMPUTE_WH@SF_TUTS.PUBLIC>create or replace table emp_basic ( first_name string , last_name string , email string , streetaddress string , city string , start_date date ); +---------------------------------------+ | status | |---------------------------------------| | Table EMP_BASIC successfully created. | +---------------------------------------+ 1 Row(s) produced. Time Elapsed: 1.083s
参考) CREATE <オブジェクト>
データファイルをステージングする
Snowflake にデータをロードするには、ローカルから直接ロードするのではなく、ステージと呼ばれる領域にファイルをアップロードし、その後テーブルへのコピーを実施する必要があります。
ステージには内部(Snowflake)ステージ
、外部(Amazon S3、Google Cloud Storage、またはMicrosoft Azure)ステージ
があり、今回は内部ステージ
を使用します。
こちら にロード用のデータがあるので、ダウンロードして任意のディレクトリに解凍しておきましょう。私は /tmp
配下に配置しました。
$ tree /tmp ├── employees01.csv ├── employees02.csv ├── employees03.csv ├── employees04.csv └── employees05.csv
PUT
を使用して内部ステージへのアップロードを行います。
miyamoto#COMPUTE_WH@SF_TUTS.PUBLIC>put file:///tmp/employees0*.csv @sf_tuts.public.%emp_basic; employees04.csv_c.gz(0.00MB): [##########] 100.00% Done (0.090s, 0.00MB/s). employees01.csv_c.gz(0.00MB): [##########] 100.00% Done (0.128s, 0.00MB/s). employees02.csv_c.gz(0.00MB): [##########] 100.00% Done (0.063s, 0.00MB/s). employees03.csv_c.gz(0.00MB): [##########] 100.00% Done (0.067s, 0.00MB/s). employees05.csv_c.gz(0.00MB): [##########] 100.00% Done (0.066s, 0.00MB/s). +-----------------+--------------------+-------------+-------------+--------------------+--------------------+----------+---------+ | source | target | source_size | target_size | source_compression | target_compression | status | message | |-----------------+--------------------+-------------+-------------+--------------------+--------------------+----------+---------| | employees01.csv | employees01.csv.gz | 370 | 288 | NONE | GZIP | UPLOADED | | | employees02.csv | employees02.csv.gz | 364 | 276 | NONE | GZIP | UPLOADED | | | employees03.csv | employees03.csv.gz | 407 | 298 | NONE | GZIP | UPLOADED | | | employees04.csv | employees04.csv.gz | 375 | 290 | NONE | GZIP | UPLOADED | | | employees05.csv | employees05.csv.gz | 404 | 303 | NONE | GZIP | UPLOADED | | +-----------------+--------------------+-------------+-------------+--------------------+--------------------+----------+---------+ 5 Row(s) produced. Time Elapsed: 4.665s
一つ目の引数はfile://<アップロードするファイル>
、@<データベース名>.<スキーマ名>.%<テーブル名>
の形式です。スキーマは事前の手順で作成していませんので、自動で作成されるpublic
を使用しています。
参考) PUT
因みに内部ステージにはいくつか種類があるようで、今回はテーブルステージ
を使用しています。詳細は以下を参照して下さい。
参考) ローカルファイルのステージの選択
内部ステージにアップロードしたファイルをテーブルにコピーする
準備が整ったので copy
を使用してファイルをテーブルにロードします。
miyamoto#COMPUTE_WH@SF_TUTS.PUBLIC>copy into emp_basic from @%emp_basic file_format = (type = csv field_optionally_enclosed_by='"') pattern = '.*employees0[1-5].csv.gz' on_error = 'skip_file'; +--------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+ | file | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name | |--------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------| | employees04.csv.gz | LOADED | 5 | 5 | 1 | 0 | NULL | NULL | NULL | NULL | | employees02.csv.gz | LOADED | 5 | 5 | 1 | 0 | NULL | NULL | NULL | NULL | | employees05.csv.gz | LOADED | 5 | 5 | 1 | 0 | NULL | NULL | NULL | NULL | | employees03.csv.gz | LOADED | 5 | 5 | 1 | 0 | NULL | NULL | NULL | NULL | | employees01.csv.gz | LOADED | 5 | 5 | 1 | 0 | NULL | NULL | NULL | NULL | +--------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+ 5 Row(s) produced. Time Elapsed: 2.760s
from
: 内部ステージ名を指定します。@%emp_basic
と指定されていますが、プロンプトの表示(miyamoto#COMPUTE_WH@SF_TUTS.PUBLIC)の通り、現在使用しているデータベース(sf_tuts)、スキーマ(public)が使用されます。省略せずに書くと@sf_tuts.public.%emp_basic
(@<データベース名.<スキーマ名>.%<テーブル名>
) です。file_format
: ロードするファイルの形式を指定します。今回の場合は CSV ファイルで項目は"
で囲まれていることを指定しています。pattern
: ロード対象のファイルを正規表現で指定できます。on_error
: ロード中にエラーが発生した場合に後続のファイルを処理するかどうか指定します。今回はskip_file(エラー対象のファイルをスキップして後続のファイルのインポートは続ける)
と指定しています。
参考) COPY INTO
ロード内容の確認
miyamoto#COMPUTE_WH@SF_TUTS.PUBLIC>select * from EMP_BASIC limit 5; +------------+-----------+--------------------------+--------------------------+--------------------+------------+ | FIRST_NAME | LAST_NAME | EMAIL | STREETADDRESS | CITY | START_DATE | |------------+-----------+--------------------------+--------------------------+--------------------+------------| | Wallis | Sizey | wsizeyf@sf_tuts.com | 36761 American Lane | Taibao | 2016-12-30 | | Di | McGowran | dmcgowrang@sf_tuts.com | 1856 Maple Lane | Banjar Bengkelgede | 2017-04-22 | | Carson | Bedder | cbedderh@sf_tuts.co.au | 71 Clyde Gallagher Place | Leninskoye | 2017-03-29 | | Dana | Avory | davoryi@sf_tuts.com | 2 Holy Cross Pass | Wenlin | 2017-05-11 | | Ronny | Talmadge | rtalmadgej@sf_tuts.co.uk | 588 Chinook Street | Yawata | 2017-06-02 | +------------+-----------+--------------------------+--------------------------+--------------------+------------+ 5 Row(s) produced. Time Elapsed: 2.705s
ロード出来ていますね!
まとめ
SnowSQL を使って CSV ファイルのロードを試してみました。ファイルを一度ステージにアップロードしてからコピーする必要があり、一手間多い感想をいだきましたが、アップロード時には自動でファイルを圧縮してくれますし、DWH で扱うデータ量を考えると理にかなっているとも感じました。
次回は S3 からのインポートを試してみたいと思います。