lookup operator

Extends the columns of a fact table with values looked-up in a dimension table.

For example, the following query results in a table that extends the FactTable ($left) with data from the DimensionTable ($right) by performing a lookup. The lookup matches each pair (CommonColumn, Col1) from FactTable with each pair (CommonColumn, Col2) in the DimensionTable. For the differences between fact and dimension tables, see fact and dimension tables.

FactTable | lookup kind=leftouter (DimensionTable) on CommonColumn, $left.Col1 == $right.Col2

The lookup operator performs an operation similar to the join operator with the following differences:

• The result doesn’t repeat columns from the $right table that are the basis for the join operation.
• Only two kinds of lookup are supported, leftouter and inner, with leftouter 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 the join operator.
• The lookup operator automatically broadcasts the $right table to the $left table (essentially, behaves as if hint.broadcast was specified). This limits the size of the $right table.

Syntax

LeftTable | lookup [kind = (leftouter|inner)] (RightTable) on Attributes

Parameters

Rules

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 are 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) appear only once in the output table.
• If kind is unspecified or kind=leftouter, then 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.
• If kind=inner, then there’s a row in the output for every combination of matching rows from left and right.

Example

The following example shows how to perform a left outer join between the FactTable and DimTable, based on matching values in the Personal and Family columns.

let FactTable=datatable(Row:string,Personal:string,Family:string) [
  "1", "Rowan",   "Murphy",
  "2", "Ellis",   "Turner",
  "3", "Ellis",   "Turner",
  "4", "Maya",  "Robinson",
  "5", "Quinn",    "Campbell"
];
let DimTable=datatable(Personal:string,Family:string,Alias:string) [
  "Rowan",  "Murphy",   "rowanm",
  "Ellis",  "Turner", "ellist",
  "Maya", "Robinson", "mayar",
  "Quinn",   "Campbell",    "quinnc"
];
FactTable
| lookup kind=leftouter DimTable on Personal, Family

Output

Related content

• Broadcast join
• leftouter join
• inner join
• join operator