Alter Warehouse Component
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.
|Name||Text||The descriptive name for the component.|
|Warehouse||Select||The name of the warehouse to be altered.|
|Command Type||Select||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 and puts the warehouse into a SUSPENDED state
Unset:Resets the current value of a warehouse property back to its default value.
|Rename To||Text||The new name of the Warehouse. (Property available only when Command Type is 'Rename To')|
|Properties||Select Multiple||Choose one or more properties to change. (Property available only when Command Type is '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, a 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)
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)
INITIALLY_SUSPENDED: Choose whether a Warehouse should be suspended upon creation or not.
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").
|Properties||Select Multiple||Choose one or more properties to return to its default value. (Property available only when Command Type is 'Unset')|
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.
We choose from the properties to alter the environment default warehouse. We then set the 'Command Type' to 'Set'. This will enable new options that allow us to select 'Max Concurrency Level'.
The new property allows MAX_CONCURRENCY_LEVEL to be 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 to the right list.