Airflow:SnowflakeOperator:Assign values from previous statement in SQL query

Question

Snowflake:Assign values from previous statement in SQL query

Requirement: Assign values from the previous statement to the next statement in SQL query , as I run the query in SnowflakeOperator in Airflow

SQL:

BEGIN
        app = 'abc';
        env = select current_database();
        start_time = select current_timestamp()::timestamp_ntz(9);
        end_time = select current_timestamp()::timestamp_ntz(9);
        duration = (end_time.getTime() - start_time.getTime()) / 1000;
       
        insert into proc_runtimes 
        (env, app, task, start_time, end_time, duration, message)
        values 
        (env, app, 'Job Start', start_time.toISOString(), end_time.toISOString(), duration, log_message]})
END

EDIT:

Requirement: Assign values from the previous statement to the next statement in SQL query, as I run the query in SnowflakeOperator in Airflow

Error: Airflow SnowflakeOperator not able to execute the anonymous block statement in the SQL file

SQL:

BEGIN
   let app := 'abc';
   let env := current_database();
   let start_time :=  current_timestamp()::timestamp_ntz(9);
   let end_time :=  current_timestamp()::timestamp_ntz(9);
   let duration := DATEDIFF(seconds, end_time, start_time);
   let log_message := 'some log';
   
   INSERT INTO proc_runtimes
      (env, app, task_name, start_time, end_time, duration, message)
   SELECT 
      :env, :app, 'Job Start', :start_time, :end_time, :duration, :log_message;
END;

Error:

2022-08-16, 19:38:43 UTC] {cursor.py:696} INFO - query: [BEGIN  let env := current_database();]
[2022-08-16, 19:38:43 UTC] {cursor.py:720} INFO - query execution done
[2022-08-16, 19:38:43 UTC] {connection.py:509} INFO - closed
[2022-08-16, 19:38:44 UTC] {connection.py:512} INFO - No async queries seem to be running, deleting session
[2022-08-16, 19:38:44 UTC] {taskinstance.py:1889} ERROR - Task failed with exception
Traceback (most recent call last):
  File "/home/airflow/.local/lib/python3.7/site-packages/airflow/providers/snowflake/operators/snowflake.py", line 120, in execute
    execution_info = hook.run(self.sql, autocommit=self.autocommit, parameters=self.parameters)
  File "/home/airflow/.local/lib/python3.7/site-packages/airflow/providers/snowflake/hooks/snowflake.py", line 301, in run
    cur.execute(sql_statement)
  File "/home/airflow/.local/lib/python3.7/site-packages/snowflake/connector/cursor.py", line 782, in execute
    self.connection, self, ProgrammingError, errvalue
  File "/home/airflow/.local/lib/python3.7/site-packages/snowflake/connector/errors.py", line 273, in errorhandler_wrapper
    error_value,
  File "/home/airflow/.local/lib/python3.7/site-packages/snowflake/connector/errors.py", line 324, in hand_to_other_handler
    cursor.errorhandler(connection, cursor, error_class, error_value)
  File "/home/airflow/.local/lib/python3.7/site-packages/snowflake/connector/errors.py", line 210, in default_errorhandler
    cursor=cursor,
snowflake.connector.errors.ProgrammingError: 001003 (42000): 01a6551a-0501-b736-0251-83014fb1394b: SQL compilation error:
syntax error line 3 at position 34 unexpected '<EOF>'.

Hi, did you try using the RESULT_SCAN function in Snowflake, it return the results as a table fo the executed queries. Eg. select * from table(result_scan(last_query_id())) Check this link for more details https://docs.snowflake.com/en/sql-reference/functions/result_scan.html — Himanshu Kandpal, Aug 16 '22 at 18:23

score 1 · Accepted Answer · answered Aug 17 '22 at 21:00

1

Resolved the issue with below statement

execute immediate $$
BEGIN
   ....
   ....
   ....
END;
$$

answered Aug 17 '22 at 21:00

Kar

790
13
36

score 0 · Answer 2 · answered Aug 16 '22 at 18:24

0

Variable should be defined(:= is assignment operator and can be later accessed:

Test table:

create or replace table proc_runtimes(env TEXT,
       app TEXT,
       task_name TEXT,
       start_time timestamp_ntz(9),
       end_time timestamp_ntz(9),
       duration TEXT,
       message TEXT);

Main block:

BEGIN
   let app := 'abc';
   let env := current_database();
   let start_time :=  current_timestamp()::timestamp_ntz(9);
   let end_time :=  current_timestamp()::timestamp_ntz(9);
   let duration := DATEDIFF(seconds, end_time, start_time);
   let log_message := 'some log';
   
   INSERT INTO proc_runtimes
      (env, app, task_name, start_time, end_time, duration, message)
   SELECT 
      :env, :app, 'Job Start', :start_time, :end_time, :duration, :log_message;
END;

Check:

SELECT * FROM proc_runtimes;

answered Aug 16 '22 at 18:24

Lukasz Szozda

162,964
23
234
275

I got error as `snowflake.connector.errors.ProgrammingError: 001003 (42000): 01a65512-0501-b734-0251-83014fb0e477: SQL compilation error: syntax error line 3 at position 42 unexpected ''.`. when executing `BEGIN let app := 'NIGHTLY_REPORTING_DELETE';]` – Kar Aug 16 '22 at 19:33
@Kar [Using Snowflake Scripting in SnowSQL and the Classic Web Interface](https://docs.snowflake.com/en/developer-guide/snowflake-scripting/running-examples.html#passing-a-block-as-a-string-literal-to-execute-immediate) – Lukasz Szozda Aug 16 '22 at 19:45
I am using this operator in Airflow to execute SQL file https://airflow.apache.org/docs/apache-airflow-providers-snowflake/stable/_modules/airflow/providers/snowflake/operators/snowflake.html#get_db_hook:~:text=%5Bdocs%5D-,class,-SnowflakeOperator(BaseOperator – Kar Aug 16 '22 at 20:09
1

@Kar Could you please do not edit question in place? Right now the question does not resembles the original one. If you need to add more details then do it explicitly as next section instead of overwrting. – Lukasz Szozda Aug 16 '22 at 20:16
Sure I will keep in mind next time, and avoid such – Kar Aug 16 '22 at 20:21

Airflow:SnowflakeOperator:Assign values from previous statement in SQL query

2 Answers2