続・個人的に使いたいときにすぐ思い出せないSnowflakeのSQLをまとめてみた

はじめに

以前に、同様の名目で記事を書いたのですが、その続きというか、ある程度溜まってきたかなと思ったので書き出してまとめてみました。

コアなネタもありますが、むしろそんなネタだからこそ久しぶりにやろうと調べようとしてつまづくのです。(と思います)

実行中のタスクの中断(強制停止)

タスクは基本的にスケジュールやストリームと組み合わせた変更検知をトリガーに実行したいときに作成します。
一方、動作確認や任意タイミングで実行したい時は、手動実行できます。

EXECUTE TASK MY_TASK;

わかりやすいですね。

一方で、一度動かしたタスク(実行中のもの)を中断させたい、強制的に止めたい時ってどうするの？については、以下になります。

SELECT SYSTEM$USER_TASK_CANCEL_ONGOING_EXECUTIONS('MY_TASK');

これは難しい...パッと出てこないですね。
タスクを中断する操作なので、task_historyを見た時のSTATE列の値がEXECUTINGからCANCELLEDになります。

これが実行できる権限についてはこちらより、

このコマンドを実行できるのは、タスク所有者（つまり、タスクに対する OWNERSHIP 権限を持つロール） または タスクに対する OPERATE 権限を持つロールのみです。

とのことです。

なお、以下はタスクのスケジュールを止めるコマンドなので解釈違いに注意です。

ALTER TASK MY_TASK SUSPEND;

こちらはshow tasksで見た時のstate列の値がsuspendedになります。

おまけ : クエリのキャンセル

こちらもシステム関数でSYSTEM$CANCEL_QUERYがあります。
例えば、クエリ履歴から長時間実行クエリのクエリIDをとってきて、停止したりすることができます。

SELECT SYSTEM$CANCEL_QUERY('01b33f87-0001-5374-0000-000178925069');

必要な権限に関しては同様にこちらによると、

次に挙げる権限を 1つ 持つロールが必要です。

• 操作を実行したユーザーの OWNERSHIP。
• 操作を実行しているウェアハウスの OPERATE または OWNERSHIP （該当する場合）。

とのことです。タスク、クエリ、いずれにしてもカスタムロールで権限分離を行う場合はOPERATE権限がポイントですね。

ストリームの残骸削除(ストリームの消費)

テスト環境等、何らかの事情で残骸が残ってしまったストリームを綺麗にしたいことがあります。

SELECTする時は他のテーブルと同じ感覚で使用できますが、空っぽにしたい時はTRUNCATEやDELETEではないんですよね。
ストリームのクリア、リセット、掃除...色々表現はありますが、それを意味する表現は公式としては「消費」と表現されてますね。
(ストリームの全削除、TRUNCATEのようなことを指す表現は、全消費？)

そこで、ストリームの特性を利用してストリームを空っぽにします。
適当なテンポラリーテーブルをCTASで作ってやればOKです。
DROP->CREATEもなくはないですが、少々強引かなと。FUTURE権限を持ってないロールが参照していると面倒なことが起こりそうです。

-- ストリームにゴミが入っている状態 (selectの結果なんらかの行が返ってくる)
SELECT * FROM MY_STREAM;

-- ストリームを全消費
CREATE OR REPLACE TEMP TABLE MY_TEMP_TABLE AS SELECT * FROM MY_STREAM;

-- ストリームが消費されて綺麗になった (selectの結果は「Query produced no results」)
SELECT * FROM MY_STREAM;

TRUNCATEを使いたくない(使えない)時の代替方法

カスタムロールを作って運用しているとき、「しまった...TRUNCATE権限つけてなかった」なんてことありませんか？
あるいは、Native AppのアプリケーションロールでTRUNCATEを実行させたいときにmanifest.ymlに書き忘れた、とかもありそうですよね。

TRUNCATE TABLE MY_DB.MY_SCHEMA.MY_TABLE;

Insufficient privileges to operate on table 'MY_TABLE'

修正できればそれでいいですが、諸々事情で修正できない、修正したくない、そんな時があるかもしれません。
そこで代替手段として、DELETEで条件指定して全部消してみる時の話です。
(DELETE権限はつけている前提ですが...)

さて、どんな条件文を書けばよかったか...

DELETE FROM MY_DB.MY_SCHEMA.MY_TABLE WHERE True;

これでいけます。
しばらく間を置くと何か列名の指定や=いるんだったかな...とか想像しちゃいますが、WHERE句にシンプルにTrueだけです。

常に真を返すWHERE 1=1と同じですね。
スクリプトで動的にSQLを組み立てるとき、WHEREの書き出しに1=1と書いておき、後続にANDで条件を追記していく時のおまじない的なあれです。

DELETE FROM MY_DB.MY_SCHEMA.MY_TABLE WHERE 1=1;

Snowflakeオブジェクトの定義(DDL)を確認して再利用したい

過去に作ったオブジェクトと同じ構成で新しくオブジェクトを作成したい...といったときに使えるGET_DDL。

これ便利ですね...都度コード見直さなくていいですからね。

ただ、実行した時のSQLと完全一致のものが返ってくるのではなく、Snowflakeが解釈したものが返ってくるイメージです。

USE ROLE SYSADMIN;
-- 通常のテーブル
CREATE TABLE MY_DB.MY_SCHEMA.MY_TABLE (ID VARCHAR, VALUE VARCHAR);
SELECT GET_DDL('TABLE', 'MY_DB.MY_SCHEMA.MY_TABLE');
-- create or replace TABLE MY_TABLE (
--	ID VARCHAR(16777216),
--	VALUE VARCHAR(16777216)
--);

-- イベントテーブルも見れる
CREATE OR REPLACE EVENT TABLE MY_DB.MY_SCHEMA.EVENT_TABLE;
SELECT GET_DDL('TABLE', 'MY_DB.MY_SCHEMA.EVENT_TABLE');
-- create or replace event table EVENT_TABLE;

-- テンポラリーテーブルも見れる
CREATE OR REPLACE TEMP TABLE MY_DB.MY_SCHEMA.MY_TEMP_TABLE (ID VARCHAR, VALUE VARCHAR);
SELECT GET_DDL('TABLE', 'MY_DB.MY_SCHEMA.MY_TEMP_TABLE');
-- create or replace TEMPORARY TABLE MY_TEMP_TABLE (
-- 	ID VARCHAR(16777216),
--	VALUE VARCHAR(16777216)
--);

TABLEは万能ですね。
一方で、GRANTする時は「何テーブルなのか」まで指定する必要があるので、この辺りの解釈がややこしいかもです。

-- 例えばEVENT TABLEに対するGRANT
GRANT SELECT ON EVENT TABLE MY_DB.MY_SCHEMA.EVENT_TABLE TO ROLE MY_ROLE;

TABLE以外でもGET_DDLを試してみます。

-- 他オブジェクトも

-- ストリーム
CREATE STREAM MY_DB.MY_SCHEMA.MY_STREAM ON TABLE MY_DB.MY_SCHEMA.MY_TABLE;
SELECT GET_DDL('STREAM', 'MY_DB.MY_SCHEMA.MY_STREAM');
-- create or replace stream MY_STREAM on table MY_TABLE;

-- ストアドプロシージャ
CREATE OR REPLACE PROCEDURE MY_DB.MY_SCHEMA.MY_PROCEDURE(ID STRING, VALUE STRING)
    RETURNS STRING
    LANGUAGE JAVASCRIPT
    STRICT
    EXECUTE AS OWNER
    AS
    $$
    var sql_command =  "INSERT INTO MY_DB.MY_SCHEMA.MY_TABLE VALUES ('" + ID + "', '" + VALUE + "')";
    try {
        snowflake.execute (
            {sqlText: sql_command}
            );
        return "Succeeded.";
        }
    catch (err)  {
        return "Failed: " + err;
        }
    $$
    ;
SELECT GET_DDL('PROCEDURE', 'MY_DB.MY_SCHEMA.MY_PROCEDURE(STRING, STRING)');
-- CREATE OR REPLACE PROCEDURE "MY_PROCEDURE"("ID" VARCHAR(16777216), "VALUE" VARCHAR(16777216))
-- RETURNS VARCHAR(16777216)
-- LANGUAGE JAVASCRIPT
-- STRICT
-- EXECUTE AS OWNER
-- AS '
--     var sql_command =  "INSERT INTO MY_DB.MY_SCHEMA.MY_TABLE VALUES (''" + ID + "'', ''" + VALUE + "'')";
--     try {
--         snowflake.execute (
--             {sqlText: sql_command}
--             );
--         return "Succeeded.";
--         }
--     catch (err)  {
--         return "Failed: " + err;
--         }
--     ';

ストアドプロシージャ等は、所有者以外で同じコマンドを実行すると、実装の中身が得られないので注意です。

-- 所有権を持たないロールで確認してみる
USE ROLE TEST_ROLE;
SELECT GET_DDL('PROCEDURE', 'MY_DB.MY_SCHEMA.MY_PROCEDURE(STRING, STRING)');
-- CREATE OR REPLACE PROCEDURE "MY_PROCEDURE"("ID" VARCHAR(16777216), "VALUE" VARCHAR(16777216))
-- RETURNS VARCHAR(16777216)
-- LANGUAGE JAVASCRIPT
-- STRICT
-- EXECUTE AS OWNER
-- AS '';

便利なGET_DDLですが、全てのオブジェクトに対応しているわけではないので注意です。(こちら参照)

POLICY （マスキング、パスワード、行アクセス、セッションポリシー）

こういうのを見るとネットワークポリシーも欲しかったなぁと思ってしまいます。

とりあえずGET_DDLを実行してみて取得できればそれでOK、取れなければ元のコードに戻って確認する、みたいな感じで使えば良さそうですね。

アカウント名を変更する

組織の統合とか、仮の名称から本運用の名称に変えるとか、命名規則を変えたいとか...諸々の事情でアカウント名を変更したいときがあります。

ORGADMINを使ってできるよなぁ...ということは思い浮かびますが、どうするんだっけ？となりました。

制約としてログイン中(=自分自身)のアカウント名は変更できないので、もう一つORGADMINが使用できるアカウントを用意しておいて、相互にアカウント名を変更し合えるようにしておく必要があります。

例として以下のシーンを想定します。

• account1はORGADMINが使えるアカウント
• account1のアカウント名を変えたいけど、自分自身では変えられない
• そこでaccount2から変えてもらうようにする
• そのためにaccount2もORGADMINが使えるようにする

別アカウントにORGADMINを割り当てる

account1にログインして実行します。

-- 現在ORGADMINが使えるアカウント(account1)で実行
USE ROLE ORGADMIN;
ALTER ACCOUNT account2 SET IS_ORG_ADMIN = TRUE;

別アカウントからORGADMINを使ってアカウント名を変えてもらう

account2にログインして実行します。

-- 先ほどORGADMINを使う許可を与えたアカウント(account2)で実行

-- まずはユーザーがORGADMINを使えるようにする
USE ROLE SECURITYADMIN;
GRANT ROLE ORGADMIN TO USER MY_USER;

-- その上でORGADMINを使ってアカウント名を変える
USE ROLE ORGADMIN;
ALTER ACCOUNT account1 RENAME TO new_account1;

以上、って感じです。

常時複数アカウントでORGADMINが使える状態がよろしくないなら、以下で取り消しておきます。

-- ORGADMINを使えるようにしておきたいアカウント(account1)で実行
USE ROLE ORGADMIN;
ALTER ACCOUNT account2 SET IS_ORG_ADMIN = FALSE;

ものによって SET で TRUE/FALSE を変更する場合と、SET/UNSET で変更する場合があるので、ややこしい...と思いつつ。

これでトライアルアカウントを作ってみたときでも、デフォルト名からサクッと名前を変えられますね...!

おまけ : アカウントを作って消す

そういえばアカウントの削除がSQLでできるようになりましたね。

USE ROLE ORGADMIN;

-- アカウントをサクッと作成
CREATE ACCOUNT account2
  ADMIN_NAME = ADMIN
  ADMIN_PASSWORD = 'Account@2'
  EMAIL = '[e-mail]'
  EDITION = BUSINESS_CRITICAL;

SHOW ORGANIZATION ACCOUNTS;

-- アカウントをサクッと削除
DROP ACCOUNT account2 GRACE_PERIOD_IN_DAYS = 3;

これでトライアルアカウントを作ってみたときでも(ry

おまけ : 同じアカウントを2回以上変更しようとすると...

エラーになります。

USE ROLE ORGADMIN;
ALTER ACCOUNT new_account1 RENAME TO renew_account1;

Account '<変更前アカウント名>' already has an old url of 'https://<組織名>-<変更前アカウント名>.snowflakecomputing.com'. Drop the old url by running 'alter account <変更前アカウント名> drop old url' or use 'save_old_url=False' in order to rename the account again.

変更前のURLが残る仕様であるため、最初にそれを消しておけば解決です。
ヒントはエラー文言に含まれてますね。

ALTER ACCOUNT new_account1 DROP OLD URL;

そもそも古いURLを残さなくていいなら、以下でエラーを出さずに何回でも変更ができます。

ALTER ACCOUNT new_account1 RENAME TO renew_account1 SAVE_OLD_URL=False;

イベントアカウントを設定する〜イベントアカウントを見る

SnowflakeのNative App Frameworkを使って、アプリの作成〜配布をするとします。

アプリのプロバイダー(配布側)でコンシューマー(配布先)のアプリのログを集約して、トラブルシューティングに役立てることができる仕組みがあります。

これはプロバイダーをイベントアカウントとして設定することで実現できます。

設定しようとすると、必要な情報が複数ページにまたがって飛び飛びで書いてあり、なかなか情報をピックアップするのに大変だった記憶があります。

しばらく間が開くと、どのページの情報を見るんだっけなぁ...から始まると思うので、まとめてみました。

前提としてイベントテーブルが必要です。

-- 作成できるロールに変更
USE ROLE SYSADMIN;
CREATE EVENT TABLE MY_DB.MY_SCHEMA.MY_EVENT_TABLE;

イベントテーブルをアカウントに紐付けます。

USE ROLE ACCOUNTADMIN;
ALTER ACCOUNT SET EVENT_TABLE = MY_DB.MY_SCHEMA.MY_EVENT_TABLE;
-- 設定されていることの確認
SHOW PARAMETERS LIKE 'EVENT_TABLE' IN ACCOUNT;

プロバイダー側でイベント(ログ)を集約する設定は、ORGADMINを使って以下を実行することでできます。

CALL SYSTEM$SET_EVENT_SHARING_ACCOUNT_FOR_REGION('<snowflake_region>', '<region_group>', '<account_name>')

パラメータが多分ぱっと見わからんのですが、以下で値が確認ができます。

USE ROLE ORGADMIN;
SHOW ORGANIZATION ACCOUNTS;

この結果で、アカウント名(account_name列)、リージョン名(snowflake_region列)が取れますので、設定値に自信がなければこれで確認するのが確実かと思います。

後は <region_group> 、なんだこのパラメータは...となるのですが、

すべてのSnowflakeマルチテナント商用リージョン（サポートされているすべてのクラウドプラットフォーム全体）は、同じ共有/一般 PUBLIC グループにあります。

日本リージョンで使っている、VPSを使っていないならPUBLICですね。

パラメータがわかったところで、実行してみます。

-- 設定前の確認
SHOW ORGANIZATION ACCOUNTS;

-- AWSの東京リージョンを使用している場合
CALL SYSTEM$SET_EVENT_SHARING_ACCOUNT_FOR_REGION('AWS_AP_NORTHEAST_1', 'PUBLIC', 'MY_ACCOUNT1')

設定後にイベントアカウントはどのアカウントかを確認するには、またもやSHOW ORGANIZATION ACCOUNTSで確認できます。

USE ROLE ORGADMIN;
SHOW ORGANIZATION ACCOUNTS;

このコマンドを実行した結果で表示される一番右側の列is_events_accountを見ると確認できます。
trueになっていればそのアカウント名がイベントアカウントです。

なお、同一組織内で1つだけtrueになるので、別アカウントを指定して実行し直すとtrueが別アカウントに移ります。
こちらによると、

組織で複数のプロバイダーがアプリケーションパッケージを公開している場合は、コンシューマーからの共有イベントを格納する、専用のSnowflakeアカウントの使用を検討してください。

とのことで、イベントアカウント専用のSnowflakeアカウントを作るんですね。

なるほど...。複数のアプリが存在する場合もですが、同一組織内で開発/検証/商用といった環境別にアカウントを分けているときでも1アカウントに集約されるという点で何か悩ましい事情が出てくるかもしれません。

また、上記はトライアルアカウント等で試そうとすると怒られますので気軽には試せないやつです...。

Events account operation failed: A trial, locked/suspended, inactive or Snowflake managed account cannot be set as Events Account.

おまけ : ORGADMINで実行したクエリの結果を加工する

上記showの結果が見づらいので例のRESULT_SCANを使って加工しよう...と思ったら罠としてORGADMINでウェアハウスが(デフォルトでは)使えない...となります。
そこで、showを実行したときのクエリIDだけもらってきて、別ロールで加工する抜け道を使ってみました。

USE ROLE ORGADMIN;
SHOW ORGANIZATION ACCOUNTS;
SET query_id = (SELECT LAST_QUERY_ID());

USE ROLE SYSADMIN;
USE WAREHOUSE my_wh;
SELECT "account_name", "is_events_account" FROM TABLE(RESULT_SCAN($query_id));

ウェアハウスのUSAGE権限を与えればよいかもですが...権限設計やらORGADMINで変にコンピュートリソース使わせたくない的な事情で下手にGRANT USAGE ON WAREHOUSE my_wh TO ROLE ORGADMIN;とかを実行したくない時の回避策って感じです。

ユーザーの接続元IPアドレスを知るあれこれ

ネットワークポリシーで接続元IPアドレスを絞ってアクセス制限をかけるのはよくあると思います。Snowflakeではユーザー単位で設定ができます。

監視やデータへのアクセス制御のため、そのユーザーがどのIPアドレスから接続したのか確認したい時、例えばLOGIN_HISTORYにログが記録されます。

-- とりあえず見る
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY;

-- ユーザーを特定してかつ新しいログから並べる
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
WHERE USER_NAME = 'MY_USER'
ORDER BY EVENT_TIMESTAMP DESC;

ACCOUNT_USAGEへの反映が遅いので、即時確認したい時はINFORMATION_SCHEMAを活用します。
INFORMATION_SCHEMA.LOGIN_HISTORY_BY_USER()を使えばユーザー名を指定できます。

SELECT * FROM TABLE(
  SNOWFLAKE.INFORMATION_SCHEMA.LOGIN_HISTORY_BY_USER(
    USER_NAME => 'MY_USER'
  )
)
ORDER BY EVENT_TIMESTAMP DESC;

上記はログインした時のIPアドレスです。その後IPアドレスが変わらないことまでは保証されません。
ネットワークポリシーだけでは抜け穴がありそうな時は、さらにその時のセッションで取得できるCURRENT_IP_ADDRESS()が使えないかを検討すると良さそうです。

SELECT CURRENT_IP_ADDRESS();

例えば、ストアドプロシージャのロジックにこの関数を仕込んでおいて、ユーザーは然るべきIPからちゃんと利用しているのかを都度確認する制御を入れる等。
機密性の高いデータにはデフォルトではアクセス権限がなく、何らかのストアドプロシージャを実行すると結果が得られるとか、行アクセスポリシーに組み込んでおくとか、色々使い道が想像できそうです。

ログイン監視目的で見る時や、SQL実行時点でリアルタイムに見る時等、用途に応じて使い分ける感じですね。

showでlike句の否定(not like)は使えなかった

ので、こうやって回避してみましたという話です。

通常のSQL文で、where句にlike条件を否定したい時、not like条件が使えます。
一方で、showではlikeは使えますがnot likeは使えなかったです。

-- タスク名に「DAILY」を含むものを表示
SHOW TASKS LIKE '%DAILY%' IN ACCOUNT;

-- タスク名に「DAILY」を含まないものを表示したいが...
-- エラー:「NOT LIKE」は使えない
SHOW TASKS NOT LIKE '%DAILY%' IN ACCOUNT;

そのため、showで得られた一覧からキーワードにマッチしないもので絞り込みたい時は、前の記事でも記載したTABLE(),RESULT_SCAN(),LAST_QUERY_ID()の力を借ります。
ということで、解決策としては以前の記事と同じ内容での再掲、といった感じです。(大事なことなので2回言いました的な)

-- 今度こそタスク名に「DAILY」を含まないものを表示する...!
SHOW TASKS IN ACCOUNT;
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "name" NOT LIKE '%DAILY%';

同じ「like」でも、コマンドによって仕様が違う点に注意です。

SHOWのlike(例えばshow tasks)

• 大文字と小文字を区別しない
• ワイルドカード文字（% および _）が使える
• 否定(NOT)が使えない

SQLのlike

• 大文字と小文字を区別する
  • 区別したくない場合はILIKEを使う (showのlikeと同じ動き)
• ワイルドカード文字（% および _）が使える
• 否定(NOT)が使える

おまけ : STARTS WITH

show tasks では starts with を指定して絞ることもできますね。

SHOW TASKS STARTS WITH 'MY_';

なお、likeと違ってこちらは大文字と小文字を区別するんですね。

-- こちらだと引っ掛からなかった...
SHOW TASKS STARTS WITH 'my_';

こちらもSQL版がありますね。

他のshowコマンドではstarts withが使えなかったりしますね。
(例えばshow proceduresとか)

本当に使えないの？と興味本位で試してみたところ、どうやら使えない(機能しない)だけで指定してもエラーにはならないんですね。

-- マニュアルには使えるとは特に書いてないけどエラーにはならなかった
-- かといって期待通りに動作しなかった(=結果はフィルタリングされなかった)
SHOW PROCEDURES STARTS WITH 'MY_';

ちょっとした隠しコマンド(無意味)を発見って感じでした。

おわりに

モヤモヤ整理も兼ねて書き出してみました。
また出てきたらまとめるかもです。

以上です。