使用仓库

所有仓库任务都可通过 Snowflake Web 界面或使用仓库 DDL 命令执行。

创建仓库

You can create a warehouse using the Snowsight or SQL:

Snowsight:
In the navigation menu, select Compute » Warehouses » Warehouse
SQL:
Execute a CREATE WAREHOUSE command.
Python:
Use the WarehouseCollection.create API (Creating a warehouse).

创建仓库时，可以指定仓库最初是在“已启动”（即运行）还是“已暂停”状态下创建。如果选择“已启动”，则为仓库配置好全部计算资源后，仓库就会开始消耗 Credit。

Note

如果您选择在“已启动”状态下创建仓库，则仓库可能需要一些时间才能完全可用，因为 Snowflake 需为仓库配置全部计算资源。

启动或恢复仓库

仓库可以随时启动，包括初始创建时。仓库创建后，恢复仓库的流程与启动仓库相同。

您可以使用以下界面恢复暂停（即非活动）仓库：

Snowsight:
In the navigation menu, select Compute » Warehouses » <suspended_warehouse_name> » » Resume
SQL:
Execute an ALTER WAREHOUSE command with the RESUME keyword.
Python:
Use the WarehouseResource.resume API (Performing warehouse operations).

启动仓库通常仅需几秒钟；但极少数情况下，可能需要更长的时间，因为 Snowflake 需为仓库配置计算资源。

仓库运行时会消耗 Credit：

为仓库配置全部计算资源后，仓库就会开始消耗 Credit。
- In a rare instance when some of the compute resources fail to provision, the warehouse only consumes credits for the provisioned compute resources.
- Once the remaining compute resources are successfully provisioned, the warehouse starts consuming credits for all requested compute resources.
While starting or resuming a warehouse often takes only a few seconds, in some instances, it can take longer as Snowflake provisions the compute resources for the warehouse.
Snowflake does not begin executing SQL statements submitted to a warehouse until all of the compute resources for the warehouse are successfully provisioned, unless any of the resources fail to provision:
- 如果仓库的任何计算资源在启动期间无法配置，Snowflake 会尝试修复失败的资源。
- During the repair process, the warehouse starts processing SQL statements once 50% or more of the requested compute resources are successfully provisioned.

在仓库运行期间，Credit 按每秒计费，每次恢复仓库时最少计费 1 分钟；然而，Credit 消耗以 60 分钟（即每小时）为单位报告。

Note

A warehouse must be running and the current warehouse for the session (i.e. in use) to process SQL statements submitted in the session. For more information, refer to Using a Warehouse in this topic.

暂停仓库

正在运行的仓库可以随时暂停，甚至在执行 SQL 语句时也是如此。关闭全部计算资源后，暂停仓库就会停止消耗 Credit。

您可以使用以下界面暂停仓库：

Snowsight:
In the navigation menu, select Compute » Warehouses » <started_warehouse_name> » » Suspend
SQL:
Execute an ALTER WAREHOUSE command with the SUSPEND keyword.
Python:
Use the WarehouseResource.suspend API (Performing warehouse operations).

当您暂停仓库时，Snowflake 会立即关闭该仓库的全部空闲计算资源，但允许任何正在执行语句的计算资源继续执行，直到语句完成，此时资源将被关闭，仓库的状态更改为“暂停”。待关闭的计算资源被视为处于“静止”模式。

调整仓库大小

仓库的大小可以随时调整，包括正在运行和处理语句时。

您可以使用以下界面调整仓库大小：

Snowsight:
In the navigation menu, select Compute » Warehouses » <warehouse_name> » » Edit
SQL:
Execute an ALTER WAREHOUSE command with SET WAREHOUSE_SIZE = ....
Python:
Use the WarehouseResource.create_or_alter API (Creating or altering a warehouse).

当仓库执行的操作将受益于更多计算资源时，将仓库调整为更大的大小非常有用，包括：

提高针对大型数据集的大型复杂查询的性能。
提高加载和卸载大量数据时的性能。

调整正在运行的仓库大小的影响

Resizing a running warehouse adds or removes compute resources in each cluster in the warehouse. All the usage and credit rules associated with starting or suspending a warehouse apply to resizing a started warehouse, such as:

Compute resources added to a warehouse start using credits when they are provisioned; however, the additional compute resources don’t start executing statements until they are all provisioned, unless some of the resources fail to provision.
仅当计算资源不再用于执行任何当前语句时，仓库中才会移除它们。

调整仓库大小不会对仓库当前正在执行的语句产生任何影响。当调整到更大的大小时，新的计算资源在完全配置后，仅用于执行仓库队列中已有的语句以及提交到仓库的所有未来语句。

Tip

To verify the additional compute resources for your warehouse have been fully provisioned, add the WAIT_FOR_COMPLETION parameter to the ALTER WAREHOUSE command. You can also use SHOW WAREHOUSES to check its state.

调整暂停仓库大小的影响

调整暂停仓库的大小不会为仓库配置任何新的计算资源。它只需指示 Snowflake 在下次恢复仓库时配置额外的计算资源，此时与启动仓库相关的所有使用和 Credit 规则都适用。

使用仓库

要在 Snowflake 中执行查询或 DML 语句，仓库必须正在运行，并且必须将其指定为提交查询/语句的会话的当前仓库。

A Snowflake session can only have one current warehouse at a time. The current warehouse for the session can be specified or changed at any time through the USE WAREHOUSE SQL command or the WarehouseResource.use_warehouse Python API.

Once a running warehouse has been set as the current warehouse for the session, queries and DML statements submitted within the session are processed by the warehouse. In the Query History and Workspaces pages in Snowsight, you can view the warehouse used to process each query/statement.

Note

Some Snowsight features require a warehouse to run SQL queries for retrieving data, such as Task Run History or Data Preview for a table. An X-Small warehouse is recommended and generally sufficient for most of these queries. For information, see Warehouse considerations.

委派仓库管理

默认情况下， ACCOUNTADMIN 角色被授予对账户中的所有仓库进行更改、暂停、描述和执行其他操作的能力。

如果您需要将这些能力委派给账户中的自定义角色，您可以授予该角色 MANAGE WAREHOUSES 权限。授予 MANAGE WAREHOUSES 权限相当于授予账户中所有仓库的 MODIFY、 MONITOR 和 OPERATE 权限。

The following examples demonstrate how you can delegate the ability to manage warehouses to a custom role named manage_wh_role. The example uses the manage_wh_role to make changes to the warehouse test_wh, which is owned by a different role (create_wh_role).

创建一个新角色，该角色将创建并拥有一个新仓库，并授予该角色 CREATE WAREHOUSE 权限：

Using the GRANT <privileges> … TO ROLE command:

CREATE ROLE create_wh_role;
GRANT CREATE WAREHOUSE ON ACCOUNT TO ROLE create_wh_role;
GRANT ROLE create_wh_role TO ROLE SYSADMIN;

Using the RoleResource.grant_privileges API:

from snowflake.core.role import Role, Securable

my_role = Role(name="create_wh_role")
my_role_res = root.roles.create(my_role)

my_role_res.grant_privileges(
  privileges=["CREATE WAREHOUSE"], securable_type="ACCOUNT"
)

root.roles['SYSADMIN'].grant_role(role_type="ROLE", role=Securable(name='create_wh_role'))

创建第二个角色来管理账户中的所有仓库，并授予该角色 MANAGE WAREHOUSES 权限：

CREATE ROLE manage_wh_role;
GRANT MANAGE WAREHOUSES ON ACCOUNT TO ROLE manage_wh_role;
GRANT ROLE manage_wh_role TO ROLE SYSADMIN;

from snowflake.core.role import Role, Securable

my_role = Role(name="manage_wh_role")
my_role_res = root.roles.create(my_role)

my_role_res.grant_privileges(
  privileges=["MANAGE WAREHOUSES"], securable_type="ACCOUNT"
)

root.roles['SYSADMIN'].grant_role(role_type="ROLE", role=Securable(name='manage_wh_role'))

Using the create_wh_role role, create a new warehouse:

USE ROLE create_wh_role;
CREATE OR REPLACE WAREHOUSE test_wh
    WITH WAREHOUSE_SIZE= XSMALL;

from snowflake.core import CreateMode
from snowflake.core.warehouse import Warehouse

root.session.use_role("create_wh_role")

my_wh = Warehouse(
  name="test_wh",
  warehouse_size="XSMALL"
)
root.warehouses.create(my_wh, mode=CreateMode.or_replace)

Change the current role to manage_wh_role:

USE ROLE manage_wh_role;

root.session.use_role("manage_wh_role")

Although the manage_wh_role does not own the test_wh, that role does have the MANAGE WAREHOUSES privilege, which means that you can:

暂停和恢复仓库：

ALTER WAREHOUSE test_wh SUSPEND;
ALTER WAREHOUSE test_wh RESUME;

my_wh_res = root.warehouses["test_wh"]
my_wh_res.suspend()
my_wh_res.resume()

更改仓库大小：

ALTER WAREHOUSE test_wh SET WAREHOUSE_SIZE = SMALL;

my_wh = root.warehouses["test_wh"].fetch()
my_wh.warehouse_size = "SMALL"
root.warehouses["test_wh"].create_or_alter(my_wh)

描述仓库：
DESC WAREHOUSE test_wh;
my_wh = root.warehouses["test_wh"].fetch() print(my_wh.to_dict())

Review Warehouse Details in Snowsight¶

You must use the ACCOUNTADMIN role, or a role granted the relevant warehouse privileges.

To review warehouses and manage warehouse details in Snowsight, complete the following steps:

Sign in to Snowsight.
In the navigation menu, select Compute » Warehouses.

然后，您可以查看仓库表、搜索仓库，或者按状态或大小筛选仓库列表。

默认情况下，您可以查看每个仓库的以下信息：

Name
Status, such as Started, Resuming, or Suspended.
Size
Clusters, indicated by a bar in the column. You can hover over the value to see how many clusters are active.
Running, for details on how many SQL statements are being executed by the warehouse.
Queued, for details on how many SQL statements are queued for the warehouse.
Owner, or the owning role for the warehouse.
Resumed, to see how long ago the warehouse was resumed. Hover over the value to see the exact date and timestamp in your local time zone.

您还可以添加列，以查看表中每个仓库的其他详细信息：

QAS (Scale Factor), to see the scale factor of the warehouse used by Query Acceleration Service (QAS). See Using the Query Acceleration Service (QAS).
Scaling Policy, to see the scaling policy defined for the warehouse. See Setting the scaling policy for a multi-cluster warehouse.
Auto Resume, to see whether auto-resume is set up for the warehouse.
Auto Suspend, to see the time period before auto-suspend occurs for the warehouse.
Created, to see when the warehouse was created. Hover over the value to see the exact date and timestamp in your local time zone.

When you select a warehouse in the Warehouses table, you can see more details:

The Warehouse Activity section provides a graph of warehouse load over a period of time, which can help you understand why a query might be running slowly. See Monitoring warehouse load for more details.
The Details section provides additional information about your warehouse, including:
- 仓库的状态。
- 仓库的大小。
- 仓库可以使用的最大和最小群集数。
- 扩展策略。
- 正在运行和排队的任务数量。
- 仓库自动暂停之前的无活动期。
- 如果仓库暂停，是否在需要时自动恢复仓库。
- 仓库最后一次恢复操作。
You can use the Privileges section to view, grant, and revoke privileges on the warehouse.