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(