Tag: Synapse

Creating a Synapse Workspace
As part of my job, I needed to research how a few things work with Synapse and Fabric. The latter includes the former, mostly. I decided to setup a workspace and a do some experimentation.

My first stop was the Stairway to Synapse Analytics. I started with Level 1, since I needed to create something.

In the portal, I created a new Synapse Analytics resource. I won’t go into details, but basically create a new resource and search. Read the article to see how this works. From there, I used a resource group I had for work stuff. I also had to create a new Data Lake Storage Gen2 account. I didn’t bother with the Managed Resource Group.

I added a user account and password and then clicked Create. I went to the summary page, where I could see the small cost for serverless. I clicked “Create” again to start the deployment.

The deployment failed.

I was leaning towards this not being helpful, but I went into the details of the error message and found something interesting. My subscription couldn’t deploy in this region (westeurope).

OK, go back, recreate the resource in UKSouth. That worked fine and I saw my resources.

At this point, I had a resource.

Adding Data

The next step for me was to add some data. I grabbed a few csv files and uploaded them to my storage account. Lots of querying in Synapse is through external tables to flat files, so I picked some files I can query.

Once these were uploaded, I was next interested to see if I could query this data. In my main Synapse resource, I see some endpoints.

I copied the serverless one and then opened SSMS. I put this, my user and pwd, and got connected. I was hoping that @@Version would tell me I was connected to Synapse, but I got the same results I get some Azure SQL Database, albeit with a different timestamp. However, ServerProperty() helps.

That works, what about my data? Let’s try a query from Level 2.

I’ll take this query and run it. I’ve adjusted this from the values in Level 2, which actually uses the Synapse Workspace explorer online.

–retrieve data from csv file
```
SELECT
TOP 100 *
FROM
OPENROWSET(
BULK 'https://synapsesqlprompt.blob.core.windows.net/sqlpromptfs/solar_2024_01.csv',
FORMAT = 'CSV',
        HEADER_ROW = TRUE,
        PARSER_VERSION = '2.0'
    ) 
WITH (
    [Time]  DATE,
    [System Production (Wh)] VARCHAR (100)
    ) AS [result]
```
It fails.

Hmmm, let’s try that in the browser. Here it works.

The error is with a credential.

There is a quickstart online, and that query works. However, there is a note in the query that I need a credential if my file is protected.

The article had this link and used the sample code to create a credential:
```
CREATE CREDENTIAL [https://synapsesqlprompt.blob.core.windows.net/sqlpromptfs]

WITH IDENTITY='SHARED ACCESS SIGNATURE'

, SECRET = '';

GO
```
Now I can run my original query and it works.

That was a pretty cool exercise for me to get started. In less than 30 minutes I was able to create a Synapse workspace, add some data, and query it.

Now to learn a bit more about how this works, and to use Flyway to deploy some objects.
August 26, 2024
Archive to the Lake

Microsoft Fabric was announced at Build in May 2023. This is the next evolution of data warehousing from Microsoft, folding in Synapse and a number of other technologies to create a simpler location for storing and analyzing data. We’ve published some articles on the platform and there’s a great presentation from Mr. Paul Andrew on Linked In. It’s worth listening to, even in the background. Paul has a nice style and a great voice.

Part of this platform is OneLake. This is a data lake for your org, just one of them, and while it’s able to store data in many formats, it’s mainly optimized to read tabular data in the delta parquet format. This is essentially a compressed text file that allows for some transactional changes to the original data in parquet format.

I don’t do a lot of work with text files, and I’ve been suspicious of using lots of CSV or other text files in a warehouse environment, which is what a lot of people were advocating a few years ago. Exporting tables into lots of files split on some field, like date, while easy, didn’t seem like the best way to move data for reporting.

Fabric, however, is optimized for reading delta files. A few presentations I’ve seen from people have advocated for exporting your data from SQL Server (or other platforms) into parquet. While I don’t know there’s a native way to do this (yet), I suspect one is coming. I’ve seen lots of articles (one, two, three, more) about how to do this now. We also have SQL Server able to read these files with external file formats already, so I’m sure we’ll have an easy way to write them soon.

Many of us struggle with large systems, especially with query performance. We’d love to archive off data, though that’s often impractical. However, in an amazing, wonderful world, maybe we’ll get lots of people doing this, writing about it in the media, and our bosses will start to let us establish an archive in the lake. We could move some data there, especially old, unchanging data. We could delete that from source systems. We could have all our users happy.

I don’t know if I see lots of data moving to the lake, but I certainly expect lots of it to be copied. If you haven’t thought about archives, data lakes, and text formats, it’s an area that seems to have a lot of growth. Perhaps it’s of interest to you and you might find a new career.

Or maybe you just hope it gets widely adopted to relieve some pressure on your OLTP server.

Steve Jones

Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

August 7, 2023