SnowflakeのSnowVillage Unconference #3でLT初登壇してきました

こちらのイベントに初参加、初登壇してきました。
CARTA HOLDINGさん、素敵なオフィスでした。

お題：実テーブルと外部テーブルとViewとマテリアライズドビューの比較

スライドとか作ってなくてSnowsight内のワークシート1個を動かしながら話すというスタイルです。
順番的に最後の枠だったのですが、みなさんバッチリスライドがあってビビりました。
そしてレベルが高い。
自分の発表内容は初級編的なところ。
「いろんなレベルのLTがあった方がいいよね（KTさんもそんなようなこと言ってたと思う）」と自分に言い聞かせつつ話しました。

使ったワークシート

以下のテキストがワークシートのコピペです。
（先頭一行目に会社名と名前を入れてましたがそこだけ削除）
しゃべりながら説明をする前提のワークシートなのでわかりづらいかもです。

どなたでもコピペしたらご自身のSnowflake環境で動きます。

--Quick Start Snowflake入門 - ゼロからはじめるSnowflake
--https://quickstarts.snowflake.com/guide/getting_started_with_snowflake_ja/index.html#0
--ここで用意されているCITIBIKEのtripsデータを使います。

--Qiita記事
--Snowflakeのステージに対するディレクトリテーブルのselectとlistコマンドの違い
--https://qiita.com/a-kamiya/items/d16f897f7341ace1ab0a
--Snowflakeの外部テーブル、完全に理解した
--https://qiita.com/a-kamiya/items/c1f2166bd1e60b3e1f40

--DB、スキーマ作成
create database TEST_DB;
create schema TEST_SCHEMA;

--QuickStartのCITIBIKEのcsvファイルは公開S3バケットのため、
--ストレージ統合（STORAGE INTEGRATION）の作成は不要

--外部ステージを作成（末尾にスラッシュあり）
CREATE STAGE CITIBIKE_STAGE 
	URL = 's3://snowflake-workshop-lab/citibike-trips/' 
	DIRECTORY = ( ENABLE = true );

--外部ステージを作成（末尾にスラッシュなし）
CREATE STAGE CITIBIKE_STAGE_WITHOUT_SLASH
	URL = 's3://snowflake-workshop-lab/citibike-trips' 
	DIRECTORY = ( ENABLE = true );
--GUI上で比較
--どちらも1件

--ワークシート上でクエリを打って確認
--比較1（Directory Table）
select * from directory(@citibike_stage);
select * from directory(@citibike_stage_without_slash);
--どちらも1件取得

--比較2（LISTコマンド）
list @citibike_stage;
--1件
list @citibike_stage_without_slash;
--12,106件
--Directory TableとLISTコマンドで挙動が違う？？
--バグなのか明確な理由があるのかわかる方いたら教えてください

--教訓：外部ステージの作成の際、末尾のスラッシュは忘れないように！
--QuickStartのページにも
--「**注意：**URLの最後には、必ずフォワードスラッシュ（/）を入れてください。これを入れておかなければ、後でバケットからデータを読み込む際にエラーが発生します。」
--とあります。

--ここまではモヤモヤの共有
--（「末尾にスラッシュなし」の外部ステージはもう使用しません）
drop stage CITIBIKE_STAGE_WITHOUT_SLASH;

--脱線終了

--本線に戻る
--ここからは通常のテーブルと外部テーブルとViewとマテリアライズドビューの比較

--CSVファイル読み込み用のファイルフォーマット（Quick Startのページから）
create or replace file format FORMAT_CSV type='csv'
  compression = 'auto' field_delimiter = ',' record_delimiter = '\n'
  skip_header = 0 field_optionally_enclosed_by = '\042' trim_space = false
  error_on_column_count_mismatch = false escape = 'none' escape_unenclosed_field = '\134'
  date_format = 'auto' timestamp_format = 'auto' null_if = ('') 
  comment = 'file format for ingesting data for zero to snowflake';

--COPY INTOする実テーブル作成（Quick Startのページから）
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
);

--外部ステージからCOPY INTOでデータロード
copy into trips from @citibike_stage file_format=FORMAT_CSV PATTERN = '.*csv.*' ;
--5件だけSELECTしてみる
select * from trips limit 5;

--外部テーブル作成（末尾に_ext）
create external table trips_ext
  with location = @citibike_stage
  file_format = FORMAT_CSV;
--外部テーブルの詳細を表示
desc table trips_ext;
--5件だけSELECTしてみる
select * from trips_ext limit 5;
--JSON形式で表示された（見づらい・・・）

--外部テーブルは扱いにくいのでビュー作成（末尾に_v）
create or replace view trips_v as
select
  nullif(value:c1,'')::integer as tripduration,
  nullif(value:c2,'')::timestamp_ntz as starttime,
  nullif(value:c3,'')::timestamp_ntz as stoptime,
  value:c4::varchar as start_station_id,
  value:c5::varchar as start_station_name,
  nullif(value:c6,'')::double as start_station_latitude,
  nullif(value:c7,'')::double as start_station_longitude,
  value:c8::varchar as end_station_id,
  value:c9::varchar as end_station_name,
  nullif(value:c10,'')::double as end_station_latitude,
  nullif(value:c11,'')::double as end_station_longitude,
  value:c12::varchar as bikeid,
  value:c13::varchar as membership_type,
  value:c14::varchar as usertype,
  value:c15::varchar as birth_year,
  value:c16::varchar as gender
from trips_ext;
--5件だけSELECTしてみる
select * from trips_v limit 5;

--外部テーブルは扱いにくいのでマテビュー作成（末尾に_mv）
create or replace materialized view trips_mv as
select
  nullif(value:c1,'')::integer as tripduration,
  nullif(value:c2,'')::timestamp_ntz as starttime,
  nullif(value:c3,'')::timestamp_ntz as stoptime,
  value:c4::varchar as start_station_id,
  value:c5::varchar as start_station_name,
  nullif(value:c6,'')::double as start_station_latitude,
  nullif(value:c7,'')::double as start_station_longitude,
  value:c8::varchar as end_station_id,
  value:c9::varchar as end_station_name,
  nullif(value:c10,'')::double as end_station_latitude,
  nullif(value:c11,'')::double as end_station_longitude,
  value:c12::varchar as bikeid,
  value:c13::varchar as membership_type,
  value:c14::varchar as usertype,
  value:c15::varchar as birth_year,
  value:c16::varchar as gender
from trips_ext;
--5件だけSELECTしてみる
select * from trips_mv limit 5;

--実テーブルと外部テーブルとViewとマテリアライズドビューの比較
SELECT * FROM TRIPS;	--実テーブル
SELECT * FROM TRIPS_EXT;	--外部テーブル
SELECT * FROM TRIPS_V;	--外部テーブルのView
SELECT * FROM TRIPS_MV;	--外部テーブルのマテリアライズドビュー
--所要時間を比較

--所要時間の比較結果
--実体のある実テーブルとマテリアライズドビューは早い、S3のファイルを見に行っている外部テーブルとViewは遅い
--都度S3のファイル参照するのはつらいので採用不可
--実体のある実テーブルと外部テーブルのマテリアライズドビュー、どっちかを採用したい

--ではどっちを使うべき？
--外部テーブルのマテリアライズドビューを使うと、COPY INTOを毎日実行とかしなくてよくてデータパイプラインがシンプルになる。
--マテリアライズドビューが勝手にデータ更新（＝S3ファイル更新）を反映してくれる。
--この点は外部テーブルの優位性あり。

--ただし、S3側で「このS3のファイル、いらないんじゃね？」って思われてファイルを消されるとマテリアライズドビューからデータも消える。
--S3ファイルもSnowflakeも自分が管理しているならいいけど、S3側のファイルは別の人の管轄だと消される可能性もある。
--最初はS3側管理者も理解していたとしても、担当者が変わったりして、消しちゃダメの理由が不明になっていつか事故が起きる予感しかない。
--この事故の予感は見逃せない。
--そのため、COPY INTOで実テーブル化するのがよいというのが私の結論。


--環境削除
drop schema TEST_SCHEMA;
drop database TEST_DB;

最後の方はコードじゃなくて私の思うところを日本語でつらつら書いています。

質問

対象データのカラムが増えたりしたらマテリアライズドビューの場合全件リフレッシュが走ってツライのでは？という質問いただきました。
正直全く考えてなかったです。
カラムが増えるのはあるあるなので考えないといけないです。

--追記
Snowflakeの中の人、本橋さんから「全くつらくない」とコメントもらいました。

登壇後のふりかえり

写真

@ak-sakatoku さんのXのポストの写真をあとで見ました。（ギリ全員顔が写ってない奇跡の写真）

初登壇で緊張してPC見ながら話せばいいのに、なぜかスクリーンを見ながら話すって感じになってました。
手元にPCがあって操作しづらいことこの上なし。
「他の登壇者さんたちは前向いてしゃべってたよなー。なんで自分はみんなと同じスクリーン見てるんだろ？」
「前を向け、前を！」
という反省点。

ダイナミックテーブル

ちょっと前に書いた、「ビューとマテリアライズドビューとダイナミックテーブル」の投稿があります。
ダイナミックテーブルも含めて試せばよかった（忘れてました）。

感想

社外では初登壇で緊張しました。
チャレンジしてよかったです。