Press "Enter" to skip to content

Curated SQL Posts

Creating a Clickable Word Cloud with Shiny

Published 2023-04-28 by Kevin Feasel

Mandy Norrbo builds a word cloud:

Word clouds are a visual representation of text data where words are arranged in a cluster, with the size of each word reflecting its frequency or importance in the data set. Word clouds are a great way of displaying the most prominent topics or keywords in free text data obtained from websites, social media feeds, reviews, articles and more. If you want to learn more about working with unstructured text data, we recommend attending our Text Mining in R course

Usually, a word cloud will be used solely as an output. But what if you wanted to use a word cloud as an input? For example, let’s say we visualised the most common words in reviews for a hotel. Imagine we could then click on a specific word in the word cloud, and it would then show us only the reviews which mention that specific word. Useful, right?

Read on to see how you can create one of these.

Comments closed

Hybrid ML and Rules-Based Fraud Detection

Published 2023-04-28 by Kevin Feasel

Ayodeji Ogunlami mixes approaches:

In developing this hybrid system, sets of rules are required as well as a machine learning model. I would be making use of a vehicle insurance dataset from Kaggle in this demonstration.

The dataset can be downloaded from this link: https://www.kaggle.com/datasets/shivamb/vehicle-claim-fraud-detection

The ML model would be built using a random forest classifier on Azure Databricks using Pyspark.

This seems to be the most sensible approach, especially given how rare actual fraud incidents are and what that imbalance does to classification algorithms.

Comments closed

Knitting R-Markdown Files into Google Docs

Published 2023-04-28 by Kevin Feasel

Benjamin Smith makes a Google Doc:

RMarkdown is a powerful framework for writing a documents that contain a mixture of text, code, and the output of the code. Popular output formats for RMarkdown Documents (.Rmd) include HTML, PDF and Word Documents. It is also possible to output RMarkdown documents as part of a static website using blogdown package and is (still!) possible to publish RMarkdown documents to WordPress sites as well (like this one)!

Recently, I started to look into the possibility of outputting an .Rmd file as a Google Doc, but I was unable to locate any out-of-box solutions. After looking into the issue I developed a small function that makes it possible!

Click through for that function.

Comments closed

Portfolio Management for Creating a Technology Strategy

Published 2023-04-28 by Kevin Feasel

Kevin Sookocheff busts out the 2×2 matrix:

Application Portfolio Management (APM) draws inspiration from financial portfolio management, which has been around since at least the 1970s. By looking at all applications and services in the organization and analyzing their costs and benefits, you can determine the most effective way to manage them as part of a larger overall strategy. This allows the architect or engineering leader to take a more strategic approach to managing their application portfolio backed by data. Portfolio management is crucial for creating a holistic view of your team’s technology landscape and making sure that it aligns with business goals.

This is for C-levels and VPs rather than individual contributors, but acts as a good way of thinking about a portfolio of applications and what to do with each.

Comments closed

Converting JSON to a Relational Schema with KQL

Published 2023-04-28 by Kevin Feasel

Devang Shah does some flattening and moving:

In the world of IoT devices, industrial historians, infrastructure and application logs, and metrics, machine-generated or software-generated telemetry, there are often scenarios where the upstream data producer produces data in non-standard schemas, formats, and structures that often make it difficult to analyze the data contained in these at scale. Azure Data Explorer provides some useful features to run meaningful, fast, and interactive analytics on such heterogenous data structures and formats. 

In this blog, we’re taking an example of a complex JSON file as shown in the screenshot below. You can access the JSON file from this GitHub page to try the steps below.

Click through for the example, which is definitely non-trivial.

Comments closed

Common Challenges Implementing PySpark Code

Published 2023-04-27 by Kevin Feasel

Amlan Patnaik looks at some common implementation problems:

Pyspark has become one of the most popular tools for data processing and data engineering applications. It is a fast and efficient tool that can handle large volumes of data and provide scalable data processing capabilities. However, Pyspark applications also come with their own set of challenges that data engineers face on a day-to-day basis. In this article, we will discuss some of the common challenges faced by data engineers in Pyspark applications and the possible solutions to overcome these challenges.

Read on for five such challenges.

Comments closed

Sampling and Inconsistent Result Counts

Published 2023-04-27 by Kevin Feasel

Kevin Wilkie does the math:

One of the things you may have noticed after reading our last post on Top (found here) is that sometimes SAMPLE doesn’t give the answer you want.

For example, we can run the same query to get 20% of the table. Remember that this table has 290 rows in total.

After seeing two runs return 69 and then 50 rows, respectively, Kevin digs in and finds out why. This got me thinking about whether a one-pass scan, assigning values based on a uniform distribution (which sounds like what is happening here) would be faster than random sampling without replacement over an array of 8-byte pointers, but then I realized that it’s way too early in the morning for me to be thinking architecture.

Comments closed

Adding Microseconds to ADF Timestamps

Published 2023-04-27 by Kevin Feasel

Rayis Imayev can’t wait for the next second:

The current addToTime function (https://learn.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions#addToTime) in Azure Data Factory (ADF) only supports a specific set of time units ranging from Year to Seconds. Since I needed to increment a timestamp by microseconds, I had to find an alternative solution in ADF. Here are my findings on how to do this using an alternate approach.

Click through for Rayis’s solution to the problem.

Comments closed

A Post-Migration Go-Live Plan for SQL Server 2022

Published 2023-04-27 by Kevin Feasel

Brent Ozar takes it slow:

You’re planning to migrate to SQL Server 2022, and you want your databases to be faster after the migration.

This is NOT a blog post about how to migrate – that’s the same as it’s been for a long time. Go build the new 2022 servers, and use log shipping or database mirroring to sync the old and new servers. (I’m not a fan of using Distributed Availability Groups to do version upgrades. You can, but it is a heck of a lot of moving parts to set up for a one-time migration.)

This is a blog post about what to do after you migrate:

Click through for five steps and what they entail.

Comments closed

Version Control for Power BI Datasets

Published 2023-04-27 by Kevin Feasel

Richard Swinbank improves on a prior version control system:

In the previous post, I outlined a possible workflow for Power BI development, and implemented an Azure DevOps pipeline to show how steps in such a workflow could be automated. To build the pipeline I stored an entire .pbix report file – data and all – in version control, which is a problem for at least two reasons:

  • storing large report files in a version control system won’t scale well
  • datasets may contain confidential or sensitive data which must be kept out of version control.

In this post I’ll look at separating a report’s dataset from its visuals, version controlling the standalone dataset (without data), and deploying the dataset automatically to Power BI.

Read on for the process.

Comments closed