CODEWITHSUNDEEP
pythonazureazure-data-explorer
superli3
superli3

Reputation: 444

Parameterizing the unusual inputs of a kusto function

I work on an Python application that queries data from Kusto using the azure-kusto-data library (https://pypi.org/project/azure-kusto-data/)

I am trying to write a kusto function to execute a particular function. This function takes in 2 inputs, a datatable input and a timespan input

So as an example:

Function Name: GenerateSomeOutputviaKusto Function Parameters: (T:(Env:string,Something:string,StartDate:datetime),inputGranularity:timespan)

As you can see, this function input uses a tabular input. Now in order to properly call this function (in say, KustoExplorer), I use a "let" statement in order to create the tabular input for this function, following the guidance from this page: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/letstatement

    let QueryTable = datatable (Environment:string, Something:string, StartDate:datetime)
    ["Prod", "Sometext", datetime("2020-11-17")];
GenerateSomeOutputviaKusto(QueryTable, 24h);

The issue is now, when I try to parameterize this function to execute this function from python. When I call this function using the kusto library in python, I need to parameterize this function.

https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/queryparametersstatement?pivots=azuredataexplorer

I have my params set up as a python dictionary, as such:

    params = {
        "env": self._env,
        "something": something,
        "startdate": self._alert_start_date_str,
    }

I am then trying to parameterize the kusto function from my python code as follows:

            declare query_parameters(env:string,
            something:string,
            starting_time:string);
        let QueryTable = datatable (Environment:string, something:string, StartDate:datetime)
            [env, something, datetime(startdate)];
            GenerateSomeOutputViaKusto(QueryTable, 24h)

When I do so, I've been running into this error:

[Retry confirmed] KustoServiceError([{'error': {'code': 'General_BadRequest', 'message': 'Request is invalid and cannot be executed.', '@type': 'Kusto.Data.Exceptions.KustoBadRequestException', '@message': "Syntax error: Query could not be parsed: SYN0002: A recognition error occurred. [line:position=5:17]. Query: 'declare query_parameters(env:string,\n something:string,\n startdate:string);\n let QueryTable = datatable (Environment:string, something:string, StartDateTime:datetime)\n [env, something, datetime(startdate)];\n QueryTable'", '@context': {'timestamp': '2020-11-20T17:50:29.4796552Z', 'serviceAlias': 'EGFOLLOWING', 'machineName': 'KEngine000005', 'processName': 'Kusto.WinSvc.Svc', 'processId': 2728, 'threadId': 9068, 'appDomainName': 'Kusto.WinSvc.Svc.exe', 'clientRequestId': 'KPC.execute;1456198f-1e9a-47de-bf4d-053208c861f2', 'activityId': '59726ecc-5a4f-4d63-91f4-a04d706b10d6', 'subActivityId': '8e05442a-34c0-427a-b569-8879e7d1e2d1', 'activityType': 'DN.FE.ExecuteQuery', 'parentActivityId': 'de01e770-e291-4f10-bf9e-a34978193451', 'activityStack': '(Activity stack: CRID=KPC.execute;1456198f-1e9a-47de-bf4d-053208c861f2 ARID=59726ecc-5a4f-4d63-91f4-a04d706b10d6 > KD.Query.Client.ExecuteQueryAsKustoDataStream/d19ed49b-78f6-4f2c-adfc-cf0f6f443bb9 > P.WCF.Service.ExecuteQueryInternalAsKustoDataStream..IClientServiceCommunicationContract/de01e770-e291-4f10-bf9e-a34978193451 > DN.FE.ExecuteQuery/8e05442a-34c0-427a-b569-8879e7d1e2d1)'}, '@permanent': True, '@text': 'declare query_parameters(env:string,\n something:string,\n startdate:string);\n let QueryTable = datatable (Environment:string, something:string, StartDateTime:datetime)\n [env, something, datetime(startdate)];\n QueryTable', '@database': 'defaultdb', '@ClientRequestLogger': '', 'innererror': {'code': 'SYN0002', 'message': 'A recognition error occurred.', '@type': 'Kusto.Data.Exceptions.SyntaxException', '@message': "Syntax error: Query could not be parsed: SYN0002: A recognition error occurred. [line:position=5:17]. Query: 'declare query_parameters(env:string,\n

I have replaced some of the sensitive fields for privacy reasons. My Kusto related question is, is this even the right way to do such a thing? The kusto docs are pretty scant when it comes to parameterizing functions, especially in corner cases like these. Parameterizing works, without using a let statement, but using a "declare" statement and "let" statement in conjunction appears to lead to issues.

Upvotes: 0

Views: 2191

Answers (1)

Yoni L.
Yoni L.

Reputation: 25955

The issue is that you're trying to pass non-constant scalar values to the datatable operator - that's not supported, regardless of using query parameters or not.

you could replace your usage of the datatable operator with print, for example:

declare query_parameters(env:string, failure_signature:string, starting_time:datetime);
let QueryTable = print Environment = env, FailureSignature = failure_signature, StartDateTime = starting_time;
QueryTable

Upvotes: 1

Related Questions