#03:ハンズオンラボガイド 「ロードするデータの準備」 – Snowflake Advent Calendar 2019 #SnowflakeDB

どうもどうもどうもどうも。大阪オフィスの玉井です。

当エントリは『Snowflake Advent Calendar 2019』の3日目のエントリです。

「HANDS-ON LAB GUIDE FOR SNOWFLAKE FREE TRIAL」は、以下の9つのコンテンツで構成されます。本日はこの中の「Module3:Preparing to Load Data」を紹介します。

  • Module 1: Prepare Your Lab Environment
  • Module 2: The Snowflake User Interface & Lab “Story”
  • Module 3: Preparing to Load Data
  • Module 4: Loading Data
  • Module 5: Analytical Queries, Results Cache, Cloning
  • Module 6: Working With Semi-Structured Data, Views, JOIN
  • Module 7: Using Time Travel
  • Module 8: Roles Based Access Controls and Account Admin
  • Module 9: Data Sharing

事前情報

このモジュールの概要

  • データベースとテーブルを作成する
  • External Stageを作成する
  • File Formatを作成する

使用するデータ

Citi Bikeというシェアサイクリングサービスの利用者に関するデータを使います。

運営元が公式に公開しているデータ(要するにオープンデータ)になります。US-EASTリージョンのAmazon S3に配置されています。利用者の性別や年齢、乗った場所や時間などが記録されています。データは、6150万件あり、オブジェクトは377個、サイズは(圧縮されて)1.9GBあります。ダブルコーテーション囲みのカンマ区切りです。

モジュール本編

3.1 データベースとテーブルの作成

CITIBIKEというデータベースを作成します。

UIの上部にある「Database」タブを選択します。次に、「Create」をクリックし、データベースにCITIBIKEという名前を付け、「Finish」をクリックします(小文字で入力しても、自動的に大文字に変換されます)。

UIの上部にある 「Worksheets」 タブをクリックすると、SQLを実行できる画面に遷移します。

ここで、最初に、Worksheets内でコンテキストを適切に設定する必要があります。右上の「Context」セクションの横にあるドロップダウンの矢印アイコンをクリックして、Worksheetsのコンテキストメニューを表示します。ここでは、ユーザーが各Worksheetsから表示および実行できる要素を制御します。 ここでは、UIを使用して、下記のようにContextを設定します。

  • Role: SYSADMIN
  • Warehouse: COMPUTE_WH (XL)
  • Database: CITIBIKE
  • Schema = PUBLIC

※私の環境がハンズオンラボと若干異なるため、微妙に設定値が異なっています。

ちなみに、ここまでで行った操作は、コンピューティングリソースを必要としないため、すべてのオブジェクトは無料で作成されています

次に、カンマ区切りデータのロードに使用するTRIPSというテーブルを作成します。UIのWorksheetsタブを使ってSQLを実行し、テーブルを作成します。

create or replace table trips 
(tripduration integer, 
  starttime timestamp, 
  stoptime timestamp, 
  start_station_id integer, 
  start_station_name string, 
  start_station_latitude float, 
  start_station_longitude float, 
  end_station_id integer, 
  end_station_name string, 
  end_station_latitude float, 
  end_station_longitude float, 
  bikeid integer, 
  membership_type string, 
  usertype string, 
  birth_year integer, 
  gender integer); 

Worksheetsに上記クエリを記述し、RunボタンかCtrl/Cmd+Enterを押すと、クエリが実行されます。SQLは、このWorksheetsから実行する以外にも、SnowSQLやPythonコネクタ等から実行する方法があります。

メニューのDatabasesを選択すると、先程作成したTRIPSテーブルが目視できます。テーブル名をクリックすると、テーブルの情報を確認することができます。

3.2 External Stageを作成する

今回は、すでに公開されているパブリックなS3バケットにステージングされているデータを扱います。このデータを使用する前に、まずバケットの場所を指定するExternal Stageを作成する必要があります。また、このハンズオンラボではEastリージョンのバケットを使用しますが、本来はSnowflakeのリージョンとS3バケットのリージョンは同じにすることがベストです(データ転送のコスト的に)。

Databases→Stages→Createとメニューを進めます。そして、今回持ってくるデータが置いてあるサービス…つまりS3を選びます。

下記のバケット情報を入力して設定を完了します。今回のバケットはパブリックのため、下記以外の設定は不要ですが、本来は基本的に非公開バケットを利用することがほとんどですので、そのときは別途キー情報等も設定する必要があります。

  • Name citibike_trips
  • Schema Name PUBLIC
  • URL s3://snowflake-workshop-lab/citibike-trips

citibike_tripsというステージを作成しました。詳細を見てみましょう。下記のクエリを実行します。

 list @citibike_trips; 

S3に配置されているファイルの情報をみることができました。

3.3 File Formatを作成する

External Stageを作成できたので、S3に置いてあるファイルからテーブルにデータをロードするぞ~ってなりますが、ちょっと待ちましょう。ロードする前に、データの構造を指定するFile Formatを作成する必要があります。

Databases→File Formats→Createとメニューを進めます。そして、下記の通り設定します。

  • Name: CSV
  • Field optionally enclosed by: Double Quote
  • Null string: 既存のテキストを削除して空にする
  • Error on Column Count Mismatch: チェックボックスを外す

File Formatが作成できました!さあついにデータをロードする時がきた?次は一体何をすればいいのか??

to be continued…(明日のModule4へ続く)

さいごに

今回はS3に置いてあるファイルをテーブルにロードするための前準備編という位置づけでした。

明日4日目は兼本侑始による「#04 : ハンズオンラボガイド『データのロード』」の予定です。お楽しみに。