RELOAD_PRECONFIGURED_DATA
Stored procedure RELOAD_PRECONFIGURED_DATA is a Snowflake stored procedure written in Python that is used to recreate the query monitors and labels
that are shipped with Sundeck without overriding any customizations that were made.
This procedure is defined in the database OPSCENTER (database where Snowflake NativeApp is installed) schema ADMIN.
ACCOUNTADMIN privilege is required to run this stored procedure.
CREATE_QUERY_MONITOR(<name>,
<condition>,
<notify_writer>,
<notify_writer_method>,
<notify_others>,
<notify_other_method>,
<cancel> )
None
NULL- if query monitors and labels were successfully reloaded.
<error>- error message string in the case of failure to reload labels or/and query_monitors.
Example below demonstrates what happens if one of the monitors that is pre-configured in Sundeck (‘Long Queries’) was accidentally deleted and user would like to reload it again. User also modified ‘Big Readers’ by reducing bytes_scanned to 5000000000 (shipped originally with bytes_scanned > 10000000000) and added one more query monitor (‘cancel_all_insert_queries’).
Below is the list of query monitors shipped with Sundeck before any modifications were made:
vicky#COMPUTE_WH@OPSCENTER.ADMIN>select name, condition from catalog.query_monitors order by name;
+------------------+-------------------------------------------------------------------------------------------------+
| NAME | CONDITION |
|------------------+-------------------------------------------------------------------------------------------------|
| Big Readers | bytes_scanned > 10000000000 |
| Costs 10 Credits | tools.approx_credits_used(warehouse_name, start_time) > 10 |
| Costs 50 Credits | tools.approx_credits_used(warehouse_name, start_time) > 50 |
| Long Queries | start_time < dateadd(minute, -10, current_timestamp()) AND NOT QUERY_TYPE = 'EXECUTE_STREAMLIT' |
+------------------+-------------------------------------------------------------------------------------------------+
4 Row(s) produced.
This is what monitors look like after user modifications:
vicky#COMPUTE_WH@OPSCENTER.ADMIN>select name, condition from catalog.query_monitors order by name;
+---------------------------+------------------------------------------------------------+
| NAME | CONDITION |
|---------------------------+------------------------------------------------------------|
| Big Readers | bytes_scanned > 5000000000 |
| Costs 10 Credits | tools.approx_credits_used(warehouse_name, start_time) > 10 |
| Costs 50 Credits | tools.approx_credits_used(warehouse_name, start_time) > 50 |
| cancel_all_insert_queries | query_type ilike 'insert' |
+---------------------------+------------------------------------------------------------+
4 Row(s) produced.
User wants to reload mistakenly deleteted ‘Long Queries’:
vicky#COMPUTE_WH@OPSCENTER.ADMIN>call RELOAD_PRECONFIGURED_DATA();
+---------------------------+
| RELOAD_PRECONFIGURED_DATA |
|---------------------------|
| |
+---------------------------+
1 Row(s) produced.
Result: ‘Long Queries’ monitor is back on the list. Modifications to monitors and new monitors are left intact.
vicky#COMPUTE_WH@OPSCENTER.ADMIN>select name, condition from catalog.query_monitors order by name;
+---------------------------+-------------------------------------------------------------------------------------------------+
| NAME | CONDITION |
|---------------------------+-------------------------------------------------------------------------------------------------|
| Big Readers | bytes_scanned > 5000000000 |
| Costs 10 Credits | tools.approx_credits_used(warehouse_name, start_time) > 10 |
| Costs 50 Credits | tools.approx_credits_used(warehouse_name, start_time) > 50 |
| Long Queries | start_time < dateadd(minute, -10, current_timestamp()) AND NOT QUERY_TYPE = 'EXECUTE_STREAMLIT' |
| cancel_all_insert_queries | query_type ilike 'insert' |
+---------------------------+-------------------------------------------------------------------------------------------------+
5 Row(s) produced.
Example only includes query monitors. Same reload logic applies to labels shipped with Sundeck.