ADX User-Defined Scalar Functions

In this article, I showed you how to create a user-defined ADX function that returns tabular data. In this article, I will show you how to create a user-defined ADX function that returns a single scalar value.

Setup

This article assumes that you have an Azure subscription, an ADX cluster, and an ADX database. See the previous articles in this series to learn how to create an ADX cluster and/or database.

For the examples in this article, we will use a table created with the following ADX commands:

.create async materialized-view 
.drop table customers

.create-merge table customers
(
FullName:string, 
LastOrderDate:datetime,
YtdSales:decimal,
YtdExpenses:decimal,
City:string,
PostalCode:string
)

.ingest inline into table customers <| 
'Bill Gates', datetime(2022-01-10 11:00:00), 1000000, 500000, 'Redmond', '98052'
'Steve Ballmer', datetime(2022-01-06 10:30:00), 150000, 50000, 'Los Angeles', '90305'
'Satya Nadella', datetime(2022-01-09 17:25:00), 100000, 50000, 'Redmond', '98052'
'Steve Jobs', datetime(2022-01-04 13:00:00), 100000, 60000, 'Cupertino', '95014'
'Larry Ellison', datetime(2022-01-04 13:00:00), 90000, 80000, 'Redwood Shores', '94065'
'Jeff Bezos', datetime(2022-01-05 08:00:00), 750000, 650000, 'Seattle', '98109'
'Tim Cook', datetime(2022-01-02 09:00:00), 40000, 10000, 'Cupertino', '95014'
'Steve Wozniak', datetime(2022-01-04 11:30:00), 81000, 55000, 'Cupertino', '95014'
'Scott Guthrie', datetime(2022-01-11 14:00:00), 2000000, 1000000, 'Redmond', '98052'
'David Giard', datetime(2022-01-02 09:01:00), 1.50, 1, 'Chicago', '60605'

Syntax

Use the .create-or-alter function command to create a new function or modify one that already exists. The syntax is:

.create-or-alter function with (docstring = description, folder=folder_name] name_of_function ( parameter_list ) { KQL_Script }

where:

description is a brief description of the function. This is optional, but it is useful to help others understand the purpose of your function.
folder_name is a logical folder in which to store the function. This is optional, but it can help to organize your functions if you have many of them.
parameter_list is a list of input parameters to the function.
KQL_Script is the KQL code to execute when the function is called.

Parameters

Parameters are passed to a function as a comma-separated list of name/data type pairs, for example:

.create async materialized-view 
startDateTime:datetime,
endDateTime:datetime,

You can make a parameter optional by adding a default value, as shown below:

.create async materialized-view 
timeBinLength:timespan = 1h

This creates an optional param named "timeBinLength" of type timespan. If this parameter is not passed to the function, it will default to 1 hour.

Sample

Here is an example of an ADX function that accepts as revenue and expense as input parameters and calculates the profit from these values:

.create async materialized-view 
.create-or-alter function
with (docstring = 'Points drone passed through rolled up by time period', folder='Samples')
Profit(
revenue:decimal,
expenses:decimal
)
{
revenue - expenses
}

Calling the Function

Calling your function requires only the function name, followed by parameters in parentheses.

The code below calls the DroneRoute function, setting the timespan to 30 minutes.

.create async materialized-view 
customers
| extend profit = Profit(YtdSales, YtdExpenses)

The results of that call are in Fig. 1.

Results of calling Profit function

Fig. 1

Conclusion

In this article, you learned how to create and call a User-Defined Scalar Function in Azure Data Explorer. See this article for information on creating a user-defined ADX function that returns a data table.