You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by "flolas (via GitHub)" <gi...@apache.org> on 2023/09/24 11:29:05 UTC
[GitHub] [airflow] flolas opened a new issue, #34583: Support split_statements AthenaOperator [Amazon provider]
flolas opened a new issue, #34583:
URL: https://github.com/apache/airflow/issues/34583
### Description
While Apache Airflow currently offers the [SQLExecuteQueryOperator](https://airflow.apache.org/docs/apache-airflow-providers-common-sql/stable/operators.html), which allows users to execute a list of SQL statements using DBApi Hook, this functionality in missing in AthenaOperator.Integrating this feature into AthenaOperator would be highly beneficial.
Another alternative would be to use [PyAthena](https://github.com/laughingman7743/PyAthena/) in conjunction with DBApiHook, but this would introduce an additional dependency.
### Use case/motivation
There are scenarios where we have multiple SQL statements that need to be executed in sequence during each retry to maintain idempotency. AWS Athena's API function StartQueryExecution does not currently support the execution of multiple SQL statements. However, this functionality is available when using the AWS CLI for Athena. Incorporating this feature into AthenaOperator would streamline this process and enhance its utility.
### Related issues
_No response_
### Are you willing to submit a PR?
- [X] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.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.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] Taragolis commented on issue #34583: Support split_statements AthenaOperator [Amazon provider]
Posted by "Taragolis (via GitHub)" <gi...@apache.org>.
Taragolis commented on issue #34583:
URL: https://github.com/apache/airflow/issues/34583#issuecomment-1733519959
My point we need to try to keep feature requests as simple as possible, otherwise it make quite a few options for implementations as result in the end we might have Operator which could do everything but very hard to support/fix
If you already have an idea how to improve AthenaOperator or add new Hook, you might create PR, in most cases better discuss implementation in the PR rather then in GH Issue - that is not something new, when discussion in the issue do not turn into the implementation
BTW, it is already exists multi-statement feature requests for AthenaOperator: https://github.com/apache/airflow/issues/8449
--
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
Re: [I] Support split_statements AthenaOperator [Amazon provider] [airflow]
Posted by "eladkal (via GitHub)" <gi...@apache.org>.
eladkal commented on issue #34583:
URL: https://github.com/apache/airflow/issues/34583#issuecomment-1761641778
> Another alternative would be to use [PyAthena](https://github.com/laughingman7743/PyAthena/) in conjunction with DBApiHook, but this would introduce an additional dependency.
we have specific issue for this https://github.com/apache/airflow/issues/34823
--
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
Re: [I] Support split_statements AthenaOperator [Amazon provider] [airflow]
Posted by "vincbeck (via GitHub)" <gi...@apache.org>.
vincbeck commented on issue #34583:
URL: https://github.com/apache/airflow/issues/34583#issuecomment-1757957768
@flolas Are you working on this one? I am just checking 👌 There is no rush
--
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] flolas commented on issue #34583: Support split_statements AthenaOperator [Amazon provider]
Posted by "flolas (via GitHub)" <gi...@apache.org>.
flolas commented on issue #34583:
URL: https://github.com/apache/airflow/issues/34583#issuecomment-1732598175
@hussein-awala I see two use cases:
* Multiple SQL statements need to be run sequentially in a single operator, if a sql statements takes too long, would be good to wait async with a trigger **[current scope of this issue]**
* There's a need to process the results of the queries directly at the worker level.
The existing AthenaOperator only waits for the query to complete; it doesn't retrieve or process the results.
To address these cases, I propose two potential implementations:
**1. AthenaOperator and AthenaHook with split_statements argument:** This would be ideal for running multiple SQL queries sequentially without returning results to the worker. The feature would also must support asynchronous waiting. As per standard practice, idempotency would be defined at the operator level.
**2. Extension of AthenaOperator and Introduction of AthenaDbApiHook:** The AthenaOperator could be extended based on BaseSQLOperator, and a new hook, AthenaDbApiHook, could be introduced. This hook would be built on top of both PyAthena and AwsBaseHook. This approach allows the AthenaOperator to be compatible with both AthenaHook and AthenaDbApiHook, making it highly versatile for addressing the two cases.
--
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
Re: [I] Support split_statements AthenaOperator [Amazon provider] [airflow]
Posted by "flolas (via GitHub)" <gi...@apache.org>.
flolas commented on issue #34583:
URL: https://github.com/apache/airflow/issues/34583#issuecomment-1758489883
@vincbeck yes, i hope to send a PR in the weekend.
--
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] hussein-awala commented on issue #34583: Support split_statements AthenaOperator [Amazon provider]
Posted by "hussein-awala (via GitHub)" <gi...@apache.org>.
hussein-awala commented on issue #34583:
URL: https://github.com/apache/airflow/issues/34583#issuecomment-1732584183
Indeed Athena has some packages with DBApi support, feel free to open a PR which add a new operator for Athena based on the `SQLExecuteQueryOperator`
--
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
Re: [I] Support split_statements AthenaOperator [Amazon provider] [airflow]
Posted by "flolas (via GitHub)" <gi...@apache.org>.
flolas commented on issue #34583:
URL: https://github.com/apache/airflow/issues/34583#issuecomment-1762259858
@eladkal, if it's acceptable to add PyAthena as a dependency in the provider, it would be preferable to support statement splitting in that implementation rather than in a boto3-based one (the current AthenaHook).
Looking forward to your PR, @treyyi. Happy to assist if needed.
--
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] Taragolis commented on issue #34583: Support split_statements AthenaOperator [Amazon provider]
Posted by "Taragolis (via GitHub)" <gi...@apache.org>.
Taragolis commented on issue #34583:
URL: https://github.com/apache/airflow/issues/34583#issuecomment-1733199882
> The existing AthenaOperator only waits async/sync for the query to complete; it doesn't retrieve or process the results.
`AthenaHook` is wrapper around `boto3`, which are not DBApi2 complaint, so if required reuse `SQLExecuteQueryOperator` then basically need to create separate hook. By the same way as it implemented for Redshift:
- [`RedshiftDataHook`](https://airflow.apache.org/docs/apache-airflow-providers-amazon/stable/_modules/airflow/providers/amazon/aws/hooks/redshift_data.html#RedshiftDataHook) wrapper around `boto3`
- [`RedshiftSQLHook`](https://airflow.apache.org/docs/apache-airflow-providers-amazon/stable/_modules/airflow/providers/amazon/aws/hooks/redshift_sql.html#RedshiftSQLHook) wrapper around [`redshift_connector`](https://github.com/aws/amazon-redshift-python-driver) and [`sqlalchemy_redshift`](https://github.com/sqlalchemy-redshift/sqlalchemy-redshift)
> SQLExecuteQueryOperator does not provide a trigger for async wait of query executions.
Most of the drivers which are used are sync, and many of dialects have only provided sync implementation
> To address these cases, I propose two potential implementations
In general it could be a two independent implementation
--
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
Re: [I] Support split_statements AthenaOperator [Amazon provider] [airflow]
Posted by "flolas (via GitHub)" <gi...@apache.org>.
flolas closed issue #34583: Support split_statements AthenaOperator [Amazon provider]
URL: https://github.com/apache/airflow/issues/34583
--
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] flolas commented on issue #34583: Support split_statements AthenaOperator [Amazon provider]
Posted by "flolas (via GitHub)" <gi...@apache.org>.
flolas commented on issue #34583:
URL: https://github.com/apache/airflow/issues/34583#issuecomment-1733478860
@Taragolis Yes, that's basically what I was proposing. Also, as you mentioned, Redshift implementation has the pattern.
--
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