Joining Multiple Types Of Data With KSQL

Robin Moffatt has an example where he enriches streaming CSV data with information stored in MySQL:

This is a continuous query that executes in the background until explicitly terminated by the user. In effect, these are stream processing applications, and all we need to create them is SQL! Here all we’ve done is an enrichment (joining two sets of data), but we could easily add predicates to the data (simply include a WHERE clause), or even aggregations.

You can see which queries are running with the SHOW QUERIES; statement. All queries will pause if the KSQL server stops, and restart automagically when the KSQL server starts again.

The DESCRIBE EXTENDED command can be used to see information about the derived stream such as the one created above. As well as simply the columns involved, we can see information about the underlying topic, and run-time stats such as the number of messages processed and the timestamp of the most recent one.

It’s pretty easy to do; click through to see just how easy.

Joining Objects In Powershell

Shane O’Neill makes a discovery:

…if there is enough data to import this into the database & use T-SQL then you can bet that’s what I’m going to do! It’s what it was designed for, I’d find it easier, and it’s probably going to be faster after you hit a certain threshold.

However, if it’s small sets and the effort of importing the data is going to slow you down and break your flow…

Well, that doesn’t have to be the case anymore.

Read on to see joins in action.

Time Zone Conversion With M

Cedric Charlier shows how to perform time zone conversions with the M language in Power Query:

Everything is fine … except if I share my code with someone from another time zone. The function DateTimeZone.ToLocal is relying on regional settings and in that case my conversion should always be from UTC to “Brussels time”.

I didn’t find any other way to ensure that I’m always converting from UTC to “Brussels time” than implementing the conversion by myself. That’s the goal of the following function

Looks like there may not be a nice “convert to a different time zone” here like lubridate::with_tz() does in R.

Read-Only Replicas With Filled TempDB

David Fowler explains what could cause a read-only secondary replica in an Availability Group to have its tempdb fill up:

When I have an issue with tempdb filling up the first thing that I usually do is try to figure out exactly what the space has been allocated to.

You can quickly figure out what process has the most space allocated by using a quick query against dm_db_session_space_usage.

SELECT session_id, database_id, user_objects_alloc_page_count + internal_objects_dealloc_page_count AS TotalAllocatedPages
FROM sys.dm_db_session_space_usage
ORDER BY TotalAllocatedPages DESC

But what if you can see that there aren’t any pages allocated to sessions?  What could be taking up all the space?  Well let’s have a little look and see exactly where those pages are allocated.

Click through to see David’s results and explanation.

Sharing Power BI Content Via E-Mail

Steve Hughes looks at the security implications of being able to share Power BI reports through e-mail:

My account does not have Power BI Pro, but now I can try it for free for 60 days and get access to the data while I am on the trial. I clicked both options, because I can. The Upgrade account option would require me to pay for Pro. However, Try Pro for free works and I was able to access the report fully. I have successfully shared my corporate content with a personal user.

Steve shows us where you can go to disable this if you want, as well as places where you can see what content has been shared.

Finding Where Power BI Local Credentials Get Stored

Eugene Meidinger hunts down where those local Power BI credentials live:

With SSIS, you have to be careful to export the SSIS files without any sensitive information included. But what about Power BI? If you save the .PBIX files on OneDrive, can you be exposing yourself to a security risk?

Looking at things, it looks like credentials for data sources are stored globally, so one wouldn’t expect them to be in the .pbix files.

Read on as he does some more sleuthing and discovers the answer.

ggplot2 Geoms And Aesthetics

Tyler Rinker digs into ggplot2’s geoms and aesthetics:

I thought it my be fun to use the geoms aesthetics to see if we could cluster aesthetically similar geoms closer together. The heatmap below uses cosine similarity and heirarchical clustering to reorder the matrix that will allow for like geoms to be found closer to one another (note that today I learned from “R for Data Science” about the seriation package [] that may make this matrix reordering task much easier).

It’s an interesting analysis of what’s available within ggplot2 and a detailed look at how different geoms fit together with respect to aesthetic options.

Multi-Class Text Classification In Python

Susan Li has a series on multi-class text classification in Python.  First up is analysis with PySpark:

Our task is to classify San Francisco Crime Description into 33 pre-defined categories. The data can be downloaded from Kaggle.

Given a new crime description comes in, we want to assign it to one of 33 categories. The classifier makes the assumption that each new crime description is assigned to one and only one category. This is multi-class text classification problem.

    • * Input: Descript
    • * Example: “STOLEN AUTOMOBILE”
    • * Output: Category
    • * Example: VEHICLE THEFT

To solve this problem, we will use a variety of feature extraction technique along with different supervised machine learning algorithms in Spark. Let’s get started!

Then, she looks at multi-class text classification with scikit-learn:

The classifiers and learning algorithms can not directly process the text documents in their original form, as most of them expect numerical feature vectors with a fixed size rather than the raw text documents with variable length. Therefore, during the preprocessing step, the texts are converted to a more manageable representation.

One common approach for extracting features from the text is to use the bag of words model: a model where for each document, a complaint narrative in our case, the presence (and often the frequency) of words is taken into consideration, but the order in which they occur is ignored.

Specifically, for each term in our dataset, we will calculate a measure called Term Frequency, Inverse Document Frequency, abbreviated to tf-idf.

This is a nice pair of articles on the topic.  Natural Language Processing (and dealing with text in general) is one place where Python is well ahead of R in terms of functionality and ease of use.

Legible Function Chaining In R

John Mount shows a few techniques for legible function chaining with R:

The dot intermediate convention is very succinct, and we can use it with base R transforms to get a correct (and performant) result. Like all conventions: it is just a matter of teaching, learning, and repetition to make this seem natural, familiar and legible.

My preference is to use dplyr + magrittr because I really do like that pipe operator.  John’s point is well-taken, however:  you don’t need to use the tidyverse to write clean R code, and there can be value in using the base functionality.

Loading JSON-Based Data Into SQL Server From .NET

Chris Koester has a quick example demonstrating one way take JSON data from .NET code and load it into SQL Server:

Next we need to create a stored procedure that will accept JSON text as a parameter and insert it into the table. Two important points here:

  • JSON text must use the NVARCHAR(MAX) data type in SQL Server in order to support the JSON functions.

  • The OPENJSON function is used to convert the JSON text into a rowset, which is then inserted into the previously created table.

The whole process is quite easy; check it out.


March 2018
« Feb