# Lookup operator

[lookup](https://docs.kloudfuse.com/platform/3.5.0/fuseql-lookup-operators/#lookup)

Retrieves specified fields from a lookup table based on matching values between your log data and the table’s primary key(s).

## lookup

Retrieves specified fields from a lookup table based on matching values between your log data and the table’s primary key(s).

Enrich log data by merging it with information from pre-defined lookup tables. Lookup adds context, detail, and external data to the logs, significantly improving analysis and troubleshooting.

### Syntax

```none
lookup field1 as alias1, field2 as alias2, ... \ (1)
  from "tableName" \ (2)
  on factTableKey1 = lookupTableKey1, factTableKey2 = lookupTableKey2, ... \ (3)
```

|     |     |
| --- | --- |
| **1** | `field1 as alias1, field2 as alias2, …​`: Defines the fields to retrieve from the lookup table, and assigns aliases to them. Mandatory when retrieving multiple fields. |
| **2** | `from "tableName"`: Specifies the name of the lookup table. |
| **3** | `on factTableKey1 = lookupTableKey1, factTableKey2 = lookupTableKey2, …​`: Establishes the join condition(s) using primary keys from both the log data (fact table) and the lookup table. |

### Limitations

- Create lookup tables and populate them in the Kloudfuse UI before using the `lookup` operator.
- Maximum size of a lookup table is 50 MB.
- Must use compatible data types for join conditions.
- Must use the lookup table’s primary key(s).

### Examples

#### Enrich Web Server Logs with User Location Data
Provide valuable geographic context for your web traffic analysis.

When using a lookup table `UserLocations` with columns `userID`, `country`, and `city`, add the user’s country and city to the web server access logs matching on the `userId` field.

```none
| lookup country as userCountry, city as userCity from "UserLocations" on userId = userID
```

The query retrieves the `country` and `city` fields from `UserLocations` lookup table by matching the `userId` field, and adds them to your logs as `userCountry` and `userCity` respectively.

#### Correlate application errors with error codes and descriptions.
Turn a simple error code within the original logs into a full description.

When you have a lookup table `ErrorCodes` with columns `errorCode` and `errorDescription`, you can add detailed error descriptions to application error logs by matching on the `errorId` field.

```none
| lookup errorDescription as errorMessage from "ErrorCodes" on errorId = errorCode
```

This query adds the `errorDescription` from the `ErrorCodes` table as `errorMessage` to the log entries.

#### Add product category and region based on productID
Quickly analyse logs related to sales or product.

Use a lookup table `ProductInformation` with columns `ProductID`, `Category`, and `Region`.

```none
| lookup Category as ProductCategory, Region as SalesRegion from "ProductInformation" on product_id = ProductID
```

Add the `Category` and `Region` to each log line as `ProductCategory` and `SalesRegion` when matching the `product_id` value from the log line with `ProductID` in the `ProductInformation` lookup table.