How to insert the query_id and query_text to the log table when query get failed

Question

Hi I created a stored procedure in snowflake, and want to log the errors into a an log table when the query gets failed. in the table want to log the error message, query_id and query_text.here is code i tried:

create or replace procedure error_log_test()
returns varchar not null
language JAVASCRIPT
as
$$
try
{
 var copy_into_cmd = `copy into my_table from @my_stage
                      file_format=(format_name= 'my_file_format')
                      on_error = skip_file;`;
var copy_into_stmt = snowflake.createStatement({sqlText: copy_into_cmd});
var result_set= copy_into_stmt.execute();
result_set.next();
}

catch(err)
{
 var log_insert_into= snowflake.createStatement({sqlText:`insert into error_table
                                                          'err.code' 
                                                          'err.message',
                                                          '${result_set.getColumnValue(7)}',
                                                          '${load_stmt.getSqlText()}';`});
     
log_insert_into.execute();

return err.message`enter code here`

$$

;

can nay suggest me the best way to do it, any guidance or suggestion are helpful.

user_5070879 · Answer

If there is really need for building custom crafted audit then both queryId and queryText could be retrieved using getSqlText() and getQueryId

Sample:

create or replace table error_table(code TEXT,message TEXT,
                                    queryid TEXT, querytext TEXT);

create or replace procedure error_log_test()
returns varchar not null
language JAVASCRIPT
as
$$
var sql_stmt;
try
{
  var copy_into_cmd = `SELECT 1/0;`;
  sql_stmt = snowflake.createStatement({sqlText: copy_into_cmd});
  var result_set= sql_stmt.execute();
  
  result_set.next();
  
}
catch(err)
{
  var queryId = sql_stmt.getQueryId();
  var queryText = sql_stmt.getSqlText()
  var log_insert_into=snowflake.createStatement({sqlText:`insert into error_table
     (code, message, queryid, querytext) VALUES (?,?,?,?);`
     ,binds : [err.code, err.message,queryId,queryText]
   });
  log_insert_into.execute();
  return err.message;
}

return 'Success';
$$
;

Run:

CALL error_log_test();

SELECT * FROM error_table;

user_1352490 · Answer

How about taking a quick peak at what's already built by the awesome Snowflake Engineers?

Firstly there's a nice GUI under the Compute->Query History - this will give you an idea of what's available.

Secondly there's a table called QUERY_HISTORY holding a tonne of info.

Willing to bet you'll get almost everything you want from here ... with the exception of the result_set.

You may however look for the 'calling' of your sed Stored Procedure then under the same session ID look for the subsequent rows inserted into the QUERY_HISTORY perhaps detecting which one failed?
```
 SELECT 
  TOP 10 *
 FROM 
      SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
 WHERE 
     EXECUTION_STATUS != 'SUCCESS';
```

How to insert the query_id and query_text to the log table when query get failed

2 Answers