Press "Enter" to skip to content

Author: Kevin Feasel

Direct Query of Flat Files in Postgres via file_fdw

Published 2024-08-14 by Kevin Feasel

Semab Tariq uses a Postgres extension:

The file_fdw (Foreign Data Wrapper) is a PostgreSQL extension that lets you access data stored in flat files, like CSV files, as if they were regular tables in your PostgreSQL database. This is useful for integrating external data sources without needing to import the data directly into your database.

file_fdw is a contrib module, meaning it’s an additional feature included with PostgreSQL but not part of its core functionality. Contrib modules provide extra capabilities and enhancements beyond the core database system.

At first, I was going to write that the mechanism looks a lot like PolyBase in SQL Server. But in actuality, it’s more like a hybrid of PolyBase and OPENROWSET, as there’s no definition of external data sources or file formats, but there is the creation of an external (“foreign”) table.

Comments closed

Building a Microsoft Fabric AI Skill to Generate Data

Published 2024-08-13 by Kevin Feasel

Sandeep Pawar tries out AI Skills in Microsoft Fabric:

In the last blog I wrote, I showed how to call the AI Skills endpoint in a Fabric notebook. Being able to call the endpoint programmatically creates many opportunities to integrate AI Skills in different applications. One that I thought of was using AI Skills as a function. Function Calling or Tools is a specific use case in Gen AI to create structured output based on a function or behavior instructed by the user. I am not referring to that as AI Skills can only return a table. Instead, what if you created a number of these AI Skills that are grounded in your data with specific functions built to get the intended output? You could serve/share these with end users who can call these functions to generate data/results. Think of these as macros in Excel.

Click through for an example of how it works.

Comments closed

Concurrent Index Creation in Postgres

Published 2024-08-13 by Kevin Feasel

Shayon Mukherjee recommends against a particular technique:

As a developer, you might have encountered situations where creating an index in PostgreSQL fails due to lock timeouts. In such scenarios, it’s tempting to use the IF NOT EXISTS as a quick fix and move on. However, this approach can lead to subtle and hard-to-debug issues in production environments.

Click through to learn more about how concurrent index creation works in Postgres and why the use of IF NOT EXISTS might not work the way you want.

Comments closed

Highlighting a Single Data Point in Power BI

Published 2024-08-13 by Kevin Feasel

Kurt Buhler points something out:

Effective visualizations provide context so that you can interpret the numbers and what they mean to you. Is this number bad or is it good? This is particularly important for visuals that aim to provide a quick, 3-second overview, like cards, KPIs, and simple trendlines. You can provide context by comparing to a target, but if no target is available, you can also compare to a measure of central tendency, like the average or median. However, instead of comparing to an aggregate, you might also want to compare to other categories.

Consider the following example, which shows the desired end result for this article: a plot which highlights a selected value so that the user can compare it to all others. This example uses some DAX and formatting with a line chart and scatterplot to achieve the result of a joint plot atop a jitter plot. If you want to learn more about what a joint plot or a jitter plot is, we gave an overview of these and similar chart types in a previous article.

This is something I find frustratingly difficult with Power BI. Kurt does a great job of showing how to get there, but it seems like it should be a lot easier to do.

Comments closed

Thoughts on Start-DbaMigration in dbatools

Published 2024-08-13 by Kevin Feasel

Chad Callihan shares an experience:

It’s time to follow up on a post from a few months ago where I said I’d give my thoughts on Start-DbaMigration from dbatools after picking up more experience. Along with Start-DbaMigration I picked up some experience with Copy-DbaDatabase to migrate databases from one server to another. Below are some thoughts on the good and bad.

Read for Chad’s scenario and some of the good and the bad of Start-DbaMigration.

Comments closed

Migrating an Azure Data Studio Connection List

Published 2024-08-13 by Kevin Feasel

Josephine Bush packs up and moves down the road:

I wanted to migrate some connections without recreating them and wondered how to do that in Azure Data Studio. It turns out to be easy. I post about whatever I’m working on, and here’s a short one for you today.

Read on to see how to do this on Windows and MacOS. But that leaves the most critical question: what about on Linux?

Linux follows the same pattern as Windows: File –> Preferences –> Settings, and then Data –> Connection.

Comments closed

Service Broker Blocking on DisableQ

Published 2024-08-13 by Kevin Feasel

Tom Zika is angling to become the Service Broker Baron:

I was paged about a blocking chain where the blocked resource was a Service Broker queue, the lead blocked transaction was called CSbRollbackHandlerTask::DisableQ, the lock mode was SCH-M and even lock partitioning was involved. I won’t repro fully this time, but I’ve covered lock partitioning repro in my post Async-stats-update-causing-blocking. The goal was to capture an instance of transaction CSbRollbackHandlerTask::DisableQ with the same lock mode.

Click through for a demo and how to troubleshoot the issue.

Comments closed

Cosmos DB HTAP into Azure Synapse Analytics and Microsoft Fabric

Published 2024-08-13 by Kevin Feasel

Paul Hernandez doesn’t want to write ETL jobs:

In the ever-evolving landscape of data management and analytics, choosing the right tools and approaches is crucial for optimizing performance and achieving business goals. Two prominent solutions that have gained traction are Azure Synapse Link for Azure Cosmos DB and Mirroring in Microsoft Fabric. Both offer unique benefits and cater to different needs, making it essential to understand their differences and use cases.

Read on to see how each of these works, as well as a quick demonstration of efficacy.

Comments closed

An Overview of Cross-Validation

Published 2024-08-12 by Kevin Feasel

Vinod Chugani explains the benefit of cross-validation in a data science project:

Many beginners will initially rely on the train-test method to evaluate their models. This method is straightforward and seems to give a clear indication of how well a model performs on unseen data. However, this approach can often lead to an incomplete understanding of a model’s capabilities. In this blog, we’ll discuss why it’s important to go beyond the basic train-test split and how cross-validation can offer a more thorough evaluation of model performance. Join us as we guide you through the essential steps to achieve a deeper and more accurate assessment of your machine learning models.

Click through for the full article.

Comments closed

Counting Character Occurrences in R

Published 2024-08-12 by Kevin Feasel

Steven Sanderson counts the ways:

Counting the occurrences of a specific character within a string is a common task in data processing and text manipulation. Whether you’re working with base R or leveraging the power of packages like stringr or stringi, R provides efficient ways to accomplish this. In this post, we’ll explore how to do this using three different methods.

Read on for three separate examples.

Comments closed