2

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?

Javier López Tomás
  • 1,063
  • 1
  • 9
  • 24

1 Answers1

4

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)

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

y2k-shubham
  • 6,703
  • 7
  • 39
  • 85
  • 1
    The default template searchpath is the folder where the dag itself is stored, so if the file is in the same folder than the DAG, you don't have to provide a template searchpath – Javier López Tomás Jul 09 '20 at 05:37