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

PDI to Jupyter Notebook

Workshop - PDI to Jupyter Notebook

This workshop demonstrates how to create a Pentaho Data Integration (PDI) pipeline that processes sales data and automatically triggers analysis in Jupyter Notebook when the output file is saved.

The topics were going to cover:

  • Creating a Jupyter Notebook

  • Installing required Python packages: jupyter, watchdog, xslxwriter

  • Create a PDI pipeline: sales_data.csv file

  • Create a File Watcher script

Pipeline

Quick overview of the pipeline:

  • Execute a PDI pipeline with sample sales_data.csv - from datasets folder

  • The file output to the pdi-output folder triggers the Jupyter Notebook to

  • Load the data - csv files from pdi-output - analyze and visualize the results

  • Export the results to the reports folder

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

Select the Host Docker OS:

PDI to Jupyter Notebook

Setup Verification

Before building the PDI pipeline, verify everything works by running the sample notebook.

  1. Verify Python Packages are Installed.

Python packages (watchdog, xlsxwriter) are automatically installed when the container starts via the post-start.sh startup script.

If packages are missing, check the container logs: docker logs jupyter-datascienc

  1. Verify Test Files Exist (Inside the Container).

Run the Sales Analysis Notebook

  1. In Jupyter Lab, navigate to notebooks/ in the file browser

  2. Open sales_analysis.ipynb

  3. Run each cell in order (Shift+Enter or use the Run menu)

  4. The notebook will:

    • Load sales_data.csv from /home/jovyan/datasets/

    • Generate a 4-panel Sales Analysis Dashboard

    • Calculate Key Metrics (revenue, average order value, profit margin)

    • Export an Excel report to /home/jovyan/reports/

sales_analysis.ipynb

  1. Check the Output Report

Open the Excel file and verify it has two sheets:

  • Summary - Key metrics (Total Revenue, Average Order Value, etc.)

  • Detailed Data - Full processed dataset

sales_analysis

Build the PDI Pipeline

The data scientists have deployed the sales_analysis.ipynb notebook. The notebook will be triggered by a File Watcher that's polling the ~/Jupyter-Notebook/pdi-output/ for:

  • sales_detailed_*.csv

So in this part of the workshop, we're going to create a simple pipeline that:

  • Loads the sales.csv

  • Cleans and performs some calculations and aggregations

  • Outputs to: ~/Jupyter-Notebook/pdi-output/ folder.

  1. Start Pentaho Data Integration (Spoon)

  1. Create a New Transformation - sales_pipeline.ktr:

sales_pipeline.ktr
sales_data.csv
15KB
Open
sales_pipeline.ktr
30KB
Open

CSV File input

The CSV File Input transform extracts data from delimited files using either a predefined schema or manually configured field layouts. Despite its name, this transform supports any delimiter—pipes, tabs, semicolons, or custom separators—not just commas.

Built for speed through optimized internal processing, this transform offers a focused subset of Text File Input capabilities with three key performance advantages:

Native I/O (NIO) uses direct system calls for faster file reading, though it's currently limited to local files without VFS support.

Parallel Processing enables distributed file reading when running multiple transform copies or in clustered mode. Each copy processes a separate file block, allowing workload distribution across multiple threads or slave nodes.

Lazy Conversion optimizes performance for pass-through data scenarios. When fields flow unchanged from input to output (like file-to-database transfers), this feature prevents unnecessary data type conversions, avoiding the overhead of converting raw data into strings, dates, or numbers.

While this transform has fewer configuration options than the general Text File Input transform, these performance optimizations make it ideal for high-throughput data processing workflows.

  1. Drag & drop a CSV File input step onto the canvas.

  2. Double-click on the step, and configure the following properties:

CSV file input

CSV File Input

  • Step type: Input > CSV file input

  • Purpose: Reads the source sales data

  • Configuration:

    1. Drag a CSV file input step onto the canvas

    2. Double-click to configure:

      • Filename: ~/Jupyter-Notebook/datasets/sales_data.csv

      • Delimiter: ,

      • Header row present: checked

    3. Click Get Fields to auto-detect the 8 columns

    4. Verify the field types: order_id (Integer), customer_id (Integer), product_name (String), product_category (String), quantity (Integer), unit_price (Number), cost (Number), order_date (String)

    5. Click Preview to verify data loads correctly (should show 250 rows)

Data Validator

The Data Validator transform enables you to define validation rules that check input data across different fields in each row. When the validator encounters a row that violates one or more validation rules, it generates an error or exception.

You can capture all validation errors by configuring an error handling hop from this transform, which will provide you with a comprehensive list of any validation failures that occur during processing.

  1. Drag & drop Data Validator step onto the canvas.

  2. Double-click on the step, and configure the following properties:

Validation: quantity

Validation - quantity

Validation: unit_price

Validation - unit_price

Data Validator (optional)

  • Step type: Validation > Data Validator

  • Purpose: Validates input data quality

  • Configuration:

    1. Add a Data Validator step and connect it from CSV file input

    2. Add validations:

      • quantity: Data type = Integer, Minimum value = 1

      • unit_price: Data type = BigNumber, Minimum value = 0.01

    3. Create an error handling hop (red hop) to a Dummy step for invalid records

Replace in String

The Replace In String transform performs simple search and replace operations on String fields.

It also supports regular expressions and group references. group references are picked up in the replace by string as $n where n is the number of the group.

You can also use hexadecimal replacements for special characters.

  1. Drag & drop Replace in string step onto the canvas.

  2. Double-click on the step, and configure the following properties:

Replace in string - standardize

Replace in String

  • Step type: Transform > Replace in String

  • Purpose: Normalise the inconsistent product_category casing

  • Configuration:

    1. Add a Replace in String step

    2. Add rows to fix each category (the dataset has mixed case like electronics / Electronics):

      In stream field
      Search
      Replace with
      Use RegEx

      product_category

      electronics

      Electronics

      no

      product_category

      clothing

      Clothing

      no

      product_category

      home

      Home

      no

      product_category

      sports

      Sports

      no

      product_category

      books

      Books

      no

    3. Set Case sensitive to no for each row to catch all variations

Calculator

The Calculator transform offers built-in functions that operate on your input field values.

Beyond the function arguments (Field A, Field B, and Field C), you need to define the return type for each function.

After calculations are complete, you can exclude specific fields from the output using the Remove option. This feature helps eliminate temporary values that aren't needed in your final pipeline.

The Calculator delivers significantly faster execution compared to custom JavaScript scripts.

  1. Drag & drop calculator step onto the canvas.

  2. Double-click on the step, and configure the following properties:

Calculator

Calculator

  • Step type: Transform > Calculator

  • Purpose: Compute derived fields

  • Configuration:

    1. Add a Calculator step

    2. Add two calculations:

      New field
      Calculation
      Field A
      Field B

      total_amount

      A * B

      quantity

      unit_price

      total_cost

      A * B

      quantity

      cost

    3. To compute profit margin, add a User Defined Java Expression step (or a second Calculator step) after this one:

      • profit_margin = (total_amount - total_cost) / total_amount

Formula

The Formula step can calculate Formula Expressions within a data stream. It can be used to create simple calculations like [A]+[B] or more complex business logic with a lot of nested if / then logic.

  1. Drag & drop Formula step onto the canvas.

  2. Double-click on the step, and configure the following properties:

Formula - profit margin

Formula

  • Step type: Scripting > Formula

  • Purpose: Calculate profit margin using Pentaho Formula Engine (Libformula)

  • Configuration:

    1. Add a Formula step and connect it from the Calculator step

    2. Click Add to create a new formula:

      Field name
      Formula
      Value type

      profit_margin

      [total_amount] - [total_cost] / [total_amount]

      Number

    3. The formula uses field references in square brackets (e.g., [total_amount])

    4. This calculates the profit margin as a decimal (e.g., 0.35 = 35% margin)

The Formula step uses Pentaho's Libformula engine which supports Excel-like formulas. For simple arithmetic like this, you could also use a Calculator step or User Defined Java Expression. However, Formula steps are more flexible for complex calculations.

Text file output

The Text File Output transform exports data to text file formats, most commonly generating CSV files that can be opened in spreadsheet applications like Excel.

This transform also supports creating fixed-width files by specifying field lengths in the fields configuration tab. You have two options for defining the output structure: use an existing Schema Definition or manually configure the field layout. When working with a Schema Definition, pair this transform with the Schema Mapping transform to align your incoming data stream with the chosen schema structure.

  1. Drag & drop Text file output step onto the canvas.

  2. Double-click on the step, and configure the following properties:

Text file output

Text File Output

  • Step type: Output > Text file output

  • Purpose: Write the processed data to the pdi-output folder

  • Configuration:

    1. Add a Text file output step

    2. Configure the File tab:

      • Filename: ~/Jupyter-Notebook/pdi-output/sales_detailed

      • Extension: csv

      • Include date in filename: Yes

      • Date time format: yyyyMMdd_HHmmss (produces sales_detailed_20250218_143022.csv)

    3. Configure the Content tab:

      • Separator: ,

      • Header: Yes

    4. Click Get Fields to populate the output field list

File watcher

The file watcher monitors the pdi-output/ directory and automatically executes the analysis notebook inside the Docker container when PDI writes a new file.

  1. Start File watcher - in a new terminal.

Expected output:

  1. Re-run the transformation. The file watcher detects the new sales_detailed.csv and auto-executes the notebook:

File watcher

The file watcher uses docker exec to run jupyter nbconvert --execute inside the container, so the notebook runs automatically without you having to open Jupyter Lab.

x

x

x

x

Quick Setup

To check the various scripts and that volume mappings are working, let's analyze a sample sales_data.csv:

  • Install some python packages

  • Load a sample dataset - test_sales_data.csv

  • Run the sales_analysis.ipynb - check container paths

  • Check ouput

Please ensure you have completed the following setup: Jupyter Notebook.

Check Jupyter Notebook is running in a Docker container ..!

To list / install python packages:

Once inside the container:

  1. Install required Python packages:

  1. Check for the test_sales_data.csv & sales_analysis.ipynb (still in container):

  1. Open the sales_analysis.ipynb notebook and RUN each section:

RUN the Notebook

  1. Check for reports: C:\Jupyter-Notebook\reports\sales_analysis_timestamp.xlsx

Reports

Check you have 2 sheets: Summary & Detailed Data.

File Watcher

x

x

x

Data Pipeline

The data scientists have deployed the sales_analysis.ipynb notebook. The notebook will be triggered by a File Watcher that's polling the C:\Jupyter-Notebook\pdi-output for:

  • sales_detailed_*.csv

So in this part of the workshop, we're going to create a simple pipeline that:

  • Loads the sales.csv

  • Cleans and performs some calculations and aggregations

  • Outputs to: C:\Jupyter-Notebook\pdi-output folder.

  1. Start Pentaho Data Integration.

Windows - PowerShell

Linux

  1. Create a New Transformation:

CSV File input

The CSV File Input transform extracts data from delimited files using either a predefined schema or manually configured field layouts. Despite its name, this transform supports any delimiter—pipes, tabs, semicolons, or custom separators—not just commas.

Built for speed through optimized internal processing, this transform offers a focused subset of Text File Input capabilities with three key performance advantages:

Native I/O (NIO) uses direct system calls for faster file reading, though it's currently limited to local files without VFS support.

Parallel Processing enables distributed file reading when running multiple transform copies or in clustered mode. Each copy processes a separate file block, allowing workload distribution across multiple threads or slave nodes.

Lazy Conversion optimizes performance for pass-through data scenarios. When fields flow unchanged from input to output (like file-to-database transfers), this feature prevents unnecessary data type conversions, avoiding the overhead of converting raw data into strings, dates, or numbers.

While this transform has fewer configuration options than the general Text File Input transform, these performance optimizations make it ideal for high-throughput data processing workflows.

  1. Drag & drop a CSV File input step onto the canvas.

  2. Double-click on the step, and configure the following properties:

x

x

Data Validator

The Data Validator transform enables you to define validation rules that check input data across different fields in each row. When the validator encounters a row that violates one or more validation rules, it generates an error or exception.

You can capture all validation errors by configuring an error handling hop from this transform, which will provide you with a comprehensive list of any validation failures that occur during processing.

x

x

x

Replace in String

The Replace In String transform performs simple search and replace operations on String fields.

It also supports regular expressions and group references. group references are picked up in the replace by string as $n where n is the number of the group.

You can also use hexadecimal replacements for special characters.

x

x

x

Calculator

The Calculator transform offers built-in functions that operate on your input field values.

Beyond the function arguments (Field A, Field B, and Field C), you need to define the return type for each function.

After calculations are complete, you can exclude specific fields from the output using the Remove option. This feature helps eliminate temporary values that aren't needed in your final pipeline.

The Calculator delivers significantly faster execution compared to custom JavaScript scripts.

x

x

x

x

Group by

The Group By transform organizes rows from a data source according to one or more specified fields, creating a single row for each distinct group. Additionally, it can compute aggregate values like sums, averages, or counts for each group.

Typical applications include determining average sales figures by product category or tallying inventory quantities for each item type.

This step requires sorted input data to function properly. When working with unsorted data, only identical consecutive rows will be grouped together correctly. Furthermore, if data is sorted externally before entering the Transformation, differences in case sensitivity within the grouping fields may lead to unexpected results.

For scenarios involving unsorted input data, consider using the Memory Group By transform instead, which can handle data regardless of its initial order.

x

x

x

Text file output

The Text File Output transform exports data to text file formats, most commonly generating CSV files that can be opened in spreadsheet applications like Excel.

This transform also supports creating fixed-width files by specifying field lengths in the fields configuration tab. You have two options for defining the output structure: use an existing Schema Definition or manually configure the field layout. When working with a Schema Definition, pair this transform with the Schema Mapping transform to align your incoming data stream with the chosen schema structure.

x

x

x

x

PreviousJupyter Notebook
NextEnrich Data

Last updated

Was this helpful?