Tableau & Snowflake: Pass-Through SQL with Session Variables and Parameters

With pass-through SQL in Tableau and Snowflake you can set session variables in Snowflake to deliver on a variety of use cases. You can create dynamic derived tables, set database security contexts, route queries or expand database connectivity beyond your imagination. I already went over passing in data elements from Tableau to a Snowflake UDF here. You can set session variables in Snowflake or just pass in parameter values from Tableau to do something in the database or to the data returned from Snowflake to Tableau.

In this example we will cover basic methods to pass in a session variable or dynamically alter a where clause in Snowflake from Tableau. These are basic examples, but they show how to get and set values from Tableau into Snowflake. With the examples you will be able to apply these methods to your use case, and bend Tableau with Snowflake to your will.

The Demo SQL
We will be using the warehouse DEMO_WH and the Snowflake Sample Data database. TLDR, just give me the workbook here. Below is some SQL that I used to set this up:

/*==============================================================================================================
 * 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
SELECT
    *
FROM
    "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE_SALES" SS JOIN 
    "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE" S ON S.S_STORE_SK = SS.SS_STORE_SK
LIMIT 100;

-- List of S_MANAGERs
SELECT S_MANAGER FROM "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE" GROUP BY 1;

-- Set Variable
SET VARIABLE_MANAGER = 'William Ward';

-- Select *
SELECT
    *
FROM
    "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE_SALES" SS JOIN 
    "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE" S ON S.S_STORE_SK = SS.SS_STORE_SK
WHERE S.S_MANAGER = $VARIABLE_MANAGER
LIMIT 100;

-- Aggregate and Count Records
SELECT
    S.S_MANAGER
    ,COUNT(*) AS COUNT_OF_RECORDS
FROM
    "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE_SALES" SS JOIN 
    "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE" S ON S.S_STORE_SK = SS.SS_STORE_SK
WHERE
    S.S_MANAGER = $VARIABLE_MANAGER
GROUP BY 
    S.S_MANAGER;
-- Step2: Plumb Tableau

Method 1: Use Initial SQL in Tableau to Set a Session Variable in Snowflake
Now that we have the SQL to get this started, open Tableau and connect to your Snowflake account. Edit the Initial SQL dialogue to Include the following. You could set multiple session variables here to your heart’s content. We will show how to do this with Method 3 below.

SET VARIABLE_MANAGER = 'William Ward'

After that connect to the SNOWFLAKE_SAMPLE_DATA database and the TPCDS_SF10TCL schema. Then drag out a Custom SQL Query and paste the following.

SELECT
    *
FROM
    "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE_SALES" SS JOIN 
    "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE" S ON S.S_STORE_SK = SS.SS_STORE_SK
WHERE S.S_MANAGER = $VARIABLE_MANAGER

Now you can query in Snowflake with the context set by Initial SQL in Tableau. You could use, size, or alter a warehouse. This method can call stored procedures or do many other things inside your database.

Method 2: Use a Tableau Parameter with In-Line SQL
Besides setting contexts with a set initial SQL statement a user may want to interact with a UDF, data output or filter state in Tableau. You can set up Tableau to do this example easier with a Quick FIlter, but this example shows the plumbing, and it can be extended to your needs.

Open a new connection to Snowflake and connect to the SNOWFLAKE_SAMPLE_DATA database and the TPCDS_SF10TCL schema. Then drag out a Custom SQL Query and paste the following. We will come back and add in the Tableau parameter later.

SELECT
    *
FROM
    "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE_SALES" SS JOIN 
    "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE" S ON S.S_STORE_SK = SS.SS_STORE_SK

Now go to a sheet and create a parameter from S_MANAGER for later use.

Now go back to the Custom SQL Query and edit it to add in the Tableau parameter on the where clause.

SELECT
    *
FROM
    "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE_SALES" SS JOIN 
    "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE" S ON S.S_STORE_SK = SS.SS_STORE_SK
WHERE S.S_MANAGER = <Parameters.PARAMETER_S_MANAGER>

When you change the parameter in Tableau the SQL sent to Snowflake includes the chosen parameter value.

Method 3: Extending the Initial SQL to a Session Variables Table
There are many aspects that can be set with session variables other than a filter context. Snowflake things like Account, User, Region, Role, Session, Session Timestamp or System Timestamp can be set. Tableau things like Application, Workbook, Version, Server User, and Server Full Name can be set. With these contexts much richer database experiences can be derived.

I used this SQL to setup the Session Variables table, and it uses an extensible JSON payload instead of a rigid schema.

/*==============================================================================================================
 * 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
                        ,'User Role',CURRENT_ROLE()::STRING
                        ,'Session Timestamp',CURRENT_TIMESTAMP()::TIMESTAMP_LTZ
                        ,'System Timestamp',SYSDATE()::TIMESTAMP_NTZ
                        ,'Variable 0','Some Value 0'
                        ,'Variable 1','Some Value 1'
                        );

INSERT INTO DEMO.PUBLIC.SESSION_VARIABLES 
    (SELECT CURRENT_SESSION(),
    OBJECT_CONSTRUCT('Session ID',CURRENT_SESSION()::INTEGER
                    ,'Region',CURRENT_REGION()::STRING
                    ,'Account',CURRENT_ACCOUNT()::STRING
                    ,'User Name',CURRENT_USER()::STRING
                    ,'User Role',CURRENT_ROLE()::STRING
                    ,'Session Timestamp',CURRENT_TIMESTAMP()::TIMESTAMP_LTZ
                    ,'System Timestamp',SYSDATE()::TIMESTAMP_NTZ
                    ,'Variable 0','Some Value 0'
                    ,'Variable 1','Some Value 1'
                    ,'S_MANAGER','William Ward'
                    )
    );

SELECT * FROM DEMO.PUBLIC.SESSION_VARIABLES;

--Use It in Custom SQL
SELECT
    *
FROM
    "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE_SALES" SS JOIN 
    "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE" S ON S.S_STORE_SK = SS.SS_STORE_SK
WHERE S.S_MANAGER = (SELECT TOP 1 V:S_MANAGER::STRING
                     FROM DEMO.PUBLIC.SESSION_VARIABLES
                     WHERE CURRENT_SESSION() = V:"Session ID"::INTEGER
                     ORDER BY V:S_MANAGER::STRING
                    )
;

Now we can insert this Initial SQL statement into Tableau to get access to all these variables at runtime. Notice you can add any name value pairs you desire along with the system functions.

INSERT INTO DEMO.PUBLIC.SESSION_VARIABLES 
    (SELECT CURRENT_SESSION(),
    OBJECT_CONSTRUCT('Session ID',CURRENT_SESSION()::INTEGER
                    ,'Region',CURRENT_REGION()::STRING
                    ,'Account',CURRENT_ACCOUNT()::STRING
                    ,'User Name',CURRENT_USER()::STRING
                    ,'User Role',CURRENT_ROLE()::STRING
                    ,'Session Timestamp',CURRENT_TIMESTAMP()::TIMESTAMP_LTZ
                    ,'System Timestamp',SYSDATE()::TIMESTAMP_NTZ
                    ,'Variable 0','Some Value 0'
                    ,'Variable 1','Some Value 1'
                    ,'S_MANAGER','William Ward'
                    ,'Tableau App',[TableauApp]
                    ,'Tableau Server User',[TableauServerUser]
                    ,'Tableau Server User Full',[TableauServerUserFull]
                    ,'Tableau Version',[TableauVersion]
                    ,'Tableau Workbook Name',[WorkbookName]
                    )
    );

Now you can call the Variables you want from the Session Variables table in-line with SQL for the current session. We are just giving the basic plumbing here, but you can extend this much deeper to meet your use case.

--Use It in Custom SQL
SELECT
    *
FROM
    "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE_SALES" SS JOIN 
    "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE" S ON S.S_STORE_SK = SS.SS_STORE_SK
WHERE S.S_MANAGER = (SELECT TOP 1 V:S_MANAGER::STRING
                     FROM DEMO.PUBLIC.SESSION_VARIABLES
                     WHERE CURRENT_SESSION() = V:"Session ID"::INTEGER
                     ORDER BY V:S_MANAGER::STRING
                    )
;

Conclusion
We are just exploring the tip of iceberg here. So many more things can be done with this connectivity from using warehouses, to altering warehouse sizes to completely controlling the security context of the user experience on Tableau Server. Happy (custom SQL with custom parameters and variables) Queuring!

Share this:

Like this:

Related

Leave a Reply Cancel reply