External files in Airflow DAG

Here is an example use Variable to make it easy.

• First add Variable in Airflow UI -> Admin -> Variable, eg. {key: 'sql_path', values: 'your_sql_script_folder'}

• Then add following code in your DAG, to use Variable from Airflow you just add.

DAG code:

import airflow
from airflow.models import Variable

tmpl_search_path = Variable.get("sql_path")

dag = airflow.DAG(
   'tutorial',
    schedule_interval="@daily",
    template_searchpath=tmpl_search_path,  # this
    default_args=default_args
)

• Now you can use sql script name or path under folder Variable

• You can learn more in this

Assuming that the sql directory is relative to the current Python file, you can figure out the absolute path to the sql file like this:

import os

CUR_DIR = os.path.abspath(os.path.dirname(__file__))

def run_query():
    # read the query
    query = open(f"{CUR_DIR}/sql/queryfile.sql")
    # run the query
    execute(query)

All relative paths are taken in reference to the AIRFLOW_HOME environment variable. Try:

• Giving absolute path
• place the file relative to AIRFLOW_HOME
• try logging the PWD in the python callable and then decide what path to give (Best option)