lookup operator
Extends the columns of a fact table with values lookedup in a dimension table.
FactTable  lookup kind=leftouter (DimensionTable) on CommonColumn, $left.Col1 == $right.Col2
Here, the result is a table that extends the FactTable
($left
) with data from DimensionTable
(referenced by $right
) by performing a lookup of each pair (CommonColumn
,Col
)
from the former table with each pair (CommonColumn1
,Col2
) in the latter table. For the differences between fact and dimension tables, see fact and dimension tables.
The lookup
operator performs an operation similar to the join operator with the following differences:
 The result does not repeat columns from the
$right
table that are the basis for the join operation.  Only two kinds of lookup are supported,
leftouter
andinner
, withleftouter
being the default.  In terms of performance, the system by default assumes that the
$left
table is the larger (facts) table, and the$right
table is the smaller (dimensions) table. This is exactly opposite to the assumption used by thejoin
operator.  The
lookup
operator automatically broadcasts the$right
table to the$left
table (essentially, behaves as ifhint.broadcast
was specified). Note that this limits the size of the$right
table.
NOTE
If the right side of the lookup is larger than several tens of MBs, the query will fail.
You can run the following query to estimate the size of the right side in bytes:
rightSide
 summarize sum(estimate_data_size(*))
Syntax
LeftTable 
lookup
[kind
=
(leftouter
inner
)] (
RightTable )
on
Attributes
Arguments

LeftTable: The table or tabular expression that is the basis for the lookup. Denoted as
$left
. 
RightTable: The table or tabular expression that is used to "populate" new columns in the fact table. Denoted as
$right
. 
Attributes: A commadelimited list of one or more rules that describe how rows from LeftTable are matched to rows from RightTable. Multiple rules are evaluated using the
and
logical operator. A rule can be one of:
Rule kind  Syntax  Predicate 

Equality by name  ColumnName  where LeftTable.ColumnName == RightTable.ColumnName 
Equality by value  $left. LeftColumn == $right. RightColumn 
where $left. LeftColumn == $right. *RightColumn 
Note
In case of 'equality by value', the column names must be qualified with the applicable owner table denoted by $left
and $right
notations.
kind
: An optional instruction on how to treat rows in LeftTable that have no match in RightTable. By default,leftouter
is used, which means all those rows will appear in the output with null values used for the missing values of RightTable columns added by the operator. Ifinner
is used, such rows are omitted from the output. (Other kinds of join are not supported by thelookup
operator.)
Returns
A table with:

A column for every column in each of the two tables, including the matching keys. The columns of the right side will be automatically renamed if there are name conflicts.

A row for every match between the input tables. A match is a row selected from one table that has the same value for all the
on
fields as a row in the other table. 
The Attributes (lookup keys) will appear only once in the output table.

kind
unspecified,kind=leftouter
In addition to the inner matches, there's a row for every row on the left (and/or right), even if it has no match. In that case, the unmatched output cells contain nulls.

kind=inner
There's a row in the output for every combination of matching rows from left and right.
Example
events_all
 project
event_uuid,
data_source_id,
original_time
 where original_time > ago(24h) and isnotempty(data_source_id)
 lookup datasources on $left.data_source_id == $right.data_source_id
 summarize EventCount=count_distinct(event_uuid) by DataSourceName=data_source_name
 order by EventCount desc
 take 10
Results
DataSourceName  EventCount 

Cisco ACE Firewall@ test.abc.acd  112346 
testUseCase0lookupDataSource  9123 
MicroStWindows@Test3  123 