Press "Enter" to skip to content

Category: Synapse Analytics

Spark ELT in Synapse Notebooks

Published 2023-04-10 by Kevin Feasel

Liliam Leme performs some data movement:

I often receive various requests from customers while working on FastTrack projects, and I have compiled some examples to help you build your solution on top of a data lake using useful tips. Most of the examples in this post use pandas, and I hope they will be helpful for you as they were for me.

Please note that all examples in this post use pyspark.

In my scenario, I exported multiple tables from SQLDB to a folder using a notebook and ran the requests in parallel.

Read on for the examples and some of the things you can do with Spark notebooks in Azure Synapse Analytics.

Comments closed

Azure Synapse Analytics March 2023 Update

Published 2023-04-06 by Kevin Feasel

Ryan Majidimehr has an update for us:

We are excited to announce that Multi-Column Distribution (MCD) for Azure Synapse Dedicated SQL pools is now Generally Available! MCD is highly desirable for easing migrations, promotes faster query performance, and reduces data skew. 

You can choose to distribute data on multiple columns to balance the data distribution in your tables and reduce data movement during query execution. Multi-Column distribution will allow you to choose up to eight columns for distribution. 

Click through for the full list of changes. It seems like there’s a decent spread between the four major pool types, with the emphasis on Data Explorer pools this month.

Comments closed

Troubleshooting Azure Synapse Link for SQL Server Issues

Published 2023-04-06 by Kevin Feasel

Kevin Chant diagnoses an issue:

In this post I want to cover common Azure Synapse Link for SQL storage permission issues. Since I helped a fellow MVP out with this recently.

To be more precise, I want to show how you can fix one of the most common issues I tend to encounter with Azure Synapse Link for SQL. Which is access to the Data Lake Storage Gen2 account.

I have encountered issues like this a few times now. For example, when I was performing my file tests for Azure Synapse Link for SQL Server 2022.

Click through to learn more about a couple of common issues, their causes, and resolutions.

Comments closed

Tips for Using a Data Lakehouse

Published 2023-03-20 by Kevin Feasel

James Serra shares some advice:

As I mentioned in my Data Mesh, Data Fabric, Data Lakehouse presentation, the data lakehouse architecture, where you use a data lake with delta lake as a software layer and skip using a relational data warehouse, is becoming more and more popular. For some customers, I will recommend “Use a data lake until you can’t”. What I mean by this is to take the following steps when building a new data architecture in Azure with Azure Synapse Analytics:

Click through for six notes.

Comments closed

Performance Tuning a Dedicated SQL Pool

Published 2023-03-17 by Kevin Feasel

Sarath Sasidharan has some guidance for us:

Synapse Dedicated pools have been battle tested at enterprise customers across the globe. We deal with data in the magnitude of PetaBytes. Synapse can provide you with the scale of the cloud and the high performance required for your enterprise-grade requirements.  The key to maximizing your performance is to follow best practices, check out best practices for dedicated SQL pools in Azure Synapse Analytics

Failure to do so causes performance issues. In such scenarios, is it important to understand where the bottlenecks are. This blog focuses on the different steps a query goes through; from the time the query is fired from the client until it returns back.  Delay caused in any of the steps would impact the overall run-time of the query and hence indicate degraded performance.

Click through for a walkthrough of each step along the way, potential problems you could run into, and remediations for those problems. Much of the advice is similar to what you’d get with SQL Server, though there are differences interspersed throughout each level.

Comments closed

February 2023 Updates for Azure Synapse Analytics

Published 2023-03-08 by Kevin Feasel

Ryan Majidimehr has a new round-up for us:

Azure Synapse Runtime for Apache Spark 3.3 has been in Public Preview since November 2022. We are excited to announce that after notable improvements in performance and stability, Azure Synapse Runtime for Apache Spark 3.3 now becomes Generally Available and ready for production workloads.   

The essential changes include features that come from upgrading Apache Spark to version 3.3.1, Delta Lake to version 2.2.0, and Python to 3.10. 

This month’s set of changes isn’t quite as big as some prior months, though there are a couple items of great importance to make up for it.

Comments closed

SqlPackage Support for the Serverless SQL Pool

Published 2023-03-06 by Kevin Feasel

Rui Cunha announces an update to SqlPackage.exe:

SqlPackage release 161.8089.0, dated February 13, 2023, brings a new feature: support for Synapse serverless SQL pools. According to the release notes, you can use SqlPackage to extract and publish both external and internal objects from serverless SQL pools. This includes the T-SQL surface area of serverless SQL pools is limited by design to external tables, external views, stored procedures, functions, statistics, and credentials. 

The following features are included in support for serverless SQL pools:

  • delta external file format
  • openrowset provider CosmosDB with PROVIDER, CONNECTION, OBJECT, and CREDENTIAL or SERVER_CREDENTIAL specified
  • openrowset format SStream
  • with () clause on openrowset

Rui also includes a quick demonstration of this new functionality.

Comments closed

Creating a Disaster Recovery Plan for Synapse

Published 2023-03-03 by Kevin Feasel

Freddie Santos talks HA/DR with Synapse:

Many of our customers have been asking about creating a disaster recovery plan for their Synapse Workspace. In a new blog series, we will cover the basics of disaster recovery and business continuity, discussing available options and custom solutions.

In this first post, we’ll review important concepts and questions to answer before building a disaster recovery plan, including the differences between High Availability and Disaster Recovery.

The focus in this post is on the dedicated SQL pool and Azure Data Lake Storage Gen2 (because people still think about Gen1?), though that’s the majority of what you’d need to think about—Spark pools and the serverless SQL pool really drive from the data lake. There’s also Data Explorer pools, which have their own storage and HA/DR capabilities.

Comments closed

Unit Testing Spark Notebooks in Synapse

Published 2023-03-03 by Kevin Feasel

Arun Sethia grabs the oscilloscope:

In this blog post, we will cover how to test and create unit test cases for Spark jobs developed using Synapse Notebook. This is an extension of my previous blog, Synapse – Choosing Between Spark Notebook vs Spark Job Definition, where we discussed selecting between Spark Notebook and Spark Job Definition. Unit testing is an automated approach that developers use to test individual self-contained code units. By verifying code behavior early, it helps to streamline coding practices for larger systems.

Arun covers three major use cases: when your code is in an external library, when it is in a separate notebook, and when it is in the same notebook.

Comments closed

Working with Managed Private Endpoints in Synapse

Published 2023-03-02 by Kevin Feasel

Sergio Fonseca continues a series on Synapse connectivity:

When you create your Azure Synapse workspace, you can choose to associate it to an Azure Virtual Network. The Virtual Network associated with your workspace is managed by Azure Synapse. This Virtual Network is called a Managed Workspace Virtual Network or Synapse Managed VNET. 

I am 100% in favor of using managed vNETs with Synapse and about 40% in favor of using Data Exfiltration Protection—it’s a lot lower because of the impact it has on your developers, though if you need it, developers will just have to deal with the added pain.

Comments closed