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 内部ステージ→AWS S3への道のり

0
Last updated at Posted at 2026-10-31

前提構築

今回行いたい条件は下記。
①Snowflkeのバックアップとして外部S3に保存したい。
②外部ステージは利用したくない
③AWS EC2に落としたのち、S3に送る。


★AWSのEC2上で作業
まずはBackupSnowflakeDB.sqlを作成

中身は下記を記載

C:\Users\Administrator>type C:\Users\Administrator\Desktop\BackupSnowflakeDB.sql
CALL SNOWTESTDB.BACKUP_ADMIN.BACKUP_DATABASE();

バックアップ用のスキーマ、内部ステージ用のテーブルを作成

★Snowsightにて作成

・バックアップ用のスキーマ作成
CREATE SCHEMA IF NOT EXISTS SNOWTESTDB.BACKUP_ADMIN;

・バックアップ用の内部ステージ用テーブルを上記スキーマに作成
CREATE STAGE IF NOT EXISTS
    SNOWTESTDB.BACKUP_ADMIN.BACKUP_STAGE;

文字コードエラーを省くための処理として環境変数を設定 ★AWS EC2にて設定 
C:\Users\Administrator>setx PYTHONUTF8 1

成功: 指定した値は保存されました。

C:\Users\Administrator>setx SNOWFLAKE_CLI_ENCODING_FILE_IO utf-8

成功: 指定した値は保存されました。

C:\Users\Administrator>setx SNOWFLAKE_CLI_ENCODING_SUBPROCESS utf-8

成功: 指定した値は保存されました。

C:\Users\Administrator>setx SNOWFLAKE_CLI_ENCODING_STDOUT utf-8

成功: 指定した値は保存されました。

プロンプトを開き直して確認 
C:\Users\Administrator>echo %PYTHONUTF8%
1

C:\Users\Administrator>echo %SNOWFLAKE_CLI_ENCODING_FILE_IO%
utf-8

C:\Users\Administrator>echo %SNOWFLAKE_CLI_ENCODING_SUBPROCESS%
utf-8

C:\Users\Administrator>echo %SNOWFLAKE_CLI_ENCODING_STDOUT%
utf-8

エクスプローラで下記を開いて編集

%USERPROFILE%.snowflake\config.toml

下記を追記

[cli.encoding]
file_io = "utf-8"
subprocess = "utf-8"
stdout = "utf-8"

ストアドプロシージャを作成

★Snowsightにて作成
CreateBackupProcedure.sql


CREATE OR REPLACE PROCEDURE SNOWTESTDB.BACKUP_ADMIN.BACKUP_DATABASE()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    v_run_id       VARCHAR;
    v_stage_path   VARCHAR;
    v_sql          VARCHAR;
    v_table_name   VARCHAR;
    v_schema_hex   VARCHAR;
    v_table_hex    VARCHAR;
    v_table_count  NUMBER DEFAULT 0;

    c_tables CURSOR FOR
        SELECT TABLE_SCHEMA, TABLE_NAME
        FROM SNOWTESTDB.INFORMATION_SCHEMA.TABLES
        WHERE TABLE_CATALOG = 'SNOWTESTDB'
          AND TABLE_TYPE = 'BASE TABLE'
          AND TABLE_SCHEMA <> 'INFORMATION_SCHEMA'
        ORDER BY TABLE_SCHEMA, TABLE_NAME;

BEGIN
    -- Create a unique backup directory.
    v_run_id := TO_VARCHAR(
        CURRENT_TIMESTAMP(),
        'YYYYMMDD_HH24MISSFF3'
    ) || '_' || REPLACE(UUID_STRING(), '-', '');

    v_stage_path :=
        '@SNOWTESTDB.BACKUP_ADMIN.BACKUP_STAGE/run_' ||
        v_run_id || '/';

    -- 1. Database DDL, including supported child objects.
    -- Base64 prevents multiline DDL from being damaged by CSV export.
    v_sql :=
        'COPY INTO ' || v_stage_path || 'DDL/database_ddl ' ||
        'FROM (SELECT BASE64_ENCODE(' ||
        'TO_BINARY(GET_DDL(''DATABASE'', ''SNOWTESTDB'', TRUE), ''UTF-8'')' ||
        ')) ' ||
        'FILE_FORMAT = (' ||
        'TYPE = CSV COMPRESSION = NONE ' ||
        'FIELD_OPTIONALLY_ENCLOSED_BY = NONE) ' ||
        'SINGLE = TRUE OVERWRITE = FALSE';

    EXECUTE IMMEDIATE :v_sql;

    -- 2. Inventory of every base table, including empty tables.
    v_sql :=
        'COPY INTO ' || v_stage_path || 'METADATA/tables ' ||
        'FROM (' ||
        'SELECT TABLE_SCHEMA, TABLE_NAME, ROW_COUNT ' ||
        'FROM SNOWTESTDB.INFORMATION_SCHEMA.TABLES ' ||
        'WHERE TABLE_CATALOG = ''SNOWTESTDB'' ' ||
        'AND TABLE_TYPE = ''BASE TABLE'' ' ||
        'AND TABLE_SCHEMA <> ''INFORMATION_SCHEMA'' ' ||
        'ORDER BY TABLE_SCHEMA, TABLE_NAME' ||
        ') ' ||
        'FILE_FORMAT = (' ||
        'TYPE = CSV COMPRESSION = NONE ' ||
        'FIELD_OPTIONALLY_ENCLOSED_BY = ''"'') ' ||
        'HEADER = TRUE SINGLE = TRUE OVERWRITE = FALSE';

    EXECUTE IMMEDIATE :v_sql;

    -- 3. Export all base tables.
    -- An empty table may produce no DATA file.
    -- Its definition and inventory entry are still backed up.
    FOR rec IN c_tables DO
        v_table_count := v_table_count + 1;

        v_schema_hex := HEX_ENCODE(rec.TABLE_SCHEMA);
        v_table_hex  := HEX_ENCODE(rec.TABLE_NAME);

        v_table_name :=
            '"SNOWTESTDB"."' ||
            REPLACE(rec.TABLE_SCHEMA, '"', '""') ||
            '"."' ||
            REPLACE(rec.TABLE_NAME, '"', '""') ||
            '"';

        v_sql :=
            'COPY INTO ' || v_stage_path ||
            'DATA/schema_' || v_schema_hex ||
            '/table_' || v_table_hex || '/ ' ||
            'FROM ' || v_table_name || ' ' ||
            'FILE_FORMAT = (' ||
            'TYPE = CSV ' ||
            'COMPRESSION = GZIP ' ||
            'FIELD_OPTIONALLY_ENCLOSED_BY = ''"'') ' ||
            'HEADER = TRUE ' ||
            'INCLUDE_QUERY_ID = TRUE';

        EXECUTE IMMEDIATE :v_sql;
    END FOR;

    RETURN
        'SUCCESS | RUN_ID=' || v_run_id ||
        ' | TABLE_COUNT=' || v_table_count ||
        ' | STAGE=' || v_stage_path;

END;
$$;

環境変数を設定

※%SqlFile% には今回作成したBackupSnowflakeDB.sqlの絶対パスを指定
★AWS EC2で設定

set PRIVATE_KEY_PASSPHRASE=XXXX
set SqlFile=C:\Users\Administrator\Desktop\BackupSnowflakeDB.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

権限の付与

★Snowsightからの実行

GRANT USAGE ON DATABASE SNOWTESTDB
TO ROLE ROLE_SNOWFLAKE_JDBC;

GRANT USAGE ON SCHEMA SNOWTESTDB.BACKUP_ADMIN
TO ROLE ROLE_SNOWFLAKE_JDBC;

GRANT READ ON STAGE SNOWTESTDB.BACKUP_ADMIN.BACKUP_STAGE
TO ROLE ROLE_SNOWFLAKE_JDBC;

GRANT WRITE ON STAGE SNOWTESTDB.BACKUP_ADMIN.BACKUP_STAGE
TO ROLE ROLE_SNOWFLAKE_JDBC;

GRANT USAGE ON PROCEDURE
SNOWTESTDB.BACKUP_ADMIN.BACKUP_DATABASE()
TO ROLE ROLE_SNOWFLAKE_JDBC;

対象のDBに対するすべての権限を今回利用するシステムユーザに付与されているロールにアタッチ

-- データベース
GRANT ALL PRIVILEGES ON DATABASE SNOWTESTDB
TO ROLE ROLE_SNOWFLAKE_JDBC;

-- 全スキーマ
GRANT ALL PRIVILEGES ON ALL SCHEMAS IN DATABASE SNOWTESTDB
TO ROLE ROLE_SNOWFLAKE_JDBC;

-- 全テーブル
GRANT ALL PRIVILEGES ON ALL TABLES IN DATABASE SNOWTESTDB
TO ROLE ROLE_SNOWFLAKE_JDBC;

-- 全ビュー
GRANT ALL PRIVILEGES ON ALL VIEWS IN DATABASE SNOWTESTDB
TO ROLE ROLE_SNOWFLAKE_JDBC;

-- 全ステージ
GRANT ALL PRIVILEGES ON ALL STAGES IN DATABASE SNOWTESTDB
TO ROLE ROLE_SNOWFLAKE_JDBC;

-- 全ファイルフォーマット
GRANT ALL PRIVILEGES ON ALL FILE FORMATS IN DATABASE SNOWTESTDB
TO ROLE ROLE_SNOWFLAKE_JDBC;

-- 全シーケンス
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN DATABASE SNOWTESTDB
TO ROLE ROLE_SNOWFLAKE_JDBC;

-- 全ストリーム
GRANT ALL PRIVILEGES ON ALL STREAMS IN DATABASE SNOWTESTDB
TO ROLE ROLE_SNOWFLAKE_JDBC;

-- 全タスク
GRANT ALL PRIVILEGES ON ALL TASKS IN DATABASE SNOWTESTDB
TO ROLE ROLE_SNOWFLAKE_JDBC;

実行

★AWS EC2での実行

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:149: UserWarning: Console is not configured for UTF-8 output. If you're using cmd.exe or Git Bash, run 'chcp.com 65001' to switch the console to the UTF-8 codepage. If you're using PowerShell 5.x, see: https://docs.snowflake.com/en/developer-guide/snowflake-cli/connecting/configure-cli#additional-step-for-windows-powershell-5x
  warnings.warn(_CONSOLE_ENCODING_WARNING)
BACKUP_DATABASE
SUCCESS | RUN_ID=20261031_204438709_192f9e76bfd14e8a99538f4e4bad80c8 | TABLE_COUNT=4 | STAGE=@SNOWTESTDB.BACKUP_ADMIN.BACKUP_STAGE/run_20261031_204438709_192f9e76bfd14e8a99538f4e4bad80c8/

内部ステージ内の確認

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% -q "LIST @SNOWTESTDB.BACKUP_ADMIN.BACKUP_STAGE;"
C:\Users\Administrator\AppData\Local\pyapp\data\snowflake-cli\12766843218322477748\3.28.0\python\Lib\site-packages\snowflake\cli\api\encoding_diagnostics.py:149: UserWarning: Console is not configured for UTF-8 output. If you're using cmd.exe or Git Bash, run 'chcp.com 65001' to switch the console to the UTF-8 codepage. If you're using PowerShell 5.x, see: https://docs.snowflake.com/en/developer-guide/snowflake-cli/connecting/configure-cli#additional-step-for-windows-powershell-5x
  warnings.warn(_CONSOLE_ENCODING_WARNING)
LIST @SNOWTESTDB.BACKUP_ADMIN.BACKUP_STAGE;
+----------------------------------------------------------------------------------------------------------------------+
| name                                        | size | md5                              | last_modified                |
|---------------------------------------------+------+----------------------------------+------------------------------|
| backup_stage/run_20261031_204438709_192f9e7 | 144  | efad00a5a9ae692eebf665cc0ebf5f22 | Sun, 1 Nov 2026 03:44:42 GMT |
| 6bfd14e8a99538f4e4bad80c8/DATA/schema_54455 |      |                                  |                              |
| 3545F534348454D41/table_544553545F5441424C4 |      |                                  |                              |
| 5/data_01c81ca0-0105-24a3-0005-7192001cf8ca |      |                                  |                              |
| _0_0_0.csv.gz                               |      |                                  |                              |
| backup_stage/run_20261031_204438709_192f9e7 | 80   | cf704ebbbf19a5fb4f3686bd69efef3f | Sun, 1 Nov 2026 03:44:42 GMT |
| 6bfd14e8a99538f4e4bad80c8/DATA/schema_54455 |      |                                  |                              |
| 3545F534348454D41/table_544553545F5441424C4 |      |                                  |                              |
| 532/data_01c81ca0-0105-24a3-0005-7192001cf8 |      |                                  |                              |
| ce_0_0_0.csv.gz                             |      |                                  |                              |
| backup_stage/run_20261031_204438709_192f9e7 | 112  | 15f0ab192c956d4651e94da800aa4a2e | Sun, 1 Nov 2026 03:44:42 GMT |
| 6bfd14e8a99538f4e4bad80c8/DATA/schema_54455 |      |                                  |                              |
| 3545F534348454D41/table_544553545F5441424C4 |      |                                  |                              |
| 55F424B/data_01c81ca0-0105-24a3-0005-719200 |      |                                  |                              |
| 1cf8d2_0_0_0.csv.gz                         |      |                                  |                              |
| backup_stage/run_20261031_204438709_192f9e7 | 6320 | 1e18d6f0bd1bd162a6ea9e334793d7a4 | Sun, 1 Nov 2026 03:44:40 GMT |
| 6bfd14e8a99538f4e4bad80c8/DDL/database_ddl  |      |                                  |                              |
| backup_stage/run_20261031_204438709_192f9e7 | 176  | e35c68183c488ccee28d3be7c57d8796 | Sun, 1 Nov 2026 03:44:41 GMT |
| 6bfd14e8a99538f4e4bad80c8/METADATA/tables   |      |                                  |                              |
+----------------------------------------------------------------------------------------------------------------------+

♯ 今回のバックアップIDに絞って確認




C:\Users\Administrator>

現在のテーブルとバックアップしたものの比較をするためのsqlファイル作成

C:\Users\Administrator\Desktop\CheckBackup.sql

USE SCHEMA SNOWTESTDB.BACKUP_ADMIN;
-- Backup metadata versus current tables


CREATE OR REPLACE TEMP FILE FORMAT BACKUP_CHECK_CSV_FORMAT
    TYPE = CSV
    SKIP_HEADER = 1
    FIELD_OPTIONALLY_ENCLOSED_BY = '"';

WITH backup_tables AS (
    SELECT
        t.$1::VARCHAR AS TABLE_SCHEMA,
        t.$2::VARCHAR AS TABLE_NAME,
        TRY_TO_NUMBER(t.$3) AS BACKUP_ROW_COUNT
    FROM @SNOWTESTDB.BACKUP_ADMIN.BACKUP_STAGE/run_20261031_204438709_192f9e76bfd14e8a99538f4e4bad80c8/METADATA/tables
        (FILE_FORMAT => BACKUP_CHECK_CSV_FORMAT) t
),
current_tables AS (
    SELECT
        TABLE_SCHEMA,
        TABLE_NAME,
        ROW_COUNT AS CURRENT_ROW_COUNT
    FROM SNOWTESTDB.INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'
      AND TABLE_SCHEMA <> 'INFORMATION_SCHEMA'
)
SELECT
    COALESCE(b.TABLE_SCHEMA, c.TABLE_SCHEMA) AS TABLE_SCHEMA,
    COALESCE(b.TABLE_NAME, c.TABLE_NAME) AS TABLE_NAME,
    b.BACKUP_ROW_COUNT,
    c.CURRENT_ROW_COUNT,
    CASE
        WHEN b.TABLE_NAME IS NULL THEN 'BACKUP_MISSING'
        WHEN c.TABLE_NAME IS NULL THEN 'CURRENT_MISSING'
        WHEN b.BACKUP_ROW_COUNT IS NULL
          OR c.CURRENT_ROW_COUNT IS NULL THEN 'UNKNOWN'
        WHEN b.BACKUP_ROW_COUNT = c.CURRENT_ROW_COUNT THEN 'OK'
        ELSE 'DIFFERENT'
    END AS CHECK_RESULT
FROM backup_tables b
FULL OUTER JOIN current_tables c
    ON b.TABLE_SCHEMA = c.TABLE_SCHEMA
   AND b.TABLE_NAME = c.TABLE_NAME
ORDER BY TABLE_SCHEMA, TABLE_NAME;

確認

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 -f "C:\Users\Administrator\Desktop\CheckBackup.sql"
C:\Users\Administrator\AppData\Local\pyapp\data\snowflake-cli\12766843218322477748\3.28.0\python\Lib\site-packages\snowflake\cli\api\encoding_diagnostics.py:149: UserWarning: Console is not configured for UTF-8 output. If you're using cmd.exe or Git Bash, run 'chcp.com 65001' to switch the console to the UTF-8 codepage. If you're using PowerShell 5.x, see: https://docs.snowflake.com/en/developer-guide/snowflake-cli/connecting/configure-cli#additional-step-for-windows-powershell-5x
  warnings.warn(_CONSOLE_ENCODING_WARNING)
[
  [
    {
      "status"  :   "Statement executed successfully."
    }
  ],
  [
    {
      "status"  :   "File format BACKUP_CHECK_CSV_FORMAT successfully created."
    }
  ],
  [
    {
      "TABLE_SCHEMA"  :   "TEST_SCHEMA"  ,
      "TABLE_NAME"  :   "JDBC_TEST_TABLE"  ,
      "BACKUP_ROW_COUNT"  :   0  ,
      "CURRENT_ROW_COUNT"  :   0  ,
      "CHECK_RESULT"  :   "OK"
    }  ,
    {
      "TABLE_SCHEMA"  :   "TEST_SCHEMA"  ,
      "TABLE_NAME"  :   "TEST_TABLE"  ,
      "BACKUP_ROW_COUNT"  :   11  ,
      "CURRENT_ROW_COUNT"  :   11  ,
      "CHECK_RESULT"  :   "OK"
    }  ,
    {
      "TABLE_SCHEMA"  :   "TEST_SCHEMA"  ,
      "TABLE_NAME"  :   "TEST_TABLE2"  ,
      "BACKUP_ROW_COUNT"  :   3  ,
      "CURRENT_ROW_COUNT"  :   3  ,
      "CHECK_RESULT"  :   "OK"
    }  ,
    {
      "TABLE_SCHEMA"  :   "TEST_SCHEMA"  ,
      "TABLE_NAME"  :   "TEST_TABLE_BK"  ,
      "BACKUP_ROW_COUNT"  :   6  ,
      "CURRENT_ROW_COUNT"  :   6  ,
      "CHECK_RESULT"  :   "OK"
    }
  ]
]

# DDLの内容を確認用SQLを用意 C:\Users\Administrator\Desktop\CheckBackupContents.sql 
-- CheckBackupContents.sql
-- Backup run: 20261031_204438709_192f9e76bfd14e8a99538f4e4bad80c8
-- Read-only verification, apart from session-local temporary file formats.

USE SCHEMA SNOWTESTDB.BACKUP_ADMIN;

CREATE OR REPLACE TEMP FILE FORMAT BACKUP_VERIFY_DATA_CSV
  TYPE = CSV
  COMPRESSION = AUTO
  SKIP_HEADER = 1
  FIELD_OPTIONALLY_ENCLOSED_BY = '"';

CREATE OR REPLACE TEMP FILE FORMAT BACKUP_VERIFY_INVENTORY_CSV
  TYPE = CSV
  COMPRESSION = NONE
  SKIP_HEADER = 1
  FIELD_OPTIONALLY_ENCLOSED_BY = '"';

CREATE OR REPLACE TEMP FILE FORMAT BACKUP_VERIFY_DDL_CSV
  TYPE = CSV
  COMPRESSION = NONE
  FIELD_OPTIONALLY_ENCLOSED_BY = NONE;

-- 1. Count actual CSV data rows for each table, including zero-row tables.
--    Counts are based on the backup files, not just INFORMATION_SCHEMA metadata.
WITH inventory AS (
  SELECT
    t.$1::VARCHAR AS TABLE_SCHEMA,
    t.$2::VARCHAR AS TABLE_NAME,
    TRY_TO_NUMBER(t.$3) AS RECORDED_ROW_COUNT
  FROM @SNOWTESTDB.BACKUP_ADMIN.BACKUP_STAGE/run_20261031_204438709_192f9e76bfd14e8a99538f4e4bad80c8/METADATA/tables
    (FILE_FORMAT => BACKUP_VERIFY_INVENTORY_CSV) t
),
actual_data AS (
  SELECT
    HEX_DECODE_STRING(REGEXP_SUBSTR(METADATA$FILENAME, 'schema_([0-9A-F]+)/', 1, 1, 'e', 1)) AS TABLE_SCHEMA,
    HEX_DECODE_STRING(REGEXP_SUBSTR(METADATA$FILENAME, 'table_([0-9A-F]+)/', 1, 1, 'e', 1)) AS TABLE_NAME,
    COUNT(*) AS CSV_ROW_COUNT,
    COUNT(DISTINCT METADATA$FILENAME) AS DATA_FILE_COUNT
  FROM @SNOWTESTDB.BACKUP_ADMIN.BACKUP_STAGE/run_20261031_204438709_192f9e76bfd14e8a99538f4e4bad80c8/DATA/
    (FILE_FORMAT => BACKUP_VERIFY_DATA_CSV)
  GROUP BY 1, 2
)
SELECT
  i.TABLE_SCHEMA,
  i.TABLE_NAME,
  i.RECORDED_ROW_COUNT,
  COALESCE(a.CSV_ROW_COUNT, 0) AS CSV_ROW_COUNT,
  COALESCE(a.DATA_FILE_COUNT, 0) AS DATA_FILE_COUNT,
  CASE
    WHEN i.RECORDED_ROW_COUNT IS NULL THEN 'UNKNOWN'
    WHEN i.RECORDED_ROW_COUNT = COALESCE(a.CSV_ROW_COUNT, 0) THEN 'OK'
    ELSE 'DIFFERENT'
  END AS CHECK_RESULT
FROM inventory i
LEFT JOIN actual_data a
  ON i.TABLE_SCHEMA = a.TABLE_SCHEMA AND i.TABLE_NAME = a.TABLE_NAME
ORDER BY i.TABLE_SCHEMA, i.TABLE_NAME;

-- 2. Check whether the saved database DDL is decodable and includes expected objects.
WITH ddl AS (
  SELECT BASE64_DECODE_STRING(t.$1::VARCHAR) AS DDL_TEXT
  FROM @SNOWTESTDB.BACKUP_ADMIN.BACKUP_STAGE/run_20261031_204438709_192f9e76bfd14e8a99538f4e4bad80c8/DDL/database_ddl
    (FILE_FORMAT => BACKUP_VERIFY_DDL_CSV) t
)
SELECT
  LENGTH(DDL_TEXT) AS DDL_CHARACTER_COUNT,
  IFF(DDL_TEXT ILIKE '%JDBC_TEST_TABLE%', 'FOUND', 'NOT_FOUND') AS EMPTY_TABLE_NAME,
  IFF(DDL_TEXT ILIKE '%TEST_TABLE2%', 'FOUND', 'NOT_FOUND') AS TEST_TABLE2_NAME,
  IFF(DDL_TEXT ILIKE '%CREATE%TABLE%', 'FOUND', 'NOT_FOUND') AS TABLE_DDL_KEYWORD
FROM ddl;

確認 
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 -f "C:\Users\Administrator\Desktop\CheckBackupContents.sql"
C:\Users\Administrator\AppData\Local\pyapp\data\snowflake-cli\12766843218322477748\3.28.0\python\Lib\site-packages\snowflake\cli\api\encoding_diagnostics.py:149: UserWarning: Console is not configured for UTF-8 output. If you're using cmd.exe or Git Bash, run 'chcp.com 65001' to switch the console to the UTF-8 codepage. If you're using PowerShell 5.x, see: https://docs.snowflake.com/en/developer-guide/snowflake-cli/connecting/configure-cli#additional-step-for-windows-powershell-5x
  warnings.warn(_CONSOLE_ENCODING_WARNING)
[
  [
    {
      "status"  :   "Statement executed successfully."
    }
  ],
  [
    {
      "status"  :   "File format BACKUP_VERIFY_DATA_CSV successfully created."
    }
  ],
  [
    {
      "status"  :   "File format BACKUP_VERIFY_INVENTORY_CSV successfully created."
    }
  ],
  [
    {
      "status"  :   "File format BACKUP_VERIFY_DDL_CSV successfully created."
    }
  ],
  [
    {
      "TABLE_SCHEMA"  :   "TEST_SCHEMA"  ,
      "TABLE_NAME"  :   "JDBC_TEST_TABLE"  ,
      "RECORDED_ROW_COUNT"  :   0  ,
      "CSV_ROW_COUNT"  :   0  ,
      "DATA_FILE_COUNT"  :   0  ,
      "CHECK_RESULT"  :   "OK"
    }  ,
    {
      "TABLE_SCHEMA"  :   "TEST_SCHEMA"  ,
      "TABLE_NAME"  :   "TEST_TABLE"  ,
      "RECORDED_ROW_COUNT"  :   11  ,
      "CSV_ROW_COUNT"  :   11  ,
      "DATA_FILE_COUNT"  :   1  ,
      "CHECK_RESULT"  :   "OK"
    }  ,
    {
      "TABLE_SCHEMA"  :   "TEST_SCHEMA"  ,
      "TABLE_NAME"  :   "TEST_TABLE2"  ,
      "RECORDED_ROW_COUNT"  :   3  ,
      "CSV_ROW_COUNT"  :   3  ,
      "DATA_FILE_COUNT"  :   1  ,
      "CHECK_RESULT"  :   "OK"
    }  ,
    {
      "TABLE_SCHEMA"  :   "TEST_SCHEMA"  ,
      "TABLE_NAME"  :   "TEST_TABLE_BK"  ,
      "RECORDED_ROW_COUNT"  :   6  ,
      "CSV_ROW_COUNT"  :   6  ,
      "DATA_FILE_COUNT"  :   1  ,
      "CHECK_RESULT"  :   "OK"
    }
  ],
  [
    {
      "DDL_CHARACTER_COUNT"  :   4668  ,
      "EMPTY_TABLE_NAME"  :   "FOUND"  ,
      "TEST_TABLE2_NAME"  :   "FOUND"  ,
      "TABLE_DDL_KEYWORD"  :   "FOUND"
    }
  ]
]


C:\Users\Administrator>

持ってくる

フォルダ作成
mkdir C:\SnowflakeBackup

実行












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?