■ はじめに

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

の続き。

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

【０】サンプル
　１）データロードの流れ
　２）データロード例
【１】ウェアハウス - warehouse
【２】データベース / スキーマ - database / schema
　補足１：Managed Access Schema
【３】ステージ - stage
【４】データロード - data loading
　１）COPY INTO <TableName>
　補足１：データロードする際の注意点
　補足２：データのアンロードについて
【５】パフォーマンスチューニング

【０】サンプル

https://docs.snowflake.com/ja/user-guide-data-load.html

の公式ドキュメントにデータロードについて記載されているが
Hello world的なことであれば、そんな難しくないので、
データロードのサンプルをみてみる

１）データロードの流れ

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

[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

Snowflake ～ウェアハウス / Warehouse ～
https://dk521123.hatenablog.com/entry/2022/12/04/000000

より抜粋
~~~~~~~~~~~~~~
* 計算リソースのクラスタ（コンピュートリソース）をウェアハウスとして定義
~~~~~~~~~~~~~~

例
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

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

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

-- database
CREATE DATABASE IF NOT EXISTS demo_database;

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

-- schema
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 <TableName> を使う

１）COPY INTO

https://docs.snowflake.com/ja/sql-reference/sql/copy-into-table.html

より抜粋
~~~~~~~~~~~~~~
ステージングされたファイルから既存のテーブルにデータをロードします
~~~~~~~~~~~~~~

例

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

補足１：データロードする際の注意点

COPY INTO <テーブル> コマンドは、データ重複を防止するために
既にテーブルにロードされたステージングされたデータファイルを無視する
 => これにより、データ再ロードしても、取り込まれない可能性がある
 => トラブル解決やデータロード時の冪等性については、
　　以下の関連記事を参照のこと。

COPY INTO したがデータが格納されていない
https://dk521123.hatenablog.com/entry/2022/12/20/152040
Snowflake ～ Removeコマンド～
https://dk521123.hatenablog.com/entry/2022/09/26/150259

* 理解を深めるために、以下の公式ドキュメントを読んでみるといいかも。

COPY INTO <テーブル>
https://docs.snowflake.com/ja/sql-reference/sql/copy-into-table.html
古いファイルをロードする
https://docs.snowflake.com/ja/user-guide/data-load-considerations-load.html#label-loading-older-files

補足２：データのアンロードについて

* 以下の関連記事を参照のこと

https://dk521123.hatenablog.com/entry/2022/07/04/172738

【５】パフォーマンスチューニング

* 以下の関連記事を参照のこと

Snowflake ～データロードのパフォーマンス～
https://dk521123.hatenablog.com/entry/2022/12/07/111847

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

Memo for Programming.

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

■ はじめに

目次

【０】サンプル

１）データロードの流れ

２）データロード例

【１】ウェアハウス - warehouse

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

補足１：Managed Access Schema

【３】ステージ - stage

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

１）COPY INTO

補足１：データロードする際の注意点

補足２：データのアンロードについて

【５】パフォーマンスチューニング

関連記事