OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

Stored Procedure: add parameter for month or hour

  • Thread starter Thread starter Estrobelai
  • Start date Start date
E

Estrobelai

Guest
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 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";
$$;

<p>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 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?</p>
<p>Example 1: CALL SEND_FAILURE_ALERT('hh', 24) >> this would scan the last 24 hours of table SNOWFLAKE.ACCOUNT_USAGE.COPY_HISTORY.</p>
<p>Example 2: CALL SEND_FAILURE_ALERT('mm', 01) >> this would scan the last month of table SNOWFLAKE.ACCOUNT_USAGE.COPY_HISTORY.</p>
<p>As seen below in the code the var "sql_query" is where the sql statement is built before it is executed.</p>
<pre><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></pre>
 
Top