Snowflakeのチュートリアル「Snowflake in 20 Minutes」をやってみた #SnowflakeDB

確かに20分くらいで全部できます
2019.10.15

こんちわ。大阪オフィスの玉井です。

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版を利用している場合、リージョン名も入っていますが、リージョン名も含みます。
  • 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側の環境によっては、別途設定が必要なので、ドキュメントをみて確認しましょう。

ステップ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が実行されます。

公式ドキュメント

ステップ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
    • コピー時にエラーが発生したときの挙動
    • 指定しない場合(デフォルトの場合)、処理は停止される

公式ドキュメント

ステップ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

公式ドキュメント

おわりに

まだ試してませんが、S3にcsvさえ置いておけば、テーブルにサクッとロードできちゃうのは便利そうですね。