0

I am facing a strange error in airflow. I try to remove the Xcom variables for a task within a certain execution-date; to create and use another instance of the Xcomvariable for the same task.

Why do I want to do this? Because the value of this Xcom variable changes during the day, and sometimes I notice a conflict, the first value is used, which messes up my workflows. I'd be happy to explain with examples if needed.

When I execute this query :

SELECT * FROM xcom WHERE dag_id='datamart_integration.tracking_marketing' AND TASK_ID='find_file' AND execution_date LIKE '%2020-12-14%'

I get 2 rows of data.

id  key value   timestamp   execution_date  task_id dag_id
4969    return_value    b'["DTM_DATALAKE_TRACKING_MARKETING_20201214_20201214060143.zip"]'  2020-12-14 10:22:50.085745  2020-12-14 05:40:38 find_file   datamart_integration.tracking_marketing
5139    return_value    b'["DTM_DATALAKE_TRACKING_MARKETING_20201214_20201214060143.zip", "DTM_DATALAKE_TRACKING_MARKETING_20201214_20201214130326.zip"]'   2020-12-14 16:50:55.596651  2020-12-14 13:35:44 find_file   datamart_integration.tracking_marketing

But when I execute this query :

DELETE FROM xcom WHERE dag_id='datamart_integration.tracking_marketing' AND TASK_ID='find_file' AND execution_date LIKE '%2020-12-14%'

I get

Execution failed on sql 'DELETE FROM xcom WHERE dag_id='datamart_integration.tracking_marketing' AND TASK_ID='find_file' AND execution_date LIKE '%2020-12-14%'': (1292, "Incorrect datetime value: '%2020-12-14%' for column 'execution_date' at row 1")

But I think the real problem occurs when I try these queries :

DELETE FROM xcom WHERE dag_id='datamart_integration.tracking_marketing' AND TASK_ID='find_file' AND execution_date BETWEEN '2020-12-14 00:00:00' AND '2020-12-14 23:59:59'

DELETE FROM xcom WHERE dag_id='datamart_integration.tracking_marketing' AND TASK_ID='find_file' AND execution_date LIKE '2020-12-14%'

I get

'NoneType' object is not iterable

No data

I'm thinking perhaps the airflow database is protected from deletion/insertion unless specified somewhere, and somehow? But then these errors would be confusing.

The airflow_db is initiated with MySQL, and I'm working on composer.

Ismail
  • 1,068
  • 1
  • 6
  • 11
Imad
  • 2,358
  • 5
  • 26
  • 55
  • https://stackoverflow.com/questions/46707132/how-to-delete-xcom-objects-once-the-dag-finishes-its-run-in-airflow didn't solve your issue? – Elad Kalif Dec 15 '20 at 19:06
  • @Elad well no, the solution `execution_date = {{ds}}` would translate to `execution_date=2020-12-14` which returns nothing for the `SELECT` statement and `'NoneType' object is not iterable` for the `DELETE` statement – Imad Dec 15 '20 at 19:12
  • Well, I tried the second solution, by going through the Xcom library programmatically, and it seems to be working. – Imad Dec 16 '20 at 09:06

1 Answers1

1

I was able to replicate this exact same error, I assume you were using Adhoc Queries from Data Profiling in Airflow's UI.

While not strictly specified in the Airflow documentation (at least not that I've found), I assume that DML statements aren't allowed to run from there; as described in the docs:

The adhoc query UI allows for simple SQL interactions with the database connections registered in Airflow

I understand that Adhoc Queries are only meant to test connections. That's probably why SELECT statements work fine, but DELETE statements throw those errors even if the query is correct.

I can confirm that running the DELETE statements within a DAG, either from a MySqlOperator or through the xcom library, as you've mentioned in the comment, works fine.

Fcojavmelo
  • 368
  • 1
  • 11