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.

• 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

Perform cross-cluster or cross-database queries

Perform cross-eventhouse or cross-database queries

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>

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 or patterns can also be included in restrict access statement. Wildcards in cluster names aren't permitted.

Wildcards in eventhouse names aren't permitted.

• 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*").*);

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 (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.

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"] )

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

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

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

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

• Cross-cluster join
• Allow cross-tenant queries and commands

• Cross-cluster join