がく＠ちゅらデータエンジニアです！
先日より、以前からやってみたかったFrosty Fridayをやる深夜RADIO的な番組を始めました。
一緒にメインMCをやってるのが、Tableau DataSaber時代からの友達の @tomowk1 さん♪
Frosty Fridayとは
2022年07年に最初のお題が投稿されたSnowflakeのスキルアップを目的とした学習コンテンツです。
1週間〜2週間に一度、Snowflakeに関するお題が出題されます。
レベル的には、初級、中級、上級があります。
Frosty Fridayへの参加方法は、
- 作ったコードをご自身のGithubなどで公開
- そのURLをお題（Week1など）にコメントを記載
回答は”公式”にはありません！
ただ、世界中でSnowflakeを使っている人のチャレンジの結果が閲覧でき、すごく参考になり面白いです。
Snowflake実務者の方々のノウハウが知れるコンテンツとなっています
現時点(2024年5月現在）で、Week94 まで出題されています。
後発ではありますが、Week1から順番にお題を進めています。
Frosty Friday Live Challenge Vol.4
今回は
Week9 - Immediate Tags, Masking Policies
Week10 - Hard Stored Procedure
です。
タイムラインはこちら
0:00 オープニング
1:30 What's New : コスト最適化、Pandas API、Dark Mode
8:30 Topics : Snowflake Notebook by Maeda-san, dbt cloud on marketplace with Sagara-san
16:36 Frosy Fridayとはなんぞ
18:27 Week9 Intermediate Tags, Masking Policies 問題説明
22:03 Week9 解法①
27:30 Week9 解法②
33:34 Week10 Hard Strored Procedures 問題説明
35:50 Week10 解法①
48:56 Week10 解法①’
54:05 Week10 解法② Snowflake Notebook版、完成形
1:08:43 クロージング
直近の話題からピックアップ
What's New
GAまつり！GA！GA！
なので、レディー・ガガ（Lady Gaga）
コスト最適化（GA）
その月の予測値がでたり、WH毎にコスト、DB毎にストレージ、よく使われてるクエリ、無駄なことしてませんか？のサジェストがあったりと
かなり使えます！！
Snowpark pandas API
DARK Mode!
みためがとってもVSC◯de！！!
あとから設定したい場合は、
画面左下部にある自分のアイコンをクリック、外観
Topics
大活躍の前田さんの記事
■dbt Cloud on Snowflake Marketplace
Frosty Friday Live Challenge
今回は
- Week9 Intermediate Tags, Masking Policies by @gak_t12
- Week10 Hard Stored Procedure by @gaku_t12
です。
解説は動画を見てください！
Week9 Intermediate Tags, Masking Policies @gaku_t12
問題
Week9 Intermediate : Tags, Masking Policies
■ストーリー
秘密を守る必要があるのは悪者だけではありません。
スーパーヒーローは悪者に対する防衛線なので、スーパーヒーローの情報は保護する必要があります。
しかし、スーパーヒーロー組織の運営は規模が大きく、システムにアクセスできる人が大勢います。
ヒーローの正体が決して明かされないようにしなくてはいけません。
より基本的なレベルでつながるために、一部のスタッフがスーパーヒーローのファーストネームを見れるようにしたいという要望が出ています。
上層部は、すべての情報を見ることができる必要があります。
組織内の役割は常に変化するため、まだ作成されていない役職に対しても、フレキシブルに対応、情報保護処理ができるようにしなくてはいけません。
■課題
タグとマスキングを使用して、data_to_be_masked テーブルから first_name 列と last_name 列をマスクします。
マスキングの挙動としては、次のようになります。
- アクセス権を持つデフォルトのユーザーは、マスクされていないhero_nameデータのみを見ることができます。
- ロールfoo1はhero_nameとfirst_nameのみを見ることができます
- ロールfoo2はテーブル全体の内容を見ることができる
- 使用されるマスキング ポリシーでは、ロール チェック機能を使用しないでください。(current_role = … など)
コードはこちら
Week10 Hard Stored Procedures @gaku_t
問題
Week10-hard : Stored Procedure
Frosty Consultingでは、ステージからデータを手動かつ動的にロードできるようにしたいという依頼を受けました。
具体的には、次のことが実現できるようにしたいと考えています。
- 単一のコマンド（ストアドプロシージャ）を実行する
- 手動で行うと、スケジュールされず、Snowpipesも使用できなくなります。
- ウェアハウスのサイズを動的に変えて実行、ファイルが 10 KB を超える場合はサイズSmallのウェアハウスを使用し、その10 KB未満のファイルは xsmall ウェアハウスで処理する。
解法①
以前、こちらにてSnowflake Scripting（Stored Procedure, Lang:SQL）の記事を書いたのですが、そこで使った記述も使っています。
INTOを使った変数(count_a）へのSQL実行結果のセット のドキュメントと例
select count(*) into :count_a from table_a;
解法②
この解法では、INFER_SCHEMAとCOPY INTO のOPTION：INCLUDE_METADATA を使っています。
COPY INTO table1 FROM @stage1
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE
INCLUDE_METADATA = (
ingestdate = METADATA$START_SCAN_TIME, filename = METADATA$FILENAME);
また、INFER_SCHEMAの結果に、JSONでMETADATAのカラムをくっつけるアイデアは、monaka（つぶあん）さんのこちらの記事を参考にしています。
この INCLUDE_METADATAを使う際のFILE FORMATでは
- parse_header = true
- error_on_column_count_mismatch = false
が必要です。
error_on_column_count_mismatch は、入力ファイルの区切り列（フィールド）の数が対応するテーブルの列の数と一致しない場合に、解析エラーを生成するかどうかを指定するブール値
copy into include_metadata を使うには、falseにする必要がある
のですが、カラム数が変わるとエラーにしたい場合には使えないなぁ・・・・という感じで、ちょっとだけ微妙です・・・・
解法③ Notebook版
動画ではSnowflake Notebookを使って解説しました
初めて使ってみたのですが、結果がきれいにでたりととても良かったです
解説（デモ）が映えますね！
list @my_stage;
select $1 as name, $2 as size from table(result_scan(last_query_id()));
これは一つのセルに入れないと動きませんでした！
list @my_stage;
のあとに、次のセルで
select $1 as name, $2 as size from table(result_scan(last_query_id()));
を実行しても動きませんでした
というような細かい点があったりしますが、総じてとても使いやすく、見た目もとても素敵でした
※これまでJupyter Notebook使ってこなかったのですが、今後Frosty FridayではNotebookつかおうかなーとおもってます♪
Discussion