Hi!
I have a DAG of three tasks. The first one checks if csv file exists, the second creates MySQL table and the third one inserts the data from csv file into MySQL table. First two tasks succeed but the last one not saying that due to secure-file-priv option I have no rigts to insert data from file to database.
default_args = {
"owner": "airflow",
"start_date": airflow.utils.dates.days_ago(1),
"retries": 1,
"retry_delay": timedelta(seconds=5)
}
with DAG('sql_operator_from_csv_to_mysql',default_args=default_args,schedule_interval='@daily', template_searchpath=['/opt/airflow/sql_files_mysql'], catchup=True) as dag:
t1 = BashOperator(task_id='check_file_exists', bash_command='ls /opt/airflow/store_files/students_data.csv | sha1sum', retries=2, retry_delay=timedelta(seconds=15))
t2 = MySqlOperator(task_id='create_mysql_table', mysql_conn_id="mysql_conn", sql="create_table.sql")
t3 = MySqlOperator(task_id='insert_into_table', mysql_conn_id="mysql_conn", sql="insert_into_table.sql")
SQL statement:
LOAD DATA INFILE '/store_files/students_data.csv' INTO TABLE students_db FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
mysql.cnf file is as following.
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= ""
I have tried to mark ‘secure-file-priv’ option as an empty string and showing the location of a csv file but none of them are working.
secure-file-priv= ""
secure-file-priv= "/opt/airflow/store_files"
Error message saying:
[2022-06-12, 09:44:24 UTC] {taskinstance.py:1774} ERROR - Task failed with exception
Traceback (most recent call last):
File "/home/airflow/.local/lib/python3.7/site-packages/airflow/providers/mysql/operators/mysql.py", line 84, in execute
hook.run(self.sql, autocommit=self.autocommit, parameters=self.parameters)
File "/home/airflow/.local/lib/python3.7/site-packages/airflow/hooks/dbapi.py", line 205, in run
self._run_command(cur, sql_statement, parameters)
File "/home/airflow/.local/lib/python3.7/site-packages/airflow/hooks/dbapi.py", line 229, in _run_command
cur.execute(sql_statement)
File "/home/airflow/.local/lib/python3.7/site-packages/MySQLdb/cursors.py", line 206, in execute
res = self._query(query)
File "/home/airflow/.local/lib/python3.7/site-packages/MySQLdb/cursors.py", line 319, in _query
db.query(q)
File "/home/airflow/.local/lib/python3.7/site-packages/MySQLdb/connections.py", line 254, in query
_mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1290, 'The MySQL server is running with the --secure-file-priv option so it cannot execute this statement')
[2022-06-12, 09:44:24 UTC] {taskinstance.py:1288} INFO - Marking task as FAILED. dag_id=sql_operator_from_csv_to_mysql, task_id=insert_into_table, execution_date=20220612T094413, start_date=20220612T094424, end_date=20220612T094424
[2022-06-12, 09:44:24 UTC] {standard_task_runner.py:98} ERROR - Failed to execute job 10 for task insert_into_table ((1290, 'The MySQL server is running with the --secure-file-priv option so it cannot execute this statement'); 1190)
[2022-06-12, 09:44:24 UTC] {local_task_job.py:154} INFO - Task exited with return code 1
[2022-06-12, 09:44:24 UTC] {local_task_job.py:264} INFO - 0 downstream tasks scheduled from follow-on schedule check
Any ideas to solve this problem?