How to run a query with parameters in Snowflake

Query parameters are a way to specify variables in a query so that a query can be reused with the ability to swap out parts of the query definition without having to modify the query definition. Let’s dig into how to run a query with parameters in Snowflake.

Parameters are often used to allow end-users to input a few parameters in a query that they otherwise cannot modify. Query parameters, in this context, are variables that are set before a query is run, and then used within a query definition.

Here are some example use cases for query parameters and the clause they typically defined in:

• SELECT : To select a specific column
• WHERE : To filter a query
• ORDER BY : To sort by a specific column

Snowflake Variables

In Snowflake, to use query parameters, define and set at least one variable to use in a subsequent query.

Variables are defined with the set command. Variable data types are not explicitly defined but are defined based on the input.

set week_date = '1993-02-02';

Once a variable has been set, we can use that variable in a query definition with a $ before the variable name. Variables are set only for the current session. They are deleted when the current session ends. To be used in a query, variables need to be set and the query which uses the variable needs to be run in the same session.

select 
    customer.c_mktsegment as market_segment,
    sum(orders.o_totalprice) as sales_amount

from 
    snowflake_sample_data.tpch_sf1.orders
    join snowflake_sample_data.tpch_sf1.customer
      on orders.o_custkey = customer.c_custkey

where 
    date_trunc('week',orders.o_orderdate) = date_trunc('week',date($week_date))

group by market_segment

order by sales_amount desc

limit 10;

When setting variables to be used as query parameters, both the variable definition and the query definition can be run at the same time to ensure they are run in the same session. To do this in a Snowflake worksheet highlight both the set statement and the query definition statement and select Run from the Snowflake GUI. To see which variables are set in the current session, run the command show variables; in a Snowflake worksheet.

User-Defined Table Function

Using Snowflake variables as query parameters are a quick and easy way to allow for ad hoc queries to be run with an easier input mechanism than modifying the query definition itself.

But, some use cases require the ability for an end-user to run a query with parameters, but the end-user should not have the ability to modify the query or even view the query definition. There is a requirement for a user interface in which an end-user has the ability to input query parameters and only the query result is returned to the end-user.

This can be achieved using a user-defined table function. User-defined table functions have input parameters, which are then used in a query definition that is defined in the function.

create or replace function weekly_top_segments(week_date varchar) 
    returns table (market_segment varchar, sales_amount number(36,2))
    as
    $$
        select 
            customer.c_mktsegment as market_segment,
            sum(orders.o_totalprice) as sales_amount

        from 
            snowflake_sample_data.tpch_sf1.orders
            join snowflake_sample_data.tpch_sf1.customer
            on orders.o_custkey = customer.c_custkey

        where 
            date_trunc('week',orders.o_orderdate) = date_trunc('week',date(week_date))

        group by market_segment

        order by sales_amount desc

        limit 10

    $$;

This first line defines the function name and function parameters. We have to specify the type of each parameter:

create or replace function weekly_top_segments(week_date varchar)

The second line specifies the output of the function as a table with a return table and then defines the columns and data type of each column in the output table. The column count and type should match the query in the function definition.

returns table (market_segment varchar, sales_amount number(36,2))

On the third and fourth lines, we use to specify the start of the query function and the query function is wrapped in $$ to specify the start and end of the query definition text.

as
$$
<query definition>
$$

The function input parameter is used in the query definition (without the $ as in the variable example). In the example above, the input parameter week_date is used in this line:

date_trunc('week',orders.o_orderdate) = date_trunc('week',date(week_date))

End-users can utilize this function by using the function, with parameters set, in a FROM clause and wrapped in table() :

select * from table(weekly_top_segments('1993-02-02'))

Conclusion

Both of these options use only Snowflake. By using other tools, such as BI platforms or programming languages, a better graphical user interface can be delivered to end-users but allows Snowflake to handle the input variable and query definition.

Using query parameters in Snowflake can expand your SQL toolset and start to allow non-SQL end users to interact with your data warehouse in a controlled manner. And that’s how to run a query with parameters in Snowflake!

Continue reading

Check out: Loading Data into Snowflake with Efficiency