set92 commented on issue #48233:
URL: https://github.com/apache/airflow/issues/48233#issuecomment-2789145087
@kunaljubce Sorry that I didn't have the email notifications ON, and I kept
going since I didn't think this issue was going to get to a fix. While working
with Snowflake + Airflow I discovered basically what you are saying.
In my mind, when you run a transaction is because you want atomicity, right?
Well, Snowflake doesn't think like that. It is needed a change on the
parameters, and then use the `SnowflakeSqlApiOperator` to make sure it will run
all sentences or none. For example, the next SQL runs, but needs
`TRANSACTION_ABORT_ON_ERROR` because if not it will commit whatever had been
run before crashing.
```
sql_statement = """
ALTER SESSION SET TRANSACTION_ABORT_ON_ERROR = TRUE;
BEGIN TRANSACTION;
CREATE OR ALTER TABLE db_test.schema_test.test1 (
txt character varying(1)
);
SELECT * FROM db. ;
COMMIT;
"""
snflk_hook = SnowflakeSqlApiHook(snowflake_conn_id=snflk_conn_id)
snflk_hook.execute_query(sql_statement, statement_count=0)
```
And of course, use the SnowflakeSqlApiHook().execute_query() instead of
SnowflakeHook().run() because if not it will not respect the transactions. I
thought it was a nice thing to comment and know about. I feel going to
`SQLExecuteQueryOperator` was nice to standardize all the different databases,
but when you see these type of problems, you remember that each database has
their nuisances and why is good to respect their way of running or doing things
on their own.
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
