Write incremental data to Synapse SQL from Synapse notebooks

Question

Write incremental data to Synapse SQL from Synapse notebooks

09842893 31

I am exploring to load incremental data to Synapse SQL(internal sql pool table) from Synapse notebooks. I see Scala has the connector to write the data (write.SynapseSQL), but looks like it doesn't allow inserts to the existing table.

Can you please point to some documentation/samples for achieving this.

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-08-10T11:10:39.19+00:00

Hi @Anonymous ,

Welcome to Microsoft Q&A forum and thanks for posting your query.

Yes, you are correct, write.SynapseSQL doesn't allow inserts to the existing table. We are reaching out to internal team to check if there are any other alternate ways to copy incremental data using Synapse Notebooks and will get back to you as soon as we have an update.

Also could you please confirm if your Synapse SQL is dedicated SQL pool?

Thank you
09842893 31 Reputation points

2021-08-10T14:17:54.543+00:00

Yes , I am trying to write to Synapse dedicated SQL pool.

1 answer

Your answer

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-08-10T11:10:39.19+00:00

Hi @Anonymous ,

Welcome to Microsoft Q&A forum and thanks for posting your query.

Yes, you are correct, write.SynapseSQL doesn't allow inserts to the existing table. We are reaching out to internal team to check if there are any other alternate ways to copy incremental data using Synapse Notebooks and will get back to you as soon as we have an update.

Also could you please confirm if your Synapse SQL is dedicated SQL pool?

Thank you
09842893 31 Reputation points

2021-08-10T14:17:54.543+00:00

Yes , I am trying to write to Synapse dedicated SQL pool.

Answer 1

KranthiPakala-MSFT 46,642 Microsoft Employee Moderator

Hi @Anonymous ,

As per my conversation with internal team, Product team are actively working on adding the overwrite and append method to the synapsesql connector. In the meantime, as a workaround it is suggested to use the connector to push the table to a temporary table on SQL dedicated and then run an insert into to the permanent table.

If you have any additional feedback regarding the product, I would recommend you to please share it in Azure Synapse Analytics user voice forum: https://feedback.azure.com/forums/307516-azure-synapse-analytics. All the feedback shared in this forum are actively monitored and reviewed by product teams.

Hope this info helps. Do let us know if you have further query.

----------

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.

09842893 31 Reputation points

2021-08-11T16:23:21.407+00:00

I understand the first part. Would I need any connector for the insert into? Or Can I just change the language to spark sql in the notebook and do it?
09842893 31 Reputation points

2021-08-13T15:17:48.487+00:00

@KranthiPakala-MSFT - Just tagging in case the response hasn't reached you
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-08-16T00:49:22.417+00:00

Hi @Anonymous ,

My apologies for the delayed response. Yes, you would need to use the JDBC connector to issue the insert command and then you can also drop the temporary table to clean up.

Hope this info helps. Please do let us know if you have further query.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-08-17T02:52:44.323+00:00

Hi @Anonymous ,

Just checking in to see if the above suggestion was helpful. If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-08-18T16:19:27.423+00:00

Hi @Anonymous ,

We still have not heard back from you. Just wanted to check if the above suggestion was helpful? If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.
09842893 31 Reputation points

2021-08-19T19:06:56.663+00:00

Thank you so much for the suggestions and my apologies for delayed response. The JDBC driver comes out of the box in synapse/should it be installed?
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-08-24T08:50:12.717+00:00

Hi @Anonymous ,

I believe it comes out of box. Please refer to this doc for more info about the drivers for Synapse SQL - https://learn.microsoft.com/azure/synapse-analytics/sql/connect-overview

Thank you
09842893 31 Reputation points

2021-08-24T21:28:24.003+00:00

Thank you .. Let me try that and get back.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-08-25T05:39:26.893+00:00

Sure, thank you :)

Share via

Write incremental data to Synapse SQL from Synapse notebooks

1 answer

Your answer