Categories:

Warehouse & Resource Monitor DDL

ALTER RESOURCE MONITOR¶

Modifies the properties and triggers for an existing resource monitor. Use this command to increase or decrease the credit quota, change the scheduling information, or change/replace the triggers for a resource monitor.

Syntax¶

ALTER RESOURCE MONITOR [ IF EXISTS ] <name> [ SET { [ CREDIT_QUOTA = <num> ]
[ FREQUENCY = { MONTHLY | DAILY | WEEKLY | YEARLY | NEVER } ]
[ START_TIMESTAMP = { <timestamp> | IMMEDIATELY } ]
[ END_TIMESTAMP = <timestamp> ] } ]
[ NOTIFY_USERS = ( <user_name> [ , <user_name> , ... ] )
[ TRIGGERS triggerDefinition [ triggerDefinition ... ] ]


Where:

triggerDefinition ::=
ON <threshold> PERCENT DO { SUSPEND | SUSPEND_IMMEDIATE | NOTIFY }


Parameters¶

name

Specifies the identifier for the resource monitor to alter. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

SET ...

CREDIT_QUOTA = num

Specifies the number of credits allocated to the resource monitor per frequency interval. When total usage for all warehouses assigned to the monitor reaches this number for the current frequency interval, the resource monitor is considered to be at 100% of quota.

If a value is not specified for a resource monitor, the monitor has no quota and will never reach 100% usage within the specified interval.

FREQUENCY = MONTHLY | DAILY | WEEKLY | YEARLY | NEVER

The frequency interval at which the credit usage resets to 0.

If you specify NEVER for the frequency, the credit usage for the warehouse does not reset.

START_TIMESTAMP = timestamp | IMMEDIATELY

The date and time when the resource monitor starts monitoring credit usage for the assigned warehouses.

If you specify IMMEDIATELY for the start timestamp, the current timestamp is used.

If you specify a date without a time, the current time is used.

If you set a time without specifying a time zone, UTC is used as the default time zone.

END_TIMESTAMP = timestamp

The date and time when the resource monitor suspends the assigned warehouses.

NOTIFY_USERS = ( user_name [ , user_name , ... ] )

Specifies the list of users to receive email notifications on resource monitors. If a user identifier includes spaces or special characters or is case-sensitive, then the identifier must be enclosed in double quotes (e.g. “Mary Smith”). See Identifier Requirements for details.

The user identifier, user_name, is the value of the name column from the output of SHOW USERS.

Each user listed must have a verified email address. For instructions on verifying email addresses in the web interface, see:

Note

• The following limitations apply for non-administrator users:

• Non-administrator users are notified by email but cannot see notifications in the classic web interface.

• Non-administrator users cannot create resource monitors.

• Non-administrator users cannot assign other users to be notified.

TRIGGERS ... (aka actions)

Specifies one or more triggers for the resource monitor. Each trigger definition consists of:

• ON threshold PERCENT (usage percentage; values larger than 100 are supported)

• DO SUSPEND | SUSPEND_IMMEDIATE | NOTIFY (action to perform when the threshold is reached).

For more details, see CREATE RESOURCE MONITOR.

Usage Notes¶

• If a SUSPEND or SUSPEND_IMMEDIATE trigger is active for a resource monitor and the trigger threshold has been reached for the specified frequency interval, thereby preventing all assigned warehouses from being started/resumed, you can use this command to either increase the credit quota above the trigger threshold or replace the trigger with a new trigger with a higher threshold.

Once the credit quota or trigger threshold for the resource monitor has been increased, assigned warehouses can be started or resumed.

• The TRIGGERS parameter is not additive (i.e. it removes all existing triggers for the resource monitor and replaces them with the specified triggers).

As a result, to make additions to the existing triggers, you must specify the new triggers and replicate the existing triggers.

• If frequency and start_timestamp parameters are set on a resource monitor, the day for the credit usage reset is calculated based on those parameters. The time the credit usage resets to 0 is 12:00 AM UTC regardless of the time specified in start_timestamp.

• The notification list is limited to a maximum number of 5 non-administrator users.

• Account administrators can view the notification list of non-administrator users in the output of SHOW RESOURCE MONITORS in the notify_user column.

Attention

Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata Fields in Snowflake.

Examples¶

Specify a new credit quota for the limiter resource monitor and replace the existing triggers for the monitor with a new set of triggers:

ALTER RESOURCE MONITOR limiter
SET CREDIT_QUOTA=2000
TRIGGERS ON 80 PERCENT DO NOTIFY
ON 100 PERCENT DO SUSPEND_IMMEDIATE;


Alter a resource monitor to send notifications to three users when 80% of the credit quota is reached. In this example, the user_name for two of the users includes a space and is therefore enclosed in double quotes:

alter resource monitor limiter
set credit_quota=2000
notify_users = (jdoe, "jane smith", "john doe")
triggers on 80 percent do notify
on 100 percent do suspend_immediate