Support run multiple sql statements from file in DBApiHook

[flolas]

Description

Support run multiple sql statements from file in JdbcOperator DBApiHook, when the provided file has multiple statements (we can check it with https://github.com/andialbrecht/sqlparse)

Use case / motivation

Running single sql file with multiple sentences with JdbcOperator or any other DBApi operator (uses run method from DbApiHook, maybe we can implement here or in JdbcOperator):

Are you willing to submit a PR?
yes

[potiuk]

One comment - this should be definitely done on DBAPI hook, not in JdbcOperator. This way all DB operators will be able to use it. Assigning you to it :)

[grahovam]

I am already using the PostgresOperator and MySqlHook to execute SQL queries that contain multiple queries seperated by semicolons. And both the operator and the hook use DbApiHook for doing so. Am I missing something here? Thank you for any clarification.

[eladkal]

I am already using the PostgresOperator and MySqlHook to execute SQL queries that contain multiple queries seperated by semicolons. And both the operator and the hook use DbApiHook for doing so. Am I missing something here? Thank you for any clarification.

Assuming you have a SQL script:
Select 1; Select 2; Both Postgres and MySQL are able to execute this script. However you will not be able to execute this script in Snowflake, Presto nor Trino. This is why for these databases we first parse the script and break the statements one by one:

airflow/airflow/providers/trino/hooks/trino.py

Lines 111 to 112 in 6ab00bf

	def _strip_sql(sql: str) -> str:
	return sql.strip().rstrip(';')

airflow/airflow/providers/snowflake/hooks/snowflake.py

Lines 276 to 277 in 6ab00bf

	split_statements_tuple = split_statements(StringIO(sql))
	sql = [sql_string for sql_string, _ in split_statements_tuple if sql_string]

After that we can execute the statements one by one.

I think though there is a catch here. Executing Update table a ... ; Update table b...; may not the same as executing Update table a ... ; and then executing Update table b...; when we break the statements there is a question of when the commit(s) gets executed.

[grahovam]

So the idea here is to not break the statements (and execute one by one) inside TrinoHook nor SnowflakeHook anymore and therefore break the statements (and execute one by one) inside DBApiHook?

[potiuk]

Yeah. Sounds like the third time is the charm

[potiuk]

Hey @flolas - how is it going ? I saw a few people interested in it :)

[flolas]

@potiuk Hi! I will work this week on the PR.

[eladkal]

Should be covered fully by #23971