今回の目的
Snowflakeでは、自動停止や自動起動を入れておくと
クエリを流していない状態ではDWHは停止し、
ストレージのみの課金となります。
ただし、クエリを流しても自動起動しない設定はできるのか検証します。
前回までの検証
①AWS閉鎖網+Snowflake Private Link
https://qiita.com/Cache-Hit/items/5f7f8788b4d97d4b553d
②AWS閉鎖網+Snowflake 内部ステージのS3への接続設定
https://qiita.com/Cache-Hit/items/61a5aac9a66bda5f0aa8
検証環境用意
今回行う検証は
・自動停止起動を入れたDWHに対して、
停止中にクエリを流してもエラーになり起動しないようにするために
Snowsightにクエリを流す。
準備
AUTO_RESUMEの設定をFALSEは
現在の設定の確認
※現在のauto_resume【ウェアハウスが一時停止された場合、クエリがウェアハウスに送信された時点で自動的に再開するか】の設定が
TRUEであることを確認
SHOW WAREHOUSES LIKE 'SNOWTESTSFLAKE';
SELECT "auto_resume"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
・設定変更
ALTER WAREHOUSE SNOWTESTSFLAKE SET AUTO_RESUME = FALSE;
Falseになっていることを確認
SHOW WAREHOUSES LIKE 'SNOWTESTSFLAKE';
対象のDWHに確認のクエリを投げてうまくいくかを確認。
※うまくいかないための検証
snowtestsflakePLの設定ファイル確認
C:\Users\Administrator>type C:\Users\Administrator\AppData\Local\snowflake\config.toml
[cli]
ignore_new_version_warning = false
[cli.logs]
save_logs = true
path = "C:\\Users\\Administrator\\AppData\\Local\\snowflake\\logs"
level = "info"
[connections.privatelinkS3]
account = "XUQYYJQ-AW77300"
user = "TESTOBATA2"
password = "8888qqqqChainsaw8%"
host = "HK33186.ap-northeast-1.privatelink.snowflakecomputing.com"
region = "ap-northeast-1"
port = 443
protocol = "https"
role = "ACCOUNTADMIN"
authenticator = "username_password_mfa"
[connections.snowtestsflakePL]
account = "XUQYYJQ-AW77300"
user = "SNOWTESTSFLAKE"
password = "8888qqqqChainsaw8%"
host = "HK33186.ap-northeast-1.privatelink.snowflakecomputing.com"
port = 443
protocol = "https"
authenticator = "USERNAME_PASSWORD_MFA"
role = "ACCOUNTADMIN"
warehouse = "SNOWTESTSFLAKE"
接続テスト
まずはテーブル確認
C:\Users\Administrator>snow sql --connection snowtestsflakePL --mfa-passcode 945391 -q "SHOW TABLES IN SCHEMA SNOWTESTDB.TEST_SCHEMA;"
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)
SHOW TABLES IN SCHEMA SNOWTESTDB.TEST_SCHEMA;
+----------------------------------------------------------------------------------------------------------------------+
| | | | | | | | | | | | | | | s | | | | | | | | | | | | |
| | | | | | | | | | | | | | | e | | | | | | | | | | | | |
| | | | | | | | | | | | | | | a | | | | | | | | | | | | |
| | | | | | | | | | | | | | | r | | | | | | | | | | | | |
| | | | | | | | | | | | | | | c | | | | | | | | | | | | |
| | | | | | | | | | | | | | | h | | | | | | | | | | | | |
| | | | | | | | | | | | | | | _ | | | | | | | | | | | | |
| | | | | | | | | | | | | | | o | | | | | | | | | | | | |
| | | | | | | | | | | | | | | p | | | | | | | | | | | | |
| | | | | | | | | | | | | | | t | | | | | | | | | | | | |
| | | | | | | | | | | | | | | i | | | | | | | | | | | | |
| | | | | | | | | | | | | | | m | | | | | | | | | | | | |
| | | | | | | | | | | | | | | i | | | | | | | | | | | | |
| | | | | | | | | | | | | c | | z | | | | o | | | | | | | | |
| | | | | | | | | | | r | | h | | a | | | | w | | | | | | i | | |
| | | d | | | | | | | | e | | a | | t | se | | | n | | | | | | s | | e |
| | | a | | | | | | | | t | | n | | i | ar | | en | e | | | | | | _ | | r |
| | | t | s | | | | | | | e | | g | | o | ch | i | ab | r | | | | | | i | | r |
| c | | a | c | | | c | | | | n | au | e | se | n | _o | s | le | _ | | | | i | | n | | o |
| r | | b | h | | | l | | | | t | to | _ | ar | _ | pt | _ | _s | r | | i | | s | | t | | r |
| e | | a | e | | | u | | | | i | ma | t | ch | p | im | e | ch | o | | s | | _ | | e | | _ |
| a | | s | m | | | s | | | | o | ti | r | _o | r | iz | x | em | l | | _ | | d | | r | ro | l |
| t | | e | a | | | t | | | | n | c_ | a | pt | o | at | t | a_ | e | | h | | y | is | a | w_ | o |
| e | | _ | _ | | | e | | b | | _ | cl | c | im | g | io | e | ev | _ | | y | is | n | _i | c | ti | g |
| d | n | n | n | k | co | r | | y | | t | us | k | iz | r | n_ | r | ol | t | is | b | _i | a | mm | t | me | g |
| _ | a | a | a | i | mm | _ | | t | ow | i | te | i | at | e | by | n | ut | y | _e | r | ce | m | ut | i | st | i |
| o | m | m | m | n | en | b | ro | e | ne | m | ri | n | io | s | te | a | io | p | ve | i | be | i | ab | v | am | n |
| n | e | e | e | d | t | y | ws | s | r | e | ng | g | n | s | s | l | n | e | nt | d | rg | c | le | e | p | g |
|---+---+---+---+---+----+---+----+---+----+---+----+---+----+---+----+---+----+---+----+---+----+---+----+---+----+---|
| 2 | T | S | T | T | | | 3 | 1 | AC | 1 | OF | O | OF | N | No | N | N | R | N | N | N | N | N | N | OF | O |
| 0 | E | N | E | A | | | | 0 | CO | | F | F | F | o | ne | | | O | | | | | | | F | F |
| 2 | S | O | S | B | | | | 2 | UN | | | F | | n | | | | L | | | | | | | | F |
| 6 | T | W | T | L | | | | 4 | TA | | | | | e | | | | E | | | | | | | | |
| - | _ | T | _ | E | | | | | DM | | | | | | | | | | | | | | | | | |
| 1 | T | E | S | | | | | | IN | | | | | | | | | | | | | | | | | |
| 0 | A | S | C | | | | | | | | | | | | | | | | | | | | | | | |
| - | B | T | H | | | | | | | | | | | | | | | | | | | | | | | |
| 1 | L | D | E | | | | | | | | | | | | | | | | | | | | | | | |
| 5 | E | B | M | | | | | | | | | | | | | | | | | | | | | | | |
| | | | A | | | | | | | | | | | | | | | | | | | | | | | |
| 1 | | | | | | | | | | | | | | | | | | | | | | | | | | |
| 9 | | | | | | | | | | | | | | | | | | | | | | | | | | |
| : | | | | | | | | | | | | | | | | | | | | | | | | | | |
| 2 | | | | | | | | | | | | | | | | | | | | | | | | | | |
| 8 | | | | | | | | | | | | | | | | | | | | | | | | | | |
| : | | | | | | | | | | | | | | | | | | | | | | | | | | |
| 1 | | | | | | | | | | | | | | | | | | | | | | | | | | |
| 0 | | | | | | | | | | | | | | | | | | | | | | | | | | |
| . | | | | | | | | | | | | | | | | | | | | | | | | | | |
| 3 | | | | | | | | | | | | | | | | | | | | | | | | | | |
| 4 | | | | | | | | | | | | | | | | | | | | | | | | | | |
| 5 | | | | | | | | | | | | | | | | | | | | | | | | | | |
| 0 | | | | | | | | | | | | | | | | | | | | | | | | | | |
| 0 | | | | | | | | | | | | | | | | | | | | | | | | | | |
| 0 | | | | | | | | | | | | | | | | | | | | | | | | | | |
| - | | | | | | | | | | | | | | | | | | | | | | | | | | |
| 0 | | | | | | | | | | | | | | | | | | | | | | | | | | |
| 7 | | | | | | | | | | | | | | | | | | | | | | | | | | |
| : | | | | | | | | | | | | | | | | | | | | | | | | | | |
| 0 | | | | | | | | | | | | | | | | | | | | | | | | | | |
| 0 | | | | | | | | | | | | | | | | | | | | | | | | | | |
+----------------------------------------------------------------------------------------------------------------------+
C:\Users\Administrator>snow sql --connection snowtestsflakePL --mfa-passcode 796266 -q "SELECT * FROM SNOWTESTDB.TEST_SCHEMA.TEST_TABLE LIMIT 10;"
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)
SELECT * FROM SNOWTESTDB.TEST_SCHEMA.TEST_TABLE LIMIT 10;
╭─ Error ──────────────────────────────────────────────────────────────────────────────────────────────────────────────╮
│ 000608 (57P03): 01c7c775-0304-f8f7-0005-7192000c7106: Warehouse 'SNOWTESTSFLAKE' is suspended. Use 'alter warehouse │
│ SNOWTESTSFLAKE resume;' command if you need to use it again. │
│ │
╰───────────────────────────────────────────────────────────────────
うまくいかないことが証明できました！