Press "Enter" to skip to content

Curated SQL Posts

Understanding the Data Lakehouse

Published 2022-04-18 by Kevin Feasel

Tom Jordan explains what data lakehouses are:

When we are thinking about data platforms, there are many different services and architectures that can be used – sometimes this can be a bit overwhelming! Data warehouses, data models, data lakes and reports are all typical components of an enterprise data platform, which have different uses and skills required. However, in the past few years a new architecture has been rising; the data lakehouse. This is an architecture that borrows ideas and concepts from several different areas, which we will be exploring in greater detail in this blog.

Click through to learn more about the origin of this term and how it draws + differs from both a data lake and a data warehouse.

Comments closed

Using the master dacpac in Azure DevOps

Published 2022-04-18 by Kevin Feasel

Koen Verbeeck makes use of system databases in a database project:

I have a database project in Visual Studio. Inside the database, I use a couple of system views to fetch some metadata about tables. To make the project build successfully, you need to add a reference to the master database in the project.

That all works fine but there’s a bit more you need to do before Azure DevOps can work with the file. Read on to learn what that thing is.

Comments closed

Determining Simple Parameterization Usage

Published 2022-04-18 by Kevin Feasel

Paul White continues a series on simple parameterization and trivial plans:

It’s more complicated than you might expect to tell from the information provided in execution plans if a SQL statement uses simple parameterization. It’s no surprise even highly experienced SQL Server users tend to get this wrong, given the contradictory information often supplied to us.

Let’s look at some examples using the Stack Overflow 2010 database on SQL Server 2019 CU 14, with database compatibility set to 150.

Read on for four classes of outcome and several ways you can determine into which your queries belong.

Comments closed

Retrieving Twitter Engagements in R

Published 2022-04-15 by Kevin Feasel

Bryan Shalloway continues looking at Twitter data:

This is a follow-up to a short post I wrote on R Access to Twitter’s v2 API. In this post I’ll walk through a few more examples of pulling data from twitter using a mix of Twitter’s v2 API as well as the {rtweet} package.

I’ll pull all Twitter users that I (brshallo) have recently been engaged by (e.g. they like my tweet) or engaged with (e.g. I like their tweet). I’ll lean towards using {rtweet} but will use {httr} in cases where it’s more convenient to use Twitter’s v2 API.

Click through for more information, including several R scripts.

Comments closed

Downloading Power BI Reports with Powershell

Published 2022-04-15 by Kevin Feasel

Jon Fletcher needs to get some PBIX files:

In this blog post I will be sharing a PowerShell script that allows multiple Power BI reports to be downloaded at once.

In the Power BI service, there is no way of downloading multiple Power BI reports at once. Therefore, users must download files one by one which is slow, time consuming and inefficient. Thankfully, there are ways around this, one of which is using PowerShell.

Read on for the script and some additional notes.

Comments closed

Streaming Data into Synapse Dedicated SQL Pool

Published 2022-04-15 by Kevin Feasel

Lionel Penuchot loads some data:

This article reviews a common pattern of streaming data (i.e. real-time message ingestion) in Synapse dedicated pool. It opens a discussion on the simple standard way to implement this, as well as the challenges and drawbacks. It then presents an alternate solution which enables optimal performance and greatly reduces maintenance tasks when using clustered column store indexes. This is aimed at developers, DBAs, architects, and anyone who works with streams of data that are captured in real-time.

I’d probably avoid the MERGE statement in there because of how many problems there are with it. That said, this is a useful pattern for trickle-loading columnstore tables.

Comments closed

Making Redis Do Your Bidding

Published 2022-04-15 by Kevin Feasel

Arun Sirpal looks at some of the command language for Azure Redis:

Now that we have created our Redis Cache lets connect to it. You can use the most common tool redis cli.exe https://redis.io/download or as I am going to do, use the Azure Portal to use the console directly, this isn’t probably the best way but it’s the easiest for this blog. 2 key points here:

Read on for those points, as well as examples of commands you can run.

Comments closed