Press "Enter" to skip to content

Curated SQL Posts

Making a Newsletter Template in R

Published 2022-11-22 by Kevin Feasel

Benjamin Smith’s ideas are intriguing to me and I wish to subscribe to his newsletter:

Jinja is a powerful templating engine that is useful in a variety of contexts. Recently, I discovered how its possible to use the power of Jinja syntax in R with the jinjar package written by David C Hall. With jinjar and the tidyRSS package by Robert Myles it is possible to make an email template that can provide short and informative updates. In his blog, I’m going to share how the jinjar and tidyRSS packages work and show how to combine them to make a simple daily email newsletter.

Read on to learn how.

Comments closed

MySQL Database Backups with mydumper

Published 2022-11-22 by Kevin Feasel

Lukas Vileikis continues a series on MySQL backup options:

There are many tools we can use to back up our MySQL databases. Some are well-known and used by the best technology companies out there (mysqldump comes to mind), and some are a little less famous, but still have their place in the MySQL world. Enter mydumper – the tool is built by the engineering team over at Percona and it‘s supposedly created to address performance issues caused by mysqldump.

Read on to see what it is and how it works.

Comments closed

Time Zone Conversion in SQL Server

Published 2022-11-22 by Kevin Feasel

Ed Pollack wants to know what time it is:

Converting a current time from one time zone to another is relatively easy. Regardless of whether daylight savings is involved or not, one simply needs to retrieve the current time in both time zones, find the difference, and apply that difference as needed to date/time calculations. Historical data is trickier, though, as times from the past may cross different daylight savings boundaries.

This article dives into all the math required to convert historical times between time zones. While seemingly academic in nature, this information can be used when building applications that interact between time zones and need to apply detailed rules to those applications and their users. These calculations will be demonstrated in T-SQL and a function built that can help in handling the math for you.

The pro tip is to store all data in UTC and perform date and time calculations at the edge, where you know the user’s time zone. Ed has plenty of good advice in here as well.

Comments closed

Automating Archive Table Creation

Published 2022-11-22 by Kevin Feasel

Aaron Bertrand doesn’t want to archive things by himself every month:

Earlier in this series (part 1 | part 2), I wrote at a high level about how to solve issues with ever-growing log tables without large delete operations or data movement to a secondary archive table. In this tip, I’ll share a few code snippets you can use to automate the generation of objects to help make these solutions hands-free.

Read on for the tips.

Comments closed

Loading Normalized Data into Cosmos DB

Published 2022-11-22 by Kevin Feasel

Koen Verbeeck does a bit of shuffling:

I loaded the data into a table in Azure SQL DB. For demo purposes, I want to transfer this data from a SQL table to a container in Azure Cosmos DB (with the NoSQL API). There are plenty of resources on the web on how to transfer a simple relational table to Cosmos DB, but I have some additional complexity. One column – flavor profiles – contains a list of flavors that is assigned to a beer.

Click through for one way to organize the data when dealing with arrays.

Comments closed

Essential Power Tools for Power BI Desktop

Published 2022-11-22 by Kevin Feasel

Jason Cockington makes several recommendations of external tools for Power BI Desktop:

The External Tools ribbon is a feature that has been available in Power BI Desktop since the July 2020 release.  This feature should be considered essential to anyone who is regularly working in the Power BI space.    In this article I recommend my top five External Tools, that I consider essential to any Power BI developer.

Read on for the recommendations. I regularly use two of the five but these do look good.

Comments closed

Fitting 10 Pounds of Data into a 5-Pound Power BI

Published 2022-11-22 by Kevin Feasel

Chris Webb does some compacting:

Power BI can handle large data volumes, but just how much data can you load into Power BI? Can Power BI handle big data? How big is “big” anyway? These are questions you may have when you’re starting out on a new Power BI project and the answers can be hard to find. Indeed the answer in most cases is “it depends”, which isn’t very helpful. In this post I will try to explain the various limits on Power BI dataset size and how you can know if you’re likely to hit them.

Click through to learn more about these limitations.

Comments closed

Disentangling a Diverging Bar Chart

Published 2022-11-21 by Kevin Feasel

Simon Rowe tackles a problem:

Have you ever found yourself looking at a graph for the first time and felt immediately overwhelmed by the sheer volume of information presented?  It can leave you wondering not only how to understand it, but also what decisions led to the creation of such a complex visual in the first place.  

Nobody sets out to make a confusing communication. Most dashboards or visuals start out quite simple…but over time they may be leveraged to do more, provide more info, and support more requirements all at once. After all that, by the time you encounter it for the first time, it’s thoroughly impenetrable.  

Read on for one extreme example of scope creep and how to draw out the most important messages.

Comments closed

Making Sets and Lists with KQL

Published 2022-11-21 by Kevin Feasel

Robert Cain is making a list and checking it twice:

In previous posts, I’ve mentioned using certain functions and operators to investigate conditions in your system. Naturally you’ll need to create lists of those items, based on certain conditions.

For example, you may want to get a list of the counters associated with an object. Or, you may want to get a list of computer where a certain condition is met.

In this article we’ll see how to get those lists using the Kusto make_set and make_list functions.

Read on to see how these two functions work, as well as their conditional brethren.

Comments closed

Backup Options for MySQL

Published 2022-11-21 by Kevin Feasel

Lukas Vileikis continues a series on backing up MySQL. Part 2 involves Percona XtraBackup:

As already stated above, Percona XtraBackup is one of the primary offerings for MySQL & Percona database administrators developed by Percona. The tool is an open-source backup utility that does not lock our databases during the backup processes it performs. Percona says that their tool can provide automatic verification of backups that have been taken, offer fast dumping and restore times, and above all, it’s supported by their award-winning consulting services helping us make sure that our data and its backups are in safe hands by day and by night.

Part 3 covers mysqlpump:

mysqlpump is a backup utility that is used via the command-line interface. The tool is very similar to mysqldump in that it provides us with the ability to take logical backups, but also different at the same time – the goal of mysqlpump is to be an extendable, parallel-supporting replacement of mysqldump. In their blog from 2015, MySQL team said that one of the primary aims of introducing mysqlpump was not be forced to implement legacy functionality that is provided by mysqldump.

Read on to see how both of these work.

Comments closed