Hi I have the below Stored Procedure on Snowflake that sends out an email with all the load errors that exist in table SNOWFLAKE.ACCOUNT_USAGE.COPY_HISTORY for the last 24 hours. This is working ok but it the timeframe is currently fixed to “the last 24 hours”. Is there a way to add 2 x parameters to this procedure “hh” / “mm” followed by a number so it will scan accordingly to what is called for?
Example 1: CALL SEND_FAILURE_ALERT(‘hh’, 24) >> this would scan the last 24 hours of table SNOWFLAKE.ACCOUNT_USAGE.COPY_HISTORY.
Example 2: CALL SEND_FAILURE_ALERT(‘mm’, 01) >> this would scan the last month of table SNOWFLAKE.ACCOUNT_USAGE.COPY_HISTORY.
As seen below in the code the var “sql_query” is where the sql statement is built before it is executed.
<code>CREATE OR REPLACE PROCEDURE SEND_FAILURE_ALERT()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
var sql_query = "SELECT file_name, stage_location, last_load_time, row_count, row_parsed, file_size, first_error_message, first_error_line_number, first_error_character_pos, first_error_column_name, error_count, error_limit, status, table_name, table_schema_name, table_catalog_name from SNOWFLAKE.ACCOUNT_USAGE.COPY_HISTORY where last_load_time between DATEADD(hh, -24, GETDATE()) and GETDATE() and status = 'Load failed'";
var sqlstmt = snowflake.createStatement({ sqlText: sql_query });
var rs = sqlstmt.execute();
var msg = `<html><body><table border="1"><tr><th>File_name</th><th>Stage_location</th><th>Last_load_time</th><th>Row_count</th><th>Row_parsed</th><th>File_size</th><th>First_error_message</th><th>First_error_line_number</th><th>First_error_character_pos</th><th>First_error_column_name</th><th>Error_count</th><th>Error_limit</th><th></th><th>Status</th><th></th><th>Table_name</th><th></th><th>Table_schema_name</th><th></th><th>Table_catalog_name</th></tr>`;
while (rs.next()) {
var File_name = rs.getColumnValue(1);
var Stage_location = rs.getColumnValue(2);
var Last_load_time = rs.getColumnValue(3);
var Row_count = rs.getColumnValue(4);
var Row_parsed = rs.getColumnValue(6);
var File_size = rs.getColumnValue(7);
var First_error_message = rs.getColumnValue(8);
var First_error_line_number = rs.getColumnValue(9);
var First_error_character_pos = rs.getColumnValue(10);
var First_error_column_name = rs.getColumnValue(11);
var Error_count = rs.getColumnValue(12);
var Error_limit = rs.getColumnValue(13);
var Status = rs.getColumnValue(14);
var Table_name = rs.getColumnValue(15);
var Table_schema_name = rs.getColumnValue(16);
var Table_catalog_name = rs.getColumnValue(17);
msg += '<tr><td>' + File_name + '</td><td>' + Stage_location + '</td><td>' + Last_load_time + '</td><td>' + Row_count + '</td><td>' + Row_parsed + '</td><td>' + File_size + '</td><td>' + First_error_message + '</td><td>' + First_error_line_number + '</td><td>' + First_error_character_pos + '</td><td>' + First_error_column_name + '</td><td>' + Error_count + '</td><td>' + Error_limit + '</td><td>' + Status + '</td><td>' + Table_name + '</td><td>' + Table_schema_name + '</td><td>' + Table_catalog_name + '</td></tr>';
}
msg += `</table></body></html>`;
var proc = "CALL SYSTEM$SEND_EMAIL('ERRORS_ALERTS', '[email protected]', 'Task Failure Alert: Snowflake Jobs', '" + msg + "','text/html')";
var stmt = snowflake.createStatement({ sqlText: proc });
stmt.execute();
return "Succeeded! Email sent";
$$;
</code>
<code>CREATE OR REPLACE PROCEDURE SEND_FAILURE_ALERT()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
var sql_query = "SELECT file_name, stage_location, last_load_time, row_count, row_parsed, file_size, first_error_message, first_error_line_number, first_error_character_pos, first_error_column_name, error_count, error_limit, status, table_name, table_schema_name, table_catalog_name from SNOWFLAKE.ACCOUNT_USAGE.COPY_HISTORY where last_load_time between DATEADD(hh, -24, GETDATE()) and GETDATE() and status = 'Load failed'";
var sqlstmt = snowflake.createStatement({ sqlText: sql_query });
var rs = sqlstmt.execute();
var msg = `<html><body><table border="1"><tr><th>File_name</th><th>Stage_location</th><th>Last_load_time</th><th>Row_count</th><th>Row_parsed</th><th>File_size</th><th>First_error_message</th><th>First_error_line_number</th><th>First_error_character_pos</th><th>First_error_column_name</th><th>Error_count</th><th>Error_limit</th><th></th><th>Status</th><th></th><th>Table_name</th><th></th><th>Table_schema_name</th><th></th><th>Table_catalog_name</th></tr>`;
while (rs.next()) {
var File_name = rs.getColumnValue(1);
var Stage_location = rs.getColumnValue(2);
var Last_load_time = rs.getColumnValue(3);
var Row_count = rs.getColumnValue(4);
var Row_parsed = rs.getColumnValue(6);
var File_size = rs.getColumnValue(7);
var First_error_message = rs.getColumnValue(8);
var First_error_line_number = rs.getColumnValue(9);
var First_error_character_pos = rs.getColumnValue(10);
var First_error_column_name = rs.getColumnValue(11);
var Error_count = rs.getColumnValue(12);
var Error_limit = rs.getColumnValue(13);
var Status = rs.getColumnValue(14);
var Table_name = rs.getColumnValue(15);
var Table_schema_name = rs.getColumnValue(16);
var Table_catalog_name = rs.getColumnValue(17);
msg += '<tr><td>' + File_name + '</td><td>' + Stage_location + '</td><td>' + Last_load_time + '</td><td>' + Row_count + '</td><td>' + Row_parsed + '</td><td>' + File_size + '</td><td>' + First_error_message + '</td><td>' + First_error_line_number + '</td><td>' + First_error_character_pos + '</td><td>' + First_error_column_name + '</td><td>' + Error_count + '</td><td>' + Error_limit + '</td><td>' + Status + '</td><td>' + Table_name + '</td><td>' + Table_schema_name + '</td><td>' + Table_catalog_name + '</td></tr>';
}
msg += `</table></body></html>`;
var proc = "CALL SYSTEM$SEND_EMAIL('ERRORS_ALERTS', '[email protected]', 'Task Failure Alert: Snowflake Jobs', '" + msg + "','text/html')";
var stmt = snowflake.createStatement({ sqlText: proc });
stmt.execute();
return "Succeeded! Email sent";
$$;
</code>
CREATE OR REPLACE PROCEDURE SEND_FAILURE_ALERT()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
var sql_query = "SELECT file_name, stage_location, last_load_time, row_count, row_parsed, file_size, first_error_message, first_error_line_number, first_error_character_pos, first_error_column_name, error_count, error_limit, status, table_name, table_schema_name, table_catalog_name from SNOWFLAKE.ACCOUNT_USAGE.COPY_HISTORY where last_load_time between DATEADD(hh, -24, GETDATE()) and GETDATE() and status = 'Load failed'";
var sqlstmt = snowflake.createStatement({ sqlText: sql_query });
var rs = sqlstmt.execute();
var msg = `<html><body><table border="1"><tr><th>File_name</th><th>Stage_location</th><th>Last_load_time</th><th>Row_count</th><th>Row_parsed</th><th>File_size</th><th>First_error_message</th><th>First_error_line_number</th><th>First_error_character_pos</th><th>First_error_column_name</th><th>Error_count</th><th>Error_limit</th><th></th><th>Status</th><th></th><th>Table_name</th><th></th><th>Table_schema_name</th><th></th><th>Table_catalog_name</th></tr>`;
while (rs.next()) {
var File_name = rs.getColumnValue(1);
var Stage_location = rs.getColumnValue(2);
var Last_load_time = rs.getColumnValue(3);
var Row_count = rs.getColumnValue(4);
var Row_parsed = rs.getColumnValue(6);
var File_size = rs.getColumnValue(7);
var First_error_message = rs.getColumnValue(8);
var First_error_line_number = rs.getColumnValue(9);
var First_error_character_pos = rs.getColumnValue(10);
var First_error_column_name = rs.getColumnValue(11);
var Error_count = rs.getColumnValue(12);
var Error_limit = rs.getColumnValue(13);
var Status = rs.getColumnValue(14);
var Table_name = rs.getColumnValue(15);
var Table_schema_name = rs.getColumnValue(16);
var Table_catalog_name = rs.getColumnValue(17);
msg += '<tr><td>' + File_name + '</td><td>' + Stage_location + '</td><td>' + Last_load_time + '</td><td>' + Row_count + '</td><td>' + Row_parsed + '</td><td>' + File_size + '</td><td>' + First_error_message + '</td><td>' + First_error_line_number + '</td><td>' + First_error_character_pos + '</td><td>' + First_error_column_name + '</td><td>' + Error_count + '</td><td>' + Error_limit + '</td><td>' + Status + '</td><td>' + Table_name + '</td><td>' + Table_schema_name + '</td><td>' + Table_catalog_name + '</td></tr>';
}
msg += `</table></body></html>`;
var proc = "CALL SYSTEM$SEND_EMAIL('ERRORS_ALERTS', '[email protected]', 'Task Failure Alert: Snowflake Jobs', '" + msg + "','text/html')";
var stmt = snowflake.createStatement({ sqlText: proc });
stmt.execute();
return "Succeeded! Email sent";
$$;