Special functions

• 1: cluster()
• 2: Cross-cluster and cross-database queries
• 3: database()
• 4: external_table()
• 5: materialize()
• 6: materialized_view()
• 7: Query results cache
• 8: stored_query_result()
• 9: table()
• 10: toscalar()

1 - cluster()

Changes the reference of the query to a remote cluster. To access a database within the same cluster, use the database() function. For more information, see cross-database and cross-cluster queries.

Changes the reference of the query to a remote Eventhouse. To access a database within the same Eventhouse, use the database() function. For more information, see cross-database and cross-cluster queries.

Syntax

cluster(name)

Parameters

Examples

Use cluster() to access remote cluster

The following query can be run on any cluster.

cluster('help').database('Samples').StormEvents | count

cluster('help.kusto.windows.net').database('Samples').StormEvents | count

Use cluster() to access remote Eventhouse

The following query can be run on any Eventhouse.

cluster('help').database('Samples').StormEvents | count

cluster('help.kusto.windows.net').database('Samples').StormEvents | count

Output

Use cluster() inside let statements

The previous query can be rewritten to use a query-defined function (let statement) that takes a parameter called clusterName and passes it to the cluster() function.

let foo = (clusterName:string)
{
    cluster(clusterName).database('Samples').StormEvents | count
};
foo('help')

Output

Use cluster() inside Functions

The same query as above can be rewritten to be used in a function that receives a parameter clusterName - which is passed into the cluster() function.

.create function foo(clusterName:string)
{
    cluster(clusterName).database('Samples').StormEvents | count
};

2 - Cross-cluster and cross-database queries

Queries run with a particular database designated as the database in context. This database acts as the default for permission checking. If an entity is referenced in a query without specifying the cluster or database, it’s resolved against this database. Queries run with a particular database designated as the database in context. This database acts as the default for permission checking. If an entity is referenced in a query without specifying the context, it’s resolved against this database.

This article explains how to execute queries that involve entities located outside the current context database.

Prerequisites

• If the clusters are in different tenants, follow the instructions in Allow cross-tenant queries and commands.

Identify the cluster and database in context

Identify the eventhouse and database in context

The following table explains how to identify the database in context by query environment.

Perform cross-cluster or cross-database queries

Perform cross-eventhouse or cross-database queries

To access entities outside the database in context, use the cluster() and database() functions to qualify the entity name.

For a table in a different database within the same cluster:

database("<DatabaseName>").<TableName>

For a table in a remote cluster:

cluster("<ClusterName>").database("<DatabaseName>").<TableName>

For a table in a different database within the same eventhouse:

database("<DatabaseName>").<TableName>

For a table in a remote eventhouse or remote service (like Azure Data Explorer) cluster:

cluster("<EventhouseClusterURI>").database("<DatabaseName>").<TableName>

Qualified names and the union operator

When a qualified name appears as an operand of the union operator, then wildcards can be used to specify multiple tables and multiple databases. Wildcards aren’t permitted in cluster names.

union withsource=TableName *, database("OtherDb*").*Table, cluster("OtherCluster").database("*").*

When a qualified name appears as an operand of the union operator, then wildcards can be used to specify multiple tables and multiple databases. Wildcards aren’t permitted in eventhouse names.

union withsource=TableName *, database("OtherDb*").*Table, cluster("OtherEventhouseClusterURI").database("*").*

Qualified names and restrict access statements

Qualified names or patterns can also be included in restrict access statement. Wildcards in cluster names aren’t permitted. Wildcards in eventhouse names aren’t permitted.

The following query restricts query access to the following entities:

• Any entity name starting with my… in the default database.
• Any table in all the databases named MyOther… of the current cluster.
• Any table in all the databases named my2… in the cluster OtherCluster.kusto.windows.net.

restrict access to (my*, database("MyOther*").*, cluster("OtherCluster").database("my2*").*);

• Any entity name starting with event… in the default database.
• Any table in all the databases named EventOther… of the current eventhouse.
• Any table in all the databases named event2… in the eventhouse OtherEventhouse.kusto.data.microsoft.com.

restrict access to (event*, database("EventOther*").*, cluster("OtherEventhouseClusterURI").database("event2*").*);

Handle schema changes of remote entities

To process a cross-cluster query, the cluster that performs the initial query interpretation needs to have the schema of the entities referenced on remote clusters. To obtain this information, a command is sent to retrieve the schemas, which are then stored in a cache.

If there’s a schema change in the remote cluster, a cached schema might become outdated. This can lead to undesired effects, including scenarios where new or deleted columns cause a Partial query failure. To solve such issues, manually refresh the schema with the .clear cache remote-schema command. To process a cross-eventhouse or eventhouse-to-ADX cluster query, the eventhouse that performs the initial query interpretation needs to have the schema of the entities referenced on remote eventhouses or clusters. To obtain this information, a command is sent to retrieve the schemas, which are then stored in a cache.

If there’s a remote schema change, a cached schema might become outdated. This can lead to undesired effects, including scenarios where new or deleted columns cause a Partial query failure. To solve such issues, manually refresh the schema with the .clear cache remote-schema command.

Functions and views

Functions and views (persistent and created inline) can reference tables across database and cluster boundaries. The following code is valid.

let MyView = Table1 join database("OtherDb").Table2 on Key | join cluster("OtherCluster").database("SomeDb").Table3 on Key;
MyView | where ...

Persistent functions and views can be accessed from another database in the same cluster.

For example, say you create the following tabular function (view) in a database OtherDb:

.create function MyView(v:string) { Table1 | where Column1 has v ...  }  

Then, you create the following scalar function in a database OtherDb:

.create function MyCalc(a:double, b:double, c:double) { (a + b) / c }  

In default database, these entities can be referenced as follows:

database("OtherDb").MyView("exception") | extend CalCol=database("OtherDb").MyCalc(Col1, Col2, Col3) | take 10

Functions and views (persistent and created inline) can reference tables across database and eventhouse boundaries. The following code is valid.

let EventView = Table1 join database("OtherDb").Table2 on Key | join cluster("OtherEventhouseClusterURI").database("SomeDb").Table3 on Key;
EventView | where ...

Persistent functions and views can be accessed from another database in the same eventhouse.

For example, say you create the following tabular function (view) in a database OtherDb:

.create function EventView(v:string) { Table1 | where Column1 has v ...  }  

Then, you create the following scalar function in a database OtherDb:

.create function EventCalc(a:double, b:double, c:double) { (a + b) / c }  

For example, say you create the following tabular function (view) in a database OtherDb:

.create function EventView(v:string) { Table1 | where Column1 has v ...  }  

Then, you create the following scalar function in a database OtherDb:

.create function EventCalc(a:double, b:double, c:double) { (a + b) / c }  

In default database, these entities can be referenced as follows:

database("OtherDb").EventView("exception") | extend CalCol=database("OtherDb").EventCalc(Col1, Col2, Col3) | take 10

Limitations of cross-cluster function calls

Tabular functions or views can be referenced across clusters. The following limitations apply:

• Remote functions must return tabular schema. Scalar functions can only be accessed in the same cluster.
• Remote functions can accept only scalar arguments. Functions that get one or more table arguments can only be accessed in the same cluster.
• Remote functions’ result schema must be fixed (known in advance without executing parts of the query). So query constructs such as the pivot plugin can’t be used. Some plugins, such as the bag_unpack plugin, support a way to indicate the result schema statically, and in this form it can be used in cross-cluster function calls.
• For performance reasons, the calling cluster caches the schema of remote entities after the initial call. Therefore, changes made to the remote entity might result in a mismatch with the cached schema information, potentially leading to query failures. For more information, see Cross-cluster queries and schema changes.

Limitations of cross-eventhouse function calls

Tabular functions or views can be referenced across eventhouses. The following limitations apply:

• Remote functions must return tabular schema. Scalar functions can only be accessed in the same eventhouse.
• Remote functions can accept only scalar arguments. Functions that get one or more table arguments can only be accessed in the same eventhouse.
• Remote functions’ result schema must be fixed (known in advance without executing parts of the query). So query constructs such as the pivot plugin can’t be used. Some plugins, such as the bag_unpack plugin, support a way to indicate the result schema statically, and in this form it can be used in cross-eventhouse function calls.
• For performance reasons, the calling eventhouse caches the schema of remote entities after the initial call. Therefore, changes made to the remote entity might result in a mismatch with the cached schema information, potentially leading to query failures. For more information, see Cross-cluster queries and schema changes.

Examples

The following cross-cluster call is valid.

cluster("OtherCluster").database("SomeDb").MyView("exception") | count

The following query calls a remote scalar function MyCalc. This call violates rule #1, so it’s not valid.

MyTable | extend CalCol=cluster("OtherCluster").database("OtherDb").MyCalc(Col1, Col2, Col3) | take 10

The following query calls remote function MyCalc and provides a tabular parameter. This call violates rule #2, so it’s not valid.

cluster("OtherCluster").database("OtherDb").MyCalc(datatable(x:string, y:string)["x","y"] )

The following cross-eventhouse call is valid.

cluster("OtherEventhouseURI").database("SomeDb").EventView("exception") | count

The following query calls a remote scalar function EventCalc. This call violates rule #1, so it’s not valid.

Eventtable | extend CalCol=cluster("OtherEventhouseClusterURI").database("OtherDb").MyCalc(Col1, Col2, Col3) | take 10

The following query calls remote function EventCalc and provides a tabular parameter. This call violates rule #2, so it’s not valid.

cluster("EventhouseClusterURI").database("OtherDb").MyCalc(datatable(x:string, y:string)["x","y"] )

The following query calls remote function SomeTable that has a variable schema output based on the parameter tablename. This call violates rule #3, so it’s not valid.

Tabular function in OtherDb.

.create function SomeTable(tablename:string) { table(tablename)  }  

In default database.

cluster("OtherCluster").database("OtherDb").SomeTable("MyTable")

cluster("OtherEventhouseClusterURI").database("OtherDb").SomeTable("EventTable")

The following query calls remote function GetDataPivot that has a variable schema output based on the data (pivot() plugin has dynamic output). This call violates rule #3, so it’s not valid.

Tabular function in OtherDb.

.create function GetDataPivot() { T | evaluate pivot(PivotColumn) }  

Tabular function in the default database.

cluster("OtherCluster").database("OtherDb").GetDataPivot()

cluster("OtherEventhouseClusterURI").database("OtherDb").GetDataPivot()

Related content

• Cross-cluster join

• Allow cross-tenant queries and commands

• Cross-cluster join

3 - database()

Changes the reference of the query to a specific database within the cluster scope.

Changes the reference of the query to a specific database within the Eventhouse scope.

``

Syntax

database(databaseName)

Parameters

Examples

Use database() to access table of other database

database('Samples').StormEvents | count

Output

Use database() inside let statements

The query above can be rewritten as a query-defined function (let statement) that receives a parameter dbName - which is passed into the database() function.

let foo = (dbName:string)
{
    database(dbName).StormEvents | count
};
foo('help')

Output

Use database() inside stored functions

The same query as above can be rewritten to be used in a function that receives a parameter dbName - which is passed into the database() function.

.create function foo(dbName:string)
{
    database(dbName).StormEvents | count
};

4 - external_table()

References an external table by name.

To accelerate queries over external delta tables, see Query acceleration policy.

Syntax

external_table( TableName [, MappingName ] )

Parameters

Authentication and authorization

The authentication method to access an external table is based on the connection string provided during its creation, and the permissions required to access the table vary depending on the authentication method. For more information, see Azure Storage external table or SQL Server external table.

Related content

• External tables overview
• Create and alter Azure Storage external tables
• Create and alter SQL Server external tables.

5 - materialize()

Captures the value of a tabular expression for the duration of the query execution so that it can be referenced multiple times by the query without recalculation.

Syntax

materialize(expression)

Parameters

Remarks

The materialize() function is useful in the following scenarios:

• To speed up queries that perform heavy calculations whose results are used multiple times in the query.
• To evaluate a tabular expression only once and use it many times in a query. This is commonly required if the tabular expression is non-deterministic. For example, if the expression uses the rand() or the dcount() functions.

Examples of query performance improvement

The following example shows how materialize() can be used to improve performance of the query. The expression _detailed_data is defined using materialize() function and therefore is calculated only once.

let _detailed_data = materialize(StormEvents | summarize Events=count() by State, EventType);
_detailed_data
| summarize TotalStateEvents=sum(Events) by State
| join (_detailed_data) on State
| extend EventPercentage = Events*100.0 / TotalStateEvents
| project State, EventType, EventPercentage, Events
| top 10 by EventPercentage

Output

The following example generates a set of random numbers and calculates:

• How many distinct values in the set (Dcount)
• The top three values in the set
• The sum of all these values in the set

This operation can be done using batches and materialize:

let randomSet = 
    materialize(
        range x from 1 to 3000000 step 1
        | project value = rand(10000000));
randomSet | summarize Dcount=dcount(value);
randomSet | top 3 by value;
randomSet | summarize Sum=sum(value)

Result set 1:

Result set 2:

Result set 3:

Examples of using materialize()

To use the let statement with a value that you use more than once, use the materialize() function. Try to push all possible operators that will reduce the materialized dataset and still keep the semantics of the query. For example, use filters, or project only required columns.

    let materializedData = materialize(Table
    | where Timestamp > ago(1d));
    union (materializedData
    | where Text !has "somestring"
    | summarize dcount(Resource1)), (materializedData
    | where Text !has "somestring"
    | summarize dcount(Resource2))

The filter on Text is mutual and can be pushed to the materialize expression. The query only needs columns Timestamp, Text, Resource1, and Resource2. Project these columns inside the materialized expression.

    let materializedData = materialize(Table
    | where Timestamp > ago(1d)
    | where Text !has "somestring"
    | project Timestamp, Resource1, Resource2, Text);
    union (materializedData
    | summarize dcount(Resource1)), (materializedData
    | summarize dcount(Resource2))

If the filters aren’t identical, as in the following query:

    let materializedData = materialize(Table
    | where Timestamp > ago(1d));
    union (materializedData
    | where Text has "String1"
    | summarize dcount(Resource1)), (materializedData
    | where Text has "String2"
    | summarize dcount(Resource2))

When the combined filter reduces the materialized result drastically, combine both filters on the materialized result by a logical or expression as in the following query. However, keep the filters in each union leg to preserve the semantics of the query.

    let materializedData = materialize(Table
    | where Timestamp > ago(1d)
    | where Text has "String1" or Text has "String2"
    | project Timestamp, Resource1, Resource2, Text);
    union (materializedData
    | where Text has "String1"
    | summarize dcount(Resource1)), (materializedData
    | where Text has "String2"
    | summarize dcount(Resource2))

6 - materialized_view()

References the materialized part of a materialized view.

The materialized_view() function supports a way of querying the materialized part only of the view, while specifying the max latency the user is willing to tolerate. This option isn’t guaranteed to return the most up-to-date records, but should always be more performant than querying the entire view. This function is useful for scenarios in which you’re willing to sacrifice some freshness for performance, for example in telemetry dashboards.

Syntax

materialized_view(ViewName, [ max_age ] )

Parameters

Examples

Query the materialized part of the view only, independent on when it was last materialized.

materialized_view("ViewName")

Query the materialized part only if it was materialized in the last 10 minutes. If the materialized part is older than 10 minutes, return the full view. This option is expected to be less performant than querying the materialized part.

materialized_view("ViewName", 10m)

Notes

• Once a view is created, it can be queried just as any other table in the database, including participate in cross-cluster / cross-database queries.
• Materialized views aren’t included in wildcard unions or searches.
• Syntax for querying the view is the view name (like a table reference).
• Querying the materialized view will always return the most up-to-date results, based on all records ingested to the source table. The query combines the materialized part of the view with all unmaterialized records in the source table. For more information, see how materialized views work for details.

7 - Query results cache

Kusto includes a query results cache. You can choose to get cached results when issuing a query. You’ll experience better query performance and lower resource consumption if your query’s results can be returned by the cache. However, this performance comes at the expense of some “staleness” in the results.

Use the cache

Set the query_results_cache_max_age option as part of the query to use the query results cache. You can set this option in the query text or as a client request property. For example:

set query_results_cache_max_age = time(5m);
GithubEvent
| where CreatedAt > ago(180d)
| summarize arg_max(CreatedAt, Type) by Id

The option value is a timespan that indicates the maximum “age” of the results cache, measured from the query start time. Beyond the set timespan, the cache entry is obsolete and won’t be used again. Setting a value of 0 is equivalent to not setting the option.

Compatibility between queries

Identical queries

The query results cache returns results only for queries that are considered “identical” to a previous cached query. Two queries are considered identical if all of the following conditions are met:

• The two queries have the same representation (as UTF-8 strings).
• The two queries are made to the same database.
• The two queries share the same client request properties. The following properties are ignored for caching purposes:
  • ClientRequestId
  • Application
  • User

Incompatible queries

The query results won’t be cached if any of the following conditions is true:

• The query references a table that has the RestrictedViewAccess policy enabled.
• The query references a table that has the RowLevelSecurity policy enabled.
• The query uses any of the following functions:
  • current_principal
  • current_principal_details
  • current_principal_is_member_of
• The query accesses an external table or an external data.
• The query uses the evaluate plugin operator.

No valid cache entry

If a cached result satisfying the time constraints couldn’t be found, or there isn’t a cached result from an “identical” query in the cache, the query will be executed and its results cached, as long as:

• The query execution completes successfully, and
• The query results size doesn’t exceed 16 MB.

Results from the cache

How does the service indicate that the query results are being served from the cache? When responding to a query, Kusto sends another ExtendedProperties response table that includes a Key column and a Value column. Cached query results will have another row appended to that table:

• The row’s Key column will contain the string ServerCache
• The row’s Value column will contain a property bag with two fields:
  • OriginalClientRequestId - Specifies the original request’s ClientRequestId.
  • OriginalStartedOn - Specifies the original request’s execution start time.

Query consistency

Queries using weak consistency can be processed on different cluster nodes. The cache isn’t shared by cluster nodes, every node has a dedicated cache in its own private storage. Therefore, if two identical queries land on different nodes, the query will be executed and cached on both nodes. By setting query consistency to affinitizedweakconsistency, you can ensure that weak consistency queries that are identical land on the same query head, and thus increase the cache hit rate. This is not relevant when using strong consistency.

Management

The following management and observability commands are supported:

• Show query results cache: Returns statistics related to the query results cache.
• Clear query results cache: Clears query results cache.
• Refresh query cache entry: a specific query cache entry can be refreshed using query_results_cache_force_refresh (OptionQueryResultsCacheForceRefresh)client request property. When set to true, this command will force query results cache to be refreshed also when an existing cache is present. This process is useful in scenarios that require queries results to be available for querying. This property must be used in combination with ‘query_results_cache_max_age’, and sent via ClientRequestProperties object. The property can’t be part of a ‘set’ statement.

Capacity

The cache capacity is currently fixed at 1 GB per cluster node. The eviction policy is LRU.

Shard level query results cache

You can use shard-level query results cache for scenarios that require the most up-to-date results, such as a live dashboard. For example, a query that runs every 10 seconds and spans the last 1 hour can benefit from caching intermediate query results at the storage (shard) level.

The shard level query results cache is automatically enabled when the Query results cache is in use. Because it shares the same cache as Query results cache, the same capacity and eviction policies apply.

Syntax

set query_results_cache_per_shard; Query

Example

set query_results_cache_per_shard;
GithubEvent
| where CreatedAt > ago(180d)
| summarize arg_max(CreatedAt, Type) by Id

8 - stored_query_result()

Retrieves a previously created stored query result.

To set a stored query result, see .set stored_query_result command.

Syntax

stored_query_result( StoredQueryResultName )

Parameters

Examples

References the stored query result named Numbers.

stored_query_result("Numbers")

Output

Pagination

The following example retrieves clicks by Ad network and day, for the last seven days:

.set stored_query_result DailyClicksByAdNetwork7Days with (previewCount = 100) <|
Events
| where Timestamp > ago(7d)
| where EventType == 'click'
| summarize Count=count() by Day=bin(Timestamp, 1d), AdNetwork
| order by Count desc
| project Num=row_number(), Day, AdNetwork, Count

Output

Retrieve the next page:

stored_query_result("DailyClicksByAdNetwork7Days")
| where Num between(100 .. 200)

Output

Related content

• Stored query results.
• .show stored_query_result command.
• .drop stored_query_result command.

9 - table()

The table() function references a table by providing its name as an expression of type string.

Syntax

table( TableName [, DataScope] )

Parameters

Valid data scope values

Returns

table(T) returns:

• Data from table T if a table named T exists.
• Data returned by function T if a table named T doesn’t exist but a function named T exists. Function T must take no arguments and must return a tabular result.
• A semantic error is raised if there’s no table named T and no function named T.

Examples

Use table() to access table of the current database

table('StormEvents') | count

Output

Use table() inside let statements

The query above can be rewritten as a query-defined function (let statement) that receives a parameter tableName - which is passed into the table() function.

let foo = (tableName:string)
{
    table(tableName) | count
};
foo('StormEvents')

Output

Use table() inside Functions

The same query as above can be rewritten to be used in a function that receives a parameter tableName - which is passed into the table() function.

.create function foo(tableName:string)
{
    table(tableName) | count
};

Use table() with non-constant parameter

A parameter, which isn’t a scalar constant string, can’t be passed as a parameter to the table() function.

Below, given an example of workaround for such case.

let T1 = print x=1;
let T2 = print x=2;
let _choose = (_selector:string)
{
    union
    (T1 | where _selector == 'T1'),
    (T2 | where _selector == 'T2')
};
_choose('T2')

Output

10 - toscalar()

Returns a scalar constant value of the evaluated expression.

This function is useful for queries that require staged calculations. For example, calculate a total count of events, and then use the result to filter groups that exceed a certain percent of all events.

Any two statements must be separated by a semicolon.

Syntax

toscalar(expression)

Parameters

Returns

A scalar constant value of the evaluated expression. If the result is a tabular, then the first column and first row will be taken for conversion.

Limitations

toscalar() can’t be applied on a scenario that applies the function on each row. This is because the function can only be calculated a constant number of times during the query execution. Usually, when this limitation is hit, the following error will be returned: can't use '<column name>' as it is defined outside its row-context scope.

In the following example, the query fails with the error:

let _dataset1 = datatable(x:long)[1,2,3,4,5];
let _dataset2 = datatable(x:long, y:long) [ 1, 2, 3, 4, 5, 6];
let tg = (x_: long)
{
    toscalar(_dataset2| where x == x_ | project y);
};
_dataset1
| extend y = tg(x)

This failure can be mitigated by using the join operator, as in the following example:

let _dataset1 = datatable(x: long)[1, 2, 3, 4, 5];
let _dataset2 = datatable(x: long, y: long) [1, 2, 3, 4, 5, 6];
_dataset1
| join (_dataset2) on x 
| project x, y

Output

Examples

Evaluate Start, End, and Step as scalar constants, and use the result for range evaluation.

let Start = toscalar(print x=1);
let End = toscalar(range x from 1 to 9 step 1 | count);
let Step = toscalar(2);
range z from Start to End step Step | extend start=Start, end=End, step=Step

Output

The following example shows how toscalar can be used to “fix” an expression so that it will be calculated precisely once. In this case, the expression being calculated returns a different value per evaluation.

let g1 = toscalar(new_guid());
let g2 = new_guid();
range x from 1 to 2 step 1
| extend x=g1, y=g2

Output