Press "Enter" to skip to content

Curated SQL Posts

Building a CRUD Application with Cloudera Operational DB and Flask

Published 2020-10-08 by Kevin Feasel

Shlomi Tubul puts together a proof of concept app:

In this blog, I will demonstrate how COD can easily be used as a backend system to store data and images for a simple web application. To build this application, we will be using Phoenix, one of the underlying components of COD, along with Flask. For storing images, we will be using an HBase (Apache Phoenix backend storage) capability called MOB (medium objects). MOB allows us to read/write values from 100k-10MB quickly. 

*For development ease of use, you can also use the Phoenix query server instead of COD. The query server is a small build of phoenix that is meant for development purposes only, and data is deleted in each build. 

Click through for the demo and for a link to the GitHub repo.

Comments closed

Read Those Error Messages

Published 2020-10-08 by Kevin Feasel

Randolph West has a public service announcement:

My boss got upset with us one day on The Project From Hell. Tempers were frayed, tensions ran high, and other euphemisms were euphemisming. In short, we were all grumpy, and as expected on a project of this nature we kept making obvious mistakes and wasting our energy chasing our tails.

Obvious? Well, yes. It turns out that the answer to a particularly common issue we were running into was explained in the first line of the stack trace of the code that kept crashing. I’m not exaggerating for the sake of this story. The actual problem was explained in the first sentence of the error, in the very first line.

Microsoft products are fairly notorious about poorly-crafted error messages, but as Randolph mentions, often the solution is there if you take care to read the message.

Comments closed

Diving Into the Window Spool Operator

Published 2020-10-08 by Kevin Feasel

Hugo Kornelis continues a series on execution plan operators:

The Window Spool operator is one of the four spool operators that SQL Server supports. Like other spool operators, it retains a copy of data it receives and can then return those rows as often as needed. The specific functionality of the Window Spool operator allows it to replay rows within a window, as defined in a ROWS or RANGE specification of an OVER clause.

Read on to see how these work, as well as a few differences from their spool brethren.

Comments closed

Exporting Environment Variables from the SSIS Catalog

Published 2020-10-08 by Kevin Feasel

Koen Verbeeck shows how we can pull environment variables out from the SSIS catalog:

Sometimes when you’ve created a SSIS catalog, along with folders, environments and environment variables, you want this content on another server. Unfortunately, you can only script out these objects in SSMS at the moment you’re creating them (thus right before you clikc ‘OK’ to confirm). Once the objects are created, you can no longer script them out using the GUI.

Luckily, there’s a whole slew of stored procedures and views in the catalog that allow us to extract information and use that information to re-create the objects. Here are two scripts that I found useful.

Click through for the scripts.

Comments closed

Clone Logins and Users with dbatools

Published 2020-10-08 by Kevin Feasel

Jana Sattainathan takes us through a couple of DBA scenarios:

One of the more frequent requests that a SQL Server DBA receives is to “Clone a login” with all its permissions. For example a request could be

– Clone BILL_BLACK’s login and create a new login JACK_JOHNSON with exactly the same privileges in all databases.
– Clone AD group login BILLING_APP_ADMINS and create a login for new AD group BILLING_APP_CONSULTANTS with the same permissions as BILLING_APP_ADMINS in all databases

Read on to see how.

Comments closed

Azure SQL Championship

Published 2020-10-08 by Kevin Feasel

Mala Mahadevan announces a contest:

Learning can be drudgery, it can also be fun. One of the fun ways to learn Azure is to take part in Azure SQL Championship – a joint attempt by Microsoft and PASS to promote Azure learning. From October 12-30, there will be daily quizzes/simple challenges to solve. If you do it right you have a chance to win some fabulous prizes as below:

Read on to learn more, including the prizes on offer.

Comments closed

Making Use of Sort Rewinds: Closest Match

Published 2020-10-08 by Kevin Feasel

Paul White follows up on an article:

In When Do SQL Server Sorts Rewind? I described how most sorts can only rewind when they contain at most one row. The exception is in-memory sorts, which can rewind at most 500 rows and 16KB of data.

These are certainly tight restrictions, but we can still make use of them on occasion.

To illustrate, I am going reuse a demo Itzik Ben-Gan provided in part one of his Closest Match series, specifically solution 2 (modified value range and indexing).

Click through for the explanation.

Comments closed

Probability Distributions in Real Life

Published 2020-10-07 by Kevin Feasel

Stephanie Glen gives us examples of where specific probability distributions appear naturally:

If you’re in the beginning stages of your data science credential journey, you’re either about to take (or have taken) a probability class. As part of that class, you’re introduced to several different probability distributions, like the binomial distribution, geometric distribution and uniform distribution. You might be tempted to skip over some elementary topics and just scrape by with a bare pass. Because, let’s face it–the way probability is taught (with dice rolls and cards) is far removed from the glamor of data science. You may be wondering

When am I ever going to calculate the probability of five die rolls in a row in real life?

Click through for the answer and for a chart provides different scenarios for real-world probability distributions.

Comments closed

When SQL Server Sorts Can Rewind

Published 2020-10-07 by Kevin Feasel

Paul White turns back the hands of time:

Sorts use storage (memory and perhaps disk if they spill) so they do have a facility capable of storing rows between loop iterations. In particular, the sorted output can, in principle, be replayed (rewound).

Still, the short answer to the title question, “Do Sorts Rewind?” is:

Yes, but you won’t see it very often.

Read the whole thing.

Comments closed

Automating Power BI Report Deployment

Published 2020-10-07 by Kevin Feasel

Martin Schoombee continues a series on Power BI automation:

Deploying the report is seemingly straight-forward, but there are some risks we need to consider:

– What should we do if the report already exists?
– If the dataset exists, what should we do if there are other reports that use this (shared) dataset?

The last item is a bit of an edge-case that we’ll have to dive deeper into, but let’s look at the basic cmdlet first.

Click through to see how, as well as some thoughts on those risks.

Comments closed