You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by GitBox <gi...@apache.org> on 2021/11/05 14:13:50 UTC
[GitHub] [airflow] ashb opened a new pull request #19425: Fix moving of dangling TaskInstance rows for SQL Server
ashb opened a new pull request #19425:
URL: https://github.com/apache/airflow/pull/19425
SQL server uses a different syntax for creating a table from a select to
the other DBs we support.
And to make the "where_query" reusable across all DBs (SQL Server
doesn't support `WHERE (col1,col2) IN ...`) the delete has been
re-written too.
<!--
Thank you for contributing! Please make sure that your code changes
are covered with tests. And in case of new features or big changes
remember to adjust the documentation.
Feel free to ping committers for the review!
In case of existing issue, reference it using one of the following:
closes: #ISSUE
related: #ISSUE
How to write a good git commit message:
http://chris.beams.io/posts/git-commit/
-->
---
**^ Add meaningful description above**
Read the **[Pull Request Guidelines](https://github.com/apache/airflow/blob/main/CONTRIBUTING.rst#pull-request-guidelines)** for more information.
In case of fundamental code change, Airflow Improvement Proposal ([AIP](https://cwiki.apache.org/confluence/display/AIRFLOW/Airflow+Improvements+Proposals)) is needed.
In case of a new dependency, check compliance with the [ASF 3rd Party License Policy](https://www.apache.org/legal/resolved.html#category-x).
In case of backwards incompatible changes please leave a note in [UPDATING.md](https://github.com/apache/airflow/blob/main/UPDATING.md).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] kaxil commented on a change in pull request #19425: Fix moving of dangling TaskInstance rows for SQL Server
Posted by GitBox <gi...@apache.org>.
kaxil commented on a change in pull request #19425:
URL: https://github.com/apache/airflow/pull/19425#discussion_r743700766
##########
File path: airflow/utils/db.py
##########
@@ -709,18 +709,63 @@ def _format_dangling_error(source_table, target_table, invalid_count, reason):
)
-def _move_dangling_run_data_to_new_table(session, source_table, target_table):
+def _move_dangling_run_data_to_new_table(session, source_table: "Table", target_table_name: str):
where_clause = "where dag_id is null or run_id is null or execution_date is null"
- session.execute(text(f"create table {target_table} as select * from {source_table} {where_clause}"))
- session.execute(text(f"delete from {source_table} {where_clause}"))
+ _move_dangling_table(session, source_table, target_table_name, where_clause)
+
+
+def _move_dangling_table(session, source_table: "Table", target_table_name: str, where_clause: str):
+ dialect_name = session.get_bind().dialect.name
+
+ delete_where = " AND ".join(
+ f"{source_table.name}.{c.name} = d.{c.name}" for c in source_table.primary_key.columns
+ )
+ if dialect_name == "mssql":
+ session.execute(
+ text(f"select source.* into {target_table_name} from {source_table} as source {where_clause}")
+ )
+ session.execute(
+ text(
+ f"delete from {source_table} from {source_table} join {target_table_name} AS d ON "
+ + delete_where
+ )
+ )
+ else:
+ # Postgres, MySQL and SQLite all have the same CREATE TABLE a AS SELECT ... syntax
+ session.execute(
+ text(
+ f"create table {target_table_name} as select source.* from {source_table} as source "
+ + where_clause
+ )
+ )
+
+ # But different join-delete syntax.
+ if dialect_name == "mysql":
+ session.execute(
+ text(
+ f"delete {source_table} from {source_table} join {target_table_name} as d on "
+ + delete_where
Review comment:
```suggestion
+ {delete_where}
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] kaxil commented on a change in pull request #19425: Fix moving of dangling TaskInstance rows for SQL Server
Posted by GitBox <gi...@apache.org>.
kaxil commented on a change in pull request #19425:
URL: https://github.com/apache/airflow/pull/19425#discussion_r743700766
##########
File path: airflow/utils/db.py
##########
@@ -709,18 +709,63 @@ def _format_dangling_error(source_table, target_table, invalid_count, reason):
)
-def _move_dangling_run_data_to_new_table(session, source_table, target_table):
+def _move_dangling_run_data_to_new_table(session, source_table: "Table", target_table_name: str):
where_clause = "where dag_id is null or run_id is null or execution_date is null"
- session.execute(text(f"create table {target_table} as select * from {source_table} {where_clause}"))
- session.execute(text(f"delete from {source_table} {where_clause}"))
+ _move_dangling_table(session, source_table, target_table_name, where_clause)
+
+
+def _move_dangling_table(session, source_table: "Table", target_table_name: str, where_clause: str):
+ dialect_name = session.get_bind().dialect.name
+
+ delete_where = " AND ".join(
+ f"{source_table.name}.{c.name} = d.{c.name}" for c in source_table.primary_key.columns
+ )
+ if dialect_name == "mssql":
+ session.execute(
+ text(f"select source.* into {target_table_name} from {source_table} as source {where_clause}")
+ )
+ session.execute(
+ text(
+ f"delete from {source_table} from {source_table} join {target_table_name} AS d ON "
+ + delete_where
+ )
+ )
+ else:
+ # Postgres, MySQL and SQLite all have the same CREATE TABLE a AS SELECT ... syntax
+ session.execute(
+ text(
+ f"create table {target_table_name} as select source.* from {source_table} as source "
+ + where_clause
+ )
+ )
+
+ # But different join-delete syntax.
+ if dialect_name == "mysql":
+ session.execute(
+ text(
+ f"delete {source_table} from {source_table} join {target_table_name} as d on "
+ + delete_where
Review comment:
```suggestion
+ {delete_where}
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] github-actions[bot] commented on pull request #19425: Fix moving of dangling TaskInstance rows for SQL Server
Posted by GitBox <gi...@apache.org>.
github-actions[bot] commented on pull request #19425:
URL: https://github.com/apache/airflow/pull/19425#issuecomment-961974048
The PR most likely needs to run full matrix of tests because it modifies parts of the core of Airflow. However, committers might decide to merge it quickly and take the risk. If they don't merge it quickly - please rebase it to the latest main at your convenience, or amend the last commit of the PR, and push it with --force-with-lease.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ashb merged pull request #19425: Fix moving of dangling TaskInstance rows for SQL Server
Posted by GitBox <gi...@apache.org>.
ashb merged pull request #19425:
URL: https://github.com/apache/airflow/pull/19425
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] kaxil commented on a change in pull request #19425: Fix moving of dangling TaskInstance rows for SQL Server
Posted by GitBox <gi...@apache.org>.
kaxil commented on a change in pull request #19425:
URL: https://github.com/apache/airflow/pull/19425#discussion_r743700766
##########
File path: airflow/utils/db.py
##########
@@ -709,18 +709,63 @@ def _format_dangling_error(source_table, target_table, invalid_count, reason):
)
-def _move_dangling_run_data_to_new_table(session, source_table, target_table):
+def _move_dangling_run_data_to_new_table(session, source_table: "Table", target_table_name: str):
where_clause = "where dag_id is null or run_id is null or execution_date is null"
- session.execute(text(f"create table {target_table} as select * from {source_table} {where_clause}"))
- session.execute(text(f"delete from {source_table} {where_clause}"))
+ _move_dangling_table(session, source_table, target_table_name, where_clause)
+
+
+def _move_dangling_table(session, source_table: "Table", target_table_name: str, where_clause: str):
+ dialect_name = session.get_bind().dialect.name
+
+ delete_where = " AND ".join(
+ f"{source_table.name}.{c.name} = d.{c.name}" for c in source_table.primary_key.columns
+ )
+ if dialect_name == "mssql":
+ session.execute(
+ text(f"select source.* into {target_table_name} from {source_table} as source {where_clause}")
+ )
+ session.execute(
+ text(
+ f"delete from {source_table} from {source_table} join {target_table_name} AS d ON "
+ + delete_where
+ )
+ )
+ else:
+ # Postgres, MySQL and SQLite all have the same CREATE TABLE a AS SELECT ... syntax
+ session.execute(
+ text(
+ f"create table {target_table_name} as select source.* from {source_table} as source "
+ + where_clause
+ )
+ )
+
+ # But different join-delete syntax.
+ if dialect_name == "mysql":
+ session.execute(
+ text(
+ f"delete {source_table} from {source_table} join {target_table_name} as d on "
+ + delete_where
Review comment:
```suggestion
+ {delete_where}
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] github-actions[bot] commented on pull request #19425: Fix moving of dangling TaskInstance rows for SQL Server
Posted by GitBox <gi...@apache.org>.
github-actions[bot] commented on pull request #19425:
URL: https://github.com/apache/airflow/pull/19425#issuecomment-961974048
The PR most likely needs to run full matrix of tests because it modifies parts of the core of Airflow. However, committers might decide to merge it quickly and take the risk. If they don't merge it quickly - please rebase it to the latest main at your convenience, or amend the last commit of the PR, and push it with --force-with-lease.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ashb merged pull request #19425: Fix moving of dangling TaskInstance rows for SQL Server
Posted by GitBox <gi...@apache.org>.
ashb merged pull request #19425:
URL: https://github.com/apache/airflow/pull/19425
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] ashb merged pull request #19425: Fix moving of dangling TaskInstance rows for SQL Server
Posted by GitBox <gi...@apache.org>.
ashb merged pull request #19425:
URL: https://github.com/apache/airflow/pull/19425
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] github-actions[bot] commented on pull request #19425: Fix moving of dangling TaskInstance rows for SQL Server
Posted by GitBox <gi...@apache.org>.
github-actions[bot] commented on pull request #19425:
URL: https://github.com/apache/airflow/pull/19425#issuecomment-961974048
The PR most likely needs to run full matrix of tests because it modifies parts of the core of Airflow. However, committers might decide to merge it quickly and take the risk. If they don't merge it quickly - please rebase it to the latest main at your convenience, or amend the last commit of the PR, and push it with --force-with-lease.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org