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

はじめに

内容はタイトル通りです。
Snowflakeを使っていて、必ずとはいかなくてもたまに発生する状況・事象にて「このエラーか。あれ、どんなSQLを書くんだっけ？」となりがちなので、振り返りがてらまとめてみました。

エラー「Dependent grant of privilege」の対応

OWNERSHIPを変更しようとした時に、以下のエラーが出ることがあります。

例 : スキーマTEST_DB.TEST_SCHEMAの所有者を別のロールに変更しようとした時。

SQL execution error: Dependent grant of privilege 'USAGE' on securable 'TEST_DB.TEST_SCHEMA' to role 'TESTROLE' exists. It must be revoked first. More than one dependent grant may exist: use 'SHOW GRANTS' command to view them. To revoke all dependent grants while transferring object ownership, use convenience command 'GRANT OWNERSHIP ON <target_objects> TO <target_role> REVOKE CURRENT GRANTS'.

言語設定を日本語にしていると、以下のようになります。

SQL実行エラー：ロール「TESTROLE」に対するセキュリティ保護可能な「TEST_DB.TEST_SCHEMA」の権限「USAGE」の依存付与が存在します。最初に取り消す必要があります。 複数の依存付与が存在する場合があります：「SHOW GRANTS」コマンドを使用してそれらを表示します。オブジェクトの所有権を転送している間にすべての依存付与を取り消すには、便利なコマンド「 <target_role> への <target_objects> の所有権の付与。現在の付与を取り消す」を使用します。

よくわからないメッセージのように見えますが、英語表示だと実行するべきSQLのヒントが表示されているので、エラーの時だけ英語にしてほしいなぁと思ったりします。

状況に応じて以下のいずれか実行して対処します。

-- REVOKE CURRENT GRANTS
GRANT OWNERSHIP ON SCHEMA TEST_DB.TEST_SCHEMA TO ROLE ROLE_NAME REVOKE CURRENT GRANTS;

-- COPY CURRENT GRANTS
GRANT OWNERSHIP ON SCHEMA TEST_DB.TEST_SCHEMA TO ROLE ROLE_NAME COPY CURRENT GRANTS;

(ついでにメモ)ユーザの所有権の移譲

Web UI(Snowsight)ではユーザの所有権を移譲できなかったのですが、SQLだとできました。カスタムロールにユーザ管理を任せたい時は、ユーザ作成時に使用するロールに気をつけないとですね。

USE ROLE USERADMIN; -- 現在のユーザの所有者
GRANT OWNERSHIP ON USER USERNAME TO ROLE ROLENAME;

SHOW系の結果にクエリしたい

TABLE + RESULT_SCANを使うやつですね。
主に直前のSHOWの結果に対して使うと思うのでLAST_QUERY_IDもセットですかね。

上記のキーワードをいずれかは思い出すのですが、実行したい時にセットで思い出せず「あーなんだっけ...」ってなります。

あとSHOWの実行はウェアハウスは使わないので、TABLE関数を使う前にUSE WAREHOUSEしておかないと「しまった...」ってなります。

USE WAREHOUSE COMPUTE_WH;
SHOW GRANTS TO ROLE TESTROLE;
SELECT "name" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "granted_on" = 'ROLE';

TABLE関数に条件を指定して結果を得たい

account_usageへの反映が遅いので、information_schemaにTABLE関数でクエリして履歴を確認したい時がありますが、なんか書き方が特殊じゃなかったっけ？となって忘れがちでした。

例 : task_history

SELECT NAME, QUERY_TEXT, STATE
FROM TABLE(snowflake.information_schema.task_history());

SELECT NAME, QUERY_TEXT, STATE
FROM TABLE(test_db.information_schema.task_history(
    task_name=>'TEST_TASK')
);

SELECT NAME, QUERY_TEXT, STATE
FROM TABLE(test_db.information_schema.task_history(
    result_limit => 100))
WHERE name like '%TEST%';

「どう書くんだっけ？」となるのは=>の部分ですね。何が指定できるかはマニュアルから都度拾う感じでしょうか。例えばtask_history()で使用できるものはこちらですね。

まあ、where句でも条件指定できますね...。

あるロールにタスクの実行履歴だけをみれるようにしたい

監視用ロールを作って利用状況を監視したい場合に要チェックですね。
ロールにタスクの所有権を持たせないと見れない？というわけではなく、以下の権限を付与するとできます。

GRANT
  MONITOR EXECUTION, -- 所有していないタスクの実行履歴が取得できる
  MONITOR USAGE      -- アカウントレベルの機能やクレジットの利用状況が取得できる
ON ACCOUNT TO ROLE TESTROLE;

すると、例えば以下のSQLで今までは見れなかったタスクの履歴が見れるようになります。

USE ROLE TESTROLE;
SELECT NAME, QUERY_TEXT
FROM TABLE(snowflake.information_schema.task_history());

ユーザに設定したネットワークポリシーを見る

以下のクエリで確認できます。

show parameters like 'NETWORK_POLICY' for user TESTUSER;

show parameters...これが忘れがちポイントですね。

ユーザに設定したパスワードポリシーを見る

以下のクエリで確認できます。

select POLICY_NAME, POLICY_KIND, REF_ENTITY_NAME
from table(snowflake.information_schema.POLICY_REFERENCES(
REF_ENTITY_NAME => 'TESTUSER',
REF_ENTITY_DOMAIN => 'USER'
));

こちらはPOLICY_REFERENCESで見れるんですね。

行アクセスポリシーの付け外し

行アクセスポリシーの付与は以下のクエリでできます。

alter table TEST_TABLE add row access policy TEST_ROW_ACCESS_POLICY on COLUMN_NAME;

行アクセスポリシーの剥奪は以下のクエリでできます。

alter table TEST_TABLE drop row access policy TEST_ROW_ACCESS_POLICY;

addとdrop...という関係性。

この手のものは、例えば

• create <-> drop
• add <-> drop
• grant <-> revoke
• set <-> unset

等、セットで覚えておきたいですね。

WebUIのクエリ履歴で「redacted」になるやつ

解決策としてコミュニティではこのような方法が記載されていました。
公式ドキュメントのこのパラメータですね。(ENABLE_UNREDACTED_QUERY_SYNTAX_ERROR)

が、これをしないでなんとかするなら以下かなと思います。

• とりあえず実行したユーザでログインして確認する
  • エラーの特定等そもそもどんなクエリが原因で失敗しているのかわからない時はこちらが早い
  • dbtのスレッド実行やGithub Actions経由で実行されているものがエラーになるとどこで転けたかなかなか特定しづらいので調べたくなりがち
  • システム用ユーザだと、パスワードを共有して共通ユーザ的に用いるのがよろしくないかもなので、開発環境以外でこの手法を取るのかが悩ましい
• snwoflake.infomation_schemaで見る
  • クエリテキストがわかっている時はこちらでよさそう
  • そうでなくても実行時間と実行結果で絞って確認する感じ

select start_time, end_time, total_elapsed_time, execution_status, error_code, error_message
from table(
    snowflake.information_schema.query_history()
)
where query_text = 'select * from test_db.test_schema.test_table;';

おわりに

とりあえずこんな感じでまとめてみました。
他にもあるかもですが、また出てきたらまたまとめたいですね。

以上です。