CODEWITHSUNDEEP
pythonazureazure-data-explorerkql
superli3
superli3

Reputation: 424

Is it possible to use a dynamic array/list as input for parameterizing a kusto query?

I have an application that uses the Python library azure.kusto.data to query data from a Kusto cluster. I'm wondering, is it possible to use a dynamic array as input for paramterizing a kusto query?

The existing documentation doesn't seem to call out such functionality. https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/queryparametersstatement?pivots=azuredataexplorer

Passing in string type values has worked fine in the past:

    params = {
        "scenario": "string",
        "env": "string2",
        "duration": "string3",
    }

    query = """
    declare query_parameters(scenario:string, env:string, duration:string);
    Some_Kusto_Query(scenario, env, duration)
    """

I will then pass the dictionary and query string into the Kusto library to get the data I need. This works fine. What does not work fine is whenever I try some version of this:

    params = {
        "scenario": "string",
        "env": "string2",
        "duration": "string3",
        "value_list": [1,2,3,4]
    }

    query = """
    declare query_parameters(scenario:string, env:string, duration:string, value_list:dynamic);
    Some_Kusto_Query(scenario, env, duration)
        | where value in value_list
    """

I've tried many different iterations of this and can't seem to get it to work. The only other idea I have at this point would be to pass in value_list as a delimited string (e.g., "1-2-3-4") and use the split() function in kusto to deserialize the string back to an array, but this doesn't seem ideal.

Any tips or pointers would be appreciated - I've scoured the Kusto docs/Google and haven't come across any good examples online.

Thanks!

Upvotes: 4

Views: 12959

Answers (3)

Redevil
Redevil

Reputation: 21

@Avnera has the correct answer, there is just one more thing: the dynamic type need to be inside the parentheses:

| where value in (value_list)

Upvotes: 0

Avnera
Avnera

Reputation: 7608

The value in the parameter list has to be a literal, for dynamic arrays a literal looks like this:

dynamic([1,2,3])

for example:

params = {
    "scenario": "string",
    "env": "string2",
    "duration": "string3",
    "value_list": "dynamic([1,2,3,4])"
}

Upvotes: 5

SendETHToThisAddress
SendETHToThisAddress

Reputation: 3704

If you construct your query in Python then you can pass it and execute it like below. I also included a test table with 2 rows to help visualize the results:

let TestTable = datatable (ScenarioCol:string, evnCol:string, durationCol:string, numericalCol:int) 
[
    "scenario 1", "env 1", "duration x", 1,
    "scenario 1", "env 1", "duration x", 9
];
let MyQuery = (scenario:string, env:string, duration:string, value_list:dynamic)
{
    TestTable
    | where ScenarioCol == scenario
    | where evnCol == env
    | where durationCol == duration
    | where numericalCol in(value_list)
};
let scenario = "scenario 1";
let env = "env 1";
let duration = "duration x";
let valueList = dynamic([1,2,3,4]);
MyQuery(scenario, env, duration, valueList)

Output:

output table

As you can see it just took the row that had a matching value from the set.

Upvotes: 0

Related Questions