A Fine Slice Of SQL Server
Robert Cain continues a look at KQL query syntax:
As with other languages such as SQL, KQL has an operator for returning a unique list of values in a column:
distinct. Using this you can return the values in a column, but only once, removing any duplicate values from the result set.
Click through for a few examples of how you can use distinct as well as one terrible pun.
Tzvia Gitlin Troyna has a flow chart for us:
Azure Data Explorer is a big data interactive analytics platform that empowers people to make data driven decisions in a highly agile environment. The factors listed below can help assess if Azure Data Explorer is a good fit for the workload at hand. These are the key questions to ask yourself.
The following flowchart table summarize the key questions to ask when you’re considering using Azure Data Explorer.
In addition to the flow chart, there is a table of three common patterns of interaction which ADE can do well.
Comments closedRobert Cain continues to project:
In the previous post, Fun With KQL – Project, we took a dive into the
projectoperator and the ways it could be used. Theprojectoperator has several variants:project-away,project-rename,project-keep, andproject-reorder. This post will take a quick look at each.
Click through to check out each one of these.
Comments closedDavid Giard builds some tests:
Our application contains many functions that return data stored in Azure Data Explorer (ADX). We wrote these functions in Kusto Query Language (KQL) and each function returns a set of data based on the arguments passed. Although developers tested these functions as they wrote them, we needed a way to validate that the functions continued to work as the code and the data changed.
Automated Unit testing is an essential part of any application development life cycle. It validates that code works properly and minimizes the risk that future code changes will break existing functionality.
In this article, I will discuss the approach we took in automating the testing of ADX functions.
Click through to see how to use the assert() function and build some tests.
Robert Cain continues a series on the Kusto Query Language:
So far in my Fun With KQL series, we have used the column tool, found on the right side of the output pane and described in my original post Fun With KQL – The Kusto Query Language, to arrange and reduce the number of columns in the output.
We can actually limit the number of columns, as well as set their order, right within our KQL query. To accomplish this we use the
projectoperator.
Read on for several good uses of the project operator.
For our current project, we are capturing into ADX the location of vehicles over time. Our customer asked us to create a function that would return all vehicles that are within a given bounding box in a given time period. This is useful information when they want to know when a vehicle returns to a building, a neighborhood, or a city.
In this article, I will show how this can be accomplished using built-in functions, the limitations of those functions, and ways to overcome those limitations.
Read on for the naive approach as well as a very interesting one using S2 cells.
Comments closedRobert Cain continues a series on learning KQL:
When dealing with data, it’s not at all uncommon to want to create a new column of data by performing a calculation with two other columns. A common example is taking two stored columns, the purchase price of an item, and its shipping cost, then adding them together to get a column which wasn’t stored in your dataset, the total amount of the sale.
The Kusto Query Language lets you accomplish this through the
extendoperator. This operator allows you to manifest new columns in your output data, based on calculations.
As always, Robert has plenty of examples available to view.
Comments closedRobert Cain continues a series on KQL:
When data is analyzed, it is seldom done on a row by row basis. Instead, data analysts look at the big picture, looking at total values. For example, the total number of times the disk transfer counter is recorded for a time period may give an indication of disk utilization.
To aggregate these values with KQL, we’ll use the
summarizeoperator.
Read on for plenty of demos.
Comments closedRobert Cain continues a series on KQL operations:
The previous post in the series covered the
takeoperator. In that post I mentioned thattakewas one of the simplest operators in KQL. But it is not the simplest, that honor goes to thecountoperator.The
countoperator does nothing more than takes the piped in dataset and returns the number of rows in it. We’ll see more in a moment.
Click through to see more.
Comments closedRobert Cain continues a series on KQL:
In this example we took the
Perftable, and piped the dataset it generated into thetakeoperator. We indicated we wanted to get10rows, which it did as you can see.It is important to understand that
takegrabs these rows at random. Further, there is no consistency between each execution oftake. You are likely to get a different set of rows with each execution. Let me run the exact same query again, so you can see the different data being returned.
Take if you want a slice, if you want a piece, if it feels alright.
Comments closed