A Fine Slice Of SQL Server
Robert Cain continues a series on KQL:
If you refer back to my post Fun With KQL – DateTime Arithmetic, you may recall that when you perform any math with datetime data types, such as subtracting one date from another to get the amount of elapsed time, the resulting data type is a timespan.
In this post, we’ll see how to format timespan data types using the
format_timespanfunction.
As always, Robert teaches by example here so go ahead and check that out.
Comments closedShaf Mahmood has a few updates for us:
The optimized autoscale feature has been in place and using reactive logic. It has been helping ADX users by adjusting the cluster size when there is an increase in resources due to ingestion or query load. The optimized autoscale feature has been further improved with predictive logic. This logic monitors the same metrics as the reactive logic and over time builds up the cluster usage pattern and uses this to forecast and plan the size of the cluster. The reactive logic is still used to ensure any forecast anomalies or usage pattern changes are still autoscaled appropriately.
There are a few cost-related updates but also updates to visuals, data ingestion, and more.
Comments closedGauri Mahajan loads some event data for analysis:
In my previous article, Getting started with Data Explorer pools in Azure Synapse, we learned how to create Data Explorer pools in Azure Synapse and the unique value that Data Explorer brings to semi-structured and free-text data. The creation of the Data Explorer pool is the first step in the process. After the pool is created, one can create data structures, ingest data and then use it for consumption. Data Explorer pool interface provides different ways of ingesting data into the pool including one-click ingestion.
Some of these techniques are really straightforward. Others—especially if you’re talking about large amounts of data—do require installing and working with local tooling.
Comments closedRobert Cain continues a series on KQL:
Reformatting dates is very common when querying data. You may, for example, wish to come up with a more compact date than the default KQL returns. Perhaps you work for an international company. In some countries you may want the date in month/day/year format, while other countries would prefer day/month/year.
Fortunately Kusto provides the
format_datetimefunction that allows you to do just this. We’ll take a look at it in this article.
Read on for a variety of examples.
Comments closedGabi Lehner announces an upcoming bugfix:
This is a Breaking change when using window functions inside partition operator sub-query scope.
Window functions require a serialized/ordered input.
There is a bug in Azure Data Explorer logic that allows using window functions inside the partition operator when the ordering is performed outside the partition query scope like this:
Click through for what currently works as well as its future replacement.
Comments closedRobert Cain reviews a rose by any other name:
In order to compact data, JSON is often used to store information within columns in databases. JSON is also useful when you want to store data, but the exact data is different on a row by row basis. Using JSON prevents the need to have a lot of extra columns that will be blank for many of the rows.
It becomes important then that a query language provides a simple method for unpacking that JSON data into useful columns. The Kusto Query Language provides that ability through the use of the
parse_jsonscalar function. In this post we’ll look at examples of how to use it to expand data stored in JSON format.
Read on to see how this relates to the todynamic function in KQL, as well as examples of how to use parse_json.
Michal Bar has a couple of posts for us. First, updates to the desktop app Kusto Explorer:
Query Automation allows you to define a workflow that contains a series of queries with rules and logic that govern the order in which they are executed. Automations can be reused, and users can re-run the workflow, to get updated results. Upon completion, the saved Automation produces an analysis report, summarizing all queries results with additional insights.
Then, updates to the ADX web explorer:
It is now possible to embed Azure Data Explorer dashboards in 3rd party apps. This comes on top of allowing embedding of the Monaco editor in 3rd party apps.
Dashboard embedding allows you to easily share data with your customers in a way that allows them to interact and explore it.
Using the various feature flags, you can control the exact controls that will be part of the embedded dashboard experience. For example, you can decide to remove the share, and add connection menu items or others.
To learn more about dashboard embedding, please read this doc Embed dashboards
Read on for the full changelog.
Comments closedRobert Cain proves it’s not the end of the line in his KQL series:
It’s not uncommon to want to use a range of values when creating a Kusto query. This might be a range of numeric values, or perhaps a range of dates.
Kusto provides this ability using the
betweenoperator. In this post we’ll see how to use it when authoring your Kusto queries.
Click through to see how you can use between as well as logical alterations such as not between.
Anshul Sharma announces another source for Azure Data Explorer:
Today we are excited to launch the ability to ingest data from Amazon Simple Storage Service (S3) into Azure Data Explorer (ADX) natively.
Amazon S3 is one of the most popular object storage services. AWS Customers use Amazon S3 to store data for a range of use cases, such as data lakes, websites, mobile applications, backup and restore, archive, applications, IoT devices, log analytics and big data analytics.
Azure Data Explorer (ADX) is a fully managed, high-performance, big data analytics platform that makes it easy to analyze high volumes of data in near real time. ADX supports ingesting data from a wide variety of sources such as Azure Blob, ADLS gen2, Azure Event Hub, Azure IoT Hub, and with popular open-source technologies such as Kafka, Logstash, Telegraph. With the new S3 support, customers can bring data from S3 natively without relying on complex ETL pipelines.
Between this, ADF/Synapse pipelines, and SQL Server 2022, it seems that Microsoft got the message that people do use multiple clouds and do want to read AWS data in Azure. Which is good because that directly benefits me…
Comments closedRobert Cain is the alpha, Robert Cain is the omega:
My post, Fun With KQL – DateTime Arithmetic, demonstrated how to do date / time math in Kusto. Then in my previous post, Fun With KQL – StartOf, we showed how to use the
StartOf...functions, to calculate the beginning of time periods based on a date that is passed in.If you’ve not read the previous post I’d highly encourage you to do so now, as there are parallels between it and the
endof...functions we’ll discuss in this article, namelyendofday,endofmonth,endofyear, andendofweek.
I’ve enjoyed Robert’s series but I really wanted this to be the last thing he covers so I could call it “EndOf the Line for KQL.” But because I don’t know if it really will be the last post in the series (other than maybe a conclusion post), I can only reference that momentary bit of cleverness here.
Comments closed