'Does materialize function not work with tabular function argument in Kusto?
I created a simple function MaterializeRemoteInputTable below which accepts the output of a table as an input. To test if the input is materialized, I am using it twice to calculate two scalar values - x and y:
.create-or-alter function MaterializeRemoteInputTable(inputTable: (State:string)) {
let materializedInput = materialize(inputTable);
let x = toscalar(materializedInput | count );
let y = toscalar(materializedInput | where State contains "S" | count);
print x, y;
}
I am using the sample kusto database's function output as an input to the above function:
cluster('https://help.kusto.windows.net').database('Samples').
GetStatesWithPopulationSmallerThan(1000000)
| invoke MaterializeRemoteInputTable()
On querying the help.kusto.windows.net cluster with my ClientActivityId, I see two queries are executed:
.show commands-and-queries
| where ClientActivityId contains "KE.RunQuery;<guid...>"
Above query outputs two rows:
GetStatesWithPopulationSmallerThan(long(1000000))|__executeAndCache|count as Count|limit long(1)|project ["b2fb..."]=["Count"]
GetStatesWithPopulationSmallerThan(long(1000000))|__executeAndCache|where (["State"] contains ("S"))|count as Count|limit long(1)|project ["5d0e2..."]=["Count"]
Since I have materialized the input to my MaterializeRemoteInputTable, why are two queries executed on the remote cluster, once each for x and y?
Solution 1:[1]
There is no issue with materialize & tabular argument to a function. The issue is a known limitation in materialize with cross cluster query that in some cases the materialization is not performed. Please note that this is only a performance issue and not a logical issue (query results are guaranteed to be correct).
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | Ziad Hamod |