Snowflakeのチュートリアル「Snowflake in 20 Minutes」をやってみた #SnowflakeDB
確かに20分くらいで全部できます
こんちわ。大阪オフィスの玉井です。
Snowflakeのチュートリアルがあったのでやってみました。
今回の作業環境
- Windows 10 Pro
- Google chrome 77.0.3865.90
- Snowflake 2019/10/08時点の最新
ステップ0：準備
CLIクライアントのインストール
Snowflakeの管理画面にログインして、右上のHelpメニューからDownloadを選びます。
CLIクライアントをダウンロードできる画面が開くので、自分のOS用のものをダウンロードしましょう。
インストールはウィザードに従うだけです。
サンプルデータの取得
公式ドキュメント（下記参照）からサンプルデータをダウンロードできます。
ダウンロードしたファイルは C:\tempに展開しておくと、後が楽です。
公式ドキュメント
ステップ1：snowSQLでログイン
ログイン
コマンドプロンプトを起動して、下記を入力します。
snowsql -a <account_name> -u <user_name>
account_name- SnowflakeのURLの
.snowflakecomputing.comより左側の部分になります。 - AWS版を利用している場合、リージョン名も入っていますが、リージョン名も含みます。
- SnowflakeのURLの
user_name- その名の通りユーザー名を入れます。
ログインに成功すると下記のようになります。
> snowsql -a <account_name> -u <user_name> Password: SnowSQL * v1.1.85 Type SQL statements or !help cmtamai#COMPUTE_WH@(no database).(no schema)>
今後の一連の操作は、ここから行います。
公式ドキュメント
Snowflake側の環境によっては、別途設定が必要なので、ドキュメントをみて確認しましょう。
- Step 1. Log into SnowSQL — Snowflake Documentation
- Connecting Through SnowSQL — Snowflake Documentation
ステップ2：Snowflake Objectの作成
データベースを作成する
sf_tutsというデータベースを作成します。
cmtamai#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
テーブルを作成する
emp_basic というテーブルを、先程のデータベースに作成します。カラムについても下記を御覧ください。
cmtamai#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: 0.692s
Virtual Warehouseを作成する
sf_tuts_whというWarehouseを作成します。Warehouseとは、Snowflakeのコンピュートノード的な存在のもので、このWarehouseがSnowflakeでの各処理を担います。Snowflakeは、DBとWarehouseがそれぞれ独立しているのが特徴です。Warehouseが行う処理の具体的な内容については下記をどうぞ。
今回は一番小さいサイズ（X-Small）を指定します。
cmtamai#COMPUTE_WH@SF_TUTS.PUBLIC>create or replace warehouse sf_tuts_wh with
warehouse_size='X-SMALL'
auto_suspend = 180
auto_resume = true
initially_suspended=true;
+--------------------------------------------+
| status |
|--------------------------------------------|
| Warehouse SF_TUTS_WH successfully created. |
+--------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.786s
今の時点ではWarehouseは停止しています。ただ、Warehouseが必要となる処理が開始されると、自動的にWarehouseが実行されます。
公式ドキュメント
- Step 2. Create Snowflake Objects — Snowflake Documentation
- CREATE DATABASE — Snowflake Documentation
- CREATE TABLE — Snowflake Documentation
- CREATE WAREHOUSE — Snowflake Documentation
ステップ3：データを配置する
データをアップロードする
ステップ0で取得したサンプルデータをSnowflakeにアップロードします。PUTコマンドを使います。配置するテーブルは、先程作ったemp_basicです。
cmtamai#SF_TUTS_WH@SF_TUTS.PUBLIC>put file://c:\temp\employees0*.csv @sf_tuts.public.%emp_basic; employees01.csv_c.gz(0.00MB): [##########] 100.00% Done (0.672s, 0.00MB/s). employees03.csv_c.gz(0.00MB): [##########] 100.00% Done (0.817s, 0.00MB/s). employees02.csv_c.gz(0.00MB): [##########] 100.00% Done (0.822s, 0.00MB/s). employees04.csv_c.gz(0.00MB): [##########] 100.00% Done (0.840s, 0.00MB/s). employees05.csv_c.gz(0.00MB): [##########] 100.00% Done (0.197s, 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: 7.125s
上記の通り、ワイルドカードも使えます。5つのCSVを一気にアップロードしました。また、PUTコマンドはデフォルトではgzipに圧縮して配置します。ちなみに、この作業で配置した場所のことをstageと呼称するようです。
LISTコマンドでファイルを確認
LISTコマンドを使用すると、そのテーブルのstageに置いてあるファイルを確認することができます。
cmtamai#SF_TUTS_WH@SF_TUTS.PUBLIC>list @sf_tuts.public.%emp_basic; +--------------------+------+----------------------------------+-------------------------------+ | name | size | md5 | last_modified | |--------------------+------+----------------------------------+-------------------------------| | employees01.csv.gz | 304 | 995841e659c8ef1ed8e4c20237205ced | Tue, 15 Oct 2019 03:45:43 GMT | | employees02.csv.gz | 288 | 4218957939517181d7d73294b01836ca | Tue, 15 Oct 2019 03:45:44 GMT | | employees03.csv.gz | 304 | 4212c06ed7f16fd1b3ada1f30854c92a | Tue, 15 Oct 2019 03:45:44 GMT | | employees04.csv.gz | 304 | 7331775bd152a05b9e3953b23c36a1b6 | Tue, 15 Oct 2019 03:45:44 GMT | | employees05.csv.gz | 304 | bd1db795831babb20eaf3289099c9fe6 | Tue, 15 Oct 2019 03:45:44 GMT | +--------------------+------+----------------------------------+-------------------------------+ 5 Row(s) produced. Time Elapsed: 0.685s
公式ドキュメント
ステップ4：データをテーブルにコピー
さっき配置したファイルを、実際にテーブルに入れます。COPYコマンドを使います。
cmtamai#SF_TUTS_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 |
|--------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| employees03.csv.gz | LOADED | 5 | 5 | 1 | 0 | NULL | NULL | NULL | NULL |
| employees05.csv.gz | LOADED | 5 | 5 | 1 | 0 | NULL | NULL | NULL | NULL |
| employees01.csv.gz | LOADED | 5 | 5 | 1 | 0 | NULL | NULL | NULL | NULL |
| employees04.csv.gz | LOADED | 5 | 5 | 1 | 0 | NULL | NULL | NULL | NULL |
| employees02.csv.gz | LOADED | 5 | 5 | 1 | 0 | NULL | NULL | NULL | NULL |
+--------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
5 Row(s) produced. Time Elapsed: 2.446s
file_format- 配置されているファイルの仕様を指定
pattern- 配置しているファイルのうち、どのファイルをコピー対象とするか
- 正規表現が使用可
on_error- コピー時にエラーが発生したときの挙動
- 指定しない場合（デフォルトの場合）、処理は停止される
公式ドキュメント
- Step 4. Copy Data into the Target Table — Snowflake Documentation
- CREATE FILE FORMAT — Snowflake Documentation
- COPY INTO table — Snowflake Documentation
ステップ5：データに対してクエリを発行する
テーブルにデータを入れたので、snowSQLで問い合わせできるようになりました。snowSQLといっても、標準SQLに対応しているので、普通の操作をする分には、新しい言語を覚える必要はありません（もちろんsnowSQL独自の部分もあるので、そこは使っていきながら覚えるしか無いと思います）。
テーブルの全データを確認する
普通のSQLでできます。
cmtamai#SF_TUTS_WH@SF_TUTS.PUBLIC>select * from emp_basic; +------------+--------------+---------------------------+-----------------------------+--------------------+------------+ | FIRST_NAME | LAST_NAME | EMAIL | STREETADDRESS | CITY | START_DATE | |------------+--------------+---------------------------+-----------------------------+--------------------+------------| | Althea | Featherstone | afeatherstona@sf_tuts.com | 8172 Browning Street, Apt B | Calatrava | 2017-07-12 | | Hollis | Anneslie | hanneslieb@sf_tuts.com | 3248 Roth Park | Aleysk | 2017-11-16 | | Betti | Cicco | bciccoc@sf_tuts.com | 121 Victoria Junction | Sinegor'ye | 2017-06-22 | | Brendon | Durnall | bdurnalld@sf_tuts.com | 26814 Weeping Birch Place | Sabadell | 2017-11-14 | | Kylila | MacConnal | kmacconnale@sf_tuts.com | 04 Valley Edge Court | Qingshu | 2017-06-22 | | Arlene | Davidovits | adavidovitsk@sf_tuts.com | 7571 New Castle Circle | Meniko | 2017-05-03 | | Violette | Shermore | vshermorel@sf_tuts.com | 899 Merchant Center | Troitsk | 2017-01-19 | | Ron | Mattys | rmattysm@sf_tuts.com | 423 Lien Pass | Bayaguana | 2017-11-15 | | Shurlocke | Oluwatoyin | soluwatoyinn@sf_tuts.com | 40637 Portage Avenue | Semënovskoye | 2017-09-12 | | Granger | Bassford | gbassfordo@sf_tuts.co.uk | 6 American Ash Circle | Kardítsa | 2016-12-30 | | Lem | Boissier | lboissier@sf_tuts.com | 3002 Ruskin Trail | Shikārpur | 2017-08-25 | | Iain | Hanks | ihanks1@sf_tuts.com | 2 Pankratz Hill | Monte-Carlo | 2017-12-10 | | Avo | Laudham | alaudham2@sf_tuts.com | 6948 Debs Park | Prażmów | 2017-10-18 | | Emili | Cornner | ecornner3@sf_tuts.com | 177 Magdeline Avenue | Norrköping | 2017-08-13 | | Harrietta | Goolding | hgoolding4@sf_tuts.com | 450 Heath Trail | Osielsko | 2017-11-27 | | 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 | | Nyssa | Dorgan | ndorgan5@sf_tuts.com | 7 Tomscot Way | Pampas Chico | 2017-04-13 | | Catherin | Devereu | cdevereu6@sf_tuts.co.au | 535 Basil Terrace | Magapit | 2016-12-17 | | Grazia | Glaserman | gglaserman7@sf_tuts.com | 162 Debra Lane | Shiquanhe | 2017-06-06 | | Ivett | Casemore | icasemore8@sf_tuts.com | 84 Holmberg Pass | Campina Grande | 2017-03-29 | | Cesar | Hovie | chovie9@sf_tuts.com | 5 7th Pass | Miami | 2016-12-21 | +------------+--------------+---------------------------+-----------------------------+--------------------+------------+ 25 Row(s) produced. Time Elapsed: 2.349s
データ（レコード）を追加する
cmtamai#SF_TUTS_WH@SF_TUTS.PUBLIC>insert into emp_basic values
('Clementine','Adamou','cadamou@sf_tuts.com','10510 Sachs Road','Klenak','2017-9-22') ,
('Marlowe','De Anesy','madamouc@sf_tuts.co.uk','36768 Northfield Plaza','Fangshan','2017-1-26');
+-------------------------+
| number of rows inserted |
|-------------------------|
| 2 |
+-------------------------+
2 Row(s) produced. Time Elapsed: 83.876s
EMAILカラムを使用した検索
メールアドレスのドメインが.ukなユーザーを出します。
cmtamai#SF_TUTS_WH@SF_TUTS.PUBLIC>select email from emp_basic where email like '%.uk'; +--------------------------+ | EMAIL | |--------------------------| | gbassfordo@sf_tuts.co.uk | | rtalmadgej@sf_tuts.co.uk | | madamouc@sf_tuts.co.uk | +--------------------------+ 3 Row(s) produced. Time Elapsed: 0.803s
ちょっとしたデータの加工
2017年1月1日より前に入社した従業員に対して、入社日（start_date）に90日を足したカラムを追加し、従業員に対する福利厚生が始まる日を出します。
cmtamai#SF_TUTS_WH@SF_TUTS.PUBLIC>select first_name, last_name, dateadd('day',90,start_date) from emp_basic where start_date <= '2017-01-01';
+------------+-----------+------------------------------+
| FIRST_NAME | LAST_NAME | DATEADD('DAY',90,START_DATE) |
|------------+-----------+------------------------------|
| Granger | Bassford | 2017-03-30 |
| Wallis | Sizey | 2017-03-30 |
| Catherin | Devereu | 2017-03-17 |
| Cesar | Hovie | 2017-03-21 |
+------------+-----------+------------------------------+
4 Row(s) produced. Time Elapsed: 0.776s
いずれも標準SQLで行っていますので、普通のDBと同じ感覚で問い合わせができます。
公式ドキュメント
ステップ6：まとめと後片付け
操作自体はステップ5で終了。ステップ6はチュートリアルのまとめ的なものになっています。公式ドキュメントを読めばOKな内容ですが、下記に自分なりにまとめたものを記します。
今回行った操作のまとめ
1.データ（ファイル）をSnowflakeに配置する
今回はcsvをSnowflakeの中に配置しました。別の方法として、Amazon S3等の外部ストレージにファイルを配置することも可能です。
2.配置したファイルからテーブルにデータを入れる
当然ながら、予めテーブルを用意しておく必要があります。また、この操作ではWarehouseが稼働します。
csvの要件
- レコード毎に改行されていること
- カンマ区切り
csvとコピー先テーブルについて
- カラム数を揃える
- カラムの順番を揃える
- カラムの型を揃える
これらが一致していない場合、データは入れられません。
後片付け
チュートリアルで使用した環境を削除します。DROPコマンドを使います。
cmtamai#SF_TUTS_WH@SF_TUTS.PUBLIC>drop database if exists sf_tuts; +-------------------------------+ | status | |-------------------------------| | SF_TUTS successfully dropped. | +-------------------------------+ 1 Row(s) produced. Time Elapsed: 0.754s
cmtamai#SF_TUTS_WH@(no database).(no schema)>drop warehouse if exists sf_tuts_wh; +----------------------------------+ | status | |----------------------------------| | SF_TUTS_WH successfully dropped. | +----------------------------------+ 1 Row(s) produced. Time Elapsed: 0.224s
公式ドキュメント
- Step 6. Summary and Clean Up — Snowflake Documentation
- Transforming Data During a Load — Snowflake Documentation
- ファイルとテーブルのカラムが合っていなくてもデータを入れる方法
- Tutorials, Videos & Other Resources — Snowflake Documentation
- 他の学習リソース
おわりに
まだ試してませんが、S3にcsvさえ置いておけば、テーブルにサクッとロードできちゃうのは便利そうですね。
![[レポート]AWSでデータメッシュアーキテクチャを構築する#ANT336 #reinvent](https://devio2023-media.developers.io/wp-content/uploads/2022/11/eyecatch_reinvent2022_session-report.png)
