Snowflakeを20分で紹介

紹介

このチュートリアルでは、Snowflakeコマンドラインクライアント SnowSQL を使用して、次のような主要な概念とタスクを紹介します。

  • Snowflakeオブジェクトの作成---データを格納するためのデータベースとテーブルを作成します。

  • データのロード---テーブルにロードするための小さなサンプル CSV データファイルを提供します。

  • クエリ---クエリのサンプルを詳しく見ます。

注釈

Snowflakeはこのチュートリアルのサンプルデータに使用されるディスク上のストレージに対して最小限の料金を請求します。このチュートリアルでは、データベースをドロップしてストレージコストを最小限に抑えるステップを示します。

Snowflakeには、サンプルデータをロードしてクエリを実行するために、 仮想ウェアハウス が必要です。稼働中の仮想ウェアハウスはSnowflakeのクレジットを消費します。このチュートリアルでは、無料クレジットを提供する 30日間のトライアルアカウント を使用するため、コストは発生しません。

学習内容

このチュートリアルでは、その方法を学びます:

  • Snowflakeオブジェクトの作成---データを格納するためのデータベースとテーブルを作成します。

  • SnowSQL のインストール---Snowflakeコマンドラインクエリツール、 SnowSQL をインストールして使用します。

    Visual Studio Codeのユーザーは、 SnowSQL の代わりに Snowflake Extension for Visual Studio Code の使用を検討してください。

  • CSV データファイルのロード--- CSV ファイルからテーブルにデータをロードするために各種のメカニズムを使用します。

  • サンプルクエリの作成と実行---新しくロードされたデータに対してさまざまなクエリを作成し、実行します。

前提条件

このチュートリアルでは、データをロードおよびクエリするためにデータベース、テーブル、仮想ウェアハウスが必要です。これらのSnowflakeオブジェクトの作成には、必要なアクセス制御権限のあるロールを持つSnowflakeユーザーが必要です。さらに、チュートリアルの SQL ステートメントを実行するには、 SnowSQL が必要です。最後に、チュートリアルでは、ロードするサンプルデータが含まれる CSV ファイルが必要です。

このチュートリアルは、既存のSnowflakeウェアハウス、データベース、テーブル、および独自のローカルデータファイルを使用して完了できますが、Snowflakeオブジェクトと提供されたデータセットを使用することをお勧めします。

このチュートリアルのためにSnowflakeを設定するには、続行する前に以下を完了します。

  1. ユーザーを作成する

    データベース、テーブル、および仮想ウェアハウスを作成するには、これらのオブジェクトを作成する権限を付与するロールがあるSnowflakeユーザーとしてログインする必要があります。

    • 30日間の試用アカウントを使用している場合は、アカウント用に作成されたユーザーとしてログインできます。このユーザーには、オブジェクトの作成に必要な権限を持ったロールがあります。

    • Snowflakeのユーザーがない場合は、このチュートリアルを実行することはできません。ユーザーを作成できるロールがない場合は、そのロールを持っている人にこのステップを実行してもらいます。ユーザーを作成できるのは、 ACCOUNTADMIN または SECURITYADMIN ロールを持つユーザーのみです。

  2. SnowSQL をインストールする

    SnowSQL をインストールするには、 SnowSQL のインストール をご参照ください。

  3. サンプルデータファイルをダウンロードする

    このチュートリアルでは、Snowflakeが提供するサンプルの従業員データファイルを CSV 形式でダウンロードします。

    サンプルデータファイルをダウンロードして解凍するには、

    1. サンプルデータファイルのセットをダウンロードします。アーカイブファイルの名前 getting-started.zip を右クリックし、リンク/ファイルをローカルファイルシステムに保存します。

    2. サンプルファイルを解凍します。このチュートリアルでは、以下のいずれかのディレクトリにファイルを解凍することを想定しています。

    • Linux/macOS: /tmp

    • Windows: C:\\temp

    各ファイルには5つのデータの記録があります。データは、フィールド区切り文字としてコンマ(,)文字を使用します。次は記録の例です。

    Althea,Featherstone,afeatherstona@sf_tuts.com,"8172 Browning Street, Apt B",Calatrava,7/12/2017
    
    Copy

各記録のフィールドを区切るコンマの前後には、空白が ありません 。これは、Snowflakeが CSV データのロード時に予期するデフォルトです。

SnowSQL にログインする

SnowSQL を取得した後、 SnowSQL を起動してSnowflakeに接続します。

  1. コマンドラインウィンドウを開きます。

  2. SnowSQL を開始します。

    $ snowsql -a <account_identifier> -u <user_name>
    
    Copy

    条件:

    • <account_identifier> はSnowflakeアカウント用の一意の識別子です。

      アカウント識別子 の推奨形式は次のとおりです。

      organization_name-account_name

      Snowflake組織とアカウントの名前。詳細については、 形式1(推奨): 組織内のアカウント名。 をご参照ください。

    • <ユーザー名> は、Snowflakeユーザーのログイン名。

    注釈

    アカウントにIDプロバイダー(IdP)が定義されている場合は、次の例に示すように、パスワードの代わりにウェブブラウザーを使用して認証することができます。

    $ snowsql -a <account_identifier> -u <user_name> --authenticator externalbrowser
    
    Copy

    詳細については、 フェデレーション認証/SSO 用のウェブブラウザーの使用 をご参照ください。

  3. SnowSQL のプロンプトが表示されたら、Snowflakeユーザーのパスワードを入力します。

ログインに成功すると、 SnowSQL は、現在のウェアハウス、データベース、およびスキーマを含むコマンドプロンプトを表示します。

注釈

アカウントからロックアウトされ、アカウント識別子を取得できない場合は、試用アカウントにサインアップしたときにSnowflakeから送信されたウェルカムメールで見つけることができます。または、 ORGADMIN と協力して アカウントの詳細を取得する ことができます。 locatorcloud、および region の値は、ウェルカムメールにも記載されています。

Snowflakeユーザーがデフォルトのウェアハウス、データベース、およびスキーマを持っていない場合、またはデフォルトのウェアハウス、データベース、およびスキーマを指定するように SnowSQL を構成しなかった場合、プロンプトには no warehouseno database、および no schema が表示されます。例:

user-name#(no warehouse)@(no database).(no schema)>
Copy

このプロンプトは、現在のセッションで選択されたウェアハウス、データベース、およびスキーマがないことを示しています。次のステップでこれらのオブジェクトを作成します。このチュートリアルの次のステップに従ってこれらのオブジェクトを作成すると、プロンプトが自動的に更新され、これらのオブジェクトの名前が含まれるようになります。

詳細については、 SnowSQL を介した接続 をご参照ください。

Snowflakeオブジェクトを作成する

このステップでは、次のSnowflakeオブジェクトを作成します。

  • データベース(sf_tuts)とテーブル(emp_basic)。サンプルデータをこのテーブルにロードします。

  • 仮想ウェアハウスsf_tuts_wh)。このウェアハウスは、データをテーブルにロードし、テーブルに対してクエリを実行するために必要なコンピューティングリソースを提供します。このチュートリアルでは、XSサイズのウェアハウスを作成します。

このチュートリアルの完了時に、これらのオブジェクトを削除します。

データベースを作成する

CREATE DATABASE コマンドを使用して sf_tuts データベースを作成します。

CREATE OR REPLACE DATABASE sf_tuts;
Copy

このチュートリアルでは、新しいスキーマを作成するのではなく、各データベースで使用可能なデフォルトのスキーマ(public)を使用します。

SnowSQL コマンドプロンプトで反映されているように、作成したデータベースとスキーマは、現在のセッションで使用されていることに注意してください。コンテキスト関数を使用してこの情報を取得することもできます。

SELECT CURRENT_DATABASE(), CURRENT_SCHEMA();
Copy

以下は結果の例です。

+--------------------+------------------+
| CURRENT_DATABASE() | CURRENT_SCHEMA() |
|--------------------+------------------|
| SF_TUTS            | PUBLIC           |
+--------------------+------------------+

テーブルを作成する

CREATE TABLE コマンドを使用して sf_tuts.publicemp_basic という名前のテーブルを作成します。

CREATE OR REPLACE TABLE emp_basic (
   first_name STRING ,
   last_name STRING ,
   email STRING ,
   streetaddress STRING ,
   city STRING ,
   start_date DATE
   );
Copy

テーブルの列数、位置、およびデータ型は、このチュートリアルの次のステップでステージングするサンプル CSV データファイルのフィールドに対応しています。

仮想ウェアハウスを作成する

CREATE WAREHOUSE コマンドを使用して、 sf_tuts_wh という名前のX-Smallウェアハウスを作成します。

CREATE OR REPLACE WAREHOUSE sf_tuts_wh WITH
   WAREHOUSE_SIZE='X-SMALL'
   AUTO_SUSPEND = 180
   AUTO_RESUME = TRUE
   INITIALLY_SUSPENDED=TRUE;
Copy

sf_tuts_wh ウェアハウスは最初は中断されていますが、 DML ステートメントも AUTO_RESUME = true を設定しています。AUTO_RESUME の設定により、コンピューティングリソースを必要とする SQL ステートメントが実行されると、ウェアハウスが自動的に開始されます。

ウェアハウスを作成すると、現在のセッションで使用されます。この情報は SnowSQL コマンドプロンプトに表示されます。次のコンテキスト関数を使用して、ウェアハウスの名前を取得することもできます。

SELECT CURRENT_WAREHOUSE();
Copy

以下は結果の例です。

+---------------------+
| CURRENT_WAREHOUSE() |
|---------------------|
| SF_TUTS_WH          |
+---------------------+

データファイルをステージする

Snowflakeステージは、テーブルからデータをロードおよびアンロードするために使用するクラウドストレージ内の場所です。Snowflakeは、次のステージの型をサポートしています。

  • 内部ステージ---Snowflake内部でデータファイルを保存するために使用します。Snowflakeの各ユーザーとテーブルは、データファイルをステージングするためにデフォルトで内部ステージを取得します。

  • 外部ステージ---Amazon S3、Google Cloud Storage、またはMicrosoft Azureで外部データファイルを保存するために使用します。データがこれらのクラウドストレージサービスにすでに保存されている場合は、外部ステージを使用してSnowflakeテーブルにデータをロードできます。

このチュートリアルでは、サンプルデータファイル(前提条件 でダウンロード)を、前に作成した emp_basic テーブルの内部ステージにアップロードします。 PUT コマンドを使用して、そのステージにサンプルデータファイルをアップロードします。

サンプルデータファイルのステージング

SnowSQLPUT コマンドを実行して、作成した emp_basic テーブルに提供されたテーブルステージに、ローカルデータファイルをアップロードします。

PUT file://<file-path>[/\]employees0*.csv @sf_tuts.public.%emp_basic;
Copy

例:

  • Linuxまたは macOS

    PUT file:///tmp/employees0*.csv @sf_tuts.public.%emp_basic;
    
    Copy
  • Windows

    PUT file://C:\temp\employees0*.csv @sf_tuts.public.%emp_basic;
    
    Copy

コマンドを詳しく見てみましょう。

  • file://<ファイルパス >[/]employees0*.csv は、ステージするローカルマシン上のファイルの完全なディレクトリパスとファイル名を指定します。ファイルシステムのワイルドカードが許可されており、複数のファイルがパターンに適合する場合は、それらすべてが表示されることに注意してください。

  • @<名前空間>.%<テーブル名> は、指定したテーブルにステージを使用することを表しており、この場合は、 emp_basic テーブルです。

このコマンドは、ステージングされたファイルを示す次の結果を返します。

+-----------------+--------------------+-------------+-------------+--------------------+--------------------+----------+---------+
| source          | target             | source_size | target_size | source_compression | target_compression | status   | message |
|-----------------+--------------------+-------------+-------------+--------------------+--------------------+----------+---------|
| employees01.csv | employees01.csv.gz |         360 |         287 | NONE               | GZIP               | UPLOADED |         |
| employees02.csv | employees02.csv.gz |         355 |         274 | NONE               | GZIP               | UPLOADED |         |
| employees03.csv | employees03.csv.gz |         397 |         295 | NONE               | GZIP               | UPLOADED |         |
| employees04.csv | employees04.csv.gz |         366 |         288 | NONE               | GZIP               | UPLOADED |         |
| employees05.csv | employees05.csv.gz |         394 |         299 | NONE               | GZIP               | UPLOADED |         |
+-----------------+--------------------+-------------+-------------+--------------------+--------------------+----------+---------+

TARGET_COMPRESSION 列に示されているように、 PUT コマンドはデフォルトで gzip を使用してファイルを圧縮します。

ステージングされたファイルのリスト(オプション)

LIST コマンドを使用して、ステージングされたファイルをリストできます。

LIST @sf_tuts.public.%emp_basic;
Copy

以下は結果の例です。

+--------------------+------+----------------------------------+------------------------------+
| name               | size | md5                              | last_modified                |
|--------------------+------+----------------------------------+------------------------------|
| employees01.csv.gz |  288 | a851f2cc56138b0cd16cb603a97e74b1 | Tue, 9 Jan 2018 15:31:44 GMT |
| employees02.csv.gz |  288 | 125f5645ea500b0fde0cdd5f54029db9 | Tue, 9 Jan 2018 15:31:44 GMT |
| employees03.csv.gz |  304 | eafee33d3e62f079a054260503ddb921 | Tue, 9 Jan 2018 15:31:45 GMT |
| employees04.csv.gz |  304 | 9984ab077684fbcec93ae37479fa2f4d | Tue, 9 Jan 2018 15:31:44 GMT |
| employees05.csv.gz |  304 | 8ad4dc63a095332e158786cb6e8532d0 | Tue, 9 Jan 2018 15:31:44 GMT |
+--------------------+------+----------------------------------+------------------------------+

データをターゲットテーブルにコピーする

ステージングされたデータをターゲットテーブルにロードするには、 COPY INTO <テーブル> を実行します。

COPY INTO <テーブル> コマンドは、 Snowflakeオブジェクトを作成する で作成した仮想ウェアハウスを使用してファイルをコピーします。

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';
Copy

条件:

  • FROM 句は、データファイルを含む場所(テーブルの内部ステージ)を指定します。

  • FILE_FORMAT 句は、ファイルタイプを CSV として指定し、文字列を囲むために使用する文字として二重引用符(")を指定します。Snowflakeは、さまざまなファイルタイプとオプションをサポートしています。これらは CREATE FILE FORMAT で説明されています。

  • PATTERN 句は、コマンドがこの正規表現(.*employees0[1-5].csv.gz)に一致するファイル名からデータをロードする必要があることを指定します。

  • ON_ERROR 句は、 COPY コマンドでファイルにエラーが発生した場合の処理を指定します。デフォルトでは、コマンドは最初のエラーが発生したときにデータのロードを停止します。この例では、エラーを含むファイルはスキップされ、次のファイルのロードに移ります。(このチュートリアルのファイルにはエラーは含まれていません。これは単に説明を目的としたものです。)

COPY コマンドには、ファイルをロードする前にファイルを確認するためのオプションもあります。追加のエラーチェックと検証の手順の詳細については、 COPY INTO <テーブル> トピックと他の データロードチュートリアル をご参照ください。

COPY コマンドは、コピーされたファイルのリストと関連情報を示す結果を返します。

+--------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file               | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|--------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| employees02.csv.gz | LOADED |           5 |           5 |           1 |           0 | NULL        |             NULL |                  NULL | NULL                    |
| employees04.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                    |
+--------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+

ロードされたデータをクエリする

標準の SQL、サポートされている 関数 また 演算子 を使用して、 emp_basic テーブルにロードされたデータをクエリできます。

標準の DML コマンド を使用して、ロードされたデータの更新や追加データの挿入など、データを操作することもできます。

すべてのデータを取得する

テーブルからすべての行と列を返します。

SELECT * FROM emp_basic;
Copy

以下は部分的な結果です。

+------------+--------------+---------------------------+-----------------------------+--------------------+------------+
| FIRST_NAME | LAST_NAME    | EMAIL                     | STREETADDRESS               | CITY               | START_DATE |
|------------+--------------+---------------------------+-----------------------------+--------------------+------------|
| 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 |
 ...
 ...
 ...
| 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 |
+------------+--------------+---------------------------+-----------------------------+--------------------+------------+
Copy

追加のデータ行を挿入する

ステージングされたファイルからテーブルにデータをロードすることに加えて、 INSERT DML コマンドを使用してテーブルに行を直接挿入できます。

たとえば、2つの追加の行をテーブルに挿入するには、

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');
Copy

メールアドレスに基づいた行をクエリする

[ NOT ] LIKE 関数を使用して、英国のトップレベルドメイン名を持つメールアドレスのリストを返します。

SELECT email FROM emp_basic WHERE email LIKE '%.uk';
Copy

以下は結果の例です。

+--------------------------+
| EMAIL                    |
|--------------------------|
| gbassfordo@sf_tuts.co.uk |
| rtalmadgej@sf_tuts.co.uk |
| madamouc@sf_tuts.co.uk   |
+--------------------------+

開始日に基づき行をクエリする

たとえば、特定の従業員の福利厚生が開始される日付を計算するために、 DATEADD 関数を使用して従業員の就労開始日に90日を追加します。開始日が2017年1月1日より前の従業員でリストをフィルタリングします。

SELECT first_name, last_name, DATEADD('day',90,start_date) FROM emp_basic WHERE start_date <= '2017-01-01';
Copy

以下は結果の例です。

+------------+-----------+------------------------------+
| FIRST_NAME | LAST_NAME | DATEADD('DAY',90,START_DATE) |
|------------+-----------+------------------------------|
| Granger    | Bassford  | 2017-03-30                   |
| Catherin   | Devereu   | 2017-03-17                   |
| Cesar      | Hovie     | 2017-03-21                   |
| Wallis     | Sizey     | 2017-03-30                   |
+------------+-----------+------------------------------+

概要、クリーンアップ、および追加のリソース

おめでとうございます。この入門チュートリアルを無事完了しました。

数分かけて、短い要約とチュートリアルで説明されている重要な点を確認します。チュートリアルで作成したオブジェクトをドロップして、クリーンアップを検討することもできます。詳細は、Snowflakeドキュメントの他のトピックをご参照ください。

概要と重要なポイント

要約すると、データのロードは2ステップで実行されます。

  1. データファイルをステージし、ロードする。ファイルは、内部(Snowflake内)または外部の場所にステージングできます。このチュートリアルでは、内部的にファイルをステージングします。

  2. ステージングされたファイルから既存のターゲットテーブルにデータをコピーします。このステップには、稼働中のウェアハウスが必要です。

CSV ファイルのロードに関する次の重要なポイントを覚えておいてください。

  • CSV ファイルは、1以上の記録で構成され、各記録には1以上のフィールドがあり、場合によってはヘッダー記録もあります。

  • 各ファイルの記録とフィールドは、区切り文字で区切られています。デフォルトの区切り文字は次のとおりです。

    記録:

    改行文字

    フィールド:

    コンマ

    つまり、Snowflakeは、 CSV ファイルの各記録が改行で区切られ、各記録のフィールド(つまり個々の値)がコンマで区切られることを想定しています。記録およびフィールドの区切り文字として 異なる 文字を使用する場合、ロード時にファイル形式の一部として 明示的に 指定する必要があります。

  • ファイルのフィールドとロードするテーブルの列の間には、次の点で 直接的な 相関があります。

    • フィールド(ファイル内)および列(ターゲットテーブル内)の数。

    • それぞれのファイル/テーブル内のフィールドと列の位置。

    • フィールドと列の文字列、数、日付などのデータ型。

    数、位置、およびデータ型にデータとの整合性がない場合、記録はロードされません。

    注釈

    Snowflakeは、フィールドとターゲットテーブルの列が正確に揃っていないファイルのロードをサポートしています。ただし、これはより高度なデータロードトピックです( ロード中のデータの変換 で説明)。

チュートリアルのクリーンアップ(オプション)

このチュートリアルで作成したオブジェクトが不要になった場合は、 DROP <オブジェクト> ステートメントでそれらをシステムから削除することができます。

DROP DATABASE IF EXISTS sf_tuts;

DROP WAREHOUSE IF EXISTS sf_tuts_wh;
Copy

接続を終了する

接続を終了するには、SnowSQL(またはそのエイリアス !disconnect)に対して !exit コマンドを使用します。

終了は、現在の接続をドロップし、これが最後の接続である場合は SnowSQL を終了します。

次の内容

次のリソースを使用して、Snowflakeについて引き続き学習しましょう。