Press "Enter" to skip to content

Day: July 8, 2020

FlinkSQL in Cloudera Streaming Analytics

Published 2020-07-08 by Kevin Feasel

Marton Balassi announces support for FlinkSQL in Cloudera Streaming Analytics:

Our 1.2.0.0 release of Cloudera Streaming Analytics Powered by Apache Flink brings a wide range of new functionality, including support for lineage and metadata tracking via Apache Atlas, support for connecting to Apache Kudu and the first iteration of the much-awaited FlinkSQL API.

Flink’s SQL interface democratizes stream processing, as it caters to a much larger community than the currently widely used Java and Scala APIs focusing on the Data Engineering crowd. Generalizing SQL to stream processing and streaming analytics use cases poses a set of challenges: we have to tackle expressing infinite streams and timeliness of records. 

All is happening as Feasel’s Law foretold.

Comments closed

TF-IDF using Spark .NET

Published 2020-07-08 by Kevin Feasel

Ed Elliott shows how you can use the Spark .NET library to perform machine learning in Apache Spark:

Native spark has two API’s for creating your ML applications. The historical one is Spark.MLLib and the newer API is Spark.ML. A little bit like how there was the old RDD API which the DataFrame API superseded, Spark.ML supersedes Spark.MLLib.

At the end of last year, .NET for Apache Spark had no support for either the Spark.ML or Spark.MLLib API’s so I started implementing Spark.ML. In a similar way that .NET for Apache Spark supports the DataFrame API and not the RDD API, I started with Spark.ML and I believe that having the full Spark ML API will be enough for anyone.

It’s awesome that Ed is helping to move Spark .NET forward in this way.

Comments closed

Licensing for SQL Server Reporting Services

Published 2020-07-08 by Kevin Feasel

Denny Cherry explains licensing scenarios for SQL Server Reporting Services:

When you license SQL Server (of which the SQL Server Reporting Services engine is a part of) you license what is called the OSE or Operating System Environment. This is basically the OS that has SQL Server installed on it. Now, this can be the virtualization host (VMware or Hyper-V) or it can be the Windows Server (SSRS isn’t available on Linux, so we don’t have to deal with that, but if SSRS was available on Linux the rules would be the same as Windows). You can install SQL Server (or SSRS) as many times inside that OSE as you want to, but you can’t install SQL Server (or SSRS) on any other machines.

SSRS licensing isn’t too difficult to understand, relatively speaking.

Comments closed

Derived Table Nesting and Performance

Published 2020-07-08 by Kevin Feasel

Itzik Ben-Gan digs into some of the performance considerations around nested derived tables:

Unnesting/substitution of table expressions is a process of taking a query that involves nesting of table expressions, and as if substituting it with a query where the nested logic is eliminated. I should stress that in practice, there’s no actual process in which SQL Server converts the original query string with the nested logic to a new query string without the nesting. What actually happens is that the query parsing process produces an initial tree of logical operators closely reflecting the original query. Then, SQL Server applies transformations to this query tree, eliminating some of the unnecessary steps, collapsing multiple steps into fewer steps, and moving operators around. In its transformations, as long as certain conditions are met, SQL Server can shift things around across what were originally table expression boundaries—sometimes effectively as if eliminating the nested units. All of this in attempt to find an optimal plan.

In this article I cover both cases where such unnesting takes place, as well as unnesting inhibitors. That is, when you use certain query elements it prevent SQL Server from being able to move logical operators in the query tree, forcing it to process the operators based on the boundaries of the table expressions used in the original query.

That’s on my list for a second reading.

Comments closed

Understanding LOOKUPVALUE in DAX

Published 2020-07-08 by Kevin Feasel

Alberto Ferrari explains the LOOKUPVALUE function:

LOOKUPVALUE is one of the most widely used functions, especially for DAX developers who come from an Excel background. Indeed, the behavior of LOOKUPVALUE is very close to the behavior of the widely-adopted VLOOKUP function in Excel. Yet, there are important differences between the two; quite often, newbies use LOOKUPVALUE instead of creating a relationship between tables that ensures higher flexibility and higher performance.

This article is an introduction to LOOKUPVALUE. If you are interested in studying the internals of LOOKUPVALUE in more detail, make sure to check the advanced LOOKUPVALUE article; in that article, we describe the behavior of the function in detail along with several performance considerations.

Click through for the article.

Comments closed

Local Variables with TOP and ORDER BY

Published 2020-07-08 by Kevin Feasel

Erik Darling points out issues with using local variables. First up is with TOP:

In case you missed it for some reason, check out this post of mine about local variables. Though it’s hard to imagine how you missed it, since it’s the single most important blog post ever written, even outside of SQL Server. It might even be more important than SQL Server. Time will tell.

While live streaming recently about paging queries, I thought that it might make an interesting post to see what happens when you use variables in places other than the where clause.

After several seconds of thinking about it, I decided that TOP would be a good enough place to muck around.

After that is ORDER BY:

I see this kind of pattern a lot in paging queries where people are doing everything in their power to avoid writing dynamic SQL for some reason.

It’s almost as if an entire internet work of SQL Server knowledge and advice doesn’t exist when they’re writing these queries.

Quite something. Quite something indeed.

I’d call out Erik’s ORDER BY examples by saying “C’mon, nobody does that!” if I hadn’t actually seen people do that…

Comments closed

Kubernetes, SQL Server, and Kerberos

Published 2020-07-08 by Kevin Feasel

Raul Gonzalez walks us through one problem with configuring SQL Server to run in an Availability Group over Kubernetes:

The problem of Kerberos is that is not easy to configure and multiple times results in the well known Anonymous Logon Error, aka Double Hop.

That’s why you will find plenty of IIS and other applications out there, using SQL logins (Impersonation Users), because Windows Authentication can be really frustrating and applications won’t be able to connect to SQL Server otherwise.

There are multiple resources in the internet that explain the Double-Hop issue, so that won’t be the scope of this post, but I will show how to correctly configure SPNs to SQL Server Availability Groups, which is the first link in the Kerberos chain.

Kubernetes isn’t the only place where you’ll find the need to set SPNs, either.

Comments closed

What MAXDOP Controls

Published 2020-07-08 by Kevin Feasel

Pedro Lopes gives us an explanation of what MAXDOP really does for us:

There are plenty of blogs on these topics, and the official documentation does a good job of explaining these (in my opinion). If you want to know more about the guidelines and ways to override for specific queries, refer to the Recommendations section in the Configure the max degree of parallelism Server Configuration Option documentation page.

But what does MAXDOP control? A common understanding is that it controls the number of CPUs that can be used by a query – previous revisions of the documentation used this abstraction. And while that is a correct abstraction, it’s not exactly accurate.

This is definitely a nice companion piece to Paul White’s article on how MAXDOP works.

Comments closed