Press "Enter" to skip to content

Month: December 2023

Microsoft Fabric SQL Endpoints and REST API

Published 2023-12-12 by Kevin Feasel

Tomaz Kastrun continues a series on Microsoft Fabric. Day 6 covers the SQL Analytics endpoint:

SQL Analytics endpoint in lakehouse is a SQL-based experience for lakehouse delta tables. By using standard T-SQL language, you can write queries to analyze data in delta tables, create functions, procedures, views and even apply security over the objects. There are some of the functionalities missing from your standard T-SQL language, but the experience is the same.

Besides the SQL experience, you can always use the corresponding items in the workspace view of Lakehouse explorer, use SQL in notebooks, or simply use SQL analytics endpoint

Day 7 looks at what subset of T-SQL syntax you can use against SQL Analytics endpoints:

You get the gist, and there are some other limitations; computed columns, indexed views, any kind of indexes, partitioned tables, triggers, user-defined types, sparse columns, surrogate keys, temporary tables and many more. Essentially, all the commands that are not supported in distributed processing mode.

The most biggest annoyance (!) is case sensitivity! Ughh.. This proves that the SQL operates like API on top of delta tables, which is translated either into PySpark commands or not directly to Spark since Spark is not case-sensitive. So, the first one will work and the second statement will be gracefully terminated.

Day 8 covers the Lakehouse REST API:

Now that we explored the lakehouse through the interface and workspaces, let’s check today, how can we use REST API. Microsoft Fabric Rest API defines a unified endpoint for operations.

Comments closed

Advent of Code Day 3

Published 2023-12-12 by Kevin Feasel

Kevin Wilkie lives the struggle. Check out part one:

Then we do something very similar to what we did in Walking Through the Advent of Code Day 2 – find the digits, and the remaining text, and we’re home free!

Notice, though, that the position we grab is found at the very end. We just find the string in the line and show where it is. Except, that doesn’t always work.

And then there’s part 2:

Hopefully, by now, you’ve read my post Walking Through the Advent of Code Day 3. After looking through it, you’re probably thinking… “What have I gotten myself into? Is SQL the way to go with all of this? Can I back out now and learn a programming language that AoC can be done in “easily”?”

Click through to experience the pain or at least have a little bit of Schadenfreude in your life.

Comments closed

Error Handling in T-SQL Stored Procedures

Published 2023-12-12 by Kevin Feasel

Erik Darling intimates that some of our code might occasionally have errors or might experience circumstances in which not everything is in perfect alignment:

Okay, look, the best post about this is, of course, by Erland Sommarskog: Error and Transaction Handling in SQL Server

Just like Erland, it has three parts and three appendices. If you want to learn how to do a whole lot of things right, give yourself a couple days to read through and digest all that.

What I’m here to talk about is some of the stuff you should think about before implementing any error handling at all.

I agree with most of Erik’s opinion here. My very mild disagreement is that I’ll still protect against things like invalid parameters or logic errors (start date before end date) in the stored procedure. I do that for three reasons:

  • Defense in depth isn’t just a security principle–it’s also a code practices principle.
  • The app gets things wrong, too. Sometimes, the app dev accidentally sends parameters in the wrong order, and it’s better to get an error early on in development versus thinking everything works because the procedure called successfully and ship it.
  • Even if “the” app correctly handles inputs, there’s always a chance some other app or process will call this stored procedure and it might not have the same error handling code built in.
Comments closed

Security in Microsoft Fabric

Published 2023-12-12 by Kevin Feasel

Alex Lisboa-Wright talks security:

In Fabric, the basic logical structure for data services is the Workspace, in which users can create Items, which are the various resources that perform all the data operations available in Fabric, such as Lakehouses, pipelines, machine learning models and so on. Each workspace is a self-contained data storage and development environment, whose user access is controlled by both workspace admins and member users. User access controls include options to manage users’ workspace roles, which determine the permissions assigned to each user. Security permissions can be managed on the workspace and item levels in the Fabric UI. MEID authentication can also be employed within Fabric, as connecting Fabric items to other Azure resources requires MEID. MEID’s Conditional Access feature can also be configured for use in Fabric (see this documentation for best practice for Fabric resources linking to other Azure services).

Read on to learn more. Fabric is a broad set of tools and technologies, so security is both important and definitely non-trivial, even when you consider that it is a software-as-a-service offering and therefore doesn’t have much going on with user-facing networking or infrastructure security.

Comments closed

Databricks Security Analysis Tool

Published 2023-12-11 by Kevin Feasel

Advait Bhadane takes a look at a tool:

In today’s data-driven world a cutting-edge platform is required that seamlessly integrates with the cloud, embraces open-source innovation and prioritises robust data security. Databricks is a pioneer in this field. Not only does it provide a unified lake house platform, but it takes data protection to the next level with its Security Analysis Tool (SAT).

In this blog, we will unravel the power of Databricks’ SAT, focusing on the pivotal role it plays in generating daily health reports for your workspaces. It will also walk you through the step-by-step process of setting SAT in your workspace.

Click through to see what this tool can do for you.

Comments closed

Goodbye, Ask SQLServerCentral

Published 2023-12-11 by Kevin Feasel

Thomas Rushton gives Ask SQLServerCentral a Viking funeral:

When I started out as a full-time DBA back in 2010, it was the first Q&A site I found that was active, friendly, and easy to use.

It differs from the main SQLServerCentral Q&A boards by not being broken down into questions relating to a particular version of SQL Server, and a particular aspect of that version. This makes it easier for someone to just ask a question without worrying about if their question is going to the right place. A great benefit to those new to the scene.

Right around the time Thomas started, I was also getting into it and was active for a couple of years until a job change made it tougher for me to dedicate the time. I appreciate everything the crew did for so long, especially because it was a really good alternative to a dedicated Stack Exchange site for databases (which I think had officially started after Ask SQLServerCentral).

Comments closed

An Overview of Lakehouses in Microsoft Fabric

Published 2023-12-11 by Kevin Feasel

Kevin Chant invites you to a swank lakhouse:

By the end of this post, you will have a good overview of Microsoft Fabric Data Lakehouses, including CI/CD options. In addition, where your SQL Server background can prove to be useful and where some Power BI knowledge can come in handy.

Plus, I share plenty of links in this post. For instance, there are a couple of useful resources to help you get started towards the bottom of this post.

Click through for the article.

Comments closed