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

potiuk 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 70fd6ada39 Enhancement: Performance enhancement for insert_rows method 
DbApiHook with fast executemany + SAP Hana support (#37246)
70fd6ada39 is described below

commit 70fd6ada395715ab3a59715f735bfeb162f2cd46
Author: David Blain <[email protected]>
AuthorDate: Sat Feb 10 22:01:47 2024 +0100

    Enhancement: Performance enhancement for insert_rows method DbApiHook with 
fast executemany + SAP Hana support (#37246)
    
    * refactor: Added executemany parameter to insert_rows method so the fast 
executemany method on the cursor can be used to achieve better performance when 
inserting in bulk. Also check if dialect is SAP Hana in _generate_insert_sql 
method so the UPSERT statement can be used as REPLACE INTO doesn't exist on SAP 
Hana.
    
    * refactor: Reformatted code to be static check compliant
    
    * refactor: Refactored DbApiHook so that insert and replace statements are 
parametrized
    
    # Conflicts:
    #       tests/providers/common/sql/hooks/test_dbapi.py
    
    * refactor: Fixed some formatting in DbApiHook
    
    ---------
    
    Co-authored-by: David Blain <[email protected]>
---
 airflow/providers/common/sql/hooks/sql.py      | 68 +++++++++++++++++---------
 tests/providers/common/sql/hooks/test_dbapi.py | 45 ++++++++++++++---
 2 files changed, 85 insertions(+), 28 deletions(-)

diff --git a/airflow/providers/common/sql/hooks/sql.py 
b/airflow/providers/common/sql/hooks/sql.py
index 5ffc8f89b6..1da7d2c739 100644
--- a/airflow/providers/common/sql/hooks/sql.py
+++ b/airflow/providers/common/sql/hooks/sql.py
@@ -37,6 +37,7 @@ from urllib.parse import urlparse
 
 import sqlparse
 from deprecated import deprecated
+from more_itertools import chunked
 from sqlalchemy import create_engine
 
 from airflow.exceptions import AirflowException, 
AirflowProviderDeprecationWarning
@@ -48,7 +49,6 @@ if TYPE_CHECKING:
     from airflow.providers.openlineage.extractors import OperatorLineage
     from airflow.providers.openlineage.sqlparser import DatabaseInfo
 
-
 T = TypeVar("T")
 
 
@@ -165,6 +165,12 @@ class DbApiHook(BaseHook):
         self.log_sql = log_sql
         self.descriptions: list[Sequence[Sequence] | None] = []
         self._placeholder: str = "%s"
+        self._insert_statement_format: str = kwargs.get(
+            "insert_statement_format", "INSERT INTO {} {} VALUES ({})"
+        )
+        self._replace_statement_format: str = kwargs.get(
+            "replace_statement_format", "REPLACE INTO {} {} VALUES ({})"
+        )
 
     @property
     def placeholder(self) -> str:
@@ -486,13 +492,13 @@ class DbApiHook(BaseHook):
         """
         Generate the INSERT SQL statement.
 
-        The REPLACE variant is specific to MySQL syntax.
+        The REPLACE variant is specific to MySQL syntax, the UPSERT variant is 
specific to SAP Hana syntax
 
         :param table: Name of the target table
         :param values: The row to insert into the table
         :param target_fields: The names of the columns to fill in the table
-        :param replace: Whether to replace instead of insert
-        :return: The generated INSERT or REPLACE SQL statement
+        :param replace: Whether to replace/upsert instead of insert
+        :return: The generated INSERT or REPLACE/UPSERT SQL statement
         """
         placeholders = [
             self.placeholder,
@@ -505,13 +511,13 @@ class DbApiHook(BaseHook):
             target_fields = ""
 
         if not replace:
-            sql = "INSERT INTO "
-        else:
-            sql = "REPLACE INTO "
-        sql += f"{table} {target_fields} VALUES ({','.join(placeholders)})"
-        return sql
+            return self._insert_statement_format.format(table, target_fields, 
",".join(placeholders))
 
-    def insert_rows(self, table, rows, target_fields=None, commit_every=1000, 
replace=False, **kwargs):
+        return self._replace_statement_format.format(table, target_fields, 
",".join(placeholders))
+
+    def insert_rows(
+        self, table, rows, target_fields=None, commit_every=1000, 
replace=False, executemany=False, **kwargs
+    ):
         """Insert a collection of tuples into a table.
 
         Rows are inserted in chunks, each chunk (of size ``commit_every``) is
@@ -523,6 +529,8 @@ class DbApiHook(BaseHook):
         :param commit_every: The maximum number of rows to insert in one
             transaction. Set to 0 to insert all rows in one transaction.
         :param replace: Whether to replace instead of insert
+        :param executemany: Insert all rows at once in chunks defined by the 
commit_every parameter, only
+            works if all rows have same number of column names but leads to 
better performance
         """
         i = 0
         with closing(self.get_conn()) as conn:
@@ -532,19 +540,35 @@ class DbApiHook(BaseHook):
             conn.commit()
 
             with closing(conn.cursor()) as cur:
-                for i, row in enumerate(rows, 1):
-                    lst = []
-                    for cell in row:
-                        lst.append(self._serialize_cell(cell, conn))
-                    values = tuple(lst)
-                    sql = self._generate_insert_sql(table, values, 
target_fields, replace, **kwargs)
-                    self.log.debug("Generated sql: %s", sql)
-                    cur.execute(sql, values)
-                    if commit_every and i % commit_every == 0:
+                if executemany:
+                    for chunked_rows in chunked(rows, commit_every):
+                        values = list(
+                            map(
+                                lambda row: tuple(map(lambda cell: 
self._serialize_cell(cell, conn), row)),
+                                chunked_rows,
+                            )
+                        )
+                        sql = self._generate_insert_sql(table, values[0], 
target_fields, replace, **kwargs)
+                        self.log.debug("Generated sql: %s", sql)
+                        cur.fast_executemany = True
+                        cur.executemany(sql, values)
                         conn.commit()
-                        self.log.info("Loaded %s rows into %s so far", i, 
table)
-
-            conn.commit()
+                        self.log.info("Loaded %s rows into %s so far", 
len(chunked_rows), table)
+                else:
+                    for i, row in enumerate(rows, 1):
+                        lst = []
+                        for cell in row:
+                            lst.append(self._serialize_cell(cell, conn))
+                        values = tuple(lst)
+                        sql = self._generate_insert_sql(table, values, 
target_fields, replace, **kwargs)
+                        self.log.debug("Generated sql: %s", sql)
+                        cur.execute(sql, values)
+                        if commit_every and i % commit_every == 0:
+                            conn.commit()
+                            self.log.info("Loaded %s rows into %s so far", i, 
table)
+
+            if not executemany:
+                conn.commit()
         self.log.info("Done loading. Loaded a total of %s rows into %s", i, 
table)
 
     @staticmethod
diff --git a/tests/providers/common/sql/hooks/test_dbapi.py 
b/tests/providers/common/sql/hooks/test_dbapi.py
index 45b40d8e48..fd9886345f 100644
--- a/tests/providers/common/sql/hooks/test_dbapi.py
+++ b/tests/providers/common/sql/hooks/test_dbapi.py
@@ -36,24 +36,30 @@ class NonDbApiHook(BaseHook):
 
 
 class TestDbApiHook:
-    def setup_method(self):
+    def setup_method(self, **kwargs):
         self.cur = mock.MagicMock(
-            rowcount=0, spec=["description", "rowcount", "execute", 
"fetchall", "fetchone", "close"]
+            rowcount=0,
+            spec=["description", "rowcount", "execute", "executemany", 
"fetchall", "fetchone", "close"],
         )
         self.conn = mock.MagicMock()
         self.conn.cursor.return_value = self.cur
+        self.conn.schema.return_value = "test_schema"
         conn = self.conn
 
-        class UnitTestDbApiHook(DbApiHook):
+        class DbApiHookMock(DbApiHook):
             conn_name_attr = "test_conn_id"
             log = mock.MagicMock()
 
+            @classmethod
+            def get_connection(cls, conn_id: str) -> Connection:
+                return conn
+
             def get_conn(self):
                 return conn
 
-        self.db_hook = UnitTestDbApiHook()
-        self.db_hook_no_log_sql = UnitTestDbApiHook(log_sql=False)
-        self.db_hook_schema_override = 
UnitTestDbApiHook(schema="schema-override")
+        self.db_hook = DbApiHookMock(**kwargs)
+        self.db_hook_no_log_sql = DbApiHookMock(log_sql=False)
+        self.db_hook_schema_override = DbApiHookMock(schema="schema-override")
 
     def test_get_records(self):
         statement = "SQL"
@@ -157,6 +163,33 @@ class TestDbApiHook:
         for row in rows:
             self.cur.execute.assert_any_call(sql, row)
 
+    def test_insert_rows_executemany(self):
+        table = "table"
+        rows = [("hello",), ("world",)]
+
+        self.db_hook.insert_rows(table, rows, executemany=True)
+
+        assert self.conn.close.call_count == 1
+        assert self.cur.close.call_count == 1
+        assert self.conn.commit.call_count == 2
+
+        sql = f"INSERT INTO {table}  VALUES (%s)"
+        self.cur.executemany.assert_any_call(sql, rows)
+
+    def test_insert_rows_replace_executemany_hana_dialect(self):
+        self.setup_method(replace_statement_format="UPSERT {} {} VALUES ({}) 
WITH PRIMARY KEY")
+        table = "table"
+        rows = [("hello",), ("world",)]
+
+        self.db_hook.insert_rows(table, rows, replace=True, executemany=True)
+
+        assert self.conn.close.call_count == 1
+        assert self.cur.close.call_count == 1
+        assert self.conn.commit.call_count == 2
+
+        sql = f"UPSERT {table}  VALUES (%s) WITH PRIMARY KEY"
+        self.cur.executemany.assert_any_call(sql, rows)
+
     def test_get_uri_schema_not_none(self):
         self.db_hook.get_connection = mock.MagicMock(
             return_value=Connection(

Reply via email to