Snowflakeの小ネタ・小技まとめ

## はじめに

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

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

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

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

```SQL
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`を使ってみると調べることができます。

```SQL
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`で絞っています。

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

```SQL
SHOW USERS;

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

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

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

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

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

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

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

[Snowflakeスクリプトでのストアドプロシージャの記述](https://docs.snowflake.com/ja/developer-guide/stored-procedure/stored-procedures-snowflake-scripting#introduction)

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

[SnowSQL および Classic Console でのSnowflakeスクリプトの使用](https://docs.snowflake.com/ja/developer-guide/snowflake-scripting/running-examples#introduction)

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

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

### for Snowsight

```SQL
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 クラシックコンソール

「$$」を使った記載

```SQL
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
$$
;
```

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

```SQL
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
'
;
```

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

参考 : [一重引用符で囲まれた文字列定数のエスケープシーケンス](https://docs.snowflake.com/ja/sql-reference/data-types-text#escape-sequences-in-single-quoted-string-constants)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/617410/3a1ce40b-ed5c-1ea6-4625-214978d88f25.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/617410/94061870-fc56-4374-1e91-a0a4688f907b.png)

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

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

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

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

```SQL
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してみると...

```SQL
call show_warehouses();
```

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

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

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

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

試してみます。

```SQL
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してみると...

```SQL
call show_users();
```

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

```text
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を実行するように定義すれば実行できるようになる、ということですね。

```SQL
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してみると...

```SQL
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`をつけてストアドプロシージャを作成すれば実行できます。

```SQL
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してみると...

```SQL
call get_has_mfa('test_user');
```

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

|property|	value|
|-|-|
|HAS_MFA|	false|


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

```text
Insufficient privileges to operate on user 'TEST_USER'
```

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

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

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

### 関連情報

- [CREATE PROCEDURE - EXECUTE AS CALLER または EXECUTE AS OWNER](https://docs.snowflake.com/ja/sql-reference/sql/create-procedure#optional-parameters:~:text=EXECUTE%20AS%20CALLER%20%E3%81%BE%E3%81%9F%E3%81%AF%20EXECUTE%20AS%20OWNER)
- [SHOW and DESCRIBE Commands](https://docs.snowflake.com/ja/developer-guide/stored-procedure/stored-procedures-rights#show-and-describe-commands)
    - これによると、以下は`EXECUTE AS OWNER`でも実行できるとのこと
        - SHOW DATABASES
        - SHOW SCHEMAS
        - SHOW WAREHOUSES


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

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

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

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

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

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

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

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

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

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

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

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

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/617410/04443ec5-9236-a14a-98b1-5208e1515c0d.png)

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

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

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

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

```SQL
-- 探したいプロシージャ名
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) |

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

```SQL
-- 上記のクエリ結果を、次のようなクエリのにコピペして実行する!
-- -- 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を書く時、普通にデータベース名、スキーマ名、テーブル名をベタ書きするかと思います。

例えば...

```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'
;
```

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

```SQL
USE DATABASE MY_DB;

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

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

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

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

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

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

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

```SQL
-- エラー
USE DATABASE $TARGET_DB;

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

エラー文言

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

これは、Snowflakeでは`変数を識別子として扱う`ためには`IDENTIFIER()`という関数を使用する必要があるためです。([参考](https://docs.snowflake.com/ja/sql-reference/identifier-literal))

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

```SQL
USE DATABASE IDENTIFIER($TARGET_DB);
```

出力は...

```TEXT
Statement executed successfully.
```

うまくいきました！

一方で...

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

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

出力は...

```TEXT
Syntax error: unexpected '.'. 
```

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

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

```SQL
-- 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)
;
```

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

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

    ```SQL
    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. 素直に1つずつ使う

    ```SQL
    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);
    ```

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

    ```SQL
    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;
    ```

といった具合ですね。

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

```SQL
-- データベース($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
;
```

実行結果

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/617410/51387b10-9806-650a-13ed-e1c2409052b6.png)

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

## おわりに

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

以上です。