How to Create View Dynamically in Snowflake?

The best part about Snowflake is that it supports almost all features that are available in any modern data warehouse. One of such a feature is dynamic SQL. You can execute SQL statements dynamically using Snowflake stored procedures. Snowflake supports JavaScript API to write stored procedures and user-defined functions. Note that, there is a difference between Snowflake stored procedure and UDFs. We will use stored procedures to create a view dynamically in Snowflake.

Create View Dynamically in Snowflake

In a data warehouse application, you will always get requirement to write dynamic SQL. One of such requirement is to write dynamic view. For example, create a view to combine results of tables starting with some string, say, ‘test’. You can use regular expressions to extract the required string and prepare a view statement as per your requirements.

Steps to Create Dynamic View in Snowflake

The following steps allows you to create dynamic queries

Define View Statement

In this step you need to define your view statement.

For example,

var sql_command = "your View statement";

Prepare VIEW Statement

The snowflake.createStatement API will prepare the View statement that you want to execute dynamically.

For example,

var stmt = snowflake.createStatement(
           {
           sqlText: sql_command
           }
        );

Execute SQL Statement or Return the View DDL

You can either execute the prepared statement or you can return the statement so that you can execute it manually.

For example,

var res = stmt.execute();

Example to Create View Dynamically in Snowflake

Now, let us put all the above mentioned steps together and generate dynamic view in stored procedures. We will search the information_schema database to search for the tables that are matching the prefix provided in the stored procedure.

The following stored procedure creates a view to combine data from tables starting with the same name, say, ‘TEST’.

create or replace procedure TEST_DB.PUBLIC.CREATE_DYNAMIC_VEIW(PREFIX VARCHAR)
  returns STRING
  language javascript
  as     
  $$
    // query to get required tables from information_schema
    var get_tables_name_stmt = "SELECT Table_Name FROM TEST_DB.INFORMATION_SCHEMA.TABLES \
            WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE UPPER('"+ PREFIX + "%');"

    var get_tables_name_stmt = snowflake.createStatement({sqlText:get_tables_name_stmt });
	
    // get required tables
    var tables = get_tables_name_stmt.execute();

	// row_count will be used to handle last union all in the view definition
    var row_count = get_tables_name_stmt.getRowCount();
    var cnt = 0; 

    // view name statement
    var create_statement = "CREATE OR REPLACE VIEW TEST_DB.PUBLIC.combine_data_v AS \n";

    // loop over table names
    while (tables.next())  {
        cnt += 1;

        // we get table name only
        var table_name = tables.getColumnValue(1); 
		
		//  Build view query statement
        create_statement += "SELECT * FROM TEST_DB.PUBLIC." + table_name 

        // add union all to statement except last one
        if (cnt < row_count){
            create_statement += "\n UNION ALL \n"
        }
     }

    // Prepare view statement
    var create_view_statement = snowflake.createStatement( {sqlText: create_statement} );
    //execute create view statement
	create_view_statement.execute();

    // return the create view statement
    return create_view_statement.getSqlText();
  $$
;

This is a simple procedure. You can always enhance it as per your requirement.