Cross-cluster and cross-database queries

Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel

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

Identify the eventhouse and database in context

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

Environment	Database/Eventhouse in context
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 eventhouse:

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

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

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

Note

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.

Tip

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 eventhouse names.

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

Note

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

Wildcards in eventhouse names aren't permitted.

The following query restricts query access to the following entities:

• 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-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 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-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-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("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("OtherEventhouseClusterURI").database("OtherDb").GetDataPivot()

Related content

• Cross-cluster join