macro-expand operator

Applies to: ✅ Azure Data Explorer

The macro-expand operator simplifies running a subquery on a set of entities, such as clusters, databases, or tables, and then combining the results into a single output.

The operator is useful when you have data spread across multiple clusters, databases, or tables. For example, when data is held in the same global region as its source, you can use the macro-expand operator in a single query across data in distinct locations, instead of running separate queries for each entity and combining the results manually.

The set of entities you want to query is called an entity group. The entity group can be stored for reuse in a database or defined directly in your query text. For more information about stored entity groups, see Entity groups.

The macro-expand operator runs the subquery separately for each entity in the group and then combines all the results into a single output. The subquery can include nested macro-expand operators. However, the identifier for the inner macro-expand must be different from the identifier for the outer macro-expand to clearly distinguish between the scope and references of each one.

Syntax

macro-expand [kind = Kind] [isfuzzy = IsFuzzy] EntityGroup as EntityIdentifier (Subquery)

macro-expand [kind = Kind] [isfuzzy = IsFuzzy] entity_group [EntityReference [, ...] as EntityIdentifier (Subquery)``]

macro-expand EntityIdentifier in EntityGroupIdentifier (Subquery)

Variations

There are several ways to specify the entity group used by the macro-expand operator:

• Inline: All elements are explicitly defined in the text of the operator invocation itself. For an example, see Calculate errors.

• Via let statement: The entity group is specified in the query using a let statement outside the macro-expand operator using the syntax:

  let EntityGroupIdentifier = entity_group [EntityReference [, ...]]

  For an example, see Calculate SKU errors using let statement.

• Using a stored entity group: The query uses an entity group stored in the database in scope rather than defined in the query.

  For an example, see Extend table with contextual scalar functions.

Parameters

Subquery contextual functions

The macro-expand subquery can reference two specialized scalar functions as if they're part of the entity being referenced:

• $current_database - Returns the database name of the entity reference.
• $current_cluster_endpoint - Returns the URL of the cluster of the entity reference.

Examples

The following examples show how to use the macro-expandoperator.

Calculate errors

The following example uses an inline variation entity group to calculate the number of errors produced by each Stock Keeping Unit (SKU). It defines an entity_group, X, that includes databases named MyDatabase in two clusters. The query then performs a subquery to filter for error logs and counts the errors by Source. Next it performs an inner join on Source with the DimCluster table to get the SKU for each source. Finally, it sums the error counts by SKU.

macro-expand entity_group [cluster('C1').database('MyDatabase'), cluster('C2').database('MyDatabase')] as X
(
    X.Logs
    | where Level == 'Error'
    | summarize Count=count() by Source
    | join kind=inner (X.DimCluster | project SKU, Source) on Source
)
| summarize Sum=sum(Count) by SKU

• The summarize operator works on the combined results of all expanded subqueries while the macro-expand operator is only applied to the subquery between the parentheses so it's clear which scope is expanded.

• The join operator is executed separately for each entity in the entity_group. In the example, the join is performed between two tables in the same entity, denoted by X. This means there was no cross-cluster join.

To write the same query without using macro-expand, it might look as follows:

union
  (
    cluster('C1').database('MyDatabase').Logs
    | where Level == 'Error'
    | summarize Count=count() by Source
    | join kind=inner (cluster('C1').database('MyDatabase').DimCluster | project SKU, Source) on Source
  ),
  (
    cluster('C2').database('MyDatabase').Logs
    | where Level == 'Error'
    | summarize Count=count() by Source
    | join kind=inner (cluster('C2').database('MyDatabase').DimCluster | project SKU, Source) on Source
  )
| summarize Sum=sum(Count) by SKU

Calculate SKU errors using let statement

The following example uses a let statement to define an entity group in a variable named Greater which includes the MyDatabase database from both C1 and C2 clusters. This entity group is then used to perform the same query in the previous example to calculate the number of errors produced by each SKU. The macro-expand operator is used to reference the Greater entity group (alias X).

let GreaterDatabase = entity_group [cluster('C1').database('MyDatabase'), cluster('C2').database('MyDatabase')];
macro-expand GreaterDatabase as X
(
    X.Logs
    | where Level == 'Error'
    | summarize Count=count() by Source
    | join kind=inner (X.DimCluster | project SKU, Source) on Source
)
| summarize Sum=sum(Count) by SKU

Extend table with contextual scalar functions

The following query uses the stored entity group variation. It runs a subquery on the Admins table from each entity using the stored entity group MyEntityGroup. For more information on how to create a stored entity, see .create entity_group command. It uses $current_database and $current_cluster_endpoint to extend the table, adding the current database and current cluster for each row. Then, it summarizes the results by counting the number of rows for each combination of current_cluster and current_database.

macro-expand MyEntityGroup as X
(
    X.Admins
    | extend current_database = X.$current_database, current_cluster = X.$current_cluster_endpoint
)
| summarize count() by current_cluster, current_database

Nested macro-expand query

The following query runs a nested subquery with an outer entity group MyEntityGroup_Outer (alias X) and an inner entity group MyEntityGroup_Inner (alias Y). It joins the Admins table from each entity in both the outer (X) and inner (Y) entity groups. The query filters for logs from the last hour. Then it extends the tables to include the current database and cluster for each row using $current_database and $current_cluster_endpoint. The query performs a join on the Source column to combine inner and outer entity groups. The prefixes lhs (left-hand side) and rhs (right-hand side) denote X and Y entity groups respectively. Finally, it summarizes the results by counting the number of rows for each combination of lhs_cluster, lhs_database, rhs_cluster, and rhs_database.

macro-expand MyEntityGroup_Outer as X
(
    macro-expand  MyEntityGroup_Inner as Y
    (
        X.Admins
        | where Timestamp > ago(1h)
        | extend lhs_database = X.$current_database, lhs_cluster = X.$current_cluster_endpoint
        | join (
            Y.Admins
            | where Timestamp > ago(1h)
            | extend rhs_database = Y.$current_database, rhs_cluster = Y.$current_cluster_endpoint
        ) on Source
    )
)
| summarize count() by lhs_cluster, lhs_database, rhs_cluster, rhs_database

Related content

• Entity groups
• join operator
• union operator
• .show entity_group(s) command
• current_database()
• current_cluster_endpoint()