Press "Enter" to skip to content

Day: October 4, 2023

Drawing Horizontal Box Plots in R

Published 2023-10-04 by Kevin Feasel

Steven Sanderson is not limited to one axis:

Boxplots are a great way to visualize the distribution of a numerical variable. They show the median, quartiles, and outliers of the data, and can be used to compare the distributions of multiple groups.

Horizontal boxplots are a variant of the traditional boxplot, where the x-axis is horizontal and the y-axis is vertical. This can be useful for visualizing data where the x-axis variable is categorical, such as species or treatment group.

Click through for an example using base R and ggplot2.

Comments closed

Reshaping Records using cdata

Published 2023-10-04 by Kevin Feasel

John Mount takes us through a common data wrangling problem:

In many data science projects we have the data, but it “is in the wrong format.” Fortunately re-formatting or reshaping data is a solved problem, with many different available tools.

For this note, I would like to show how to reshape data using the data algebra‘s cdata data reshaping tool. This should give you familiarity with a tool to use on your own data.

Click through for an example in Python. Mount and Nina Zumel also have an R package for cdata.

Comments closed

Safe Mode for Updates in MySQL

Published 2023-10-04 by Kevin Feasel

Chad Callihan is no dummy:

Did you know MySQL has a flag designed to prevent accidentally changing more data than you intended? If not, I think you’ll find it easy to remember as the flag has a memorable name: ‘i-am-a-dummy.’ If you have this flag set and leave off a WHERE clause when updating or deleting data, MySQL will prevent the statement from executing.

Let’s walk through an example using i-am-a-dummy and its “Safe Updates” Workbench counterpart.

Seems like this should be on by default for most servers.

Comments closed

SQL Server 2022 on SuSE Enterprise Linux Now Available in Azure Marketplace

Published 2023-10-04 by Kevin Feasel

Arvind Mahadevan has an announcement:

We are pleased to announce that we have worked with both SUSE and Microsoft engineering teams to release the latest SQL Server 2022 on SLES v15 SP5 Azure Marketplace image. This is in alignment with our goal to have the latest SQL Server on Linux Azure Marketplace images.

It’s a short post but does give us an idea of where they’re at on Linux support. Support for Ubuntu 22.04 is still in preview, so I’d expect that to come out soon as well.

Comments closed

String Regularization and Tokenization in SQL Server

Published 2023-10-04 by Kevin Feasel

Aaron Bertrand saves some space:

The Stack Exchange network logs a lot of web traffic – even compressed, we average well over a terabyte per month. And that is just a summarized cross-section of our overall raw log data, which we load into a database for downstream security and analytical purposes. Every month has its own table, allowing for partitioning-like sliding windows and selective indexes without the additional restrictions and management overhead. (Taryn Pratt talks about these tables in great detail in her post, Migrating a 40TB SQL Server Database.)

It’s no surprise that our log data is massive, but could it be smaller? Let’s take a look at a few typical rows. While these are not all of the columns or the exact column names, they should give an idea why 50 million visitors a month on Stack Overflow alone can add up quickly and punish our storage:

Click through for one technique Aaron has to tighten things up a bit.

Comments closed

Distinct Counts in Power BI and KQL

Published 2023-10-04 by Kevin Feasel

Dany Hoter needs a distinct count:

Calculating distinct counts on massive distributed datasets is not trivial.

Kusto (aka Azure Data Explorer/KQL database in Fabric) dcount and dcountif functions use a special algorithm to return an estimate of distinct counts

The new functions count_distinct and count_distinctif were recently added to calculate exact distinct counts. These two functions are much more expensive than the original ones.

Read on for more details on how this all works.

Comments closed