Read DB table
Read shipped orders from a database table using Table Input.
Workshop - Read DB table
Build a transformation that reads ORDERS rows from a database.
Filter to shipped orders, calculate lead time, and label late shipments.
What you’ll do
Read rows with Table Input
Generate SQL with Get SQL select statement
Add a calculated field with Calculator
Bucket values with Number range
Sort and format output for review
Prerequisites
Pentaho Data Integration installed and configured
A working database connection. See Database Connections.
Basic
SELECTandWHERE
Estimated time: 20 minutes
Workshop files
Download the following files.
Keep the filenames unchanged.
Save them in your workshop folder.
Create a new transformation
Use any of these options to open a new transformation tab:
Select File > New > Transformation
Use
Ctrl+N(Windows/Linux) orCmd+N(macOS)
Table Input
Read rows from a database using a connection and SQL.
In this workshop, filter to orders with STATUS = 'Shipped'.
Start Spoon.
Drag Table Input onto the canvas.
Open the step properties.
Configure the step to match your environment:
Select your database Connection
Use Get SQL select statement to generate a base query
Add a
WHEREclause for shipped orders
Example filter:
Select Preview. Confirm you get shipped orders.
Select OK.
Checkpoint: Preview shows only rows with STATUS = 'Shipped'.
Calculator
Add a derived field using built-in functions. Use Calculator for speed and simple expressions.
Add a hop from Table Input to Calculator.
Drag Calculator onto the canvas.
Open the step properties.
Configure the calculation shown in the screenshot.
Select OK.
This creates order_time.
It represents the day difference between required and shipped dates.
Add a hop from Calculator to Number range.
Drag Number range onto the canvas.
Open the step properties.
Configure the ranges as shown.
This writes an output label (for example, order_status) based on order_time.
Use the same labels and thresholds as the screenshot.
Select OK.
Sort rows
Sort output to match how you want to read it. This is also a common prerequisite for merge-style steps.
Add a hop from Number range to Sort rows.
Drag Sort rows onto the canvas.
Open the step properties.
Configure the sort keys as shown.
Select OK.
If you hit memory errors, lower the sort size. PDI spills to temp files when needed.
Add a hop from Sort rows to Select values.
Drag Select values onto the canvas.
Open the step properties.
Configure the field selection and type changes shown.
Select OK.
This formats REQUIREDDATE and SHIPPEDDATE.
Troubleshooting
Preview shows zero rows
Confirm the WHERE STATUS = 'Shipped' filter matches your source values.
SQL errors
Select Get SQL select statement again. Then re-apply your WHERE clause.
Date or number conversion issues Fix types in Select values. Re-run the preview.
Out of memory during sort Lower the sort size in Sort rows, or increase JVM memory.
Last updated
Was this helpful?