CODEWITHSUNDEEP
unionuser-defined-functionskqlazure-data-explorer
Qingsheng he
Qingsheng he

Reputation: 9

Union the results with loop to call function for each table row

I am pretty new to Azure Data Explorer (Kusto) queries. I have a stored function, that takes a string as a parameter, does some querying around that input and return a data table.

QueryFunc(input: string)
{
   //does some queries and result a table
}

I can union the result by running like:

union QueryFunc('input1'), QueryFunc('input2')

But as the inputs set are dynamic which it can be got by other query like blew as a column.

let inputSets = xxx
xxx
xxx;

But when I want to loop each of the inputs to call the func QueryFunc and union the result like below(input is a column in inputSets):

inputSets | union QueryFunc(input)

It reported error that:

Semantic error: 'union' operator: Failed to resolve table or scalar expression named 'input'

Is there any way I can loop each of the query results and invoke func QueryFunc and union all the result together?

Upvotes: 1

Views: 449

Answers (3)

Qingsheng he
Qingsheng he

Reputation: 9

It seems we don't have a solution on this in kusto func/queries, but this could be achieved by using power automate to create a cloud automation workflow and it support foreach mechanism which can work individually on each result.

Upvotes: -1

decius
decius

Reputation: 1445

If the QueryFunc only gives you a scalar value, you could do the loop like this:

let data = pack_array("a", "b", "c", "d");
let queryFunc = (input:string) { strcat(input, "1") };
range i from 0 to array_length(data) - 1 step 1
| extend Result = queryFunc (data[i])
| summarize ResultList = make_list(Result)
| project ConcatenatedResults = strcat_array(ResultList, "")

Result is:

Result

When the QueryFunc gives you a table, I think there is no way, at least that I know of, to make the unions dynamic in a loop.

Upvotes: 0

Aswin
Aswin

Reputation: 7156

From the error message, the result of both function and the result from let statement should be similar. Your function returns different type and let statement is of different type. Below is the sample code which joins a function and table.

let QueryFunc = (a:long) {
  datatable(Date:datetime, Event:string, MoreData:dynamic) [
    datetime(1910-06-11), "Born", dynamic({"key1":"value1", "key2":"value2"}),
    datetime(1930-01-01), "Enters Ecole Navale", dynamic({"key1":"value3", "key2":"value4"}),
    datetime(1953-01-01), "Published first book", dynamic({"key1":"value5", "key2":"value6"}),
    datetime(1997-06-25), "Died", dynamic({"key1":"value7", "key2":"value8"}),
]
| where strlen(Event) > a
| extend key2 = MoreData.key2
| project Event
};
let Table_sample=   datatable( Event:string) [
    "Born",
    "Published",
];
union QueryFunc(12),Table_sample

This query unions the result from QueryFunc() function and Table_sample.

demo

Upvotes: 0

Related Questions