Kusto cannot project a value into a user defined function

1
votes

I have a query in our domain which I can not get it to work. I used datatable to mimic my issue. I'm trying to use a projected value in an userdefined function.

// this works
let f = (a:int) {
    datatable (b:string, d:int) ["2015-12-31", 1, "2016-12-30", 2, "2014-01-05", 3]
    | as dataset
    | where d == a
    | project b;
};
datatable (d:int) [1, 2, 3]
| as dataset
| project toscalar(f(2))

// this doesnt work, why is the 'd' not used (projected) in function q. 
// if I add toscalar to the project it also doesnt work
let f = (a:int) {
    datatable (b:string, d:int) ["2015-12-31", 1, "2016-12-30", 2, "2014-01-05", 3]
    | as dataset
    | where d == a
    | project b;
};
datatable (d:int) [1, 2, 3]
| as dataset
| project toscalar(f(d))

What am I missing here, I expect the '| project' use the function (f) for each result.

here the 2 queries to tinker with.

first query

second query

thanks

2
azureazure-data-explorerkql
This is the restriction of user-defined function, the column can not be used as the parameter of user-defined function. You can take a look at the restrctions here - Ivan Yang
The comment is not correct. The column value can be sent to the user-defined-function. The reason that example does not work is using 'toscalar()' with row-context (meaning that toscalar() cannot be invoked for-each row-value). - Alexander Sloutsky
@AlexanderSloutsky, you're right. I just wanted to explain it more simpler, but gave an incorrect explanation, my bad. - Ivan Yang

2 Answers

3
votes

There is a way to achieve this (without join) is to create a dynamic map (property bag) using toscalar() and then use it as lookup dictionary. 

let f = (a:int) {
    let _lookup = toscalar 
    (
        datatable (b:string, d:int) ["2015-12-31", 1, "2016-12-30", 2, "2014-01-05", 3]
        | extend p = pack(tostring(d), b)
        | summarize make_bag(p)
     );
    _lookup[tostring(a)]
};
datatable (d:int) [1, 2, 3]
| project result=f(d)
1
votes

This is the restriction of user-defined function, toscalar() cannot be invoked for-each row-value. You can take a look at the restrctions here.

Here is a workaround, which can achieve your goal(You can also use this query link to run it directly):

let f = (T:(d:int)) {
    let table1 = datatable (b:string, d:int) ["2015-12-31", 1, "2016-12-30", 2, "2014-01-05", 3]
    | as dataset;
    T
    | join (
       table1 
    ) on d
    | project b  
};

datatable (d:int) [1, 2, 3]
| as dataset
| invoke f()

Test result as below:

enter image description here