topic Re: Multi Statement Writes from Spark to Snowflake in Data Engineering

Multi Statement Writes from Spark to Snowflake

pvignesh92 — Fri, 10 Mar 2023 08:32:38 GMT

Does Spark support multi statement writes to Snowflake in a single session? To elaborate, I have a requirement where I need to do

A selective deletion of data from a Snowflake table and
Insert records to Snowflake table ( Ranges from around 1 M rows)

I want to wrap these both into a single transaction so that I could get Consistency here. I can't use overwrite mode as it will overwrite my entire table in Snowflake. I have seen runQuery of sfutils support multiStatements but it is more to handle data within Snowflake and not from Databricks to Snowflake.

Thanks,

Vignesh

Re: Multi Statement Writes from Spark to Snowflake

-werners- — Fri, 10 Mar 2023 10:53:38 GMT

are you looking for something like a merge (upsert) or a replaceWhere?

Re: Multi Statement Writes from Spark to Snowflake

pvignesh92 — Fri, 10 Mar 2023 12:16:14 GMT

Yes. It's kind of replace where. My input tables are in Databricks layer. So I join them and write to a dataframe. Then I want to write this dataframe to Snowflake table by selectively deleting certain data as I have separate pipeline for different regions. So my overwrite on Snowflake table should only be for that region.

Re: Multi Statement Writes from Spark to Snowflake

Lakshay — Fri, 10 Mar 2023 12:40:07 GMT

Hi @Vigneshraja Palaniraj , Could you please check the below document if this meets your use case

https://docs.databricks.com/sql/language-manual/delta-merge-into.html

Re: Multi Statement Writes from Spark to Snowflake

-werners- — Fri, 10 Mar 2023 12:40:32 GMT

The only way this can be done in a single command is if snowflake has something like a replaceWhere. I did some searching but did not find anything.

Re: Multi Statement Writes from Spark to Snowflake

pvignesh92 — Fri, 10 Mar 2023 12:42:56 GMT

Hi @Lakshay Goel . Thanks for your response. This option is more about when you want to update your delta table that is existing in Databricks layer itself. But I'm looking for the option when I want to write to Snowflake with dataframe write API using Spark snowflake connector

Re: Multi Statement Writes from Spark to Snowflake

pvignesh92 — Fri, 10 Mar 2023 12:45:00 GMT

Yes. I have tried the DELETE command using sfutils runQuery and then my data insertion using df.write method. They are captured in different sessions with two different session id in Snowflake. I basically want to wrap these two statements as a single transaction

Re: Multi Statement Writes from Spark to Snowflake

-werners- — Fri, 10 Mar 2023 13:15:57 GMT

if snowflake does not have the possibility, it is not possible in spark either.

Re: Multi Statement Writes from Spark to Snowflake

pvignesh92 — Tue, 21 Mar 2023 12:16:54 GMT

In my analysis, I got the below understanding

If your data is sitting in Snowflake and you have a set of DDL/DML queries that need to wrapped into a single transaction, you can use MULTI_STATEMENT option to 0 and use snowflake utils runQuery method to run them as single transaction. All these command will be executed in Snowflake with a single session id
If the requirement is to write a dataframe computed from Databricks to Snowflake along with DDL command, then they will be executed as two different transactions with two different session ids. So not really possible out of the box to wrap them to a single session.