使用 Airflow MySQL Operator 运行 MySQL 脚本时出现问题 [英] Problem Running MySQL Script with Airflow MySQL Operator

问题描述

我对使用 MySQL 操作符和 Apache Airflow 调用 MySQL 脚本不太了解.

I'm not understanding something about using the MySQL operator to call and a MySQL script with Apache Airflow.

当我运行这个任务时...

When I run this task...

    MySQLTest    = MySqlOperator(
                                task_id='MySQLTest',
                                sql = '/home/user/DBScripts/MySQLScript/SampleMySQLScript.sql',
                                mysql_conn_id = 'mysql_db_connect',                                
                                autocommit = True, 
                                database = 'segments' 
                                )

我在回溯中遇到此错误...

I get this error in traceback...

jinja2.exceptions.TemplateNotFound: /home/user/DBScripts/MySQLScript/SampleMySQLScript.sql

如果我提供整个 SQL 脚本作为参数，则 DAG 任务运行良好.

The DAG task runs fine if I provide the entire SQL script as a parameter.

我不熟悉 Jinja 模板.

I'm not familiar with Jinja templating.

学习将我的脚本编写为 Jinja 模板是否更容易?我应该导入脚本的文本并将其分配给我传递的变量吗?有没有办法编写 Airflow 任务，使其不期待 Jinja 模板?

Is it easier to learn to write my scripts as a Jinja template? Should I import the text of the script and assign it to a variable that I pass? Is there a way to write the Airflow task so that it isn't expecting a Jinja template?

推荐答案

此错误信息表示未找到 .sql 文件.

This error message means that the .sql file is not found.

使用:

MySQLTest = MySqlOperator(
    task_id='MySQLTest',
    sql='test.sql',
    mysql_conn_id='mysql_db_connect',
    autocommit=True,
    database='segments',
    dag=dag
)

test.sql 与 DAG 文件位于同一文件夹中的位置:

Where test.sql is located on the same folder as the DAG file works fine:

如果 .sql 文件的路径与 DAG 文件无关，您可以使用 template_searchpath 来定义 jinja 将查找的文件夹列表(非相关)用于模板.

If the path of the .sql file is not relative to the DAG file you can use template_searchpath to define the list of folders (non relative) where jinja will look for the templates.

因此您的代码可能如下所示:

So Your code could look like:

default_args = {  # pylint: disable=invalid-name
    'owner': 'airflow',
    'start_date': datetime(2020, 12, 03),
}
with DAG(
        dag_id='my_sql_dag',
        default_args=default_args,
        schedule_interval=None,
        template_searchpath=['/home/user/DBScripts/MySQLScript']
) as dag:
    MySQLTest = MySqlOperator(
        task_id='MySQLTest',
        sql='SampleMySQLScript.sql',
        mysql_conn_id='mysql_db_connect',
        autocommit=True,
        database='segments'
    )

这篇关于使用 Airflow MySQL Operator 运行 MySQL 脚本时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！