UPDATE_WAREHOUSE_POOL
Stored procedure UPDATE_WAREHOUSE_POOL is a Snowflake stored procedure that is used to update an existing warehouse pool object.
This procedure is defined in database SUNDECK (database where Snowflake NativeApp is installed) schema ADMIN.
APP ADMIN privilege is required to run this stored procedure.
UPDATE_WAREHOUSE_POOL(<name>,
<max_concurrent_credits>,
<default_warehouse_sz>,
<allowed_roles>,
<target_label>)
<name>- name of warehouse pool to update.
<max_concurrent_credits>- maximum number of credits a warehouse pool can use concurrently using a one-hour rate. Sundeck may create up to 10 warehouses as a part of a pool, but will always ensure that the number of active warehouses will not exceed the peak credits.
<default_warehouse_sz>- All queries that arrive at the pool and haven’t been seen before should be routed to a warehouse of this size. Supported values:
X-Small
Small
Medium
Large
X-Large
2X-Large
3X-Large
4X-Large
5X-Large
6X-Large
<allowed_roles>- list of roles that are allowed to access the warehouse pool.
<target_label>- The Sundeck label that describes the set of queries that this pool is expected to support. This label is used to inform the pool on how it should be defined.
NULL- if warehouse pool was successfully created.
<error>- error message string in the case of failure to update warehouse pool.
Let’s update warehouse pool named ‘AUTO_ROUTING_POOL’ to have’X-SMALL’ default warehouse, peak credit 3, allows role ‘CS_TEST1’ and target_label to ‘Writes’
ube@sundeck.io#COMPUTE_WH@OPSCENTER.ADMIN>call UPDATE_WAREHOUSE_POOL('AUTO_ROUTING_POOL',
3, 'X-Small', ['CS_TEST1'], 'Writes');
+-----------------------+
| UPDATE_WAREHOUSE_POOL |
|-----------------------|
| NULL |
+-----------------------+
Below you can see a updated record in the WAREHOUSE_POOLS view for the warehouse named AUTO_ROUTING_POOL:
ube@sundeck.io#COMPUTE_WH@OPSCENTER.ADMIN>select name,max_concurrent_credits,allowed_roles,default_warehouse_size,targe
t_label from CATALOG.WAREHOUSE_POOLS where name = 'AUTO_ROUTING_POOL';
+-------------------+------------------------+---------------+------------------------+--------------+
| NAME | MAX_CONCURRENT_CREDITS | ALLOWED_ROLES | DEFAULT_WAREHOUSE_SIZE | TARGET_LABEL |
|-------------------+------------------------+---------------+------------------------+--------------|
| AUTO_ROUTING_POOL | 3 | [ | X-Small | Writes |
| | | "CS_TEST1" | | |
| | | ] | | |
+-------------------+------------------------+---------------+------------------------+--------------+
1 Row(s) produced. Time Elapsed: 0.878s
ube@sundeck.io#COMPUTE_WH@OPSCENTER.ADMIN>