CODEWITHSUNDEEP
snowflake-cloud-data-platform
sounak das
sounak das

Reputation: 51

Can we use Parameterized Views in Snowflake?

Can we use Parameterized Views in Snowflake. Such as pass the table name or database name as parameters instead of hardcoding it?

Upvotes: 4

Views: 12331

Answers (4)

gojimmypi
gojimmypi

Reputation: 536

Although when using the interactive SQL worksheet on Snowflake, you can do this:

 SET target_table_name='myTable';
 SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=$target_table_name

That does not work programmatically. Instead, as described here, a parameterized query such as a view uses this syntax:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=(?)

And yes, the name of that first (?) parameter is 1.

I'm working on my SnowflakeSQLHelper (an adaptation of the Microsoft Patterns and Practices) that will help when attaching parameters.

Upvotes: 1

bradleykirwan
bradleykirwan

Reputation: 168

I think your best bet is to use session variables in conjunction with a regular view.

A session variable can be referenced in the view DDL, and will need to be set in any sessions querying the view.

To do this, you can make use of the IDENTIFIER function in Snowflake, which lets you use text as an object identifier.

create table t1 (col1 number, col2 number);
create table t2 (col1 number, col2 number);


set ti = 't1';
create view v1 as select col1, col2 from identifier($ti);

Before you query the view, you will need to set the session variable (ti in this case) to the table name (fully qualified if need be).

set ti = 't1';
select * from v1; -- returns data from t1

set ti = 't2';
select * from v1; -- returns data from t2

Upvotes: 2

Mike Walton
Mike Walton

Reputation: 7369

I think the best way to handle something like this would be to create a UDTF that acts like a view that has been parameterized. So, in essence, you'd reference the UDTF like a view and pass the parameters into the UDTF, which would then return the data that you wish to use. Note that Snowflake has 2 options for UDTF (SQL and Javascript):

https://docs.snowflake.net/manuals/sql-reference/udf-table-functions.html https://docs.snowflake.net/manuals/sql-reference/udf-js-table-functions.html

Upvotes: 1

Rich Murnane
Rich Murnane

Reputation: 2940

I have not found a way to do this, so I've created what I call a "wrapper view" in the past when I need something like this, example as follows.

I hope this helps...Rich

--create source tables and test records
CREATE TABLE t1 (id NUMBER, str VARCHAR);
CREATE TABLE t2 (id NUMBER, str VARCHAR);
CREATE TABLE t3 (id NUMBER, str VARCHAR);
INSERT INTO t1 VALUES(1, 'record from t1');
INSERT INTO t1 VALUES(2, 'record from t1');
INSERT INTO t2 VALUES(100, 'record from t2');
INSERT INTO t2 VALUES(101, 'record from t2');
INSERT INTO t3 VALUES(998, 'record from t3');
INSERT INTO t3 VALUES(999, 'record from t3');

--create the "wrapper" view
CREATE VIEW vw_t AS (
SELECT 't1' as table_name, * FROM t1
UNION ALL
SELECT 't2' as table_name, * FROM t2
UNION ALL 
SELECT 't3' as table_name, * FROM t3);

--try it out
SELECT * 
FROM   vw_t
WHERE  table_name = 't3';

--results
TABLE_NAME  ID     STR
t3          998    record from t3
t3          999    record from t3

Upvotes: 1

Related Questions