Ctrlk
For the complete documentation index, see llms.txt. This page is also available as Markdown.

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 SELECT and WHERE

Estimated time: 20 minutes

Workshop files

Download the following files.

Keep the filenames unchanged.

Save them in your workshop folder.

tr_reading_database_warehouse.ktr
25KB
Open
Read from a database

Create a new transformation

Use any of these options to open a new transformation tab:

  • Select File > New > Transformation

  • Use Ctrl+N (Windows/Linux) or Cmd+N (macOS)

Table Input

Read rows from a database using a connection and SQL. In this workshop, filter to orders with STATUS = 'Shipped'.

  1. Start Spoon.

  1. Drag Table Input onto the canvas.

  2. Open the step properties.

  3. Configure the step to match your environment:

    • Select your database Connection

    • Use Get SQL select statement to generate a base query

    • Add a WHERE clause for shipped orders

Example filter:

Table input

  1. Select Preview. Confirm you get shipped orders.

  2. 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.

  1. Add a hop from Table Input to Calculator.

  2. Drag Calculator onto the canvas.

  3. Open the step properties.

  4. Configure the calculation shown in the screenshot.

Calculate diff days

  1. Select OK.

This creates order_time. It represents the day difference between required and shipped dates.

Number range

Map numeric values into named buckets. This makes reports easier to scan.

  1. Add a hop from Calculator to Number range.

  2. Drag Number range onto the canvas.

  3. Open the step properties.

  4. Configure the ranges as shown.

Number range

This writes an output label (for example, order_status) based on order_time. Use the same labels and thresholds as the screenshot.

  1. Select OK.

Sort rows

Sort output to match how you want to read it. This is also a common prerequisite for merge-style steps.

  1. Add a hop from Number range to Sort rows.

  2. Drag Sort rows onto the canvas.

  3. Open the step properties.

  4. Configure the sort keys as shown.

Sort rows

  1. Select OK.

If you hit memory errors, lower the sort size. PDI spills to temp files when needed.

Select values

Keep only fields you need. Fix types, lengths, and formats for downstream steps.

  1. Add a hop from Sort rows to Select values.

  2. Drag Select values onto the canvas.

  3. Open the step properties.

  4. Configure the field selection and type changes shown.

  5. Select OK.

This formats REQUIREDDATE and SHIPPEDDATE.

Run and validate

Run the transformation and inspect the final stream.

  1. In Spoon, select Run.

  2. In Execution Results, open Preview data for Select values.

Status of 'shipped' orders

Checkpoint: You see shipped orders plus your derived fields (order_time, and the range label).

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.

PreviousCreate DB table
NextUpdate DB table

Last updated

Was this helpful?