Introduction to Azure Synapse Serverless SQL pools

Azure Synapse is a super-scalable analytics service for enterprise data warehousing and Big Data analytics. It gives you two options: Dedicated SQL pool or Serverless on-demand resources. This article will describe the Serverless SQL pool.

Azure Synapse Serverless SQL Pool is a query service that works with Azure Data Lake. It enables you to access data through standard T-SQL syntax and standard protocols. This makes your data available from a wide range of Business Intelligence tools (such as Power BI). Serverless SQL Pools have a MPP (Massively Parallel Processing) architecture, which can analyze huge amounts of data in seconds to minutes. Since it is serverless, you are only charged for the data processed by your queries.

Creating your Azure Data Lake Storage

You will need an Azure Data Lake Storage (Gen2) to serve as storage for your Azure Synapse workspace. In this introduction, I will also use the same Azure Data Lake Storage for storing demo data that you can run queries against.

I recommend starting with a blank Storage account. In the Azure Portal, click "Create a resource" and then select "Storage account".

Azure Synapse Serverless Create Storage Account
  • Choose StorageV2
  • Choose Locally-redundant storage (LRS) if you want to keep down costs
  • On the Advanced tab under Data Lake Storage Gen2, set "Hierarchical namespace" to Enabled.

After your Storage Account has been created, go to the Containers screen.

Azure Synapse Serverless Create Storage Account Container

Then add a container named "demo".

Azure Synapse Serverless Create Storage Account Container

Creating your Azure Synapse Analytics Workspace

Next, let's create the Azure Synapse workspace. Search for Azure Synapse Analytics and click create.

Under "Select Data Lake Storage Gen2", select the storage account that you created previously. Under "File system name", select the container you created.

Azure Synapse Serverless Create Workspace

Complete the next step. For testing purposes, you won't need double encryption.

Azure Synapse Serverless Create Workspace

Complete the remaining steps and confirm the creation of your Synapse workspace.

Creating your Sample Data

Let's now create some sample data for running your Azure Synapse SQL queries.

One way to quickly create some random data is to generate it from PowerShell. You have PowerShell easily available from the portal by the CloudShell feature.

Azure Synapse Cloudshell

This will give you a terminal windows like below.

Azure Synapse Cloudshell

Now you can copy the script below, modify it with your resource names and paste it into the CloudShell terminal window. It should create your sample test files in less than a minute.

 1$resourcegroup = "your resource group"
 2$account = "your storage account"
 3$container = "demo"
 4$folder = "~/demodata"
 5$numberoffiles = 10
 6$linesperfile = 10000
 8if(!(Test-Path -path $folder))
10    New-Item -ItemType Directory -Path $folder
13Set-AzCurrentStorageAccount -ResourceGroupName $resourcegroup -Name $account
15for ($i = 0; $i -lt $numberoffiles; $i++)
17    $filename = "sales$((Get-Random 1000000).ToString("D6")).txt"
18    $path = "$folder/$filename"
19    Write-Output "Creating file $path"
21    $content = "Date`tRegionID`tProductID`tQuantity`r`n"
22    for ($l = 0; $l -lt $linesperfile; $l++)
23    {
24        $content += "$((Get-Date).AddDays((Get-Random 365) - 365).ToString("yyyyMMdd"))`t$((Get-Random 10) + 1)`t$((Get-Random 25) + 1)`t$((Get-Random 1000) + 1)`r`n"
25    }
26    Set-Content -Path $path -Value $content
28    Write-Output "Uploading file $path"
29    Set-AzStorageBlobContent -Container $container -File $path -Blob $filename
31    Remove-Item -Path $path

It will generate 10 files, each with 10000 lines of test data. They will be uploaded into your Data Lake.

Setting up your Database and Data Source

Open Synapse Studio

First click on your newly created Synapse Workspace.

Azure Synapse Resources

Then click to Open Synapse Studio.

Azure Synapse Open Synapse Studio

Create SQL Script

In Azure Synapse Studio, start by clicking on the Develop tab.

Azure Synapse Studio

The create a new SQL Script.

Azure Synapse Studio New SQL Script

Now you get an empty script that you can edit and run. This will be the place where you run the following SQL statements.

Create Database

By default, Azure Synapse Serverless SQL queries will run against the master database of your pool. It is better to create your own user database. To create your database, run this code in your SQL script:


Click the refresh button. Your database should become available in the "Use database" list.

Azure Synapse Studio Create Database

Create Data Source

Although you can access the Data Lake directly from your queries without creating any data source, it is better to create one. Run the following SQL script:


Make sure you run the script inside your user database (not the master database).

Running your first Azure Synapse Serverless SQL query

If you have completed all the previous steps, you can now easily run queries against your test data!

Try running this SQL script:

 2    TOP 100 *
 5        BULK '/demo/sales*.txt',
 6        DATA_SOURCE = 'demo',
 7        FORMAT='CSV',
 8        PARSER_VERSION = '2.0',
 9        FIELDTERMINATOR = '\t',
10        ROWTERMINATOR = '\n',
11        HEADER_ROW = TRUE
12    ) 
13    WITH ([Date] [int], [RegionID] [int], [ProductID] [int], Quantity [int])
14    AS [sales]

You can omit the WITH-clause if you wish, because Azure Synapse can autodetect the columns from your files. However, I recommend you do include it so you ensure you have control of your schema.

Notice the wildcard (sales*.txt), which means Azure Synapse will read ALL files matching that pattern.

There are currently two version of the parser. Version 2.0 is the fastest, so I recommend using it.

Now let's also try grouping and aggregating data:

 2    [Month] = [Date] / 100,
 3    [RegionID],
 4    [ProductID],
 5    [Quantity] = SUM([Quantity])
 8        BULK '/demo/sales*.txt',
 9        DATA_SOURCE = 'demo',
10        FORMAT='CSV',
11        PARSER_VERSION = '2.0',
12        FIELDTERMINATOR = '\t',
13        ROWTERMINATOR = '\n',
14        HEADER_ROW = TRUE
15    ) 
16    WITH ([Date] [int], [RegionID] [int], [ProductID] [int], Quantity [int])
17    AS [sales]
18GROUP BY [Date] / 100, RegionID, ProductID
19ORDER BY [Date] / 100, RegionID, ProductID

The output should look something like this:

Azure Synapse Studio Query Results

Creating a View

While Serverless pools can't store data in their databases, you can create views in them that point to your data sources.

By creating views, you will make querying much easier for your users. For example you could create a view for the demo data source like this:

 1CREATE VIEW dbo.DemoSales AS
 3    *
 6        BULK '/demo/sales*.txt',
 7        DATA_SOURCE = 'demo',
 8        FORMAT='CSV',
 9        PARSER_VERSION = '2.0',
10        FIELDTERMINATOR = '\t',
11        ROWTERMINATOR = '\n',
12        HEADER_ROW = TRUE
13    ) 
14    WITH ([Date] [int], [RegionID] [int], [ProductID] [int], Quantity [int])
15    AS [sales]

Anyone could then easily query the view and do whatever they wish with the data. For example:

2    [Month] = [Date] / 100,
3    [RegionID],
4    [ProductID],
5    [Quantity] = SUM([Quantity])
6FROM dbo.DemoSales
7GROUP BY [Date] / 100, RegionID, ProductID
8ORDER BY [Date] / 100, RegionID, ProductID

Connecting with Management Studio

A strenght of Serverless Pools is that you can continue using your standard tools. For instance, you can connect using SQL Server Management Studio.

To connect, you will need to know the Serverless SQL endpoint, which you can find on the Overview page of your Synapse workspace (not Synapse Studio) in the Azure Portal.

Azure Synapse Management Studio Serverless SQL Endpoint

Then you can easily connect from SQL Server Management Studio (SSMS) using the credentials that you entered when creating the Synapse workspace:

Azure Synapse Management Studio Connect


Related Posts