UPDATE_QUERY_MONITOR

Stored procedure UPDATE_QUERY_MONITOR is a Snowflake SQL stored procedure that is used to modify a query monitor object. This procedure is defined in database OPSCENTER (database where Snowflake NativeApp is installed) schema ADMIN.

ACCOUNTADMIN privilege is required to run this stored procedure.

See also:: UPDATE_QUERY_MONITOR, DELETE_QUERY_MONITOR, SHOW PROCEDURES, DESCRIBE PROCEDURE

Syntax

CREATE_QUERY_MONITOR(<name of the query monitor to update>
                     <new name of the query monitor>,
                     <condition>,
                     <notify_writer>,
                     <notify_writer_method>,
                     <notify_others>,
                     <notify_other_method>,
                     <cancel> )

Arguments

<name of the query monitor to update>: a string that represents query monitor name we are updating.
<name>: a string that represents a new query monitor name. If name of the query monitor remains unchanged, then both names will be the same.
<condition>: text of the query monitor condition. Should not include WHERE clause.
<notify_writer>: boolean value that specifies whether to notify author of the query. If set to True, author will be notified.
<notify_writer_method>: string that specifies notification method: EMAIL or SLACK.
<notify_other>: comma separated list of recepients of the notification.
<notify_other_method>: string that specifies notification method.
<cancel>: boolean value that indicates whether to cancel the query. If set to True, query will be cancelled.

Returns

NULL: if a query monitor was successfully updated.
<error>: error message string in the case of failure to update a query monitor.

Example:

vicky#COMPUTE_WH@OPSCENTER.(no schema)>call ADMIN.CREATE_QUERY_MONITOR(
                                        'cancel_all_insert_queries',
                                        'query_type ilike \'insert\'',
                                        True,
                                        'EMAIL',
                                        'josh@sundeck.io,robert@sundeck.io,susannah@sundeck.io,vicky@sundeck.io',
                                        'EMAIL',
                                        True
                                       );
+----------------------+                                                        
| CREATE_QUERY_MONITOR |
|----------------------|
| NULL                 |
+----------------------+
1 Row(s) produced.

Let’s update query monitor to send notification only to robert@sundeck.io:

vicky#COMPUTE_WH@OPSCENTER.ADMIN>call ADMIN.UPDATE_QUERY_MONITOR('cancel_all_insert_queries',
                                                                'cancel_all_insert_queries',
                                                                'query_type ilike \'insert\'',
                                                                True,
                                                                'EMAIL',
                                                                'robert@sundeck.io',
                                                                'EMAIL',
                                                                True
                                                                );
+----------------------+                                                        
| UPDATE_QUERY_MONITOR |
|----------------------|
| NULL                 |
+----------------------+
1 Row(s) produced. 

vicky#COMPUTE_WH@OPSCENTER.ADMIN>select 
                                        name, 
                                        condition, 
                                        notify_other 
                                from    catalog.query_monitors
                                where   name = 'cancel_all_insert_queries';
+---------------------------+---------------------------+-------------------+   
| NAME                      | CONDITION                 | NOTIFY_OTHER      |
|---------------------------+---------------------------+-------------------|
| cancel_all_insert_queries | query_type ilike 'insert' | robert@sundeck.io |
+---------------------------+---------------------------+-------------------+
1 Row(s) produced.