こん◯◯は！ がく＠ちゅらデータエンジニアです。
Frosty Friday 4日目の記事です。
Frosty Friday Week30 SnowSQL and Scripting
今回はSnowSQLを使って、環境構築を行うチャレンジです。
※Terraformでやれるよなーーって思いながらやってました（笑）
こちらがチャレンジの内容になります。
先日、YoutubeLiveで解説した動画がこちらになります。
Week30のチャレンジ内容
※Google翻訳ほぼそのままです。
今週は、どんな営業マンも夢中になるような課題について見ていきます。
すぐに使える「データベース イン ア ボックス」または汎用テンプレート:
SnowSQL を使用して、一方ではこのスクリプトに、もう一方では対象のスノーフレーク インスタンスを指定して、すぐに使用できるデータベースを作成できるようにします。
プライマリ スクリプト (任意の形式) を作成します。実行すると、以下が作成されます。
– 4 つのデータベース。名前は、Development、Testing、Acceptance、Production。
– すべてのデータベース (Public 以外) に、security という 1 つの追加スキーマ。
– 開発環境に 'dev_user' という 1 つの追加スキーマ。
– 3 人のユーザー: security_user、dev_user、regular_user。
ユーザーのデフォルト値:
password = abc123。
セカンダリ ロールを有効にする必要があります。
– dev_role および security_role という 2 つの追加ロール。
– dev_role は、セキュリティ スキーマの変更を除き、すべてのデータベース内のすべての (将来の) テーブル、ビュー、スキーマを作成およびクエリできる必要があります。
– security_role は、すべてのデータベース内のすべての (将来の) テーブル、ビュー、スキーマを作成およびクエリできる必要があります。
– dev_user には dev_role が、security_user には security_role が必要です。
– ロール public は Production.Public のみをクエリできます。
– default_wh と呼ばれる 1 つの小さなウェアハウス
次の内容を含むセカンダリ スクリプトを作成します。
二次スクリプト
テーブル departments(dep_id varchar、dep_name varchar) を作成します。
部門に VALUES
('d001′,'マーケティング')、
('d002′,'財務')、
('d003′,'人事')、
('d004′,'生産')、
('d005′,'開発')、
('d006′,'品質管理')、
('d007′,'販売')、
('d008′,'研究')、
('d009′,'顧客サービス') を挿入します。
正しいパス (引数) を指定すると、データベース イン ア ボックスはこのセカンダリ スクリプトを呼び出す必要があります。
セカンダリ スクリプトへのパスは、プライマリ スクリプトにハードコードしないでください。
やってみよう
私の回答は、下記のGithubレポジトリで公開しています。
まずは、今回のお題のSnowSQLで接続するところです。
※SnowSQLのインストールは、こちらを参考にしてください。
※私は、homebrewを使ってインストールしました。
snowsql -a $SNOWSQL_ACCOUNT -u $SNOWSQL_USER --authenticator externalbrowser
環境変数については、direnvというコマンドを使っています
export SNOWSQL_ACCOUNT=********.ap-northeast-1.aws
export SNOWSQL_USER=********@churadata.okinawa
cd Frosty-Friday/week030_intermediate_snowsql_and_scripting
とすると
こんな感じで読み込んでくれます。
あと、私の場合は、認証にSSOを使う環境でやっているため
--authenticatgor externalbrowser
というコマンドを使っています。
KeyPairを使う場合は、JWTとかを指定しますね
コマンドを実行すると
こんなメッセージが出たあとに、ブラウザで認証が走ります。
認証が済むとブラウザにこんなメッセージがでて
SnowSQL上でログインが出来ます。
試しに、
show databases like 'FF_%';
とコマンドを打つと
FF_で始まるDatabase一覧が出ますね
※ここはどんなSQLでもいいっちゃいい
解法1 まずは、ベタ書き
ここで、ベタ書きをしたファイルを
まず最初に4つのDATABASEを作りましょう
create database ff_30_development;
create database ff_30_testing;
create database ff_30_acceptance;
create database ff_30_production;
こちらをSnowSQLに貼り付けて実行します。
このコマンドの最後に
-- Execute the secondary script
use role sysadmin;
use database ff_30_development;
use schema "FF_30_DEVELOPMENT"."PUBLIC";
!source week30_secondary.sql
!source week30_secondary.sql を行うとweek30_secondary.sqlの中身のSQLが実行されます。
ここで、クリーンアップしましょう
!source week30_cleanup.sql
できれいになりました。
解法2 変数展開を使って効率よくする
まず をつかって、ユーザを作ります
変数展開をするためには、
!set variable_substitution=true
という設定をしなけば変数展開されません
設定することで変数展開ができます。
database_nameとrole_nameを設定してみます
!define database_name=ff_30_development;
!define role_name=ff_30_dev_role;
と設定したあとに
create database &database_name;
create schema &database_name.security;
use role securityadmin;
-- dev_role should be able to create and query all (future) tables, views, and schemas in every database, except for changing anything in the security schemas.
grant usage, create schema on database &database_name to role &role_name;
grant usage, create table, create view on schema &database_name.public to role &role_name;
grant select on future tables in schema &database_name.public to role &role_name;
grant select on all tables in schema &database_name.public to role &role_name;
grant select on future views in schema &database_name.public to role &role_name;
grant select on all views in schema &database_name.public to role &role_name;
こちらを実行すれば、変数展開されて作成＆権限の付与ができます。
!define database_name=ff_30_testing;
!define role_name=ff_30_dev_role;
として、上記のSQLをSnowSQLで実行すればできます
解法2' snowsqlコマンドから叩き込む
-- まずユーザとロールを作成
snowsql --authenticator externalbrowser -f week30_2_1_user_and_role.sql
-- 変数展開をtrueにして、変数を指定し、データベースオブジェクトをつくって、権限付与する
snowsql --authenticator externalbrowser -f week30_2_2.sql -o variable_substitution=true --variable database_name=ff_30_testing --variable role_name=ff_30_dev_role
-- クリーンナップ
snowsql --authenticator externalbrowser -f week30_cleanup.sql
このようにコマンドラインから、変数の設定とSQLの実行が出来ます
【おまけ】SnowSQLのプロンプトを変更
-- default
!set prompt_format=[user]#[warehouse]@[database].[schema]>
-- サンプル(https://docs.snowflake.com/ja/user-guide/snowsql-use#prompt-example)
!set prompt_format=[#FF0000][user].[role].[#00FF00][database].[schema].[#0000FF][warehouse]>
-- 多すぎ、とりあえず全部乗っけた
!set prompt_format=[#0000FF]([account])([role])[#000000][user]@[#00FF00][database].[#009900][schema].[#0000FF][warehouse]>
-- Snowsightでの表示を参考に
!set prompt_format=[#0000FF]([role])[#00FFFF][warehouse][#000000]@[#00FF00][database].[#009900][schema]>
こんな感じで色を付けたり出来ます。
個人的にはSnowsightにでている情報量をだす
!set prompt_format=[#0000FF]([role])[#00FFFF][warehouse][#000000]@[#00FF00][database].[#009900][schema]>
がいいかなーと思っています
SnowSQLの「コマンド」Tips
!system ls
とするとローカルのファイル名リストがみれます
なにげに結構使えます♪
SnowSQLで使えるコマンド（！から始まるもの）
まとめ
SnowSQLを使ってみましたが、補完機能などSnowsightよりもよっぽど高機能で非常に使いやすかったです。
SnowSQLはGithub Actionsから使ったりもしています。
Terraformで対応していない部分とかをSnowSQLで代替するようにな使い方です。
接続先を環境変数で切り替えることも出来ますし、こちらで切り替えながら色々やってみるのもいいかなと思いました
Discussion