Airflow:Snowflake:Return rows from last query in a file

Question

Requirement: Returns all rows from the last query in the Snowflake SQL file

Issue: It's not returning all the rows , but only one row

Code

    def get_ids(self):
        snflk_hook = SnowflakeHook(snowflake_conn_id=self.snflk_conn_id)
        with closing(snflk_hook.get_conn()) as conn:
            with closing(conn.cursor(DictCursor)) as cur:
                cur.execute(self.sql)
                ids = []
                for row in cur:
                    ids.append(row)
            return mongo_ids

In the SQL File as

BEGIN
CREATE OR REPLACE TEMP TABLE ABC_ID AS
SELECT
    *
FROM
    XYZ;

SELECT
    ID
FROM
    ABC_ID;
END

The last query should return all the ID's

user_5070879 · Answer

It is possible to return resultse from Snowflake Scripting block using RESULTSET:

CREATE OR REPLACE TABLE XYZ AS 
SELECT 1 AS ID UNION ALL SELECT 2;

Code:

BEGIN
  CREATE OR REPLACE TEMP TABLE ABC_ID AS
  SELECT  *
  FROM  XYZ;

  LET res RESULTSET := (SELECT ID FROM ABC_ID);
  
  RETURN TABLE(res);
END

Output:

Airflow:Snowflake:Return rows from last query in a file

1 Answers