Sample Queries

Usage examples: Analyse Single statement

The most basic usage scenario, is intended to be used whenever you need to debug/analyze a query in isolation

SELECT * 
FROM TABLE(CODE_SCHEMA.GET_AP('select * from tab1;')) ap
JOIN CODE_SCHEMA.AP_LOOKUP l ON l.ap_code = ap.ap_type;

Usage examples: Analyse Multiple statements

Similar to Analyse Single statement, apart from in this example we pass multiple statements to FlowHigh using multiple ways: - Literal string containing multiple statements (1 row input)

SELECT * 
FROM TABLE(CODE_SCHEMA.GET_AP('select * from tab1;select * from tab2;'));

AP_TYPE	POS	INPUT_STATEMENT	STATUS	STATUS_MSG	AP_QUERY_PART
AP_05	7-1	select * from tab1;select * from tab2;		OK	*
AP_05	26-1	select * from tab1;select * from tab2;		OK

Literal strings using VALUES function

Literal strings using VALUES function

SELECT * FROM (SELECT *
  FROM (VALUES ('select * from tab1','select * from tab2')) AS src (stmt)
 , TABLE(CODE_SCHEMA.GET_AP(src.stmt))) ap
 left JOIN CODE_SCHEMA.AP_LOOKUP l ON l.ap_code = ap.ap_type;

Literal strings using VALUES function WITH concatenation of the input

SELECT * FROM (SELECT * FROM (
SELECT LISTAGG(stmt, ' ; ') as stmt
  FROM (VALUES ('select * from tab1'), ('select * from tab2')) AS src (stmt)
) a, TABLE(CODE_SCHEMA.GET_AP(a.stmt))) ap
left JOIN CODE_SCHEMA.AP_LOOKUP l ON l.ap_code = ap.ap_type;

Usage examples: SQL Corpus

We ship a small SQL corpus that contains various SQL anti patterns. It is created when you install the app.

select l from ( select *
from CODE_SCHEMA.BAD_SQL_CORPUS corpus,
table(CODE_SCHEMA.GET_AP(corpus.sql_query)))  ap
left JOIN CODE_SCHEMA.AP_LOOKUP l ON l.ap_code = ap.ap_type;

Usage examples: Query History

– QUERY_HISTORY_BY_SESSION

SELECT * FROM (
    SELECT *
    FROM TABLE(information_schema.query_history_by_session())
         ,TABLE(CODE_SCHEMA.GET_AP(query_text))) ap
     left JOIN CODE_SCHEMA.AP_LOOKUP l ON l.ap_code = ap.ap_type;

– QUERY_HISTORY_BY_USER

SELECT * FROM (
    SELECT *
    FROM TABLE(information_schema.query_history_by_user())
         ,TABLE(CODE_SCHEMA.GET_AP(query_text))() ap
              left JOIN CODE_SCHEMA.AP_LOOKUP l ON l.ap_code = ap.ap_type;

– QUERY_HISTORY_BY_WAREHOUSE

SELECT * FROM (
    SELECT *
    FROM TABLE(information_schema.query_history_by_warehouse())
         ,TABLE(CODE_SCHEMA.GET_AP(query_text))) ap
         left JOIN CODE_SCHEMA.AP_LOOKUP l ON l.ap_code = ap.ap_type;

Usage examples: Account usage query history

Requires IMPORTED PRIVILEGES grants on snowflake database

– to grant IMPORTED PRIVELEGES

USE ROLE ACCOUNTADMIN; 
GRANT IMPORTED PRIVILEGES ON DATABASE snowflake TO ROLE FH_APP_CONSUMER;
USE ROLE FH_APP_CONSUMER;

– Check SQL anti patterns in the Snowflake account level query history.

select * from (select * from snowflake.account_usage.query_history, table(CODE_SCHEMA.GET_AP(query_text)) limit 100 ) ap
left JOIN CODE_SCHEMA.AP_LOOKUP l ON l.ap_code = ap.ap_type;

Usage example: Stored Procedure

– prep step in case there is no available database we can write to (required even for temporary functions)

USE ROLE SYSADMIN;
CREATE DATABASE FH_TEST;
GRANT OWNERSHIP ON DATABASE FH_TEST TO ROLE FLOWHIGH_DEV_ROLE;
GRANT OWNERSHIP ON SCHEMA FH_TEST.PUBLIC TO ROLE FLOWHIGH_DEV_ROLE;
--
USE ROLE FLOWHIGH_DEV_ROLE;
USE FLOWHIGH_APP_FH;
CREATE SCHEMA IF NOT EXISTS FH_TEST.PUBLIC;
CREATE OR REPLACE TEMPORARY PROCEDURE FH_TEST.public.get_and_lookup(inp string)
returns table(ap_type string
            , pos string
            , input_statement string
            , status string
            , status_msg string
            , ap_query_part string
            , ap_name string
            , ap_code string
            , severity string
            , CORRECT_FLG string
            , PERFORMANCE_FLG string
            , READABLE_FLG string
            , DOC_LINK string
            , content string)
LANGUAGE SQL
AS
DECLARE
  res RESULTSET DEFAULT (SELECT *
    FROM TABLE(FLOWHIGH_APP_FH.CODE_SCHEMA.GET_AP(:inp)) ap
    JOIN FLOWHIGH_APP_FH.CODE_SCHEMA.AP_LOOKUP l ON l.ap_code = ap.ap_type);
BEGIN
    RETURN TABLE(res);
END;

To use the stored procedure

CALL  FH_TEST.public.get_and_lookup('select * from tab1;');

Enable logging and sharing with Provider

– enable logs

USE ROLE ACCOUNTADMIN;
CREATE DATABASE FlowHigh_Data;
USE FlowHigh_Data;
CREATE SCHEMA IF NOT EXISTS LOGS;
CREATE EVENT TABLE IF NOT EXISTS LOGS.app_events;
ALTER ACCOUNT SET EVENT_TABLE = FlowHigh_Data.LOGS.app_events;
-- to share logs with the provider (Sonra)
ALTER APPLICATION FlowHigh_App_FH SET SHARE_EVENTS_WITH_PROVIDER = TRUE;
USE ROLE FLOWHIGH_DEV_ROLE;