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-24

今回は、バッチの中身を作るため、snow connectionを利用しないアクセスにて接続を考えます。

構成

目的

対象のテーブルの値を取得する。
その値をcsvで出す上にヘッダーを消す。かつ余計な空白を消す。

環境変数入力

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 SCHEMA=TEST_SCHEMA
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の中身

SELECT * FROM SNOWTESTDB.TEST_SCHEMA.TEST_TABLE;

余計な空白を削除

C:\Users\Administrator>snow sql --temporary-connection --accountname %ACCOUNT% --username %UserName% --role %ROLE% --warehouse %DWH% --database %DB% --schema %SCHEMA% --host %HOST% --port %PORT% --authenticator %AUTH% --private-key-path %PRIVATE% --format %FORMAT% -f "%SqlFile%" | findstr /n "^" | findstr /v /b "1:" | findstr /r /v "^[0-9]*:$" | powershell -NoProfile -Command "$input | ForEach-Object { $_ -replace '^[0-9]+:', '' }"
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)
101,Tokyo
102,Osaka
103,nagoya
104,Yamada
105,Tanaka
106,Smith

C:\Users\Administrator>

ファイル確認

C:\Users\Administrator>type "C:\Users\Administrator\Desktop\TEST_TABLE.csv"
101,Tokyo
102,Osaka
103,nagoya
104,Yamada
105,Tanaka
106,Smith

C:\Users\Administrator>

余計な空白を削除

snow sql --temporary-connection --accountname %ACCOUNT% --username %UserName% --role %ROLE% --warehouse %DWH% --database %DB% --schema %SCHEMA% --host %HOST% --port %PORT% --authenticator %AUTH% --private-key-path %PRIVATE% --format %FORMAT% -f "%SqlFile%" | powershell -NoProfile -Command "$lines=@($input | Select-Object -Skip 1 | Where-Object { $_ -ne '' }); [Console]::Write(($lines -join [Environment]::NewLine))" > "C:\Users\Administrator\Desktop\TEST_TABLE.csv"

ファイル確認

C:\Users\Administrator>type "C:\Users\Administrator\Desktop\TEST_TABLE.csv"
101,Tokyo
102,Osaka
103,nagoya
104,Yamada
105,Tanaka
106,Smith
C:\Users\Administrator>

最終的にcsvに吐かないで確認するコマンド

\Users\Administrator>snow sql --temporary-connection --accountname %ACCOUNT% --username %UserName% --role %ROLE% --warehouse %DWH% --database %DB% --schema %SCHEMA% --host %HOST% --port %PORT% --authenticator %AUTH% --private-key-path %PRIVATE% --format %FORMAT% -f "%SqlFile%" | powershell -NoProfile -Command "$lines=@($input | Select-Object -Skip 1 | Where-Object { $_ -ne '' }); [Console]::Write(($lines -join [Environment]::NewLine))"
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)
101,Tokyo
102,Osaka
103,nagoya
104,Yamada
105,Tanaka
106,Smith
C:\Users\Administrator>

容量などを確認するバッチ

c:\>type "ExportSnowflakeTableInventory.bat"
@echo off
setlocal
set "SQL_FILE=%~dp0SnowflakeTableInventory.sql"
set "OUT_FILE=%~dp0SnowflakeTableInventory.csv"
set "ERR_FILE=%~dp0SnowflakeTableInventory_error.log"

if not exist "%SQL_FILE%" (
  echo ERROR: SQL file not found: "%SQL_FILE%"
  exit /b 1
)
if not defined PRIVATE_KEY_PASSPHRASE (
  echo ERROR: PRIVATE_KEY_PASSPHRASE is not set in this CMD session.
  exit /b 1
)

snow sql -c audit_ec2 --format CSV -f "%SQL_FILE%" > "%OUT_FILE%" 2> "%ERR_FILE%"
if errorlevel 1 (
  echo ERROR: Snowflake SQL failed. Check "%ERR_FILE%"
  del "%OUT_FILE%" >nul 2>&1
  exit /b 1
)
echo SUCCESS: "%OUT_FILE%"
exit /b 0

sqlファイル

c:\>type "SnowflakeTableInventory.sql"
-- Inventory of accessible base tables in SNOWTESTDB.TEST_SCHEMA.
-- SCAN_KB is scan-accessible bytes, NOT billed storage size.
SELECT
    TABLE_NAME AS "Name",
    ROW_COUNT AS "Rows",
    CAST(NULL AS NUMBER(38,2)) AS "Reserved_KB",
    ROUND(BYTES / 1024.0, 2) AS "Scan_KB",
    CAST(NULL AS NUMBER(38,2)) AS "Data_KB",
    CAST(NULL AS NUMBER(38,2)) AS "Index_KB",
    CAST(NULL AS NUMBER(38,2)) AS "Unused_KB"
FROM SNOWTESTDB.INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'TEST_SCHEMA'
  AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME;

結果

c:\>type "ExportSnowflakeTableInventory.bat"
@echo off
setlocal
set "SQL_FILE=%~dp0SnowflakeTableInventory.sql"
set "OUT_FILE=%~dp0SnowflakeTableInventory.csv"
set "ERR_FILE=%~dp0SnowflakeTableInventory_error.log"

if not exist "%SQL_FILE%" (
  echo ERROR: SQL file not found: "%SQL_FILE%"
  exit /b 1
)
if not defined PRIVATE_KEY_PASSPHRASE (
  echo ERROR: PRIVATE_KEY_PASSPHRASE is not set in this CMD session.
  exit /b 1
)

snow sql -c audit_ec2 --format CSV -f "%SQL_FILE%" > "%OUT_FILE%" 2> "%ERR_FILE%"
if errorlevel 1 (
  echo ERROR: Snowflake SQL failed. Check "%ERR_FILE%"
  del "%OUT_FILE%" >nul 2>&1
  exit /b 1
)
echo SUCCESS: "%OUT_FILE%"
exit /b 0

c:\>type "SnowflakeTableInventory.sql"
-- Inventory of accessible base tables in SNOWTESTDB.TEST_SCHEMA.
-- SCAN_KB is scan-accessible bytes, NOT billed storage size.
SELECT
    TABLE_NAME AS "Name",
    ROW_COUNT AS "Rows",
    CAST(NULL AS NUMBER(38,2)) AS "Reserved_KB",
    ROUND(BYTES / 1024.0, 2) AS "Scan_KB",
    CAST(NULL AS NUMBER(38,2)) AS "Data_KB",
    CAST(NULL AS NUMBER(38,2)) AS "Index_KB",
    CAST(NULL AS NUMBER(38,2)) AS "Unused_KB"
FROM SNOWTESTDB.INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'TEST_SCHEMA'
  AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME;

c:\>

単発実行

C:\Users\Administrator>snow sql -c audit_ec2 --format CSV -f "C:\SnowflakeTableInventory.sql"
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)
Name,Rows,Reserved_KB,Scan_KB,Data_KB,Index_KB,Unused_KB
JDBC_TEST_TABLE,0,,0.00,,,
TEST_TABLE,6,,1.00,,,
TEST_TABLE2,3,,1.00,,,
TEST_TABLE_BK,6,,1.00,,,

データベース確認バッチ

c:\>type "ExportSnowflakeDatabaseStorage.bat"
@echo off
setlocal
set "SQL_FILE=C:\snowflake-jdbc\SnowflakeDatabaseStorage.sql"
set "CSV_FILE=C:\snowflake-jdbc\SnowflakeDatabaseStorage.csv"
set "ERROR_FILE=C:\snowflake-jdbc\SnowflakeDatabaseStorage_error.log"

if not exist "%SQL_FILE%" (
  echo ERROR: SQL file not found: %SQL_FILE%
  exit /b 1
)
if not defined PRIVATE_KEY_PASSPHRASE (
  echo ERROR: PRIVATE_KEY_PASSPHRASE is not set in this CMD session.
  exit /b 1
)

snow sql -c audit_ec2 --format CSV -f "%SQL_FILE%" > "%CSV_FILE%" 2> "%ERROR_FILE%"
if errorlevel 1 (
  echo ERROR: Snowflake SQL failed. Check "%ERROR_FILE%".
  exit /b 1
)
echo SUCCESS: "%CSV_FILE%"
exit /b 0

c:\>

SQLファイル

c:\>aws s3 cp s3://snowtestsflake/Object/SnowflakeDatabaseStorage.sql c:\
download: s3://snowtestsflake/Object/SnowflakeDatabaseStorage.sql to .\SnowflakeDatabaseStorage.sql

c:\>type "SnowflakeDatabaseStorage.sql"
-- Latest available daily average storage by database (not real-time).
-- Requires access to SNOWFLAKE.ACCOUNT_USAGE.DATABASE_STORAGE_USAGE_HISTORY.
WITH latest AS (
    SELECT DATABASE_NAME, USAGE_DATE,
           AVERAGE_DATABASE_BYTES, AVERAGE_FAILSAFE_BYTES
    FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASE_STORAGE_USAGE_HISTORY
    WHERE DELETED IS NULL
      AND DATABASE_NAME IS NOT NULL
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY DATABASE_NAME ORDER BY USAGE_DATE DESC
    ) = 1
)
SELECT
    DATABASE_NAME AS "Database_Name",
    TO_CHAR(CURRENT_TIMESTAMP(), 'YYYY/MM/DD') AS "Report_Date",
    TO_CHAR(CURRENT_TIMESTAMP(), 'HH24:MI:SS') AS "Report_Time",
    TO_CHAR(USAGE_DATE, 'YYYY/MM/DD') AS "Usage_Date",
    ROUND(AVERAGE_DATABASE_BYTES / 1048576, 2) AS "Used_MB",
    ROUND(AVERAGE_FAILSAFE_BYTES / 1048576, 2) AS "Failsafe_MB",
    ROUND((AVERAGE_DATABASE_BYTES + AVERAGE_FAILSAFE_BYTES) / 1048576, 2) AS "Total_Reported_MB"
FROM latest
ORDER BY DATABASE_NAME;

実行結果

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)
Database_Name,Report_Date,Report_Time,Usage_Date,Used_MB,Failsafe_MB,Total_Reported_MB
DROPPED_USER$SNOWTESTSFLAKE_1791786576,2026/10/26,15:03:26,2026/10/26,0.0,0.0,0.0
SNOWFLAKE,2026/10/26,15:03:26,2026/10/26,15.1,0.84,15.94
SNOWTESTDB,2026/10/26,15:03:26,2026/10/26,0.0,0.01,0.01
USER$SNOWTESTSFLAKE,2026/10/26,15:03:26,2026/10/26,0.0,0.0,0.0
USER$TESTOBATA2,2026/10/26,15:03:26,2026/10/26,0.0,0.0,0.0
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?