stringsnowflake-cloud-data-platformexecute-immediate

How to EXECUTE a STRING in Snowflake


Following up from this thread

with a as (
  select * from hubspot.information_schema.tables
  where table_catalog = 'HUBSPOT' AND TABLE_SCHEMA = 'MONGODB' and table_name != '_SDC_REJECTED' and table_type = 'BASE TABLE'
),
b as (
  select * ,
    $$SELECT * FROM HUBSPOT.MONGODB.TABLE_NAME$$ t,
    replace(t,'TABLE_NAME',table_name) as sql
  from a
)
, test as (
select listagg(sql,'\nUNION ALL\n') within group (order by table_schema, table_catalog)
from b
    )
EXECUTE IMMEDIATE SELECT * FROM test
;

I have managed to create a select statement which unions a lit of tables from a specific schema. How do I then execute this string?

I've tried EXECUTE IMMEDIATE but I keep getting this error: 'Syntax error: unexpected 'EXECUTE'. (line 20)'


Solution

  • Snowflake script block could be used:

    DECLARE 
      res RESULTSET;
      sqlQuery TEXT;
    BEGIN
       WITH a as (
         select * 
         from hubspot.information_schema.tables
         where table_catalog = 'HUBSPOT' 
          AND TABLE_SCHEMA = 'MONGODB' 
          and table_name != '_SDC_REJECTED' 
          and table_type = 'BASE TABLE'
        ),b as (
           select * ,
           $$SELECT * FROM HUBSPOT.MONGODB.TABLE_NAME$$ t,
        replace(t,'TABLE_NAME',table_name) as sql
           from a
        ), test as (
        select listagg(sql,'\nUNION ALL\n') 
               within group (order by table_schema, table_catalog) AS query
         from b
        )
        SELECT query
        INTO :sqlQuery
        FROM test;
    
        res := (EXECUTE IMMEDIATE :sqlQuery);
       RETURN TABLE(res);
    END;
    

    Related: SELECT ... INTO ... and EXECUTE IMMEDIATE

    Another option is session variable(though the length of string is limited to 256 bytes):

    SET sqlQuery = (SELECT ... );
    -- SELECT query should return single column/row
    
    EXECUTE IMMEDIATE $sqlQuery;