Cross-cluster and cross-database queries - Kusto

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.

Kusto Explorer The default database is the one selected in the connections panel , and the current cluster is the cluster containing that database. Azure Data Explorer web UI The default database is the one selected in the connection pane , and the current cluster is the cluster containing that database. Client libraries Specify the default database and cluster by the


   Data Source

and


   Initial Catalog

properties of the Kusto connection strings . Kusto Explorer The default database is the one selected in the connections panel and the current eventhouse is the eventhouse containing that database. Real-Time Intelligence KQL queryset The default database is the current database selected either directly or through an eventhouse. Client libraries Specify the default database with the database URI , used for the


   Data Source

properties of the Kusto connection strings . For the eventhouse, use its cluster URI. You can find it by selecting System Overview in the Eventhouse details section for the selected eventhouse.

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>
To execute a query, you must have viewer permission to the default database and
to every other database referenced in the query.
For more information, see Kusto role-based access control.
The number of records returned from a query is limited by default, even if there's no specific use of the take operator. To lift this limit, use the notruncation client request option. For more information, see Query limits.
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("*").*
The name of the default database is also a potential match, so database("*") specifies all tables of all databases including the default.
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

In this article

Prerequisites

Identify the cluster and database in context

Identify the eventhouse and database in context

Perform cross-eventhouse or cross-database queries

Qualified names and the union operator

Qualified names and restrict access statements

Handle schema changes of remote entities

Functions and views

Limitations of cross-cluster function calls

Limitations of cross-eventhouse function calls

Examples

Related content