数据仓库平台Snowflake的全面管理指南-51CTO.COM

译者 | 陈峻

审校 | 重楼

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