New Destination: Azure Synapse

[freesmith]

Tell us about the new integration you’d like to have

Which source and which destination? Which frequency?
Azure Datawarehouse (synapse) destination Anything from 30'-24h

Describe the context around this new integration

Which team in your company wants this integration, what for? This helps us understand the use case.
Data team works with Azure therefore it is interesting for them have this as destination.

Describe the alternative you are considering or using

What are you considering doing if you don’t have this integration through Airbyte?
We will use BigQuery although all the infrastructure is in Azure

Related issues

• Issues while connecting to Azure Synapse #4503
• Issues connecting to Azure Synapse #18408

Related PR that had to be reverted

• Destination MSSQL: make Azure Synapse Compatibility In Test #18294

┆Issue is synchronized with this Asana task by Unito

[michel-tricot]

Thank you @freesmith

We are actually about to double down on our destination connectors. Out of curiosity, do you know if they have a JDBC driver available? That would make getting an MVP fairly quick for us.

[freesmith]

Apologies as I am no expert. I tried looking for answers but was not able to find a clear one. Sorry. I'd say yes based on this (https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/connect-overview) but again, not clear.

[michel-tricot]

No problem! let us take a look at it

[eduardgruy]

+1. We see Synapse getting traction.
Please note that there are couple of solutions under the Azure Synapse workspace.

1st there is Synapse dedicated SQL pool

this is comparable to AWS Redshift and you can also use a similar loading strategy. That means JDBC drivers, COPY statement and ADLS Gen2 as staging layer.

2nd solution is Synapse serverless pool which is comparable to AWS Athena. Basically it's a serverless query engine to which you can register external tables. To integrate you would need to provide data in Parquet/CSV,.. on ADLS gen2 and create an external table over it.
In ideal scenario you would create 2 Delta lake tables (history & merged) in ADLS gen2 and register both of them as external tables on Synapse serverless pool, but:

• AFAIK Delta lake support is not yet done
• you can't use DML statements in Synapse serverless so you would need to use different processing engine to move data from the history table to the merged one.

3rd solution is Apache spark pool. This is similar to Databricks, but I think it lacks some Delta lake related features ( SQL API for deltalake like merge/delete/..? ). I think you could connect with JDBC/Apache Livy and use it to create, manipulate parquet/delta lake/CSV tables on ADLS Gen2.

Hope this will be helpful to someone.

[sherifnada]

@eduardgruy thanks a ton for the amazing context. This will be super helpful. We are bandwidth constrained at the moment but this is top of the list of priorities for us.

[hammadhasandogar]

+1 Synapse is used in enterprises using Azure stack for their data management, surprise to see that Synapse and ADLS are not part of hundreds of connectors available in Airbyte.
@sherifnada - do you have timeline defined for Azure connectors? i.e. Synapse and ADLS.

[sherifnada]

@hammadhasandogar we bumped back Azure synapse and currently have no defined ETA. The fastest way to get this destination into Airbyte is via PR contribution at the moment!

[hammadhasandogar]

@sherifnada thanks for your reply, will hopefully share progress on this, since my team already started looking into it and very much interested on introducing Synapse and ADLS connectors.

[jordandakota]

+1. We see Synapse getting traction. Please note that there are couple of solutions under the Azure Synapse workspace.

1st there is Synapse dedicated SQL pool this is comparable to AWS Redshift and you can also use a similar loading strategy. That means JDBC drivers, COPY statement and ADLS Gen2 as staging layer.

2nd solution is Synapse serverless pool which is comparable to AWS Athena. Basically it's a serverless query engine to which you can register external tables. To integrate you would need to provide data in Parquet/CSV,.. on ADLS gen2 and create an external table over it. In ideal scenario you would create 2 Delta lake tables (history & merged) in ADLS gen2 and register both of them as external tables on Synapse serverless pool, but:

• AFAIK Delta lake support is not yet done
• you can't use DML statements in Synapse serverless so you would need to use different processing engine to move data from the history table to the merged one.

3rd solution is Apache spark pool. This is similar to Databricks, but I think it lacks some Delta lake related features ( SQL API for deltalake like merge/delete/..? ). I think you could connect with JDBC/Apache Livy and use it to create, manipulate parquet/delta lake/CSV tables on ADLS Gen2.

Hope this will be helpful to someone.

Since this was posted, the SQL Serverless implementation in Synapse has gone general availability / full support of Delta lake tables:

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/query-delta-lake-format
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/resources-self-help-sql-on-demand#delta-lake

You can't however use SQL Serverless to create or update delta tables, must use spark.

SQL Dedicated supports various drivers:

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/connect-overview#supported-drivers-and-connection-strings
https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server?toc=/azure/synapse-analytics/sql-data-warehouse/toc.json&bc=/azure/synapse-analytics/sql-data-warehouse/breadcrumb/toc.json&view=azure-sqldw-latest&preserve-view=true

[ryjabe]

@evantahler curious if this is on your radar, or still in the backlog?

[evantahler]

Thanks for the ping! We are currently focused on rebuilding some of the core architecture to allow for more flexibility & speed for how our destinations operate. Once that work is completed we will revisit adding additional DB sources.

[RobLucchi]

@evantahler
Hello,

any temporary solution for the issue of Not enforcing the primary key?

mentioned here
#18408
or
#18297

[lfigueira]

Hi -

CC @evantahler @marcosmarxm

I am facing this issue when connecting to Synapse using the MSSQL connector. From 2149 it appears there is consensus that a new Synapse connector needs to be built. Am I correct? Has there been any progress on this?

[evantahler]

At this time, there are no plans to build a synapse connector. That said, we are about to release a big update for the general-case MSSQL connector which may help - stay tuned!

cc @rodireich

[rodireich]

It looks like the problem in source mssql is more limited in scope:
#30642
#4546

[lfigueira]

Hi @evantahler - thanks for the quick feedback; these are good news (fingers crossed!). Do you have any idea of when this update will be released? Alternatively is there any ticket/issue/milestone I can track?

[hugovandernotte]

Hey @evantahler and @rodireich,

Same questions as @lfigueira :) Has this update been released yet? Or any temporal solution as asked by @RobLucchi earlier?