cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
cancel
Showing results for 
Search instead for 
Did you mean: 

Multi Statement Writes from Spark to Snowflake

pvignesh92
Honored Contributor

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

  1. A selective deletion of data from a Snowflake table and
  2. 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

1 ACCEPTED SOLUTION

Accepted Solutions

pvignesh92
Honored Contributor

In my analysis, I got the below understanding

  1. 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
  2. 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.

View solution in original post

8 REPLIES 8

-werners-
Esteemed Contributor III

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

pvignesh92
Honored Contributor

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.

-werners-
Esteemed Contributor III

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.

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

-werners-
Esteemed Contributor III

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

Lakshay
Esteemed Contributor
Esteemed Contributor

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

pvignesh92
Honored Contributor

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

pvignesh92
Honored Contributor

In my analysis, I got the below understanding

  1. 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
  2. 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.
Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.