CODEWITHSUNDEEP
pythonairflow
Beppe C
Beppe C

Reputation: 13963

Airflow PostgresOperator only finds SQL file in the same folder as the DAG

I am only able to run a SQL file if I place it in the same folder as the DAG:

create_tables = PostgresOperator(
task_id="create_tables",
dag=dag,
postgres_conn_id="redshift",
sql="create_tables.sql"
)

If the create_tables.sql is in a different folder (parent folder, sibling folder) PostgresOperator throws jinja2.exceptions.TemplateNotFound, I have tried absolute path, relative paths (../create_tables.sql) but no luck.

How can I understand the file loading mechanism of this operator?

Upvotes: 3

Views: 1940

Answers (1)

Javier Lopez Tomas
Javier Lopez Tomas

Reputation: 2352

When defining the dag you have to add as argument template_searchpath, which is an absolute path of the folder. Then you just have to call the name of the file. Example:

with DAG('retraining_sagemaker',
         default_args=default_args,
         schedule_interval = timedelta(days=21),
         template_searchpath = ['/home/ubuntu/airflow_ci/current/scripts/antifraud']
         ) as dag:


    start = DummyOperator(
            task_id = 'start')

    generate_train_and_test_tables = PostgresOperator(
        task_id = 'generate_train_and_test_tables',
        sql = ['generate_train_and_test_tables.sql'],
        postgres_conn_id = 'redshift',
        autocommit = True)

Upvotes: 6

Related Questions