■ はじめに
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/2022/09/01/220643
より抜粋 ~~~~~~~ * ファイルからデータをロードする際に、そのファイルを置く場所 ~~~~~~~ => 後述「【４】データロード - data loading」の「サンプル」をみると 何のためにあるのか理解できるかも。 => ステージ - stage の詳細は、以下の関連記事を参照のこと。
Snowflake ～ 基本編 / ステージ ～
https://dk521123.hatenablog.com/entry/2022/09/01/220643
【４】データロード - 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 );
補足：データのアンロードについて
* 以下の関連記事を参照のこと
https://dk521123.hatenablog.com/entry/2022/07/04/172738
【５】リソースモニター - 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
Snowflake ～ 基本編 / ステージ ～
https://dk521123.hatenablog.com/entry/2022/09/01/220643
Snowflake ～ データ アンロード ～
https://dk521123.hatenablog.com/entry/2022/07/04/172738
Snowflake ～ Removeコマンド ～
https://dk521123.hatenablog.com/entry/2022/06/20/095659
エラー「Failed to cast variant value "" to XXX」時の対応
https://dk521123.hatenablog.com/entry/2021/12/17/102659