【完全公開】Twitter投稿した人を自動的にSnowflakeへ登録して、イベント参加者にデータベースアクセスできるようにするスクリプト
Twitter上で投稿した人のTwitter IDを使ってSnowflake（データベース）のユーザーを自動的に作成するスクリプトを完全公開します！
Snowflakeのフリートライアルとスクリプトのコピペで誰でも無料でいますぐ実装可能です。
Twitter×Snowflakeはコミュニティイベントを劇的にインタラクティブにする虎の巻！
私はSnowflakeでプロダクトマーケティングマネージャーをしています。
このスクリプトは、もともとはSnowflakeがどれだけ安定してみなさんのリクエストに応えられるか、そしてどれだけ使いやすいUIを持っているかを百聞は一見に如かずで体験してもらうために作成したものでした。
しかしテック系コミュニティのイベントが未だほぼ選択肢なしでオンラインで続いていく中、コミュニティの醍醐味であるインタラクション、双方向のコミュニケーションをYouTubeなどの配信イベントでどうやって演出していくか、悩んでいる方も多いのではないかと考え、このスクリプトがそのようなコミュニティイベント担当者の方のヒントの一つになればと思い、公開することにしました。
このスクリプトを使うとこんなことができるようになります。
Twitterでイベントハッシュタグをつけて投稿した人のTwitter IDをそのままSnowflakeユーザーIDとして、みなさんが用意したSnowflake環境へアクセス可能
これらはすべてこれまで私がこの仕組みを使って実施してきた完全オンラインのコミュニティイベントです。リアクションが少ないと言われるオンラインイベントですが上記の謎解き大会は
TECHPLAY 事前登録数：123
YouTubeライブ配信時のピーク視聴数：110 (89.4%が当日参加)
ツイートしたユーザー数：92 (ライブ視聴者のうち85.5%がアクティブ)
イベント当日の総ツイート数: 722件
という驚異的な数字を叩き出しています。
Snowflakeはどなたでも１ヶ月間約400ドル分のフリートライアルを利用することができます。このスクリプトを使えば事前準備を簡単に終えられるので、イベント事前準備から当日までスケジュールをしっかり管理すれば１ヶ月で十分イベント開催可能です。ぜひこちらを利用して参加者と双方向でコミュニケーションするイベント企画にご活用ください。
スクリプトの使い方
では、いよいよスクリプトの公開です。本スクリプトはあくまでもTwitterデータからユーザーを作成するスクリプトに限定しています。
実際にイベントで使用するには以下の操作が別途必要になります。
フリートライアルの開設（リージョン、エディションの選択）
Twitterデータを取得（無料で使用できるMatillion Data Loaderを使用）
共有するデータの作成
これらは今後私も解説を予定していますが、GUIベースで説明がなくても操作できるか、他のリソースで調査可能な範疇でもありますので、このスクリプトさえあればできる方も多いのではないかと思います。
イベント前準備用スクリプト
まずはイベント前準備に使用するスクリプトです。こちらでイベント参加者のユーザーを自動的に作成する設定が完了します。
冒頭のカスタム設定のところに必要な値をセットしてください。
show variables;
で自分のセットした値を確認可能です。
そのあとのイベント前準備以下の部分はすべて選択して、まとめて実行していただければ動くようになっています。
実行が完了すると、ご自分のSnowflake環境（自分がアクセスしているURLをそのまま提供すればOK）に以下でアクセスできるようになります。
ユーザーID： Twitter ID
パスワード： 設定したパスワード（例ではpassword_123）
-- カスタム設定。編集してください。
set pwd = 'password_123'; // 参加者用の初期パスワード（初回ログイン時に各自で変更）
set customrole = 'test_role'; // 参加者用のカスタムロールの名称（権限設定で使用）
set dbname = 'superstore'; // アクセスするデータベース名
set whname = 'handson_wh'; // 参加者が使用するウェアハウス名
set tweettbl = 'PC_MATILLIONLOADER_DB.PUBLIC.SNOWVILLAGE_TWEETS'; // ツイートが記録されているテーブル名をフルパスで指定（データベース.スキーマ.テーブル）
set tweetdate = '2022-1-19 00:00:00.000'; // この日付以降にツイートした人のアカウントを作成
set task_interval = '1440 MINUTE'; // ユーザー作成処理の間隔。開始前までは1日間隔で、開始直前に2〜5分とかまで縮めるのがおすすめ
show variables; // 値確認用
-- イベント前準備
// ロールの作成（権限設定）
use role securityadmin;
create or replace role identifier($customrole);
grant role identifier($customrole) to role sysadmin;
// 参加者がデータ処理をするときに使用するウェアハウスの作成と使用権を付与
// 一番節約した設定にしています。10億など大きいデータセットを扱う場合は適宜変更してください（数千万件くらいなら十分これでいけます）
use role sysadmin;
create or replace warehouse identifier($whname) with warehouse_size = 'XSMALL' warehouse_type = 'STANDARD' auto_suspend = 60 auto_resume = TRUE min_cluster_count = 1 max_cluster_count = 1 scaling_policy = 'STANDARD';
grant usage on warehouse identifier($whname) to role identifier($customrole);
// 作成したロールにデータベース、スキーマ、テーブルの参照権限を追加
// 注：スキーマはデフォルトで作成されるPUBLICの使用を想定
use database identifier($dbname);
grant usage on database identifier($dbname) to role identifier($customrole);
grant usage on schema public to role identifier($customrole);
grant select on all tables in database identifier($dbname) to role identifier($customrole);
// ツイートした人のTwitter IDをユーザー名にしてSnowflakeユーザーを作成するストアドプロシージャと自動実行タスクの設定
// ストアドプロシージャやタスクを作成するためのデータベース作成
use role sysadmin;
create or replace database createuser;
use database createuser;
// 変数用テーブル作成
create or replace table customsetting
(pwd VARCHAR, customrole VARCHAR, dbname VARCHAR, whname VARCHAR, tweettbl VARCHAR, tweetdate VARCHAR);
// 変数をテーブルにセット
create or replace warehouse createuser with warehouse_size = 'XSMALL' warehouse_type = 'STANDARD' auto_suspend = 60 auto_resume = TRUE min_cluster_count = 1 max_cluster_count = 1 scaling_policy = 'STANDARD';
use warehouse createuser;
insert overwrite into customsetting (pwd, customrole, dbname, whname, tweettbl, tweetdate) values ($pwd,$customrole,$dbname,$whname,$tweettbl,$tweetdate);
// ストアドプロシージャ作成
use role accountadmin;
create or replace procedure create_user(PWD STRING,TWEETTBL STRING,TWEETDATE STRING,WHNAME STRING,DBNAME STRING,CUSTOMROLE STRING)
returns varchar not null
language javascript
as
$$
var pwd = PWD;
var tweettbl = TWEETTBL;
var tweetdate = TWEETDATE;
var whname = WHNAME;
var dbname = DBNAME;
var customrole = CUSTOMROLE;
var return_value = "";
var display_name = "";
var SQL_STMT = "SELECT FROM_USER_SCREEN_NAME FROM "+tweettbl+" WHERE CREATED_AT >= \'"+tweetdate+"\' GROUP BY 1";
var stmt = snowflake.createStatement({sqlText: SQL_STMT});
/* Creates result set */
var res = stmt.execute();
while (res.next()) {
return_value = res.getColumnValue(1);
var statement_usercreate = "CREATE USER IF NOT EXISTS \""+return_value+"\" PASSWORD = \'"+pwd+"\' MUST_CHANGE_PASSWORD = TRUE DEFAULT_WAREHOUSE = "+whname+" DEFAULT_NAMESPACE = "+dbname+".PUBLIC DEFAULT_ROLE = "+customrole+"";
var statement_v2 = snowflake.createStatement( {sqlText: statement_usercreate} );
var result_v2 = statement_v2.execute();
var statement_usergrant = "GRANT ROLE "+customrole+" TO USER \""+return_value+"\";";
var statement_v3 = snowflake.createStatement( {sqlText: statement_usergrant} );
var result_v3 = statement_v3.execute();
}
return statement_usercreate;
$$
;
// ストアドプロシージャ実行
call create_user((select pwd from customsetting),(select tweettbl from customsetting),(select tweetdate from customsetting),(select whname from customsetting),(select dbname from customsetting),(select customrole from customsetting));
// タスクの作成
create or replace task create_user_task
USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
SCHEDULE = $task_interval
AS
call create_user((select pwd from customsetting),(select tweettbl from customsetting),(select tweetdate from customsetting),(select whname from customsetting),(select dbname from customsetting),(select customrole from customsetting));
// タスク実行開始
alter task if exists create_user_task resume;
// タスクの実行確認
select *
from table(information_schema.task_history())
order by scheduled_time desc
limit 20;
-- イベント前準備終了Tips：
イベント前準備のスクリプトは、大体イベント開始1週間前くらいから設定してあげると、当日までの間にこのイベントに参加するために参加者がツイートしてくれるので事前盛り上げが可能です。
フリートライアルの費用を節約するために、イベント前は1日1回ユーザーを作成するバッチが動くようにしています。（task_intervalの値を変えれば変更可能）
私はいつもユーザー作成次第、参加者の方にアクセスして確認してもらえるようにしていますが、当日までに間に誰かがすごくたくさんクエリを投げてしまうのが嫌だなという場合は、直前まで初期パスワードを伝えなければ回避できます。（コミュニティ系のイベントでそんないたずらをする方はいらっしゃらないのであまり心配する必要ないと思いますが…ちなみに、誰がどう使ったかはすべて記録されます）
もしくはリソースモニターを使って1日あたりの使用量を制限することもできます。
イベント直前設定（オプション）
イベント開始直前により頻度を上げてユーザーを作成するように変更します。これでTwitterを見て盛り上がっているのを見て、飛び入りで参加する参加者を受け入れることができます。
3 MINUTEとなっているところの数字を好きな数字に変更してください。
もちろん、クレジットを節約したい場合はしっかり事前締め切りを設けて、直前参加はできないようにすることも可能です。
-- イベント開始直前（タスクの実行間隔を短くする）
use role accountadmin;
use database createuser;
use warehouse createuser;
// タスクを停止（設定変更時には停止が必要）
alter task if exists create_user_task SUSPEND;
// タスクの実行間隔の変更
alter task create_user_task set schedule = '3 MINUTE'; // ユーザー作成処理の間隔。好きな分数を入れてください
// タスク実行開始
alter task if exists create_user_task resume;
// タスクの実行確認
select *
from table(information_schema.task_history())
order by scheduled_time desc
limit 20;
-- イベント開始直前準備終了イベント中のトラブル対応
イベント中に何かの原因でアクセスできない方もいます。せっかく楽しみにしてくださっている方がアクセスできないのは困りますし、Twitterで困っていると発言している方をケアしないのはそもそもイベントの見え方としてもよろしくないです。
もちろん助けられる限界はありますが可能な限り助けてあげるためのスクリプトがこちらです。イベント担当のメンバーに余力があれば当日裏でこのスクリプトを実行してあげられるメンバーが別で待機していると良いです。
アクセスできない原因としては大体以下のようなものが考えられます。
ユーザーが作成できていない
ツイートデータが取り込めていない
ツイートしてもらったハッシュタグは間違っていないか？
Twitterの仕様か、稀に正しいハッシュタグでもうまく取得できないケースがあるためRTや画像付きではなくシンプルなテキストツイートも試してもらう
Snowflakeユーザーが作成できていない
タスクが止まっていないか？
ストアドプロシージャは機能しているか？
ユーザーが作成できているのにアクセスできない場合
Twitter IDが正しく入力できているか？（@以降がID。表示名ではない）
自分でセットしたパスワードを忘れた
誤ったパスワードを複数回入力してロックがかかった
このような状況に対して特にイベント中は迅速な対応が求められるので、強制的にユーザーを作成したりパスワードをリセットしたりするためのスクリプトです。
まずは初期設定を全て実行してから、それぞれの対応に合わせたクエリを選んで実行してください。
-- 当日緊急対応用スクリプト
// 初期設定
use role accountadmin;
use database createuser;
use warehouse createuser;
set pwd = (select pwd from customsetting);
set customrole = (select customrole from customsetting);
set dbname = (select dbname from customsetting);
set whname = (select whname from customsetting);
set tweettbl = (select tweettbl from customsetting);
set tweetdate = (select tweetdate from customsetting);
-- 取込済みTwitter IDとSnowflakeユーザーの付き合わせでユーザー作成不具合の原因を突き止める
// SNOWFLAKE_USERの列がNULLの場合はストアドプロシージャを動かす。→Aへ
// このリストにない場合はツイートが取り込めていないのでユーザーIDを指定して強制的に作成する。→Bへ
select FROM_USER_NAME TWITTER_DISPLAY_NAME,FROM_USER_SCREEN_NAME TWITTER_ID,NAME SNOWFLAKE_USER
from identifier($tweettbl)
left outer join SNOWFLAKE.ACCOUNT_USAGE.USERS
on NAME = FROM_USER_SCREEN_NAME
where CREATED_AT >= $tweetdate
group by FROM_USER_SCREEN_NAME,FROM_USER_NAME,NAME
order by FROM_USER_SCREEN_NAME ASC;
// A：Twitterデータ取込済みのストアドプロシージャを動かす
call create_user((select pwd from customsetting),(select tweettbl from customsetting),(select tweetdate from customsetting),(select whname from customsetting),(select dbname from customsetting),(select customrole from customsetting));
// B：ツイートデータが取り込まれていない場合にIDを手入力して強制的にユーザーを作成する
set username = 'ここにID';
create user if not exists identifier($username) PASSWORD = $pwd MUST_CHANGE_PASSWORD = TRUE DEFAULT_WAREHOUSE = $whname DEFAULT_NAMESPACE = $dbname DEFAULT_ROLE = $customrole;
grant role identifier($customrole) to user identifier($username);
-- その他よくある問い合わせ対応
// パスワードロックを解除
alter user "ここにID" set mins_to_unlock= 0;
// 初期パスワードを忘れた人のためにパスワードを初期パスワードに戻して再設定してもらう
alter user "ここにID" set PASSWORD = $pwd must_change_password = true;
-- データ確認
// 事前に参加表明ツイートしてくれたみなさんを確認
select FROM_USER_SCREEN_NAME,FROM_USER_NAME,CREATED_AT,TEXT from identifier($tweettbl)
where CREATED_AT >= $tweetdate
order by CREATED_AT DESC;
// 作成されたユーザーを確認
// ※注：ユーザーを作成してからACCOUNT_USAGEスキーマへの反映まで数分時間差がある。すぐに確認できない点に注意。即確認したい場合はGUI参照のこと
select NAME,CREATED_ON
from SNOWFLAKE.ACCOUNT_USAGE.USERS
where DELETED_ON IS NULL
and DEFAULT_ROLE = $customrole
order by CREATED_ON DESC;
-- 緊急対応終了イベント終了後
イベントが終わったらユーザーや本件のために使用したオブジェクトを削除します。
特に、タスクは動きっぱなしになっているとクレジットが使われ続けてしまうので必ず止めましょう。
ユーザーもそのままにしているといつまでもアクセスできてしまい、クレジットを消費してしまいますので適度なタイミングで削除します。
ハンズオンの場合は復習用にイベントの週の週末まで開けておくことが多いです。熱心な方がどなたかわかるので嬉しいですね。
ライブ感を重視するイベントの場合は、イベント中にユーザー削除を実行すると、アクセスしていたユーザーはその場でログイン画面まで戻される（らしい）ので祭りのあと感が演出できます。
お好きなタイミングで実行してみてください。
-- イベント終了後（ユーザー削除処理）
use role accountadmin;
use database createuser;
use warehouse createuser;
// ユーザー自動作成タスクを停止
alter task if exists create_user_task SUSPEND;
// ユーザーを削除するストアドプロシージャ作成
create or replace procedure delete_user(TWEETTBL STRING,TWEETDATE STRING)
returns varchar not null
language javascript
as
$$
var tweettbl = TWEETTBL;
var tweetdate = TWEETDATE;
var return_value = "";
var SQL_STMT = "SELECT FROM_USER_SCREEN_NAME FROM "+tweettbl+" WHERE CREATED_AT >= \'"+tweetdate+"\' GROUP BY 1";
var stmt = snowflake.createStatement({sqlText: SQL_STMT});
/* Creates result set */
var res = stmt.execute();
while (res.next()) {
return_value = res.getColumnValue(1);
var statement_usercreate = "DROP USER IF EXISTS \""+return_value+"\"";
var statement_v2 = snowflake.createStatement( {sqlText: statement_usercreate} );
var result_v2 = statement_v2.execute();
}
return statement_usercreate;
$$
;
call delete_user((select tweettbl from customsetting),(select tweetdate from customsetting));
-- イベント終了後処理終了
-- 今回作成したオブジェクトの削除
set customrole = (select customrole from customsetting);
set whname = (select whname from customsetting);
drop database if exists createuser;
drop warehouse if exists createuser;
drop warehouse if exists identifier($whname);
drop role if exists identifier($customrole);
show roles;
show tasks;以上がTwitterデータからユーザーを作成するスクリプトです。
注意事項
こちらは私KTが個人の勉強のために作ったものであり、デモ環境やフリートライアル環境などを使用してイベント専用でお使いいただくことを前提に作成しております。エラーハンドリングなどの処理も行っておらず、あくまでもお遊び用のスクリプトです。
このスクリプトを実行したことで起こるいかなる影響にも責任は持てませんので、環境や利用クレジットについてはよく考慮の上、ご利用ください。特に、業務で使っている本番のSnowflakeアカウントで実験する場合、重々注意をお願いいたします。処理を簡素化するためにACCOUNTADMINロール（最強権限）を多用しています。
なお、改善の余地などあればご提案・ご意見は大変ありがたいです。
終わりに
一年前に初めてこの仕組みを作ってみて、自分自身もSQLを勉強できましたし、私が大切にしているコミュニティイベントを盛り上げることまでできて、まさに一石二鳥でした。
みなさんにもこの体験をシェアしたく、スクリプト公開に踏み切りました。
みなさんに提供するにあたって使いやすいように結構アップデートしています。自分だけが使うのであればとりあえず動けばいいや、ですが人に見せるとなると整えたり効率化したいところが山ほどありました…（笑）今回、公開に向けてやりたいことはおおむね実装できてよかったです。
今後は実際にフリートライアル開設から実際に実装するところを動画で解説したり、このスクリプト自体も色々ハマりポイントがあったのでSQL解説編なんかもやってみたいなと思っています。
最後に、みなさんからこのスクリプトを使ってこんな企画をやってみた！などのフィードバックがあればこれ以上の喜びはありません。
それではまたどこかでお会いしましょう！
参考）実際にこのスクリプトを動かして実施したイベント
この記事が気に入ったらサポートをしてみませんか？