Alter Warehouse
- DarkLight
Alter Warehouse
- DarkLight
Article Summary
Share feedback
Thanks for sharing your feedback!
This article is specific to the following platforms - Snowflake.
Alter Warehouse Component
The Alter Warehouse Component allows users to edit a warehouse from within Matillion ETL for Snowflake. This component makes use of Snowflake's Alter Warehouse functionality.
Properties
Snowflake Properties | ||
|---|---|---|
| Property | Setting | Description |
| Name | Text | A human-readable name for the component. |
| Warehouse | Select | The Snowflake warehouse. The special value, [Environment Default], uses the Snowflake warehouse defined in the Matillion ETL environment setup. For more information, read Virtual Warehouses. |
| Command Type | Select | Select the operation to perform on the Snowflake warehouse. Abort All Queries: aborts all the queries currently running or scheduled on a warehouse. Rename To: rename this warehouse. Resume: choose to bring back a suspended warehouse to a usable state by provisioning compute resources. Set: alters the properties of a warehouse. Suspend: choose to remove all compute nodes from a warehouse. Additionally, puts the warehouse into a SUSPENDED state. Unset: resets the current value of a warehouse property back to its default value. |
| New Name | Text | The new name for the Snowflake warehouse. This property is only available when Command Type is set to Rename To. |
| Properties | Property | Choose one or more properties to change. This property is only available when Command Type is set to Set. The available properties are as follows: MAX_CONCURRENCY_LEVEL: specifies the maximum number of SQL statements (queries, DDL, DML, etc.) a warehouse cluster can execute concurrently. Note: The maximum concurrency is 8 times the number of processors on your cloud instance. For example: An instance with 2 processors has a maximum concurrency of 16. STATEMENT_QUEUED_TIMEOUT_IN_SECONDS: specifies the time, in seconds, an SQL statement (query, DDL, DML, etc.) can be queued on a warehouse before it is canceled by the system. STATEMENT_TIMEOUT_IN_SECONDS: specifies the time, in seconds, after which a running SQL statement (query, DDL, DML, etc.) is canceled by the system. COMMENT: adds a comment or overwrites the existing comment for the warehouse. WAREHOUSE_SIZE: choose a warehouse size. MAX_CLUSTER_COUNT: the number of available clusters (1, 2, 3, 4 or 5). MIN_CLUSTER_COUNT: (optionally) set to a value greater than 1. AUTO_SUSPEND: if auto-suspend is enabled, the warehouse is automatically suspended if the warehouse is inactive for the specified period of time. (TRUE or FALSE) AUTO_RESUME: if auto-resume is enabled, the warehouse is automatically resumed when any statement that requires a warehouse is submitted to the current warehouse for the session. (TRUE or FALSE) RESOURCE_MONITOR: sets the warehouse to use a resource monitor. SCALING_POLICY: specifies the policy for automatically starting and shutting down clusters in a multi-cluster warehouse running in auto-scale mode ("STANDARD" or "ECONOMY"). |
| Value | The value of the corresponding property. | |
| Properties | Property Selector | Select one or more properties to return to their default value. Move from the left list to the right list to include in the unset operation. This property is only available when Command Type is set to Unset. |
Example
In this example, the Alter Warehouse component is used to change the max concurrency level of a warehouse, changing the number of concurrent SQL queries it is capable of. To begin, a simple orchestration job is created that includes the Alter Warehouse component, as below.
The Command Type property is configured as Set. This will enable us to select Max Concurrency Leveldfn> in the next property.
The new property, Properties, allows MAX_CONCURRENCY_LEVEL to set the number of possible concurrent SQL queries. In this example, we have chosen 10.
Once property windows are closed, this job can be run to alter the warehouse. We can also choose to unset properties using a similar job but choosing Unset as our Command Type. Properties to be unset can be chosen by moving them from the left list to the right list.
