BigDataDave1

{
  "COLUMNS": [
    {
      "COLUMN_NAME": "CAL_DATE",
      "COLUMN_PATH": "ANALYTICS.PUBLIC.CALENDAR_DATE.CAL_DATE",
      "DATA_TYPE": "DATE",
      "IS_IDENTITY": "NO",
      "IS_NULLABLE": "NO",
      "ORDINAL_POSITION": 1,
      "TABLE_CATALOG": "ANALYTICS",

BigDataDave1

--Single Table Version
WITH
    COLUMNS_JSON AS (
        SELECT
            TABLE_CATALOG,
            TABLE_SCHEMA,
            TABLE_NAME,
            COLUMN_NAME,
            TABLE_CATALOG || '.' || TABLE_SCHEMA || '.' || TABLE_NAME AS TABLE_PATH,
            TABLE_CATALOG || '.' || TABLE_SCHEMA || '.' || TABLE_NAME || '.' || COLUMN_NAME AS COLUMN_PATH,

BigDataDave1

{
  "COLUMN_ID": 3956141,
  "COLUMN_NAME": "BYTES_SCANNED",
  "DATA_TYPE": "NUMBER",
  "DELETED": "2020-10-19 23:12:15.898 -0700",
  "IS_IDENTITY": "NO",
  "IS_NULLABLE": "YES",
  "IS_SELF_REFERENCING": "NO",
  "NUMERIC_PRECISION": 38,
  "NUMERIC_PRECISION_RADIX": 10,

BigDataDave1

[
  4186182,
  "QUEUED_OVERLOAD_TIME",
  507422,
  "TRANSIENT_IS_QUERY_HISTORY",
  29,
  "PUBLIC",
  27,
  "SALES",
  26,

BigDataDave1

/*==============================================================================================================
 * Sending in multiple statements from Tableau into a Session Variable Table
==============================================================================================================*/
CREATE OR REPLACE TABLE DEMO.PUBLIC.SESSION_VARIABLES
    (SESSIONID NUMBER , V VARIANT);
 
SELECT OBJECT_CONSTRUCT('Session ID',CURRENT_SESSION()::INTEGER
                        ,'Region',CURRENT_REGION()::STRING
                        ,'Account',CURRENT_ACCOUNT()::STRING
                        ,'User Name',CURRENT_USER()::STRING

BigDataDave1

/*==============================================================================================================
 * Cool Stuff you can do with Snowflake and Tableau
 * David A Spezia
 * Sept 22 2020 | Set a Session Variable in Snowflake
==============================================================================================================*/
-- Step1: Need Data
SELECT * FROM "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE" LIMIT 100;
SELECT * FROM "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE_SALES" LIMIT 100;
 
-- Test Join

BigDataDave1

-- seek and destroy all long running queries for account, user or warehouse scope
create or replace procedure seek_and_destroy_long_running_queries ( THRESHOLD_IN_SECONDS double, TYPE string, NAME string )
    RETURNS STRING
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
    AS
    $$
    //Variables declared here because I am old school and dont declare on the fly like some cowboy
    var result="";
    var rowCount=0;

BigDataDave1

-- all queries for warehouse version
create or replace function get_long_running_queries_warehouse ( threshold_in_seconds number, warehouse_name string )
    returns table (
      query_id varchar,
      user_name varchar,
      warehouse_name varchar,
      start_time TIMESTAMP_LTZ(3),
      total_elasped_time_in_seconds number)
    as  'select 
            query_id,

BigDataDave1

-- all queries for user version
create or replace function get_long_running_queries_user ( threshold_in_seconds number, user_name string )
    returns table (
      query_id varchar,
      user_name varchar,
      warehouse_name varchar,
      start_time TIMESTAMP_LTZ(3),
      total_elasped_time_in_seconds number)
    as  'select 
            query_id,

BigDataDave1

-- Get queries running longer than X seconds
-- Created by David A Spezia July 2019
-- Run this in SnowSQL:
-- select distinct * from "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."CATALOG_SALES";>
-- select distinct * from "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."CATALOG_SALES";>
-- select distinct * from "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."CATALOG_SALES";
  
-- all queries in account version
create or replace function get_long_running_queries ( threshold_in_seconds number )
    returns table (