UNION ALL on all tables starting with a certain string

Question

I would like to combine tables starting with the same name into one table. For example let's say I have a database with tables 'EXT_ABVD', 'EXT_ADAD','EXT_AVSA','OTHER', and I want to combine all tables beginning with 'EXT_', I would want the result of

select col1 ,col2 from EXT_ABVD
union all
select col1 ,col2 from EXT_ADAD
union all
select col1 ,col2 from EXT_AVSA;

I would like to do this on a regular basis (daily for example), and every time this runs there may be new tables starting with 'EXT_'. I don't want to update the union_all query manually.

I am new to Snowflake and don't know how can I do that? Can I use a script inside Snowflake?

Answer 1

Given these tables:

CREATE TABLE TEST_DB.PUBLIC.EXT_ABVD (col1 INTEGER, col2 INTEGER);
CREATE TABLE TEST_DB.PUBLIC.EXT_ADAD (col1 INTEGER, col2 INTEGER);
CREATE TABLE TEST_DB.PUBLIC.EXT_ADAQ (col1 INTEGER, col2 INTEGER);

A view like this could be dynamically created:

CREATE OR REPLACE VIEW TEST_DB.PUBLIC.union_view AS 
SELECT * FROM TEST_DB.PUBLIC.EXT_ABVD
 UNION ALL 
SELECT * FROM TEST_DB.PUBLIC.EXT_ADAD
 UNION ALL 
SELECT * FROM TEST_DB.PUBLIC.EXT_ADAQ

Using this Procedure:

create or replace procedure TEST_DB.PUBLIC.CREATE_UNION_VEIW(TBL_PREFIX VARCHAR)
  returns VARCHAR -- return final create statement
  language javascript
  as     
  $$
    // build query to get tables from information_schema
    var get_tables_stmt = "SELECT Table_Name FROM TEST_DB.INFORMATION_SCHEMA.TABLES \
            WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE '"+ TBL_PREFIX + "%';"

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

    // get result set containing all table names
    var tables = get_tables_stmt.execute();

    // to control if UNION ALL should be added or not
    // this could likely be handled more elegantly but i don't know JavaScript :)
    var row_count = get_tables_stmt.getRowCount();
    var rows_iterated = 0; 

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

    // loop over result set to build statement
    while (tables.next())  {
        rows_iterated += 1;

        // we get values from the first (and only) column in the result set
        var table_name = tables.getColumnValue(1); 

        // this will obviously fail if the column count doesnt match
        create_statement += "SELECT * FROM TEST_DB.PUBLIC." + table_name 

        // add union all to all but last row
        if (rows_iterated < row_count){
            create_statement += "\n UNION ALL \n"
        }
     }

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

    // return the create statement as text
    return create_statement.getSqlText();
  $$
  ;

Which we would call like this: CALL CREATE_UNION_VIEW('EXT_A');

This is just a basic example so logic for column counts, schemas etc. likely needs to be added. But given this I think you will be able to figure out how to deal with result sets, parameters and statements.

Edit: See here for how to set up a task that would run a procedure on daily basis. The most basic would in this case look like this:

create or replace task create_union_task
  warehouse = COMPUTE_WH
  schedule = '1440 minute' -- once every day
as
  CALL CREATE_UNION_VIEW('EXT_A');

Answer 2

The only way you can achieve this currently is via a Snowflake Stored Procedure.

You don't specify how you want to consume the result of the query, but a convenient way is via a VIEW. So the Stored Procedure has to generate a VIEW definition containing the query in your question.