[GitHub] [airflow] pohek321 opened a new issue #22390: RedshiftSQLOperator is limited to executing one sql statement

[GitBox <gi...@apache.org>]

pohek321 opened a new issue #22390:
URL: https://github.com/apache/airflow/issues/22390


   ### Apache Airflow Provider(s)
   
   amazon
   
   ### Versions of Apache Airflow Providers
   
   apache-airflow-providers-amazon==3.0.0
   
   ### Apache Airflow version
   
   2.2.4 (latest released)
   
   ### Operating System
   
   Debian GNU/Linux
   
   ### Deployment
   
   Astronomer
   
   ### Deployment details
   
   Using the `astrocloud` cli. Image from dockerfile is:
   
   ```FROM quay.io/astronomer/astro-runtime:4.2.0```
   
   In `requirements.txt`, I've downloaded the amazon provider using:
   
   ```apache-airflow-providers-amazon==3.0.0```
   
   ### What happened
   
   According to the Astronomer Registry, the [RedshiftSQLOperator](https://registry.astronomer.io/providers/amazon/modules/redshiftsqloperator) should work if you pass a templated file to it with multiple SQL statements. Unfortunately, that is not the case. When doing so, the RedshiftSQLOperator returns the following error:
   
     ```redshift_connector.error.ProgrammingError: {'S': 'ERROR', 'C': '42601', 'M': 'cannot insert multiple commands into a prepared statement', 'F': '../src/pg/src/backend/tcop/***.c', 'L': '3065', 'R': 'exec_parse_message'}```
   
   
   ### What you think should happen instead
   
   In the `sql` parameter of a RedshiftSQLOperator, the user should be able to pass multiple statements that get executed as a transaction against a redshift database
   
   ### How to reproduce
   
   Assuming you have a sandbox and access to a redshift database, create a sql script that has multiple sql statements in it and call it with the RedshiftSQLOperator. Your DAG will look something like [this gist](https://gist.github.com/pohek321/5d3332f350d5442c56550614a0007cf7). Assuming your `fct_listing.sql` file has multiple statements in it, you will get the error message listed above.
   
   ### Anything else
   
   I did find on a github forum that the execution of multi-statement .SQL files wasn't supported for the [redshift_connector](https://pypi.org/project/redshift-connector/) and their work around to the problem included using [sqlparse](https://pypi.org/project/sqlparse/) to separate each statement passed to the connector. Click [here](https://github.com/aws/amazon-redshift-python-driver/issues/39) for more details on that. I'm planning on implementing said solution in a PR.
   
   ### Are you willing to submit 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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airflow] pohek321 commented on issue #22390: RedshiftSQLOperator is limited to executing one sql statement

[GitBox <gi...@apache.org>]

pohek321 commented on issue #22390:
URL: https://github.com/apache/airflow/issues/22390#issuecomment-1073432244


   Opened [PR 22391](https://github.com/apache/airflow/pull/22391)


-- 
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