Can we include custom sql scripts to run in Airflow deployment?

Admin 1
Viewed 552

I am trying to run a airflow job to perform some SQL operations on Snowflake instance using MWAA(managed Airflow from AWS). While setting up the Airflow in MWAA its asks for DAG, plugin and requirements. Is there any way to include custom sql scripts?

Below is the example that I have tried:


with DAG(
    'Snowflake_Prototype',
    default_args=default_args,
    schedule_interval='@daily',
    start_date=days_ago(1),
    template_searchpath="include"
) as dag:

    start = DummyOperator(task_id='start')
    
    t2 = SnowflakeOperator(
            task_id='create_staging_tables',
            snowflake_conn_id=SNOWFLAKE_CONN_ID,
            sql='create_staging.sql'
        )

I am planning to run sql command in creating_staging.sql. For which, I have uploaded sql script "create_staging.sql" to a folder called include in s3 and then referenced it airflow via template_searchpath in DAG declaration. Looks like its not airflow is not importing all the contents of the s3. It does not seem to locate the file. Is there any other way to include the relevant scripts when running airflow via MWAA.

mwaa airflow
2 Answers

If you refer to the create_staging.sql like that - with relative reference, I think it should be placed next to the dag itself, not in a dedicated "include" folder. So just place it in the same directory where you place your dags.

Alternatively you might try to find the full path of where the "include" folder is mounted eventually in MWAA worker and use full "absolute" path.

user_5167010

You can add the files by providing template_searchpath as mentioned below. template_searchpath='/usr/local/airflow/dags/[ your base folder which includes sql].

Ex: /usr/local/airflow/dags/load/sql/

user_38581930
Related