You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by vi...@apache.org on 2023/12/04 19:19:08 UTC

(airflow) branch main updated: Fix handling of single quotes in `RedshiftToS3Operator` (#35986)

This is an automated email from the ASF dual-hosted git repository.

vincbeck pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/airflow.git


The following commit(s) were added to refs/heads/main by this push:
     new 04a781666b Fix handling of single quotes in `RedshiftToS3Operator` (#35986)
04a781666b is described below

commit 04a781666be2955ed518780ea03bc13a1e3bd473
Author: Vincent <97...@users.noreply.github.com>
AuthorDate: Mon Dec 4 14:19:00 2023 -0500

    Fix handling of single quotes in `RedshiftToS3Operator` (#35986)
---
 .../amazon/aws/transfers/redshift_to_s3.py         |  5 +-
 .../amazon/aws/transfers/test_redshift_to_s3.py    | 82 ++++++++++++++++++++++
 2 files changed, 86 insertions(+), 1 deletion(-)

diff --git a/airflow/providers/amazon/aws/transfers/redshift_to_s3.py b/airflow/providers/amazon/aws/transfers/redshift_to_s3.py
index 47e997cd4f..6517e53f5e 100644
--- a/airflow/providers/amazon/aws/transfers/redshift_to_s3.py
+++ b/airflow/providers/amazon/aws/transfers/redshift_to_s3.py
@@ -18,6 +18,7 @@
 """Transfers data from AWS Redshift into a S3 Bucket."""
 from __future__ import annotations
 
+import re
 from typing import TYPE_CHECKING, Iterable, Mapping, Sequence
 
 from airflow.exceptions import AirflowException
@@ -141,8 +142,10 @@ class RedshiftToS3Operator(BaseOperator):
     def _build_unload_query(
         self, credentials_block: str, select_query: str, s3_key: str, unload_options: str
     ) -> str:
+        # Un-escape already escaped queries
+        select_query = re.sub(r"''(.+)''", r"'\1'", select_query)
         return f"""
-                    UNLOAD ('{select_query}')
+                    UNLOAD ($${select_query}$$)
                     TO 's3://{self.s3_bucket}/{s3_key}'
                     credentials
                     '{credentials_block}'
diff --git a/tests/providers/amazon/aws/transfers/test_redshift_to_s3.py b/tests/providers/amazon/aws/transfers/test_redshift_to_s3.py
index c9ab782f63..977975211a 100644
--- a/tests/providers/amazon/aws/transfers/test_redshift_to_s3.py
+++ b/tests/providers/amazon/aws/transfers/test_redshift_to_s3.py
@@ -217,6 +217,88 @@ class TestRedshiftToS3Transfer:
         assert secret_key in unload_query
         assert_equal_ignore_multiple_spaces(mock_run.call_args.args[0], unload_query)
 
+    @pytest.mark.parametrize(
+        "table_as_file_name, expected_s3_key, select_query, expected_query",
+        [
+            [
+                True,
+                "key/table_",
+                "SELECT 'Single Quotes Break this Operator'",
+                "SELECT 'Single Quotes Break this Operator'",
+            ],
+            [
+                False,
+                "key",
+                "SELECT 'Single Quotes Break this Operator'",
+                "SELECT 'Single Quotes Break this Operator'",
+            ],
+            [
+                True,
+                "key/table_",
+                "SELECT ''Single Quotes Break this Operator''",
+                "SELECT 'Single Quotes Break this Operator'",
+            ],
+            [
+                False,
+                "key",
+                "SELECT ''Single Quotes Break this Operator''",
+                "SELECT 'Single Quotes Break this Operator'",
+            ],
+            [False, "key", "SELECT ''", "SELECT ''"],
+        ],
+    )
+    @mock.patch("airflow.providers.amazon.aws.hooks.s3.S3Hook.get_connection")
+    @mock.patch("airflow.models.connection.Connection")
+    @mock.patch("boto3.session.Session")
+    @mock.patch("airflow.providers.amazon.aws.hooks.redshift_sql.RedshiftSQLHook.run")
+    def test_custom_select_query_unloading_with_single_quotes(
+        self,
+        mock_run,
+        mock_session,
+        mock_connection,
+        mock_hook,
+        table_as_file_name,
+        expected_s3_key,
+        select_query,
+        expected_query,
+    ):
+        access_key = "aws_access_key_id"
+        secret_key = "aws_secret_access_key"
+        mock_session.return_value = Session(access_key, secret_key)
+        mock_session.return_value.access_key = access_key
+        mock_session.return_value.secret_key = secret_key
+        mock_session.return_value.token = None
+        mock_connection.return_value = Connection()
+        mock_hook.return_value = Connection()
+        s3_bucket = "bucket"
+        s3_key = "key"
+        unload_options = ["HEADER"]
+
+        op = RedshiftToS3Operator(
+            select_query=select_query,
+            s3_bucket=s3_bucket,
+            s3_key=s3_key,
+            unload_options=unload_options,
+            include_header=True,
+            redshift_conn_id="redshift_conn_id",
+            aws_conn_id="aws_conn_id",
+            task_id="task_id",
+            dag=None,
+        )
+
+        op.execute(None)
+
+        unload_options = "\n\t\t\t".join(unload_options)
+        credentials_block = build_credentials_block(mock_session.return_value)
+
+        unload_query = op._build_unload_query(credentials_block, select_query, s3_key, unload_options)
+
+        assert mock_run.call_count == 1
+        assert access_key in unload_query
+        assert secret_key in unload_query
+        assert_equal_ignore_multiple_spaces(mock_run.call_args.args[0], unload_query)
+        assert f"UNLOAD ($${expected_query}$$)" in unload_query
+
     @pytest.mark.parametrize("table_as_file_name, expected_s3_key", [[True, "key/table_"], [False, "key"]])
     @mock.patch("airflow.providers.amazon.aws.hooks.s3.S3Hook.get_connection")
     @mock.patch("airflow.models.connection.Connection")