Currently Snowflake does not show the parent and child relationships between executed Stored Procedure and queries triggered from within the Stored Procedure from the QUERY_HISTORY view. This is quite an inconvenience if we want to find out what are the queries triggered by a certain Stored Procedure.
I found a workaround to this issue, though it is not perfect, it should work for the time being.
Run the following queries:
create or replace procedure sp_test() returns string language javascript as $$ snowflake.execute({sqlText: "select * from ericlin.test.test"}); return 'true'; $$; alter session set QUERY_TAG = 'sp_test'; call sp_test(); alter session set QUERY_TAG = default;
The trick here is to add a QUERY_TAG before calling the Stored Procedure, then reset it, so that the following queries won’t be wrongly tagged.
Then use the below query against QUERY_HISTORY to find the parent and child queries:
with parent_sp as ( select regexp_replace(query_text, 'call (.*)\\(.*', '\\1') as key, * from table(information_schema.query_history()) where query_text ilike 'call%' ), child_sp as ( select QUERY_TAG as key, * from table(information_schema.query_history()) where query_text not ilike 'call %' ) select p.query_id as parent_query_id, p.query_text as parent_query_text, c.query_id as child_query_id, c.query_text as child_query_text from parent_sp p JOIN child_sp c ON (p.key = c.key and p.start_time <= c.start_time and p.end_time >= c.end_time and p.session_id = c.session_id );
The result looks like below:
***************************[ 1 ]*************************** PARENT_QUERY_ID | 019fe893-0602-e072-0000-01d109aa67f2 PARENT_QUERY_TEXT | call sp_test(); CHILD_QUERY_ID | 019fe893-0602-e072-0000-01d109aa67f6 CHILD_QUERY_TEXT | select * from ericlin.test.test
Hopefully this can help!