Passing macros value to sql file in airflow

Question

I have a sql file, having a sql query :-

delete from xyz where id in = 3 and time = '{{ execution_date.subtract(hours=2).strftime("%Y-%m-%d %H:%M:%S") }}';

Here I am writing macro in sql query itself, I want to pass it's value from python file where the operator is calling this sql query.

time = f'\'{{{{ execution_date.subtract(hours= {value1}).strftime("%Y-%m-%d %H:%M:%S") }}}}\''

I want to pass this global time variable to sql file instead of writing the complete macro there again.

PostgresOperator(dag=dag,
                 task_id='delete_entries', 
                 postgres_conn_id='database_connection',
                 sql='sql/delete_entry.sql')

if I use time in query using jinja template as {{ time }}, instead of evaluating it, it is passed as a complete string only. Please help, stuck on this for long.

Just a suggestion to use eval while evaluating "time" . Not really sure if that would work but you can give it a shot. Also, try to use a different variable name as "time" is also internal and might cause unexpected behaviour. — Priya Agarwal
– Priya Agarwal, Commented Jun 9, 2021 at 5:43
Sure will try your suggestion, I used time just while asking question, in real code it's something else. — Arpit Pruthi
– Arpit Pruthi, Commented Jun 9, 2021 at 5:52
If I understand you correctly you want to use time in two operators thus you want to avoid defining it twice? — Elad Kalif
– Elad Kalif, Commented Jun 9, 2021 at 6:38

Elad Kalif · Accepted Answer · 2021-08-08 07:49:03Z

1

Since you want to use f'\'{{{{ execution_date.subtract(hours= {value1}).strftime("%Y-%m-%d %H:%M:%S") }}}}\'' in two operators without duplicating the code you can define it as user macro.

from datetime import datetime
from airflow import DAG
from airflow.providers.postgres.operators.postgres import PostgresOperator


def ds_macro_format(execution_date, hours):
    return execution_date.subtract(hours=hours).strftime("%Y-%m-%d %H:%M:%S")


user_macros = {
    'format': ds_macro_format
}

default_args = {
    'owner': 'airflow',
    'start_date': datetime(2021, 6, 7),
}

dag = DAG(
    "stackoverflow_question1",
    default_args=default_args,
    schedule_interval="@daily",
    user_defined_macros=user_macros
)

PostgresOperator(dag=dag,
                 task_id='delete_entries',
                 postgres_conn_id='database_connection',
                 sql='sql/delete_entry.sql')

and the delete_entry.sql as:

delete from xyz where id in = 3 and time = {{ format(execution_date, hours=2) }};

Lets say you want also to use the macro in BashOperator you can do:

BashOperator(
    task_id='bash_task',
    bash_command='echo {{ format(execution_date, hours=2) }} ',
    dag=dag,
)

edited Aug 8, 2021 at 7:49

answered Jun 9, 2021 at 8:02

Elad Kalif

16.4k3 gold badges29 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Arpit Pruthi Over a year ago

execution date itself is a macro {{ execution_date.subtract(hours=hours).strftime("%Y-%m-%d %H:%M:%S") }}. It should not be passed as a variable and that python function returns this template as a string instead of evaluating it, you can check it once.

Elad Kalif Over a year ago

Not sure rhat I follow what is your issue

Arpit Pruthi Over a year ago

I am saying execution date is not a variable that I want to use, it's a macro. '{{ execution_date }}'. Python function will return this itself as a string instead of evaluating the template.

Elad Kalif Over a year ago

I really don't understand what you are asking. Your question was you set {{ time }} and what you saw in the sql code is {{ time }} instead of the evaluation of it. I provided a solution for this problem so if it's not answering your issue then your question isn't well defined. Please edit your question and explain what you are really after.

Collectives™ on Stack Overflow

Passing macros value to sql file in airflow

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related