Can we use Parameterized Views in Snowflake?

snowflake parameterized query
how to set bind variable in snowflake
snowflake dynamic sql
snowsql variables
snowflake clone view
snowflake udf
snowflake create or replace
snowflake create view missing column specification

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

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

Can we use Parameterized Views in Snowflake?, 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. Can we use Parameterized Views in Snowflake. Such as pass the table name or database name as parameters instead of hard-coding it in the view DDL ? The 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.

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

Table, View, & Sequence DDL, is stored in tables. Views can be used to display selected rows and columns in one or more tables. Snowflake provides parameters that let you control the behavior of your account, individual user sessions, and objects. All the parameters have default values, which can be set and then overridden at different levels depending on the parameter type (Account, Session, or Object).

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

SQL Variables, , it's not a window into a database. Rather, it is a separate object holding query results with data refreshed periodically. The Snowflake documentation for Lateral Join mention that in SELECT FROM <left_hand_table_expression>, LATERAL ( <in_line_view> ) The in_line_view could be: An in-line view (a v

Snowflake Materialized Views: A Fast, Zero-Maintenance, Accurate , If you do not want the column names in the view to be the same as the column If the parameter is not included in the CREATE VIEW statement, then the new a view and then grant privileges on that view to a role, the role can use the view  Can we use Parameterized Views in Snowflake? Hot Network Questions What is the narrative difference between a Charisma and Wisdom saving throw?

CREATE VIEW, A view can be used almost anywhere that a table can be used (joins, subqueries, etc.). For example, using the views created above: Show all of the types of  After connecting directly to Snowflake, we can now run queries and pass parameters to and from the activities. Below is a screenshot of a custom activity’s extended properties settings. This illustrates how we passed parameterized values to the command.

Overview of Views, It is running in Snowflake when we uncheck the Use Parameterized queries Is parameterized queries supported by Snowflake DB OR do we  We can help! Using the above approach, we have designed a Data Load Accelerator using Talend that provides a configuration managed data ingestion solution. You can load Structured and Semi-Structured datasets from Cloud Storage supported by Snowflake, achieve complete audit and traceability, and avoid writing individual ETL pipelines per use

Comments
  • Actually I have a requirement where I cannot pass the actual table names and the database names in the ddl scripts. There are multiple cross views, as in includes tables from multiple databases. And the requirement is to pass parameters in place of the actual names so that they can be updated dynamically