Azure Data Explorer input bindings for Azure Functions (preview) (2023)

Article
05/23/2023

The Azure Data Explorer input binding retrieves data from a database.

Examples

A C# function can be created using one of the following C# modes:

In-process class library: compiled C# function that runs in the same process as the Functions runtime.
Isolated worker process class library: compiled C# function that runs in a worker process that is isolated from the runtime. Isolated worker process is required to support C# functions running on non-LTS versions .NET and the .NET Framework.
C# script: used primarily when creating C# functions in the Azure portal.

In-process
Isolated process

More samples for the Azure Data Explorer input binding are available in the GitHub repository.

This section contains the following examples:

HTTP trigger, get rows by ID from query string
HTTP trigger, get multiple rows from route data

The examples refer to Product class and a corresponding database table:

public class Product{ [JsonProperty(nameof(ProductID))] public long ProductID { get; set; } [JsonProperty(nameof(Name))] public string Name { get; set; } [JsonProperty(nameof(Cost))] public double Cost { get; set; }}

.create-merge table Products (ProductID:long, Name:string, Cost:double)

HTTP trigger, get row by ID from query string

The following example shows a C# function that retrieves a list of products given a productId. The function is triggered by an HTTP request that uses a parameter for the ID. That ID is used to retrieve a list of Product that matches the query.

Note

The HTTP query string parameter is case-sensitive.

HTTP trigger, get multiple rows from route parameter

The following example shows a C# function that retrieves documents returned by the query. The function is triggered by an HTTP request that uses route data to specify the value of a KQL function parameter. GetProductsByName is a simple function that retrieves a set of products that match a product name

.create function ifnotexists GetProductsByName(name:string){ Products | where Name == name}

using System.Collections.Generic;using Microsoft.AspNetCore.Http;using Microsoft.AspNetCore.Mvc;using Microsoft.Azure.WebJobs;using Microsoft.Azure.WebJobs.Extensions.Http;namespace Microsoft.Azure.WebJobs.Extensions.Kusto.Samples.InputBindingSamples{ public static class GetProductsFunction { [FunctionName("GetProductsFunction")] public static IActionResult Run( [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "getproductsfn/{name}")] HttpRequest req, [Kusto(Database:"productsdb" , KqlCommand = "declare query_parameters (name:string);GetProductsByName(name)" , KqlParameters = "@name={name}", Connection = "KustoConnectionString")] IEnumerable<Product> products) { return new OkObjectResult(products); } }}

More samples for the java Azure Data Explorer input binding are available in the GitHub repository.

This section contains the following examples:

HTTP trigger, get multiple rows
HTTP trigger, get row by ID from query string

The examples refer to a Product class (in a separate file Product.java) and a corresponding database table:

package com.microsoft.azure.kusto.common;import com.fasterxml.jackson.annotation.JsonProperty;public class Product { @JsonProperty("ProductID") public long ProductID; @JsonProperty("Name") public String Name; @JsonProperty("Cost") public double Cost; public Product() { } public Product(long ProductID, String name, double Cost) { this.ProductID = ProductID; this.Name = name; this.Cost = Cost; }}

HTTP trigger, get multiple rows

The example uses a route parameter to specify the name of the ID of the products. All matching products are retrieved from the products table.

package com.microsoft.azure.kusto.inputbindings;import com.microsoft.azure.functions.HttpMethod;import com.microsoft.azure.functions.HttpRequestMessage;import com.microsoft.azure.functions.HttpResponseMessage;import com.microsoft.azure.functions.HttpStatus;import com.microsoft.azure.functions.annotation.AuthorizationLevel;import com.microsoft.azure.functions.annotation.FunctionName;import com.microsoft.azure.functions.annotation.HttpTrigger;import com.microsoft.azure.functions.kusto.annotation.KustoInput;import com.microsoft.azure.kusto.common.Product;import java.util.Optional;public class GetProducts { @FunctionName("GetProducts") public HttpResponseMessage run( @HttpTrigger(name = "req", methods = { HttpMethod.GET}, authLevel = AuthorizationLevel.ANONYMOUS, route = "getproducts/{productId}") HttpRequestMessage<Optional<String>> request, @KustoInput(name = "getjproducts", kqlCommand = "declare query_parameters (productId:long);Products | where ProductID == productId", kqlParameters = "@productId={productId}", database = "productsdb", connection = "KustoConnectionString") Product[] products) { return request.createResponseBuilder(HttpStatus.OK).header("Content-Type", "application/json").body(products) .build(); }}

HTTP trigger, get row by ID from query string

The following example shows a query the products table by the product name. The function is triggered by an HTTP request that uses a query string to specify the value of a query parameter. That parameter is used to filter the Product records in the specified query.

package com.microsoft.azure.kusto.inputbindings;import com.microsoft.azure.functions.HttpMethod;import com.microsoft.azure.functions.HttpRequestMessage;import com.microsoft.azure.functions.HttpResponseMessage;import com.microsoft.azure.functions.HttpStatus;import com.microsoft.azure.functions.annotation.AuthorizationLevel;import com.microsoft.azure.functions.annotation.FunctionName;import com.microsoft.azure.functions.annotation.HttpTrigger;import com.microsoft.azure.functions.kusto.annotation.KustoInput;import com.microsoft.azure.kusto.common.Product;import java.util.Optional;public class GetProductsQueryString { @FunctionName("GetProductsQueryString") public HttpResponseMessage run(@HttpTrigger(name = "req", methods = { HttpMethod.GET}, authLevel = AuthorizationLevel.ANONYMOUS, route = "getproducts") HttpRequestMessage<Optional<String>> request, @KustoInput(name = "getjproductsquery", kqlCommand = "declare query_parameters (name:string);GetProductsByName(name)", kqlParameters = "@name={Query.name}", database = "productsdb", connection = "KustoConnectionString") Product[] products) { return request.createResponseBuilder(HttpStatus.OK).header("Content-Type", "application/json").body(products) .build(); }}

More samples for the Azure Data Explorer input binding are available in the GitHub repository.

This section contains the following examples:

HTTP trigger, get multiple rows
HTTP trigger, get row by ID from query string

The examples refer to a database table:

HTTP trigger, get multiple rows

The following example shows an Azure Data Explorer input binding in a function.json file and a JavaScript function that reads from a query and returns the results in the HTTP response.

The following is binding data in the function.json file:

{ "bindings": [ { "authLevel": "function", "name": "req", "direction": "in", "type": "httpTrigger", "methods": [ "get" ], "route": "getproducts/{productId}" }, { "name": "$return", "type": "http", "direction": "out" }, { "name": "productget", "type": "kusto", "database": "productsdb", "direction": "in", "kqlCommand": "declare query_parameters (productId:long);Products | where ProductID == productId", "kqlParameters": "@productId={productId}", "connection": "KustoConnectionString" } ], "disabled": false}

The configuration section explains these properties.

The following is sample JavaScript code:

module.exports = async function (context, req, productget) { return { status: 200, body: productget };}

HTTP trigger, get row by name from query string

The following is binding data in the function.json file:

{ "bindings": [ { "authLevel": "function", "name": "req", "direction": "in", "type": "httpTrigger", "methods": [ "get" ], "route": "getproductsfn" }, { "name": "$return", "type": "http", "direction": "out" }, { "name": "productfnget", "type": "kusto", "database": "productsdb", "direction": "in", "kqlCommand": "declare query_parameters (name:string);GetProductsByName(name)", "kqlParameters": "@name={Query.name}", "connection": "KustoConnectionString" } ], "disabled": false}

The configuration section explains these properties.

The following is sample JavaScript code:

module.exports = async function (context, req, producproductfngettget) { return { status: 200, body: productfnget };}

More samples for the Azure Data Explorer input binding are available in the GitHub repository.

This section contains the following examples:

HTTP trigger, get multiple rows
HTTP trigger, get records using a KQL Function

HTTP trigger, get multiple rows

The following example shows an Azure Data Explorer input binding in a function.json file and a Python function that reads from a query and returns the results in the HTTP response.

The following is binding data in the function.json file:

{ "scriptFile": "__init__.py", "bindings": [ { "authLevel": "Anonymous", "type": "httpTrigger", "direction": "in", "name": "req", "methods": [ "get" ], "route": "getproducts/{productId}" }, { "type": "http", "direction": "out", "name": "$return" }, { "name": "productsdb", "type": "kusto", "database": "sdktestsdb", "direction": "in", "kqlCommand": "declare query_parameters (productId:long);Products | where ProductID == productId", "kqlParameters": "@productId={Query.productId}", "connection": "KustoConnectionString" } ]}

The configuration section explains these properties.

The following is sample Python code:

import azure.functions as funcfrom Common.product import Productdef main(req: func.HttpRequest, products: str) -> func.HttpResponse: return func.HttpResponse( products, status_code=200, mimetype="application/json" )

HTTP trigger, get row by ID from query string

The following is binding data in the function.json file:

{ "bindings": [ { "authLevel": "function", "name": "req", "direction": "in", "type": "httpTrigger", "methods": [ "get" ], "route": "getproductsfn" }, { "name": "$return", "type": "http", "direction": "out" }, { "name": "productfnget", "type": "kusto", "database": "productsdb", "direction": "in", "kqlCommand": "declare query_parameters (name:string);GetProductsByName(name)", "kqlParameters": "@name={Query.name}", "connection": "KustoConnectionString" } ], "disabled": false}

The configuration section explains these properties.

The following is sample Python code:

import azure.functions as funcdef main(req: func.HttpRequest, products: str) -> func.HttpResponse: return func.HttpResponse( products, status_code=200, mimetype="application/json" )

Attributes

The C# library uses the KustoAttribute attribute to declare the Azure Data Explorer bindings on the function, which has the following properties:

Attribute property	Description
Database	Required. The database against which the query has to be executed.
Connection	Required. The name of the variable that holds the connection string, resolved through environment variables or through function app settings. Defaults to look up on the variable KustoConnectionString, at runtime this variable is looked up against the environment. Documentation on connection string can be found at Kusto connection strings for example:`"KustoConnectionString": "Data Source=https://_cluster_.kusto.windows.net;Database=_Database_;Fed=True;AppClientId=_AppId_;AppKey=_AppKey_;Authority Id=_TenantId_`
KqlCommand	Required. The KqlQuery that has to be executed. Can be a KQL query or a KQL Function call
KqlParameters	Optional. Parameters that act as predicate variables for the KqlCommand. For example "@name={name},@Id={id}" where the parameters {name} and {id} is substituted at runtime with actual values acting as predicates. Neither the parameter name nor the parameter value can contain a comma (`,`) or an equals sign (`=`).
ManagedServiceIdentity	Optional. A managed identity can be used to connect to Azure Data Explorer. To use a System managed identity, use "system", any other identity names are interpreted as user managed identity

Annotations

In the Java functions runtime library, uses the @KustoInput annotation (com.microsoft.azure.functions.kusto.annotation.KustoInput):

Element	Description
name	Required. The name of the variable that represents the query results in function code.
database	Required. The database against which the query has to be executed.
connection	Required. The name of the variable that holds the connection string, resolved through environment variables or through function app settings. Defaults to look up on the variable KustoConnectionString, at runtime this variable is looked up against the environment. Documentation on connection string can be found at Kusto connection strings for example:`"KustoConnectionString": "Data Source=https://_cluster_.kusto.windows.net;Database=_Database_;Fed=True;AppClientId=_AppId_;AppKey=_AppKey_;Authority Id=_TenantId_`
kqlCommand	Required. The KqlQuery that has to be executed. Can be a KQL query or a KQL Function call
kqlParameters	Optional. Parameters that act as \predicate variables for the KqlCommand. For example "@name={name},@Id={id}" where the parameters {name} and {id} is substituted at runtime with actual values acting as predicates. Neither the parameter name nor the parameter value can contain a comma (`,`) or an equals sign (`=`).
managedServiceIdentity	A managed identity can be used to connect to Azure Data Explorer. To use a System managed identity, use "system", any other identity names are interpreted as user managed identity

Configuration

The following table explains the binding configuration properties that you set in the function.json file.

function.json property	Description
type	Required. Must be set to `kusto`.
direction	Required. Must be set to `in`.
name	Required. The name of the variable that represents the query results in function code.
database	Required. The database against which the query has to be executed.
connection	Required. The name of the variable that holds the connection string, resolved through environment variables or through function app settings. Defaults to look up on the variable KustoConnectionString, at runtime this variable is looked up against the environment. Documentation on connection string can be found at Kusto connection strings for example:`"KustoConnectionString": "Data Source=https://_cluster_.kusto.windows.net;Database=_Database_;Fed=True;AppClientId=_AppId_;AppKey=_AppKey_;Authority Id=_TenantId_`
kqlCommand	Required. The KqlQuery that has to be executed. Can be a KQL query or a KQL Function call
kqlParameters	Optional. Parameters that act as predicate variables for the KqlCommand. For example "@name={name},@Id={id}" where the parameters {name} and {id} is substituted at runtime with actual values acting as predicates. Neither the parameter name nor the parameter value can contain a comma (`,`) or an equals sign (`=`).
managedServiceIdentity	A managed identity can be used to connect to Azure Data Explorer. To use a System managed identity, use "system", any other identity names are interpreted as user managed identity

When you're developing locally, add your application settings in the local.settings.json file in the Values collection.

Usage

The attribute's constructor takes the Database and the attributes KQLCommand, KQLParameters, and the Connection setting name. The KQLCommand can be a KQL statement or a KQL function. The connection string setting name corresponds to the application setting (in local.settings.json for local development) that contains the Kusto connection strings for example: "KustoConnectionString": "Data Source=https://_**cluster**_.kusto.windows.net;Database=_**Database**_;Fed=True;AppClientId=_**AppId**_;AppKey=_**AppKey**_;Authority Id=_**TenantId**_. Queries executed by the input binding are parameterized and the values provided in the KQLParameters are used at runtime.

Next steps

Save data to a table (Output binding)