數據倉庫平臺Snowflake的全面管理指南

譯文精選

作者：陳峻 2024-09-13 12:25:43

大數據數據倉庫

Snowflake的管理涉及到各種任務，從用戶和角色管理到數據加載和安全性等方面。

譯者 | 陳峻

審校 | 重樓

Snowflake是一個功能強大且基于云端的數據倉庫平臺。它的可擴展性、靈活性和易用性在業界聞名遐邇。Snowflake的整體架構與管理視圖，如下圖所示：

本著保證其可用性、安全性、以及數據完整性的目的，我們下面將深入討論日常管理Snowflake的各項任務角色。總的說來，如圖展示了與Snowflake相關的角色：

1. 用戶管理

用戶管理是Snowflake管理員的一項關鍵活動。有效的用戶管理對于維護安全性和操作效率都是至關重要的。通常，管理員可以按需創建、修改和刪除用戶。其中：

創建用戶

管理員應使用CREATE USER命令來配置新用戶。例如：

SQL
CREATE USER XXXXX 
 PASSWORD = 'StrongPassword123' 
 DEFAULT_ROLE = 'PUBLIC'
 DEFAULT_WAREHOUSE = 'my_warehouse'
 DEFAULT_NAMESPACE = 'my_database.public'
 MUST_CHANGE_PASSWORD = TRUE;

此處的XXXXXuser是使用指定的密碼和默認角色創建的。
MUST_CHANGE_PASSWORD參數可確保用戶在首次登錄時更改其密碼。

修改用戶

我們可以使用ALTER USER命令來更改用戶的密碼，例如：

SQL
ALTER USER XXXXX 
 SET PASSWORD = 'NewStrongPassword456';

刪除用戶

DROP USER命令可用于從DB中刪除用戶：

SQL
DROP USER john_doe;

2. 角色管理

Snowflake的角色管理有助于將資源分配給用戶群。管理員可以創建、分配和刪除角色，以有效地管理用戶權限。

創建角色

你可使用CREATE ROLE命令創建新的角色：

SQL
CREATE ROLE data_scientist;

分配角色

可向用戶授予某個角色：

SQL
 GRANT ROLE data_scientist TO USER XXXXX;

刪除角色

若要刪除角色，可使用如下命令：

SQL
DROP ROLE data_scientist;

3. 管理倉庫

Snowflake的倉庫是執行查詢的虛擬計算集群。高效的倉庫管理保證了高效的查詢性能和資源利用率。

創建倉庫

創建新倉庫的命令為：

SQL
CREATE WAREHOUSE my_warehouse
 WITH 
 WAREHOUSE_SIZE = 'X-Small'
 AUTO_SUSPEND = 300
 AUTO_RESUME = TRUE;

此命令定義了倉庫的大小、以及自動暫停與恢復的設置。
注意，我們可以在命令級別設置Auto_Suspend參數，而不是從前端設置。

修改倉庫

我們可以使用ALTER WAREHOUSE命令來更改倉庫的大小或自動暫停的時間：

SQL
ALTER WAREHOUSE my_warehouse
 SET WAREHOUSE_SIZE = 'Small'
 SET AUTO_SUSPEND = 600;

擴展虛擬倉庫

若需要根據工作負載來擴縮容，可調整如下大小：

SQL
ALTER WAREHOUSE my_warehouse SET WAREHOUSE_SIZE ='Medium';

使用多集群倉庫

你可通過啟用多集群倉庫，來處理大量并發的查詢：

SQL
ALTER WAREHOUSE my_warehouse SET MAX_CLUSTERS=3;

刪除倉庫

請使用DROP WAREHOUSE命令，來刪除倉庫：

SQL
DROP WAREHOUSE my_warehouse;

4. 數據庫和模式（Schema）管理

Snowflake中的數據庫和模式是以系統、隔離和分層的方式進行組織和管理的，而且具有受控的訪問機制。管理員可以有效地創建、修改和刪除各種對象和結構化數據。

創建數據庫

請使用命令CREATE DATABASE創建新的數據庫：

SQL
CREATE DATABASE my_database;

創建模式

模式可以幫助我們在數據庫中有效地組織對象和數據集。你可以使用CREATE SCHEMA方法創建：

SQL
CREATE SCHEMA my_database.my_schema;

刪除數據庫

請使用命令DROP DATABASE刪除數據庫及其所有對象：

SQL
DROP DATABASE my_database CASCADE;

5. 管理表

表是存儲數據的基本單位。管理員可以按需創建、修改和刪除表。

創建表

請使用CREATE TABLE來建立新的表：

SQL
CREATE TABLE my_database.my_schema.my_table (
 id INT AUTOINCREMENT,
 name STRING,
 created_at TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (id)
);

修改表

添加列或更改表結構可以通過ALTER TABLE命令來完成：

SQL
ALTER TABLE my_database.my_schema.my_table 
 ADD COLUMN email STRING;

刪除表

請使用命令DROP TABLE刪除表：

SQL
DROP TABLE my_database.my_schema.my_table;

6. 數據加載和卸載

加載和卸載數據是Snowflake數據管理中的關鍵任務之一。

加載數據

我們可以通過如下方式將數據從一個stage加載到表中：

SQL
COPY INTO my_database.my_schema.my_table
 FROM @my_stage/my_data_file.csv
 FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY = '"');

卸載數據

若要將數據從表卸載到stage，則可：

SQL
COPY INTO @my_stage/unloaded_data/
 FROM my_database.my_schema.my_table
 FILE_FORMAT = (TYPE = 'CSV');

7.監測和優化

監控系統的性能和優化資源的使用，是維護高效的Snowflake環境的關鍵。

查詢歷史記錄

請通過如下操作來進行歷史查詢：

SQL
SELECT * FROM TABLE(information_schema.query_history())
 WHERE query_text ILIKE '%my_query%';

優化查詢性能

請使用分析查詢來執行計劃并優化查詢。

SQL 
EXPLAIN SELECT * FROM my_table WHERE column = 'value';

使用物化視圖(Materialized Views)

你可通過預計算和存儲結果來加速復雜的查詢。

SQL
CREATE MATERIALIZED VIEW my_view AS
SELECT column1, SUM(column2) FROM my_table GROUP BY column1;

倉庫的使用情況

檢查倉庫的使用情況將有助于我們了解當前的性能：

SQL
SELECT * FROM INFORMATION_SCHEMA.WAREHOUSE_LOAD_HISTORY
 WHERE WAREHOUSE_NAME = 'my_warehouse'
 AND START_TIME > DATEADD(day, -1, CURRENT_TIMESTAMP);

8. 安全和訪問控制

安全性也是Snowflake數據管理和環境控制的一個關鍵方面。Snowflake在管理其各個對象時，需要提供對于數據訪問的精細控制。

授予權限

我們可以通過如下語句授予對數據表的訪問權限：

SQL
GRANT SELECT ON TABLE my_database.my_schema.my_table TO ROLE data_scientist;

撤銷權限

若要撤銷訪問權限，則：

SQL
REVOKE SELECT ON TABLE my_database.my_schema.my_table FROM ROLE data_scientist;

9. 數據共享

作為Snowflake的一項功能，數據共享促進了不同賬戶之間的協作。

創建共享

我們可以通過如下語句來創建共享：

SQL
CREATE SHARE my_share;

將對象添加到共享

若要在共享中包含數據表，請使用如下命令：

SQL
ALTER SHARE my_share ADD TABLE my_database.my_schema.my_table;

授予對共享的訪問權限

如果要允許訪問共享，那么請使用：

SQL
GRANT USAGE ON SHARE my_share TO ROLE consumer_role;

10. 備份和恢復

Snowflake雖然提供自動化的數據保護，但是你也可以按需執行手動備份和還原操作。

創建備份

你可以使用數據庫克隆進行備份：

SQL
CREATE DATABASE my_database_backup CLONE my_database;

從備份中恢復

若要恢復數據，請使用：

SQL
CREATE DATABASE my_restored_database CLONE my_database_backup;

11. 賬戶管理

與賬戶管理相關的管理任務可以確保我們正確地配置和監控Snowflake的各項設置。

查看賬戶信息

我們可以使用如下命令來訪問賬戶參數：

SQL
SHOW PARAMETERS IN ACCOUNT;

配置賬戶參數

請使用如下命令來調整賬戶設置：

SQL
ALTER ACCOUNT SET PARAMETER = 'value';

在Snowflake中，賬戶的各項參數在管理和配置賬戶的行為和功能方面起著至關重要的作用。這些參數會直接影響到Snowflake環境的各項操作。其中，

Auto Resume：決定了倉庫在暫停時所提交的查詢是否應自動恢復。

SQL
SHOW PARAMETERS IN ACCOUNT LIKE 'AUTO_RESUME';

Auto Suspend：可指定倉庫在自動暫停之前的非活動時間量（以秒為單位）

SQL
SHOW PARAMETERS IN ACCOUNT LIKE 'AUTO_SUSPEND';

Default Role：定義分配給新用戶的默認角色

SQL
SHOW PARAMETERS IN ACCOUNT LIKE 'DEFAULT_ROLE';

Encryption：可指定是否為帳戶啟用數據加密

SQL
SHOW PARAMETERS IN ACCOUNT LIKE 'ENCRYPTION';

Fail-Safe：確定是否要啟用故障保護功能，該功能可以提供超出time-travel周期的數據恢復選項

SQL
SHOW PARAMETERS IN ACCOUNT LIKE 'FAILSAFE';

Max Concurrency Level：設置倉庫中可執行的最大并發查詢數

SQL
SHOW PARAMETERS IN ACCOUNT LIKE 'MAX_CONCURRENCY_LEVEL'

Query Tag：允許設置可用于監控和跟蹤查詢性能的默認查詢標簽

SQL
SHOW PARAMETERS IN ACCOUNT LIKE 'QUERY_TAG';

Replication：配置數據庫的復制設置，從而在Snowflake不同區域或賬戶之間啟用數據復制

SQL
SHOW PARAMETERS IN ACCOUNT LIKE 'REPLICATION';

Share：管理與數據共享相關的設置，包括默認共享設置等

SQL
SHOW PARAMETERS IN ACCOUNT LIKE 'SHARE';

Timestamp Output Format：定義時間戳輸出的默認格式

SQL
SHOW PARAMETERS IN ACCOUNT LIKE 'TIMESTAMP_OUTPUT_FORMAT';

Use Catalog：指定用于查詢和操作的默認目錄

SQL
SHOW PARAMETERS IN ACCOUNT LIKE 'USE_CATALOG'

Use Schema：定義用于查詢和操作的默認模式

SQL
SHOW PARAMETERS IN ACCOUNT LIKE 'USE_SCHEMA';

Warehouse Size：設置新創建的倉庫的默認大小

SQL
SHOW PARAMETERS IN ACCOUNT LIKE 'WAREHOUSE_SIZE';

Time Zone：指定帳戶的默認時區

SQL
SHOW PARAMETERS IN ACCOUNT LIKE 'TIMEZONE';

Result Scan Timeout：設置掃描查詢結果的超時時間

SQL
SHOW PARAMETERS IN ACCOUNT LIKE 'RESULT_SCAN_TIMEOUT';

Auto Scale：確定是否為倉庫啟用自動擴展，以根據工作負載調整計算資源

SQL
SHOW PARAMETERS IN ACCOUNT LIKE 'AUTO_SCALE';

SQL
SHOW PARAMETERS IN ACCOUNT LIKE 'LOGIN_HISTORY_RETENTION_DAYS';

Database Restore：配置與數據庫還原操作相關的設置

SQL
SHOW PARAMETERS IN ACCOUNT LIKE 'DATABASE_RESTORE';

12. 存儲管理

雖然Snowflake會自動管理存儲，但是了解和監控存儲使用情況，對于成本管理和優化仍然是至關重要的。

查看存儲使用情況

要檢查數據庫或模式正在使用多少存儲空間，請執行以下操作：

SQL
SELECT * FROM INFORMATION_SCHEMA.STORAGE_USAGE
 WHERE TABLE_SCHEMA = 'my_schema';

管理數據保留

Snowflake提供了時間旅行和故障安全等數據恢復功能，可以有效地管理保留期限。

Time Travel：能夠允許訪問特定保留期的歷史數據

SQL
ALTER TABLE my_table SET DATA_RETENTION_TIME_IN_DAYS=7;

Fail-safe：在時間旅行周期之外提供額外的數據恢復層，但用戶無法配置

刪除不必要的數據

若要有效地管理存儲，請定期刪除舊的或未使用的表和數據庫：

SQL
DROP TABLE my_database.my_schema.old_table;

成本管理

控制管理與Snowflake資源相關的成本，對于預算管理可謂至關重要。

跟蹤成本

Snowflake的成本跟蹤功能可用于監控和分析你的支出，這對于在定義的預算內管理成本同樣十分關鍵。

SQL
SELECT * FROM ACCOUNT_USAGE.COST_HISTORY
WHERE START_TIME > DATEADD(day,-30,CURRRENT_TIMESTAMP);

設置預算警報

針對成本閾值實施警報和通知，可避免產生意外費用。

查看和調整資源使用情況

定期查看資源的使用情況，并調整倉庫大小、數據保留設置和并發的相關設置，以優化成本。

小結

綜上所述，Snowflake的管理會涉及到各種任務，從用戶和角色管理到數據加載和安全性等方面。通過掌握這些任務，管理員可以維護一個安全、高效且組織良好的數據環境。可以說，這些實踐不僅可以確保最佳性能，還可以提高Snowflake環境中數據管理的整體有效性。

譯者介紹

陳峻（Julian Chen），51CTO社區編輯，具有十多年的IT項目實施經驗，善于對內外部資源與風險實施管控，專注傳播網絡與信息安全知識與經驗。

原文標題：Snowflake Administration: A Comprehensive Step-by-Step Guide，作者：Harshavardhan Yedla

責任編輯：華軒來源： 51CTO

數據倉庫 Snowflake