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,xslxwriterCreate a PDI pipeline: sales_data.csv file
Create a File Watcher script
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:
Setup Verification
Before building the PDI pipeline, verify everything works by running the sample notebook.
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
Verify Test Files Exist (Inside the Container).
Run the Sales Analysis Notebook
In Jupyter Lab, navigate to notebooks/ in the file browser
Open sales_analysis.ipynb
Run each cell in order (Shift+Enter or use the Run menu)
The notebook will:
Load
sales_data.csvfrom/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/
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
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.
Start Pentaho Data Integration (Spoon)
Create a New Transformation - sales_pipeline.ktr:
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.
Drag & drop a CSV File input step onto the canvas.
Double-click on the step, and configure the following properties:
CSV File Input
Step type: Input > CSV file input
Purpose: Reads the source sales data
Configuration:
Drag a CSV file input step onto the canvas
Double-click to configure:
Filename:
~/Jupyter-Notebook/datasets/sales_data.csvDelimiter:
,Header row present: checked
Click Get Fields to auto-detect the 8 columns
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)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.
Drag & drop Data Validator step onto the canvas.
Double-click on the step, and configure the following properties:
Validation: quantity
Validation: unit_price
Data Validator (optional)
Step type: Validation > Data Validator
Purpose: Validates input data quality
Configuration:
Add a Data Validator step and connect it from CSV file input
Add validations:
quantity: Data type = Integer, Minimum value = 1unit_price: Data type = BigNumber, Minimum value = 0.01
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.
Drag & drop Replace in string step onto the canvas.
Double-click on the step, and configure the following properties:
Replace in String
Step type: Transform > Replace in String
Purpose: Normalise the inconsistent
product_categorycasingConfiguration:
Add a Replace in String step
Add rows to fix each category (the dataset has mixed case like
electronics/Electronics):In stream fieldSearchReplace withUse RegExproduct_category
electronicsElectronicsno
product_category
clothingClothingno
product_category
homeHomeno
product_category
sportsSportsno
product_category
booksBooksno
Set Case sensitive to
nofor 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.
Drag & drop calculator step onto the canvas.
Double-click on the step, and configure the following properties:
Calculator
Step type: Transform > Calculator
Purpose: Compute derived fields
Configuration:
Add a Calculator step
Add two calculations:
New fieldCalculationField AField Btotal_amountA * B
quantityunit_pricetotal_costA * B
quantitycostTo 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.
Drag & drop Formula step onto the canvas.
Double-click on the step, and configure the following properties:
Formula
Step type: Scripting > Formula
Purpose: Calculate profit margin using Pentaho Formula Engine (Libformula)
Configuration:
Add a Formula step and connect it from the Calculator step
Click Add to create a new formula:
Field nameFormulaValue typeprofit_margin[total_amount] - [total_cost] / [total_amount]Number
The formula uses field references in square brackets (e.g.,
[total_amount])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.
Drag & drop Text file output step onto the canvas.
Double-click on the step, and configure the following properties:
Text File Output
Step type: Output > Text file output
Purpose: Write the processed data to the pdi-output folder
Configuration:
Add a Text file output step
Configure the File tab:
Filename:
~/Jupyter-Notebook/pdi-output/sales_detailedExtension:
csvInclude date in filename: Yes
Date time format:
yyyyMMdd_HHmmss(producessales_detailed_20250218_143022.csv)
Configure the Content tab:
Separator:
,Header: Yes
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.
Start File watcher - in a new terminal.
Expected output:
Re-run the transformation. The file watcher detects the new
sales_detailed.csvand auto-executes the notebook:
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:
Install required Python packages:
Check for the test_sales_data.csv & sales_analysis.ipynb (still in container):
Open the sales_analysis.ipynb notebook and RUN each section:
Check for reports: C:\Jupyter-Notebook\reports\sales_analysis_timestamp.xlsx
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.
Start Pentaho Data Integration.
Windows - PowerShell
Linux
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.
Drag & drop a CSV File input step onto the canvas.
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
Last updated
Was this helpful?