■ はじめに

https://dk521123.hatenablog.com/entry/2021/11/02/130111
https://dk521123.hatenablog.com/entry/2021/11/22/212520

の続き。

Snowflake を触わる機会があった。
主に、データロードまでやったのだが
そこにいきつくまでに、色々なことを学べたので
そのことをまとめておく。

【０】サンプル
　１）データロードの流れ
　２）データロード例
【１】ウェアハウス - warehouse
【２】データベース / スキーマ - database / schema
【３】ステージ - stage
【４】データロード - data loading
【５】リソースモニター - resource monitor

【０】サンプル

* まず、そんな難しくないので、
　データロードのサンプルをみてみる

１）データロードの流れ

* データロードの一連の流れは、以下の通り。

[1] データロード用のテーブルを用意
[2] ロード用のステージを作成
[3] データロード

２）データロード例

* 以下のサイトに書いてあるハンズオンをスクリプトでやってみる

https://dev.classmethod.jp/articles/snowflake-advent-calendar-2019-03-hands-on-lab-guide/

使用データ

* 以下のデータが使えるようなので、使ってみる

https://ride.citibikenyc.com/system-data

スクリプト例

-- 使用するロール/ウェアハウスを指定
USE ROLE sysadmin;
USE WAREHOUSE compute_wh;

-- 専用のDBを作成
CREATE DATABASE demo_bank;

-- 使用するDB/Schemaを指定
USE DATABASE demo_bank;
USE SCHEMA public;

-- データロード用のテーブルを用意
CREATE OR REPLACE TABLE demo_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 string, 
  end_station_name string, 
  end_station_latitude string, 
  end_station_longitude string, 
  bikeid integer, 
  membership_type string, 
  usertype string, 
  birth_year string, 
  gender integer);

-- ロード用のステージを作成
CREATE STAGE demo_stage
 URL = 's3://snowflake-workshop-lab/citibike-trips/';

-- データファイルの一覧表示
LIST @demo_stage;

-- データロード
COPY INTO demo_trips
  FROM @demo_stage
  FILE_FORMAT = (
    TYPE=CSV,
    FIELD_OPTIONALLY_ENCLOSED_BY = '"',
    EMPTY_FIELD_AS_NULL = TRUE,
    ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE
);

-- データ確認
SELECT * FROM demo_trips LIMIT 10;

データ表示

TRIPDURATION STARTTIME   STOPTIME    START_STATION_ID    START_STATION_NAME  START_STATION_LATITUDE  START_STATION_LONGITUDE END_STATION_ID  END_STATION_NAME    END_STATION_LATITUDE    END_STATION_LONGITUDE   BIKEID  MEMBERSHIP_TYPE USERTYPE    BIRTH_YEAR  GENDER
498 2013-09-10 08:26:07.000 2013-09-10 08:34:25.000 243 Fulton St & Rockwell Pl 40.687979   -73.978474  167 E 39 St & 3 Ave 40.7489006  -73.97604882    20184       Subscriber  1964    1
706 2013-09-10 08:26:07.000 2013-09-10 08:37:53.000 401 Allen St & Rivington St 40.72019576 -73.98997825    497 E 17 St & Broadway  40.73704984 -73.99009296    18059       Subscriber  1990    2
807 2013-09-10 08:26:07.000 2013-09-10 08:39:34.000 465 Broadway & W 41 St  40.75513557 -73.98658032    335 Washington Pl & Broadway    40.72903917 -73.99404649    18097       Subscriber  1972    2
506 2013-09-10 08:26:08.000 2013-09-10 08:34:34.000 521 8 Ave & W 31 St 40.75044999 -73.99481051    509 9 Ave & W 22 St 40.7454973  -74.00197139    16600       Customer        0
256 2013-09-10 08:26:09.000 2013-09-10 08:30:25.000 168 W 18 St & 6 Ave 40.73971301 -73.99456405    453 W 22 St & 8 Ave 40.74475148 -73.99915362    15629       Subscriber  1983    1
1256    2013-09-10 08:26:12.000 2013-09-10 08:47:08.000 521 8 Ave & W 31 St 40.75044999 -73.99481051    305 E 58 St & 3 Ave 40.76095756 -73.96724467    19190       Subscriber  1979    1
300 2013-09-10 08:26:13.000 2013-09-10 08:31:13.000 492 W 33 St & 7 Ave 40.75019995 -73.99093085    472 E 32 St & Park Ave  40.7457121  -73.98194829    15693       Subscriber  1974    1
596 2013-09-10 08:26:14.000 2013-09-10 08:36:10.000 293 Lafayette St & E 8 St   40.73028666 -73.9907647 472 E 32 St & Park Ave  40.7457121  -73.98194829    15562       Subscriber  1970    1
848 2013-09-10 08:26:15.000 2013-09-10 08:40:23.000 519 Pershing Square N   40.75188406 -73.97770164    475 E 16 St & Irving Pl 40.73524276 -73.98758561    19689       Subscriber  1950    1
558 2013-09-10 08:26:16.000 2013-09-10 08:35:34.000 147 Greenwich St & Warren St    40.71542197 -74.01121978    296 Division St & Bowery    40.71413089 -73.9970468 15242       Subscriber  1960    2

【１】ウェアハウス - warehouse

* 計算リソースのクラスタ（コンピュートリソース）をウェアハウスとして定義
 => サイズが存在していて、大きければレスポンスも早くなるがお金も掛かる

cf. warehouse = 倉庫

* 以下のサイトが参考になるかも。

https://dev.classmethod.jp/articles/snowflake-introduction-virtualwarehouses/

サンプル

https://docs.snowflake.com/ja/sql-reference/sql/create-warehouse.html

CREATE WAREHOUSE demo_warehouser
  -- 仮想ウェアハウスのサイズ（デフォルト：XSMALL）
  with warehouse_size = 'XSMALL'
  max_cluster_count = 2
  min_cluster_count = 1
  -- ウェアハウスが自動的に中断されるまでの非アクティブの秒数
  auto_suspend = 120
  -- SQL文の送信時にウェアハウスを自動的に再開するかどうか
  auto_resume = TRUE
  -- ウェアハウスが最初に「一時停止」状態で作成されるかどうか
  INITIALLY_SUSPENDED = TRUE
;

【２】データベース / スキーマ - database / schema

https://docs.snowflake.com/ja/sql-reference/ddl-database.html

* 以下のように書いているが、database -> schemas -> tables みたいな関係

database

* スキーマの論理的なグループ

schema

* データベースオブジェクト（テーブル、ビューなど）の論理的なグループ

サンプル

database
https://docs.snowflake.com/ja/sql-reference/sql/create-database.html

CREATE DATABASE IF NOT EXISTS demo_database;

schema
https://docs.snowflake.com/ja/sql-reference/sql/create-schema.html

CREATE SCHEMA IF NOT EXISTS demo_schema
  -- 管理スキーマを指定
  -- 管理スキーマでは、スキーマの所有者がスキーマ内のオブジェクトすべての権限付与を管理
  WITH MANAGED ACCESS
;

補足：Managed Access Schema

* 以下のサイトを参照。

https://qiita.com/zairu/items/32befa4a863c0c32709c

【３】ステージ - stage

https://dk521123.hatenablog.com/entry/2021/11/02/130111

より抜粋

* ファイルからデータをロードする際に、そのファイルを置く場所

 => 後述「【５】データロード - data loading」の「サンプル」をみると
　　何のためにあるのか理解できるかも。

サンプル

https://docs.snowflake.com/ja/sql-reference/sql/create-stage.html
例１：CREATE IF NOT EXISTS

-- For External
CREATE TEMPORARY STAGE IF NOT EXISTS demo_temp_stage
  URL = 's3://your-s3-bucket-name/demo_warehouse/demo_database/demo_table/'
  -- ストレージの場所がプライベート/保護されている場合にのみ必要
  STORAGE_INTEGRATION = xxxxx
  FILE_FORMAT = (TYPE =PARQUET COMPRESSION = SNAPPY)
;

例２：CREATE OR REPLACE

-- For External
CREATE OR REPLACE TEMPORARY STAGE demo_temp2_stage
  URL = 's3://your-s3-bucket-name/demo_warehouse/demo_database/demo_table/'
  -- ストレージの場所がプライベート/保護されている場合にのみ必要
  STORAGE_INTEGRATION = xxxxx
  FILE_FORMAT = (TYPE =PARQUET COMPRESSION = SNAPPY)
;

【４】データロード - data loading

* COPY INTO を使う

サンプル

USE DATABASE demo_database;
USE SCHEMA demo_schema;

-- [1] Create table 
CREATE TABLE demo_database.demo_schema.copyed_demo_table IF NOT EXISTS (
  id BIGINT NOT NULL
  , name VARCHAR(100) NOT NULL
  , age int
);

-- [2] Create temp stage
-- => s3内にあるデータを外部テーブルとして定義している
CREATE TEMPORARY STAGE IF NOT EXISTS demo_database.demo_schema.demo_temp_stage
  URL = 's3://your-s3-bucket-name/demo_warehouse/demo_database/demo_table/'
  STORAGE_INTEGRATION = xxxxx
  FILE_FORMAT = (TYPE =PARQUET COMPRESSION = SNAPPY)
;

-- [3] Loading
COPY INTO demo_database.demo_schema.copyed_demo_table
FROM
(
  SELECT
    $1:id
    , $1:name
    , $1:age
  FROM
    @demo_database.demo_schema.demo_temp_stage
);

【５】リソースモニター - resource monitor

* データロードには、関わってないが、学んだんでメモしておく

https://docs.snowflake.com/ja/user-guide/resource-monitors.html

サンプル

CREATE RESOURCE MONITOR demo_resource_monitor WITH
  -- 頻度間隔ごとにリソースモニターに割り当てられたクレジット数
  REDIT_QUOTA = 8
  FREQUENCY = DAILY
  START_TIMESTAMP = IMMEDIATELY
  TRIGGERS
   ON 50 percent do notify
   ON 75 percent do notify
   ON 99 percent do notify
;

Snowflake ～基礎知識編～
https://dk521123.hatenablog.com/entry/2021/11/02/130111
Snowflake ～入門編 / Hello world ～
https://dk521123.hatenablog.com/entry/2021/11/22/212520
Snowflake ～基本編 / データ型～
https://dk521123.hatenablog.com/entry/2021/12/16/095524
Snowflake ～基本編 / アクセス制御～
https://dk521123.hatenablog.com/entry/2021/11/16/231010
Snowflake ～基本編 / ゼロコピークローン～
https://dk521123.hatenablog.com/entry/2021/11/27/134934
エラー「Failed to cast variant value "" to XXX」時の対応
https://dk521123.hatenablog.com/entry/2021/12/17/102659

プログラムの超個人的なメモ

Memo for Programming.

【Snowflake】Snowflake ～基本編 / データロード～

■ はじめに

目次

【０】サンプル

１）データロードの流れ

２）データロード例

【１】ウェアハウス - warehouse

サンプル

【２】データベース / スキーマ - database / schema

サンプル

補足：Managed Access Schema

【３】ステージ - stage

サンプル

【４】データロード - data loading

サンプル

【５】リソースモニター - resource monitor

サンプル

関連記事