page91
73
operator_type STRING,
operator_id NUMBER,
operator_attributes VARIANT,
row_multiple NUMBER
);
To work around the
GET_QUERY_OPERATOR_STATS
single-input value limitation, you
will create a JavaScript stored procedure called
sp_get_cartesian_join_queries
.
Snowflake scripting can also be used to deliver equivalent functionality and may
be preferrable; you can find further information at
https://docs.snowflake.com/en/
developer-guide/stored-procedure/stored-procedures-snowflake-scripting
. I
leave it to you to convert the example code from JavaScript to SQL scripting.
Regardless of implementation approach, you may want to amend the core query
driving predicates.
CREATE OR REPLACE PROCEDURE sp_get_cartesian_join_queries()
RETURNS string
LANGUAGE javascript
EXECUTE AS CALLER
AS
$$
var sql_stmt = "";
var err_state = "";
var recset = "";
var query_id = "";
var result = "";
sql_stmt = "SELECT query_id\n"
sql_stmt += "FROM snowflake.account_usage.query_history\n"
sql_stmt += "WHERE query_type IN ( 'SELECT', 'CREATE_TABLE_AS_
SELECT' )\n"
sql_stmt += "AND warehouse_name IS NOT NULL\n"
//Exclude cached results
sql_stmt += "AND execution_status = 'SUCCESS'\n"
//Include completed queries
sql_stmt += "AND bytes_scanned > 0\n"
//Must have scanned data
ChapTer 3
The Query profiler