0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@Cache-Hit

AWS閉鎖+Snowflake クエリの実行の動作を確認するためのプロシージャ作成

0
Last updated at Posted at 2026-10-31

これの続きになります。

今回の検証はSnowflakeで実施したクエリがうまくいっているかを確認するプロシージャを作成します。

★Snowsightに手作業

今回の情報
タイプ 値
Snowflake中央管理データベース SNOWFLAKE
今回利用するデータベース SNOWTESTDB
SNOWTESTDBを利用するシステムユーザ SVC_SNOWFLAKE_JDBC
SVC_SNOWFLAKE_JDBCユーザのロール ROLE_SNOWFLAKE_JDBC

Snowflake中央管理データベースにの管理権限を今回のユーザのロールにアタッチする。

GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE ROLE_SNOWFLAKE_JDBC;

Snowsightでプロシージャを作成
データベースはSNOWTESTDB
スキーマはTEST_SCHEMA
プロシージャ名はTEST_CHECK_BAKUP()


SNOWTESTDB.TEST_SCHEMA.TEST_CHECK_BAKUP() 
CREATE OR REPLACE PROCEDURE SNOWTESTDB.TEST_SCHEMA.TEST_CHECK_BAKUP()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS 'DECLARE
  v_query_id VARCHAR;
  v_status   VARCHAR;
BEGIN
  -- 自分が最後に実行したINSERTのクエリIDとステータスを取得
  SELECT query_id, execution_status INTO :v_query_id, :v_status
  FROM TABLE(SNOWTESTDB.INFORMATION_SCHEMA.QUERY_HISTORY_BY_USER(RESULT_LIMIT => 1000))
  WHERE query_text ILIKE ''INSERT%''
  ORDER BY start_time DESC
  LIMIT 1;

  IF (v_query_id IS NULL) THEN
    RETURN ''INSERTが見つかりません'';
  END IF;

  RETURN v_query_id || '' : '' || v_status;
END';

今作成したプロシージャの利用権限を、今回利用するロールに作成

GRANT USAGE ON PROCEDURE SNOWTESTDB.TEST_SCHEMA.TEST_CHECK_BAKUP() TO ROLE ROLE_SNOWFLAKE_JDBC;

今回の環境変数を実施
※本来はバッチで行いたいです

★AWS側のEC2で作業

set PRIVATE_KEY_PASSPHRASE=XXXX
set SqlFile=C:\Users\Administrator\Desktop\testsql2.sql
set ACCOUNT=XUQYYJQ-AW77300
set UserName=SVC_SNOWFLAKE_JDBC
set ROLE=ROLE_SNOWFLAKE_JDBC
set DWH=SNOWTESTSFLAKE
set DB=SNOWTESTDB
set HOST=HK33186.ap-northeast-1.privatelink.snowflakecomputing.com
set PORT=443
set AUTH=SNOWFLAKE_JWT
set PRIVATE="C:\\snowflake-jdbc\\keys\\rsa_key.p8" 
set FORMAT=csv
set SqlFile=C:\Users\Administrator\Desktop\testsql2.sql

testsql2.sqlの中身

C:\Users\Administrator>type C:\Users\Administrator\Desktop\testsql2.sql
CALL SNOWTESTDB.TEST_SCHEMA.TEST_CHECK_BAKUP();

現在のテーブルの中身を確認

snow sql --temporary-connection --accountname %ACCOUNT% --username %UserName% --role %ROLE% --warehouse %DWH% --database %DB% --host %HOST% --port %PORT% --authenticator %AUTH% --private-key-path %PRIVATE% --format %FORMAT% -q "SELECT * FROM SNOWTESTDB.TEST_SCHEMA.TEST_TABLE LIMIT 100;"


C:\Users\Administrator\AppData\Local\pyapp\data\snowflake-cli\12766843218322477748\3.28.0\python\Lib\site-packages\snowflake\cli\api\encoding_diagnostics.py:156: UserWarning: Encoding mismatch detected. Run 'snow helpers detect-encoding' for more details.
  warnings.warn(msg)
ID,NAME
101,Tokyo
102,Osaka
103,nagoya
104,Yamada
105,Tanaka
106,Smith
107,OJIISAN
108,OBASAN
109,OJISAN
110,OBAASAN

新しくINSERT文を入れる。

INSERT INTO SNOWTESTDB.TEST_SCHEMA.TEST_TABLE (ID, NAME)  VALUES  (111, 'KENSYOU');

実行

※--schema TEST_SCHEMAを追加しています。

C:\Users\Administrator>snow sql --temporary-connection --accountname %ACCOUNT% --username %UserName% --role %ROLE% --warehouse %DWH% --database %DB% --host %HOST% --port %PORT% --authenticator %AUTH% --private-key-path %PRIVATE% --format %FORMAT% -q "INSERT INTO SNOWTESTDB.TEST_SCHEMA.TEST_TABLE (ID, NAME)  VALUES  (111, 'KENSYOU');"
C:\Users\Administrator\AppData\Local\pyapp\data\snowflake-cli\12766843218322477748\3.28.0\python\Lib\site-packages\snowflake\cli\api\encoding_diagnostics.py:156: UserWarning: Encoding mismatch detected. Run 'snow helpers detect-encoding' for more details.
  warnings.warn(msg)
number of rows inserted
1

レコードが正常に追加されたことを確認

C:\Users\Administrator>snow sql --temporary-connection --accountname %ACCOUNT% --username %UserName% --role %ROLE% --warehouse %DWH% --database %DB% --host %HOST% --port %PORT% --authenticator %AUTH% --private-key-path %PRIVATE% --format %FORMAT% -q "SELECT * FROM SNOWTESTDB.TEST_SCHEMA.TEST_TABLE LIMIT 100;"
C:\Users\Administrator\AppData\Local\pyapp\data\snowflake-cli\12766843218322477748\3.28.0\python\Lib\site-packages\snowflake\cli\api\encoding_diagnostics.py:156: UserWarning: Encoding mismatch detected. Run 'snow helpers detect-encoding' for more details.
  warnings.warn(msg)
ID,NAME
101,Tokyo
102,Osaka
103,nagoya
104,Yamada
105,Tanaka
106,Smith
107,OJIISAN
108,OBASAN
109,OJISAN
110,OBAASAN
111,KENSYOU

最後にプロシージャを行う。

C:\Users\Administrator>snow sql --temporary-connection --accountname %ACCOUNT% --username %UserName% --role %ROLE% --warehouse %DWH% --database %DB% --host %HOST% --port %PORT% --authenticator %AUTH% --private-key-path %PRIVATE% --format %FORMAT% -f "%SqlFile%"
C:\Users\Administrator\AppData\Local\pyapp\data\snowflake-cli\12766843218322477748\3.28.0\python\Lib\site-packages\snowflake\cli\api\encoding_diagnostics.py:156: UserWarning: Encoding mismatch detected. Run 'snow helpers detect-encoding' for more details.
  warnings.warn(msg)
TEST_CHECK_BAKUP
01c8171e-0105-1e50-0005-7192001a0d6e : SUCCESS

うまくいったことを確認。


実際にこのクエリIDで調べてみる。

クエリの情報一覧を出す。
※今回は見やすいようにフォーマットをJSONで出力

C:\Users\Administrator>snow sql --temporary-connection --accountname %ACCOUNT% --username %UserName% --role %ROLE% --warehouse %DWH% --database %DB% --host %HOST% --port %PORT% --authenticator %AUTH% --private-key-path %PRIVATE%  --format json  -q "SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE QUERY_ID = '01c8171e-0105-1e50-0005-7192001a0d6e';"
C:\Users\Administrator\AppData\Local\pyapp\data\snowflake-cli\12766843218322477748\3.28.0\python\Lib\site-packages\snowflake\cli\api\encoding_diagnostics.py:156: UserWarning: Encoding mismatch detected. Run 'snow helpers detect-encoding' for more details.
  warnings.warn(msg)
[
    {
        "QUERY_ID": "01c8171e-0105-1e50-0005-7192001a0d6e",
        "QUERY_TEXT": "INSERT INTO SNOWTESTDB.TEST_SCHEMA.TEST_TABLE (ID, NAME)  VALUES  (111, 'KENSYOU');",
        "DATABASE_ID": 42,
        "DATABASE_NAME": "SNOWTESTDB",
        "SCHEMA_ID": null,
        "SCHEMA_NAME": null,
        "QUERY_TYPE": "INSERT",
        "SESSION_ID": 1532246763803206,
        "AUTHN_EVENT_ID": 23380276849,
        "USER_NAME": "SVC_SNOWFLAKE_JDBC",
        "ROLE_NAME": "ROLE_SNOWFLAKE_JDBC",
        "WAREHOUSE_ID": 62,
        "WAREHOUSE_NAME": "SNOWTESTSFLAKE",
        "WAREHOUSE_SIZE": "X-Small",
        "WAREHOUSE_TYPE": "STANDARD",
        "CLUSTER_NUMBER": 1,
        "QUERY_TAG": "",
        "EXECUTION_STATUS": "SUCCESS",　　#★うまくいっていることを確認
        "ERROR_CODE": null,
        "ERROR_MESSAGE": null,
        "START_TIME": "2026-10-30T21:14:04.695000-07:00",
        "END_TIME": "2026-10-30T21:14:05.447000-07:00",
        "TOTAL_ELAPSED_TIME": 752,
        "BYTES_SCANNED": 1024,
        "PERCENTAGE_SCANNED_FROM_CACHE": 0.0,
        "BYTES_WRITTEN": 1024,
        "BYTES_WRITTEN_TO_RESULT": 5,
        "BYTES_READ_FROM_RESULT": 0,
        "ROWS_PRODUCED": 11,
        "ROWS_INSERTED": 1,
        "ROWS_UPDATED": 0,
        "ROWS_DELETED": 0,
        "ROWS_UNLOADED": 0,
        "BYTES_DELETED": 0,
        "PARTITIONS_SCANNED": 1,
        "PARTITIONS_TOTAL": 1,
        "BYTES_SPILLED_TO_LOCAL_STORAGE": 0,
        "BYTES_SPILLED_TO_REMOTE_STORAGE": 0,
        "BYTES_SENT_OVER_THE_NETWORK": 0,
        "COMPILATION_TIME": 213,
        "EXECUTION_TIME": 421,
        "QUEUED_PROVISIONING_TIME": 118,
        "QUEUED_REPAIR_TIME": 0,
        "QUEUED_OVERLOAD_TIME": 0,
        "TRANSACTION_BLOCKED_TIME": 0,
        "OUTBOUND_DATA_TRANSFER_CLOUD": null,
        "OUTBOUND_DATA_TRANSFER_REGION": null,
        "OUTBOUND_DATA_TRANSFER_BYTES": 0,
        "INBOUND_DATA_TRANSFER_CLOUD": null,
        "INBOUND_DATA_TRANSFER_REGION": null,
        "INBOUND_DATA_TRANSFER_BYTES": 0,
        "LIST_EXTERNAL_FILES_TIME": 0,
        "CREDITS_USED_CLOUD_SERVICES": 6e-05,
        "RELEASE_VERSION": "10.39.101",
        "EXTERNAL_FUNCTION_TOTAL_INVOCATIONS": 0,
        "EXTERNAL_FUNCTION_TOTAL_SENT_ROWS": 0,
        "EXTERNAL_FUNCTION_TOTAL_RECEIVED_ROWS": 0,
        "EXTERNAL_FUNCTION_TOTAL_SENT_BYTES": 0,
        "EXTERNAL_FUNCTION_TOTAL_RECEIVED_BYTES": 0,
        "QUERY_LOAD_PERCENT": 100,
        "IS_CLIENT_GENERATED_STATEMENT": false,
        "QUERY_ACCELERATION_BYTES_SCANNED": 0,
        "QUERY_ACCELERATION_PARTITIONS_SCANNED": 0,
        "QUERY_ACCELERATION_UPPER_LIMIT_SCALE_FACTOR": 0,
        "TRANSACTION_ID": 1793420044800000000,
        "CHILD_QUERIES_WAIT_TIME": 0,
        "ROLE_TYPE": "ROLE",
        "QUERY_HASH": "94934a45f6bf92a2cd129478093f44c2",
        "QUERY_HASH_VERSION": 2,
        "QUERY_PARAMETERIZED_HASH": "94934a45f6bf92a2cd129478093f44c2",
        "QUERY_PARAMETERIZED_HASH_VERSION": 1,
        "SECONDARY_ROLE_STATS": "{\"roleCount\":2,\"roleIds\":[2221,2191]}",
        "ROWS_WRITTEN_TO_RESULT": 1,
        "QUERY_RETRY_TIME": null,
        "QUERY_RETRY_CAUSE": null,
        "FAULT_HANDLING_TIME": null,
        "USER_TYPE": "SERVICE",
        "USER_DATABASE_NAME": null,
        "USER_DATABASE_ID": null,
        "USER_SCHEMA_NAME": null,
        "USER_SCHEMA_ID": null,
        "RESOURCE_CONSTRAINT": "STANDARD_GEN_1",
        "GENERATION": "1",
        "BIND_VALUES": null,
        "AGENT_TYPE": null
    }
]
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?