• azurecoder

Kusto Lightning Fact 3: .create function

In the previous lightning fact we looked at using variables through the let command. In this tutorial we'll look at using functions which give us a a bunch of reusability.

.create function ifnotexists 
with (docstring = 'A simple function to get aggregates of countries', folder='CountryFunctions')
AllCountrySummaryState()  {
sales 
| project Country, Profit = Total_Profit / 1000000, Earnings = Total_Revenue / 1000000, Year = getyear(Order_Date)
| summarize Earnings = round(sum(Earnings)), Profit = round(sum(Profit)), TransactionCountPerYear = count() by Country, Year
| order by Country, Year asc
| extend EarningsRatio = round((Profit / Earnings) * 100, 2)
}

This will create a function if it doesn't already exist with a description and putting it into a folder called CountryFunctions.


There's a few interesting caveats in this function. We get profit, earnings per country per year + the number of transactions in each year. From this we can create an earnings ratio to see how much variance there has been over the years in earnings per country.


You can also use .drop function to drop the function as you would expect.


A feature here is also the keyword extend which allows you to add to the project query without projecting out everything else again.

48 views0 comments

Recent Posts

See All

Dropping a SQL table in your Synapse Spark notebooks

One of the nice things with Spark Pools in Azure Synapse Analytics is how easy it is to write a data frame into a dedicated SQL Pool. curated_df.write.mode("overwrite").synapsesql("curation.dbo.feed_i

What makes a data engineer?

Note: this post is mainly about Azure but it can apply to any cloud. You get to a stage when you hear enough definitions of what people think a job is to take you to the point of nervous exhaustion. T