How to execute an SQL query from file in Airflow? (PostgresSQL operator)

Question

I am using PostgresSQL Operator. The task is the following:

emailage_transformations = PostgresOperator(
    task_id = 'emailage_transformations',
    sql = '/home/ubuntu/airflow_ci/current/scripts/antifraud/emailage_transformations.sql',
    postgres_conn_id = 'redshift',
    autocommit = True,
    dag = dag)

At first, the content of the file was the next:

select cd_pedido_nr,fraud_score,risk_band,ip_risk_level
into antifraud.stg_emailage_id_pedido
from antifraud.stg_emailage_id_email e
left join antifraud.info_emails i on id_email = cd_email_nr
;

And the error I got was

jinja2.exceptions.TemplateNotFound: /home/ubuntu/airflow_ci/current/scripts/antifraud/emailage_transformations.sql

So I have added a couple of brackets to the query for complying with jinja2 templating and now the file code is:

{select cd_pedido_nr,fraud_score,risk_band,ip_risk_level
into antifraud.stg_emailage_id_pedido
from antifraud.stg_emailage_id_email e
left join antifraud.info_emails i on id_email = cd_email_nr
;}

However, I still have the same error. How could I solve it?

Answer 1

I recon as told in following links, you ought to provide a template_searchpath to your DAG so as to enable it picking your external files (SQL or other files)

• External files in Airflow DAG
• How to pass SQL as file with parameters to Airflow Operator
• Template_searchpath gives TemplateNotFound error in Airflow and cannot find the SQL script

Alternatively making external file discoverable, such as by modifying AIRFLOW_HOME or through other tricks can also work