synapse | Synapse : Matrix homeserver written in Python/Twisted | Math library

Go to your storage account -> Access Control (IAM) -> Role Assigments and check if you can find ther role storage-blob-data-contributor if not add it.

This role shoulde be added automaticly but there are exceptions fron this rule

Detials are here how-to-grant-workspace-managed-identity-permissions

You cannot nest these activities so If and Switch will not appear on the menu when you are inside an If or a Switch. You also cannot nest For Each activities. Simply come back up to the top level of your pipeline and you will see the activities.

If you have more complex logic, think about using the logical functions in the expression language like and and or, or using like a Stored Procedure, Databricks Notebook, Azure Synapse Notebook, etc

In APIM v4, we maintain these in the memory and we are not writing to the file system.

This is explained here - https://apim.docs.wso2.com/en/3.2.0/install-and-setup/setup/distributed-deployment/synchronizing-artifacts-in-a-gateway-cluster/#inbuilt-artifact-synchronization

The straight-forward answer to your question is they all are ETL/ELT and Data Analytics tool with some different approach and features.

When comes to Azure Data Factory vs Synapse, they both are almost same except some features. When building an analytics solution in Azure, we recommend starting with Synapse since you have a fully integrated design experience and Azure analytics product conformance in a single pane of glass. Azure Data Factory used for Migration databases and copy files. You can find most differences between these two services here: Differences from Azure Data Factory - Azure Synapse Analytics

Azure Data Factory vs Databricks: Key Differences

Azure Data Factory vs Databricks: Purpose

ADF is primarily used for Data Integration services to perform ETL processes and orchestrate data movements at scale. In contrast, Databricks provides a collaborative platform for Data Engineers and Data Scientists to perform ETL as well as build Machine Learning models under a single platform.

Azure Data Factory vs Databricks: Ease of Usage

Databricks uses Python, Spark, R, Java, or SQL for performing Data Engineering and Data Science activities using notebooks. However, ADF provides a drag-and-drop feature to create and maintain Data Pipelines visually. It consists of Graphical User Interface (GUI) tools that allow delivering applications at a higher rate.

Azure Data Factory vs Databricks: Flexibility in Coding

Although ADF facilitates the ETL pipeline process using GUI tools, developers have less flexibility as they cannot modify backend code. Conversely, Databricks implements a programmatic approach that provides the flexibility of fine-tuning codes to optimize performance.

Azure Data Factory vs Databricks: Data Processing

Businesses often do Batch or Stream processing when working with a large volume of data. While batch deals with bulk data, streaming deals with either live (real-time) or archive data (less than twelve hours) based on the applications. ADF and Databricks support both batch and streaming options, but ADF does not support live streaming. On the other hand, Databricks supports both live and archive streaming options through Spark API.

Azure Synapse vs Databricks: Critical Differences

Azure Synapse vs Databricks: Data Processing

Apache Spark powers both Synapse and Databricks. While the former has an open-source Spark version with built-in support for .NET applications, the latter has an optimized version of Spark offering 50 times increased performance. With optimized Apache Spark support, Databricks allows users to select GPU-enabled clusters that do faster data processing and have higher data concurrency.

Azure Synapse vs Databricks: Smart Notebooks

Azure Synapse and Databricks support Notebooks that help developers to perform quick experiments. Synapse provides co-authoring of a notebook with a condition where one person has to save the notebook before the other person observes the changes. It does not have automated version control. However, Databricks Notebooks support real-time co-authoring along with automated version control.

Azure Synapse vs Databricks: Developer Experience

Developers get Spark environment only through Synapse Studio and do not support any other local IDE (Integrated Development Environment). It also lacks Git integration with Synapse Studio Notebooks. Databricks, on the other hand, enhances developer experience with Databricks UI, and Databricks Connect that remotely connects via Visual Studio or Pycharm within Databricks.

Azure Synapse vs Databricks: Architecture

Azure Synapse architecture comprises the Storage, Processing, and Visualization layers. The Storage layer uses Azure Data Lake Storage, while the Visualization layer uses Power BI. It also has a traditional SQL engine and a Spark engine for Business Intelligence and Big Data Processing applications. In contrast, Databricks architecture is not entirely a Data Warehouse. It accompanies a LakeHouse architecture that combines the best elements of Data Lakes and Data Warehouses for metadata management and data governance.

Source: https://hevodata.com/learn/azure-data-factory-vs-databricks/, https://hevodata.com/learn/azure-synapse-vs-databricks/

There are a number of factors to consider here. Some ideas:

• how fast can the table be read? What indexing / materialized views are in place? Is there any contention at the database level to rule out?
• Recommendation: ensure database is set up for fast read on the table you are exporting
• as you are on-premises, what is the local network card setup and throughput?
• Recommendation: ensure local network setup is as fast as possible
• as you are on-premises, you must be using a Self-hosted Integration Runtime (SHIR). What is the spec of this machine? eg 8GB RAM, SSD for spooling etc as per the minimum specification. Where is this located? eg 'near' the datasource (in the same on-premises network) or in the cloud. It is possible to scale out SHIRs by having up to four nodes but you should ensure via the metrics available to you that this is a bottleneck before scaling out.
• Recommendation: consider locating the SHIR 'close' to the datasource (ie in the same network)
• is the SHIR software version up-to-date? This gets updated occasionally so it's good practice to keep it updated.
• Recommendation: keep the SHIR software up-to-date
• do you have Express Route or going across the internet? ER would probably be faster
• Recommendation: consider Express Route. Alternately consider Data Box for a large one-off export.
• you should almost certainly land directly to ADLS Gen 2 or blob storage. Going straight into the database could result in contention there and you are dealing with Synapse concepts such as transaction logging, DWU, resource class and queuing contention among others. View the metrics for the storage in the Azure portal to determine it is under stress. If it is under stress (which I think unlikely), consider multiple storage accounts
• Recommendation: load data to ADLS2. Although this might seem like an extra step, it provides a recovery point and avoids contention issues by attempting to do the extract and load all at the same time. I would only load directly to the database if you can prove it goes faster and you definitely don't need the recovery point
• what format are you landing in the lake? Converting to parquet is quite compute intensive for example. Landing to the lake does leave an audit trail and give you a position to recover from if things go wrong
• Recommendation: use parquet for a compressed format. You may need to optimise the file size.
• ultimately the best thing to do would be one big bulk load (say taking the weekend) and then do incremental upserts using a CDC mechanism. This would allow you to meet your 2 hour window.
• Recommendation: consider a one-off big bulk load and CDC / incremental loads to stay within the timeline

In summary, it's probably your network but you have a lot of investigation to do first, and then a number of options I've listed above to work through.

I had to contact Microsoft support and their reply was the following:

ARM templates cannot be used to create a linked service. This is due to the fact that linked services are not ARM resources, for examples, synapse workspaces, storage account, virtual networks, etc. Instead, a linked service is classified as an artifact. To still complete the task at hand, you will need to use the Synapse REST API or PowerShell. Below is the link that provides guidance on how to use the API. https://docs.microsoft.com/en-us/powershell/module/az.synapse/set-azsynapselinkedservice?view=azps-7.1.0

This limitation in ARM is applied only to Synapse and they might fix this in the future.

Additional references:

https://feedback.azure.com/d365community/idea/05e41bf1-0925-ec11-b6e6-000d3a4f07b8

https://feedback.azure.com/d365community/idea/48f1bf78-2985-ec11-a81b-6045bd7956bb

Azure Synapse Analytics dedicated SQL pools scripts are sensitive to empty batches, eg this script would generate the same error:

Msg 104309, Level 16, State 1, Line 1 There are no batches in the input script.

Currently, after the dblookup the query result is your message. So if you respond after the log statement for example but skip the enrich, you should send those variables back to the client.

You could also use a PayloadFactory or XSLT transformation to transform the message if you want. But the data is already there, you actually lose it right now due to the enrich.

First Some Background

Synapse stores all artifacts in JSON format. In the Git repo, they are in the following folder structure:

Inside each folder are the JSON files that define the artifacts. Folder sqlscript contains the JSON for SQL Scripts in the following format:

NOTE: the Synapse folder of the script is just a property - this is why all SQL Script names have to be unique across the entire workspace.

Extracting the script

The workspace does allow you to Export SQL to a .sql file:

There are drawbacks: you have to do it manually, 1 file at a time, and you cannot control the output location or SQL file name.

To pull the SQL back out of the JSON, you have to access the properties.content.query property value and save it as a .sql file. As far as I know, there is no built in feature to automatically save a Script as SQL. Simple Copy/Paste doesn't really work because of the \ns.

I think you could automate at least part of this with an Azure DevOps Pipeline (or a GitHub Action). You might need to copy the JSON file out to another location, and then have a process (Data Factory, Azure Function, Logic App, etc.) read the file and extract the query.