Database Lookups

Workshop - Database Lookup

The Database lookup step allows you to look for values in a database table.

In this guided demonstration, you will:

Configure the following steps:
- Database Lookup

The following content has been automatically generated by an AI system and should be used for informational purposes only. We cannot guarantee the accuracy, completeness, or timeliness of the information provided.

Any actions taken based on this content are at your own risk. We recommend seeking qualified expertise or conducting further research to validate and supplement the information provided.

Create a new Transformation

Any one of these actions opens a new Transformation tab for you to begin designing your transformation.

By clicking File > New > Transformation
By using the CTRL-N hot key

Data grid

The Data Grid step allows you to enter a static list of rows in a grid. This is usually done for testing, reference or demo purposes.

Drag the Data Grid step onto the canvas.
Open the Data grid properties dialog box.
Ensure the following details are configured, as outlined below:

User defined Java expression

The User Defined Java Expression step in Pentaho Data Integration allows you to write custom Java code that executes on each row of your data transformation. This step is useful when you need to perform complex calculations or data manipulations that aren't possible with PDI's standard steps.

You can access field values using the get("fieldname") method and create multiple expressions within a single step. Each expression produces a new output field in your data stream. The step handles type conversion automatically, making it flexible for various data operations.

Common uses include mathematical calculations, string manipulations, conditional logic, and date transformations. It's particularly valuable when you need Java-specific functionality or want to simplify your transformation by replacing multiple basic steps with a single, powerful Java expression.

Drag the User Defined Java expression step onto the canvas.
Open the User defined Java expression properties dialog box.
Ensure the following details are configured, as outlined below:

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

Database lookup

The Database lookup step has 3 options

Simple Lookup

The Database lookup step allows you to look up values in a database table. Lookup values are added as new fields onto the stream.

Drag the Data Grid step onto the canvas.
Open the Data grid properties dialog box.
Ensure the following details are configured, as outlined below:

The ‘key’ fields are where you specify the conditions. Each row in the grid represents a comparison between a column in the table, and a field in your stream, by using one of the provided comparators.

In this example:

WHERE PRODUCTNAME LIKE '%Aston Martin%' AND BUYPRICE < 90 WHERE PRODUCTNAME LIKE '%'Ford Falcon%' AND BUYPRICE < 70 WHERE PRODUCTNAME LIKE '%Corvette'%' AND BUYPRICE < 70

The Database lookup step allow us to retrieve any number of columns based on the search criteria. Each database column you enter in the lower grid will become a new field in your dataset.

You can rename them (this is particularly useful if you already have a field with the same name) and supply a default value if no record is found in the search. In the workflow, you added three fields: PRODUCTNAME, PRODUCTSCALE, and BUYPRICE.

For values where there’s no match for PRODUCTNAME, ‘not available’ is returned. In the Preview, notice there are no PRODUCTNAMES that match %Ford Falcon% where the max price is less than the max price of 70.

PreviousLookups NextScripting

Last updated 2 hours ago