Press "Enter" to skip to content

Author: Kevin Feasel

Approximate Count Distinct

Published 2021-03-09 by Kevin Feasel

Deepthi Goguri looks at the APPROX_COUNT_DISTINCT() function in SQL Server 2019:

In the previous post, we learned about Scalar UDF Inliningfeature. In this post, let’s focus on the Approximate QP with Approx_Count_Distinct feature introduced as a part of SQL Server 2019 Intelligent Query Processing features. This feature is useful to get the approximate count of distinct values just like the  Count distinct function to get the distinct number of records but this new feature will take less amount of CPU and memory to process the query.

Click through for more info. The algorithm itself is also pretty interesting.

Comments closed

SQL Server Data Types: Bit vs the World

Published 2021-03-09 by Kevin Feasel

Kevin Chant is a fan of the bit type:

I decided to tweak it a bit for this post, to provide a humorous comparison between the bit data type and others that are available in SQL Server. In reality, this won’t cover every single one.

By the end of this post, you will some see pitfalls to using certain data types and some tips on how to avoid them. Plus, you will find out which data type is my least favourite to use.

Click through for a “haha-just-serious” take on an underappreciated datatype which nonetheless can’t decide if it’s a boolean or not.

Comments closed

Spring Cleaning Shiny Projects

Published 2021-03-08 by Kevin Feasel

Mirai Solutions has some tips on cleaning up Shiny apps:

How to apply the spring cleaning principles and advanced programming to your Shiny App.

1. Deep breeze and allocate some time

Do not avoid spring cleaning simply because you don’t know where to start from. Prioritize some time for the task and get inspired by our following points.

Click through for advice on tools and processes to make this code easier to understand. H/T R-Bloggers

Comments closed

Capturing Extended Properties when a Table is Dropped

Published 2021-03-08 by Kevin Feasel

Thomas Rushton attempts to preserve extended property history:

Custom extended properties can be a useful tool when creating a self-documenting database. However, in an environment where tables are routinely dropped and recreated by software/systems not within your control, it may be desirable to have these stored so they can be repopulated when the table is recreated.

Read on to learn about the results of each test. Also read on for a use of the term “faff” in its appropriate context.

Comments closed

Hash Distributions, Shuffling, and Data Types

Published 2021-03-08 by Kevin Feasel

Reiss McSporran explains an issue in Azure Synapse Analytics dedicated SQL pools:

Oh dear. Shuffle. On the surface, shuffle is not necessarily a bad thing and may even be expected, but not in this scenario. So what is shuffle and why can it be bad?

Shuffle occurs when a part of a distributed table is moved to a different node during query execution. To do this a hash value is computed using the join columns, the node is then found that has that hash value and the row is then sent to that node for processing. If the tables you’re joining on have different hash distributions, you’d expect this to happen, (hence why it’s not always a bad thing!), but in this case I’ve used the same value to hash both of my tables, so what’s gone wrong?

Click through to see what’s gone wrong.

Comments closed

Power BI: Parquet Files and Streamed Binary Values Error

Published 2021-03-08 by Kevin Feasel

Chris Webb explains an error:

If you’re using the new Parquet connector in Power BI there’s a chance you will run into the following error:

Parameter.Error: Parquet.Document cannot be used with streamed binary values.
Details:
[Binary]

This isn’t a bug or anything that can be fixed, so it’s important to understand why it occurs and what you can do about it.

Click through for the explanation.

Comments closed

The Importance of Configuring SQL Server Agent Alerts

Published 2021-03-08 by Kevin Feasel

Chad Callihan has a story in three acts:

Once upon a time, I was having a relaxing morning…until I received an e-mail alert about an Error Number 825 on a database. The error stated:

‘D:\sql_dat\DatabaseName.mdf_MSSQL_DBCC47’ at offset 0x00000004b9e000 succeeded after failing 1 time(s) with error: 121(The semaphore timeout period has expired.). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

That’s not how you want your day to go. Fortunately, the database was not one that needed to be online 24/7 so I felt I could relax a little bit while starting to investigate. At this point, the issue seemed to be with one database.

Click through to see if everyone lived happily ever after.

Comments closed

Bayesian vs Frequentist Approaches to Machine Learning

Published 2021-03-05 by Kevin Feasel

Ajit Jaokar has an interesting series. Here’s part one:

The arguments / discussions between the Bayesian vs frequentist approaches in statistics are long running. I am interested in how these approaches impact machine learning. Often, books on machine learning combine the two approaches, or in some cases, take only one approach. This does not help from a learning standpoint.  

So, in this two-part blog we first discuss the differences between the Frequentist and Bayesian approaches. Then, we discuss how they apply to machine learning algorithms.

Part two extends from there:

Sampled from a distribution: Many machine learning algorithms make assumptions that the data is sampled from a frequency. For example, linear regression assumes gaussian distribution and logistic regression assumes that the data is sampled from a Bernoulli distribution. Hence, these algorithms take a frequentist approach

My biases push me toward Bayesian approaches, and I really like what I see in Stan, but these techniques do often require a lot more processing power.

1 Comment

Testing Kafka with Kerberos and SSH

Published 2021-03-05 by Kevin Feasel

Daniel Osvath has a guide for us:

Kerberos authentication is widely used in today’s client/server applications; however getting started with Kerberos may be a daunting task if you don’t have prior experience. Information on setting up Kerberos with an SSH server and client on the web is fragmented and hasn’t been presented in a comprehensive end-to-end way on a simple local setup.

At Confluent, several of our connectors for Apache Kafka® support Kerberos-based authentication. For development and testing of these connectors, we often leverage containers due to their fast, iterative benefits. This tutorial aims to provide a simple setup for a Kerberos test environment with SSH for a passwordless authentication that uses Kerberos tickets. You may use this as a guide for testing the Kerberos functionality of SSH-based client-server applications in a local environment or as a hands-on tutorial if you’re new to Kerberos. To understand the basics of Kerberos before diving into this tutorial, you may find this video helpful. Additionally, if you are looking for a non-SSH-based setup, the setup below for the KDC server container may also be useful.

Click through for two approaches to the problem.

Comments closed