KDDIアジャイル開発センター（KAG）Advent Calendar 2024

KDDIアジャイル開発センター株式会社

Snowflakeの小ネタ・小技まとめ

Snowflake

Last updated at 2024-12-03Posted at 2024-12-02

はじめに

Snowflakeの小ネタ・小技集のような記事です。

これができそうでできない、細かい部分でエラーがでてうまくいかない、といった感じでモヤモヤした時に調べながら試行錯誤してなるほど〜となった経験をまとめてみました。

SHOW等の結果に含まれている列の型を知りたい時

SHOW系コマンドを叩いて、その後RESULT_SCANとTABLE関数でテーブルとして結果を得るとして、そのテーブルにある列の型が何なのか？を知りたい時がありました。

SHOW USERS;

SELECT "name", "created_on"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

↑を実行したとして次のような結果が得られたとします。

name	created_on
SNOWFLAKE	`yyyy-MM-dd HH:mm:ss.SSS -0700`

この "created_on" カラムの型ってなんだろう？と思いました。

IS_TIMESTAMP_LTZ ?
IS_TIMESTAMP_NTZ ?
IS_TIMESTAMP_TZ ?

そんな時は、SYSTEM$TYPEOFを使ってみると調べることができます。

SHOW USERS;

SELECT SYSTEM$TYPEOF("created_on")
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
LIMIT 1;

SYSTEM$TYPEOF("CREATED_ON")
TIMESTAMP_LTZ(3)[SB8]

"created_on"の型は TIMESTAMP_LTZ(3) であることがわかりました。
出力が複数行あるとその行数分情報が表示されるので、LIMIT 1で絞っています。

あるいは、おもむろに以下のクエリを試し打ちしても型が判明します。

SHOW USERS;

SELECT TYPEOF("created_on")
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

実行結果はエラーなのですが...

Invalid argument types for function 'TYPEOF': (TIMESTAMP_LTZ(3))

これでも "created_on"の型がわかりました。

本来的にはSYSTEM$TYPEOFを実行すればいいのですが、システム関数であることを忘れていてTYPEOF (本来は半構造化データ用の関数)を実行してしまっても型は分かるので、目的は達成できます。

Snowflakeスクリプトを書く時、Snowsightでは文字列リテラル区切り文字($$等)は不要

公式ドキュメントによると...

Snowflakeスクリプトでのストアドプロシージャの記述

SnowSQL または Classic Console でSnowflakeスクリプトプロシージャを作成する場合は、ストアドプロシージャの本文の前後に文字列リテラル区切り文字（' または $$）を使用する必要があります。

SnowSQL および Classic Console でのSnowflakeスクリプトの使用

SnowSQL または Classic Console を使用している場合にこの問題を回避するには、Snowflakeスクリプトのブロックの開始と終了の前後に区切り文字を使用する必要があります。

とのことです。
Snowflakeスクリプトを書く場合において、同じストアドプロシージャを記述する際のお作法が複数ある感じですね。

for Snowsight

CREATE OR REPLACE PROCEDURE output_message(
    input_message VARCHAR DEFAULT 'Hello',
    show_date BOOLEAN DEFAULT TRUE
)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
DECLARE
  result_message VARCHAR;
BEGIN
  IF (show_date) THEN
    LET date := (SELECT current_date);
    result_message := 'Message: ' || input_message || ', Date: ' || TO_VARCHAR(date, 'YYYY/MM/DD');
  ELSE
    result_message := 'Message: ' || input_message;
  END IF;
  RETURN result_message;
END;

↑Snowsightでよく操作をする場合はこちらで良さそうですね。

for SnowSQL or クラシックコンソール

「$$」を使った記載

CREATE OR REPLACE PROCEDURE output_message(
    input_message VARCHAR DEFAULT 'Hello',
    show_date BOOLEAN DEFAULT TRUE
)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
DECLARE
  result_message VARCHAR;
BEGIN
  IF (show_date) THEN
    LET date := (SELECT current_date);
    result_message := 'Message: ' || input_message || ', Date: ' || TO_VARCHAR(date, 'YYYY/MM/DD');
  ELSE
    result_message := 'Message: ' || input_message;
  END IF;
  RETURN result_message;
END
$$
;

「'(シングルクォート)」を使った記載

CREATE OR REPLACE PROCEDURE output_message(
    input_message VARCHAR DEFAULT 'Hello',
    show_date BOOLEAN DEFAULT TRUE
)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
'
DECLARE
  result_message VARCHAR;
BEGIN
  IF (show_date) THEN
    LET date := (SELECT current_date);
    result_message := \'Message: \' || input_message || \', Date: \' || TO_VARCHAR(date, \'YYYY/MM/DD\');
  ELSE
    result_message := \'Message: \' || input_message;
  END IF;
  RETURN result_message;
END
'
;

シングルクォートを使い出すとさらに面倒ですね...
本文中に出てくる「'」は「\'」と書かないとエラーになります。

参考 : 一重引用符で囲まれた文字列定数のエスケープシーケンス

Snowsight上ではいずれの書き方もできますが、文字列リテラル区切り文字を使用しない場合はシンタックスハイライトが効くのでSQL文が読みやすくなります。

文字列リテラル区切り文字なし : こちらは見やすい!

文字列リテラル区切り文字あり : こちらは見づらい...

同じコードなのに、見やすさが段違いですね。
シンタックスハイライトが効かなくてやりづらかった状況から解放されますね。
Snowflakeスクリプトを書くときは是非参考にしてみてください。

ストアドプロシージャ内でSHOW/DESCを実行する際の注意点

ストアドプロシージャ内でSHOWを実行したいケースがあります。

まず初めに、(実用性はともかく)以下のようなプロシージャを見てみます。

CREATE OR REPLACE PROCEDURE show_warehouses()
RETURNS TABLE (
    "name" STRING,
    "type" STRING,
    "size" STRING
)
LANGUAGE SQL
AS
  DECLARE
    res RESULTSET;
  BEGIN
    EXECUTE IMMEDIATE 'show warehouses';
    res := (select "name", "type", "size" from table(result_scan(last_query_id())));
    RETURN TABLE(res);
  END;
;

これをcallしてみると...

call show_warehouses();

以下のような結果が得られます。

name	type	size
MY_WH	STANDARD	X-Small
SYSTEM$STREAMLIT_NOTEBOOK_WH	STANDARD	X-Small

上記はSHOW WAREHOUSESをストアドプロシージャ内で実行している例です。

一方で、SHOW USERSのようなコマンドは、上記と同じような要領でストアドプロシージャを作成すると実行できません(エラーになります)。

試してみます。

CREATE OR REPLACE PROCEDURE show_users()
RETURNS TABLE (
    "name" STRING,
    "created_on" TIMESTAMP_LTZ(3)
)
LANGUAGE SQL
AS
  DECLARE
    res RESULTSET;
  BEGIN
    EXECUTE IMMEDIATE 'show users';
    res := (select "name", "created_on" from table(result_scan(last_query_id())));
    RETURN TABLE(res);
  END;
;

callしてみると...

call show_users();

以下のようなエラーになります。

Uncaught exception of type 'STATEMENT_ERROR' on line 4 at position 4 : Stored procedure execution error: Unsupported statement type 'SHOW USER'.

そこで、SHOW USERSをストアドプロシージャ内で実行したい場合は、作成する際にEXECUTE AS CALLER を指定すれば実行できるようになります。

ストアドプロシージャの実行者と同じ権限でSHOWを実行するように定義すれば実行できるようになる、ということですね。

CREATE OR REPLACE PROCEDURE show_users()
RETURNS TABLE (
    "name" STRING,
    "created_on" TIMESTAMP_LTZ(3)
)
LANGUAGE SQL
EXECUTE AS CALLER -- これが必要
AS
  DECLARE
    res RESULTSET;
  BEGIN
    EXECUTE IMMEDIATE 'show users';
    res := (select "name", "created_on" from table(result_scan(last_query_id())));
    RETURN TABLE(res);
  END;
;

callしてみると...

call show_users();

以下のような結果が得られます。

name	created_on
SNOWFLAKE	`yyyy-MM-dd HH:mm:ss.SSS -0700`
TEST_USER	`yyyy-MM-dd HH:mm:ss.SSS -0700`

EXECUTE AS CALLER を指定しない場合、つまりデフォルトでは EXECUTE AS OWNER となるため、callするとエラーになります。

これはDESC系も同様です。EXECUTE AS CALLERをつけてストアドプロシージャを作成すれば実行できます。

CREATE OR REPLACE PROCEDURE get_has_mfa(user_name STRING)
RETURNS TABLE (
    "property" STRING,
    "value" STRING
)
LANGUAGE SQL
EXECUTE AS CALLER -- これが必要
AS
  DECLARE
    res RESULTSET;
  BEGIN
    EXECUTE IMMEDIATE 'desc user ' || user_name;
    res := (
        select "property", "value"
        from table(result_scan(last_query_id()))
        where "property" = 'HAS_MFA'
    );
    RETURN TABLE(res);
  END;
;

callしてみると...

call get_has_mfa('test_user');

以下のような結果が得られます。

property	value
HAS_MFA	false

なお、上記の例は desc user コマンドですが、情報を確認したいユーザーに対する所有権がなければ以下のエラーになります。

Insufficient privileges to operate on user 'TEST_USER'

こういった挙動からも EXECUTE AS CALLER でないとストアドプロシージャ経由でSHOWやDESCを実行できないように制御されているのは、よくできていますね。

あるストアドプロシージャがEXECUTE AS OWNERとして定義されていて、そのストアドプロシージャの所有者とユーザーの所有者が同一ロールだったとします。
ストアドプロシージャのUSAGE権を持った他のロールがそのストアドプロシージャcallしたときに、所有者と同じ情報を閲覧できてしまうのが穴になってしまいます。
ストアドプロシージャを実行できる人全員が同じ情報を意図せず参照できてしまう状況を避けたい時がありますからね。

ストアドプロシージャ内のロジックにSHOWやDESCを組み込みたいケースはあると思うので、実行者(caller)の設定には要注意です。

ストアドプロシージャが持つ引数の型リストをパッと取得するには

ストアドプロシージャは、同じ名前でも引数の型リストが異なるものを定義できるようになっているため、特定のストアドプロシージャの情報を GET_DDL や DESC、あるいは SHOW GRANTS ON 等で参照するためには、ストアドプロシージャ名とそのストアドプロシージャを実行する際に指定する引数の型全て(引数の型リスト)を指定する必要があります。

-- エラー
DESC PROCEDURE MY_DB.MY_SCHEMA.SHOW_USERS;

-- エラーメッセージ
-- Argument types of function 'SHOW_USERS' must be specified.

このストアドプロシージャはどんな引数を取るんだっけか...?
引数の型がわかっていると次のように実行できます。

-- 引数の型を全て指定する
DESC PROCEDURE MY_DB.MY_SCHEMA.TEST_PROCEDURE(VARCHAR, FLOAT, ARRAY);

-- そもそも引数を取らないものは () を指定するだけ
DESC PROCEDURE MY_DB.MY_SCHEMA.SHOW_USERS();

1〜数個の引数を取るストアドプロシージャならまだいいのですが、より多くの引数を取ったり、引数の順や型の順まで覚えるのは、あまり現実的ではありません。

しかも引数の名前はどうでもよくて、引数の型だけが必要なんです。

これ、結構面倒くさいですよね...

-- 引数の型と数、どうなってたっけ...
DESC PROCEDURE MY_DB.MY_SCHEMA.TEST_PROCEDURE(
    -- ここに入れるべきものが分からない...
)
;

で、調べるにもサクッといかない気がしますよね。

調べたら調べたで、引数名と型がセットで出てきてしまい、単純コピペってわけでもないので、やっぱり面倒くさい...

とりあえず画面で見てみると...

SQLワークシートの画面の左ペインで見るはできますが、非常に惜しい...
これをコピペできないのです。

「Place name in editor」や「Copy name」では、識別子までしかコピーしてくれません。

-- その後ろの情報が欲しいんだよ...
MY_DB.MY_SCHEMA.TEST_PROCEDURE

では、パッと欲しい情報を引っ張ってくるにはどうするのか？
多分、とりあえずこうする他ない気がしました。

-- 探したいプロシージャ名
SET TARGET_PROC = 'TEST_PROCEDURE';

-- プロシージャ名(識別子)と引数の型のリストを得る
SHOW PROCEDURES IN ACCOUNT;
SELECT
 CONCAT("catalog_name", '.', "schema_name", '.') || 
 SUBSTR(
    "arguments",
    1, 
    POSITION('RETURN' IN "arguments") - 1
 ) as TARGET_PROC
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "name" = $TARGET_PROC

こんな感じで得られます。

TARGET_PROC
MY_DB.MY_SCHEMA.TEST_PROCEDURE(VARCHAR, FLOAT, ARRAY)

この結果をコピペすれば、実行する際にモヤモヤしていたクエリもサクッと実行できます!

-- 上記のクエリ結果を、次のようなクエリのにコピペして実行する!
-- -- DESC PROCEDURE <ここを置換する>;
-- -- SHOW GRANTS ON PROCEDURE <ここを置換する>;
-- -- SELECT GET_DDL('PROCEDURE', '<ここを置換する>');

-- これも!
DESC PROCEDURE MY_DB.MY_SCHEMA.TEST_PROCEDURE(VARCHAR, FLOAT, ARRAY);
-- これも!!
SHOW GRANTS ON PROCEDURE MY_DB.MY_SCHEMA.TEST_PROCEDURE(VARCHAR, FLOAT, ARRAY);
-- これも!!!
SELECT GET_DDL('PROCEDURE', 'MY_DB.MY_SCHEMA.TEST_PROCEDURE(VARCHAR, FLOAT, ARRAY)');

先ほどのSQLをテンプレートとして、どこかに退避しておいて使い回すのが良いかと思います。

どうしても変数でデータベース/スキーマ/テーブル等の識別子を指定したい時

通常はSQLを書く時、普通にデータベース名、スキーマ名、テーブル名をベタ書きするかと思います。

例えば...

-- データベース(MY_DB)内の
-- スキーマ(MY_SCHEMA)配下にある
-- テーブル(TEST_TABLE)が持つカラムの一覧とそのデータ型を得る

SELECT
    COLUMN_NAME, DATA_TYPE
FROM
    MY_DB.INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA = 'MY_SCHEMA'
    AND TABLE_NAME = 'TEST_TABLE'
;

もしくは、コンテキストを指定してからクエリを実行したりします。

USE DATABASE MY_DB;

SELECT
    COLUMN_NAME, DATA_TYPE
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA = 'MY_SCHEMA'
    AND TABLE_NAME = 'TEST_TABLE'
;

即興で書く分にはこれでいいのですが、記述を使いまわしたい時にちょっと考えてしまいます。
データベース、スキーマ、テーブルの記載箇所が飛び飛びで、毎回変更するのが面倒です。

そこで、変数化していい感じにまとまらないかな、と思い至ります。

-- こんな感じ
SET TARGET_DB = 'MY_DB';
SET TARGET_SCHEMA = 'MY_SCHEMA';
SET TARGET_TABLE = 'TEST_TABLE';

そして、これらを使ってクエリを実行したいわけです。

いざ、これらの変数を使って、想像で次のようなクエリを実行してみるとうまくいかないのです。

一旦、簡単なクエリで動作確認してみます。

-- エラー
USE DATABASE $TARGET_DB;

-- こちらもエラー
SELECT
    *
FROM
    $TARGET_DB.$TARGET_SCHEMA.$TARGET_TABLE;

エラー文言

Syntax error: unexpected '$TARGET_DB'. (line XX)

これは、Snowflakeでは変数を識別子として扱うためにはIDENTIFIER()という関数を使用する必要があるためです。(参考)

では、IDENTIFIER()を使ってみると...

USE DATABASE IDENTIFIER($TARGET_DB);

出力は...

Statement executed successfully.

うまくいきました！

一方で...

-- これはダメ？
SELECT
    *
FROM
    IDENTIFIER($TARGET_DB.$TARGET_SCHEMA.$TARGET_TABLE);

-- これもダメ？
SELECT
    *
FROM
    IDENTIFIER($TARGET_DB).IDENTIFIER($TARGET_SCHEMA).IDENTIFIER($TARGET_TABLE);

出力は...

Syntax error: unexpected '.'.

残念ながら、いずれもできませんでした。
IDENTIFIER()で指定できるのは、1つの変数だけのようです。

色々試した以下のいずれもエラーでした。

-- NGシーン集
SELECT
    * 
FROM
    IDENTIFIER($TARGET_DB || '.' || $TARGET_SCHEMA || '.' || $TARGET_TABLE)
;

SELECT
    * 
FROM
    IDENTIFIER(CONCAT($TARGET_DB, '.', $TARGET_SCHEMA, '.', $TARGET_TABLE))
;

EXECUTE IMMEDIATE 
    'SELECT * FROM ' || IDENTIFIER($TARGET_DB) || '.' || IDENTIFIER($TARGET_SCHEMA) || '.' || IDENTIFIER($TARGET_TABLE)
;

ではどうすればいいか？
うまくいく方法は以下になります。

識別子を結合して別変数に代入する

SET TARGET_DB = 'MY_DB';
SET TARGET_SCHEMA = 'MY_SCHEMA';
SET TARGET_TABLE = 'TEST_TABLE';

SET TARGET_OBJECT=$TARGET_DB||'.'||$TARGET_SCHEMA||'.'||$TARGET_TABLE;

SELECT * FROM IDENTIFIER($TARGET_OBJECT);

素直に1つずつ使う

SET TARGET_DB = 'MY_DB';
SET TARGET_SCHEMA = 'MY_SCHEMA';
SET TARGET_TABLE = 'TEST_TABLE';

USE DATABASE IDENTIFIER($TARGET_DB);
USE SCHEMA IDENTIFIER($TARGET_SCHEMA);
SELECT * FROM IDENTIFIER($TARGET_TABLE);

クエリを変数化 (IDENTIFIER()を使わないで済む)

SET TARGET_DB = 'MY_DB';
SET TARGET_SCHEMA = 'MY_SCHEMA';
SET TARGET_TABLE = 'TEST_TABLE';

SET QUERY_STRING = 'SELECT * FROM ' || $TARGET_DB || '.' || $TARGET_SCHEMA || '.' || $TARGET_TABLE;
EXECUTE IMMEDIATE $QUERY_STRING;

といった具合ですね。

お題目のクエリ(あるテーブルのカラム一覧とその型を得る)を変数化して実行するには、以下で実現できそうです。

-- データベース($TARGET_DB)内の
-- スキーマ($TARGET_SCHEMA)配下にある
-- テーブル($TARGET_TABLE)が持つカラムの一覧とそのデータ型を得る

SET TARGET_DB = 'MY_DB';
SET TARGET_SCHEMA = 'MY_SCHEMA';
SET TARGET_TABLE = 'TEST_TABLE';

SET TARGET_OBJECT=$TARGET_DB || '.INFORMATION_SCHEMA.COLUMNS';

SELECT
    COLUMN_NAME, DATA_TYPE
FROM
    IDENTIFIER($TARGET_OBJECT)
WHERE
    TABLE_SCHEMA = $TARGET_SCHEMA
    AND TABLE_NAME = $TARGET_TABLE
;

実行結果

SQLワークシート上でSQLを実行する際や、ストアドプロシージャにする際等、参考にしてみてください。

おわりに

色々ありました...。
やればやるほど、細かいところで「おや？」となる部分が出てきて、奥深いですね。

以上です。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

Snowflakeの小ネタ・小技まとめ

はじめに

SHOW等の結果に含まれている列の型を知りたい時

Snowflakeスクリプトを書く時、Snowsightでは文字列リテラル区切り文字($$等)は不要

for Snowsight

for SnowSQL or クラシックコンソール

ストアドプロシージャ内でSHOW/DESCを実行する際の注意点

関連情報

ストアドプロシージャが持つ引数の型リストをパッと取得するには

どうしても変数でデータベース/スキーマ/テーブル等の識別子を指定したい時

おわりに