Getting Started with PDI

If you're new to Pentaho Data Integration (PDI), start here.

This topic consolidates the transformation and job tutorials into one page.

• Getting started with PDI and Hadoop

Pentaho Data Integration (PDI) tutorial

This tutorial is for new users of the Pentaho suite.

You build a transformation and a job using the PDI client (Spoon).

Transformations describe data flows for ETL.

Jobs coordinate ETL activities and dependencies.

In this scenario, you load a CSV file of sales data into a database.

Several customer records are missing postal codes.

You resolve missing postal codes before loading the data.

Prerequisites

To complete this tutorial, you need:

• An installed version of the Pentaho 30-day trial.

Tutorial steps

• Step 1: Extract and load data

• Step 2: Filter for missing codes

• Step 3: Resolve missing data

• Step 4: Clean the data

• Step 5: Run the transformation

• Step 6: Orchestrate with jobs

Step 1: Extract and load data

In Step 1, you retrieve data from a CSV flat file and set up a basic transformation.

In this step

• Create a new transformation

• View the content in the sample file

• Edit and save the transformation

• Load data into a relational database

Create a new transformation

Follow these steps to create a new transformation.

To insert a variable into a field that accepts variables, put your cursor in the field and press CTRL+Spacebar.

Fields that accept variables have a blue diamond.

1. Select File > New > Transformation in the upper-left corner of the PDI window.

   
2. Under the Design tab, expand the Input node, then drag a Text File Input step onto the canvas.

3. Double-click the Text File input step.

   

   Text File Input File tab
4. In the Step Name field, type Read Sales Data.

5. Click Browse to locate the sales_data.csv source file in the ...\design-tools\data-integration\samples\transformations\files folder.

6. Change File type to *.csv. Select sales_data.csv, then click OK.

7. Click Add.

   The path to the file appears under Selected Files.

View the content in the sample file

Follow these steps to look at the contents of the sample file.

1. Click the Content tab, then set the Format field to Unix.

2. Click the File tab again and click Show file content in the lower section of the window.

3. When the Number of lines (0-all lines) window appears, click OK.

4. In the Content of first file window, examine the file:

   • Check the delimiter.

   • Check the enclosure character.

   • Check whether a header row is present.

   In the sample, the input file is comma-delimited.

   It uses the quotation mark (") enclosure character.

   It contains a single header row with field names.

5. Click Close.

Step 1: Edit and save the transformation

Follow these steps to provide information about the data’s content.

1. Click the Content tab. Use the fields to define how your data is formatted.

2. Verify these settings:

   • Separator: comma (,)

   • Enclosure: quotation mark (")

   • Header: selected

   • Number of header lines: 1

   

   Text File Input Content tab
3. Click the Fields tab, then click Get Fields to retrieve the input fields from your source file.

4. When the Number of lines to sample window appears, enter 0, then click OK.

5. If the Scan Result window appears, click Close.

   

   Text File Input Fields tab
6. To verify the data is read correctly, click the Content tab, then click Preview Rows.

7. In the preview window, click OK to accept the default number of rows.

8. Review the data for missing, incomplete, or inconsistent values. For example:

   • STATE and POSTALCODE both contain <null>.

   • COUNTRY contains both USA and United States.

9. Click OK to save the step settings.

10. Open the Transformation Properties window using one of these methods:

    • Right-click an empty area on the canvas, then select Properties.

    • Double-click an empty area on the canvas, then select Properties.

    • Press CTRL+T.

11. In the Transformation Name field, enter Getting Started Transformation, then click OK.

12. Save the transformation. Select File > Save.

When you save a transformation for the first time, you’re prompted for a file location and name.

The usual file extension is .ktr.

Load data into a relational database

Now you are ready to take all records that exit the Filter rows step where POSTALCODE is not null (the true condition) and load them into a database table.

You will use the Table Output step and a hop from the Text File Input step to direct the data stream into a database table.

This section uses a pre-existing sample database that is established during the Pentaho installation.

It starts along with the server.

Create the Table Output step

Follow these instructions to create the Table Output step.

1. Under the Design tab, expand the Output node.

2. Drag a Table Output step into your transformation.

3. Create a hop between Read Sales Data and Table Output:

   1. Press SHIFT.

   2. Click Read Sales Data and drag to Table Output.

   3. Release SHIFT.

4. Double-click the Table Output step to open its Edit properties dialog box.

5. Rename the Table Output step to Write to Database.

Create a connection to the database

Follow these steps to create a connection to the database.

1. Click New next to the Connection field.

2. In the Database Connection window, provide these settings:

   • Connection Name: Sample Data

   • Connection Type: Hypersonic

   • Host Name: localhost

   • Database Name: sampledata

   • Port Number: 9001

   • User Name: pentaho_admin

   • Password: password

3. Click Test. If the test succeeds, click OK.

If the test fails, confirm the sample database is running.

• Windows: Start and stop the Pentaho Server for configuration on Windows

• Linux: Start and stop the Pentaho Server for configuration on Linux

1. Click OK to exit the Database Connection window.

Define the Data Definition Language (DDL)

DDLs are the SQL commands that define structures in a database, such as CREATE TABLE.

1. Enter SALES_DATA in the Target Table field.

2. Since the table doesn’t exist, you can generate and execute the DDL based on the data stream coming from Read Sales Data.

3. In the Table Output window, select Truncate Table.

   

   Table Output step Truncate table field
4. Click SQL to generate the DDL for creating your target table.

5. In the Simple SQL editor window, review the generated SQL statements.

   

   Simple SQL editor
6. Click Execute.

7. Examine the results, then click OK to close the results window.

8. Click Close in the Simple SQL editor window.

9. Click OK to close the Table Output window.

10. Save your transformation.

Step 2: Filter for missing codes

After completing Step 1: Extract and load data, you are ready to add a transformation component to your data pipeline.

The source file contains several records that are missing postal codes.

This section filters out records that have missing postal codes.

It ensures that only complete records are loaded into the database table.

Preview the rows read by the input step

Follow these steps to preview the rows read by the input step.

1. Right-click the Read Sales Data step and select Preview.

   

   Transformation Menu showing how to access Preview
2. Specify the number of rows to preview.

   Optionally, set breakpoints to pause execution when a condition is met.

3. Click the Quick Launch button.

   Preview the data. Notice that several input rows are missing values for POSTALCODE.

   

   Preview showing missing postalcode fields
4. Click Stop on the preview window to end the preview.

Separate the records with missing postal codes

Follow these instructions to use the Filter Rows transformation step to separate records missing postal codes.

You resolve these records later in the tutorial.

1. Add a Filter Rows step to your transformation.

   Under the Design tab, select Flow > Filter Rows.

2. Insert your Filter Rows step between your Read Sales Data step and your Write to Database step.

   To create a hop, click a step, hold SHIFT, then click-and-drag to the next step.

   1. Right-click and delete the hop between the Read Sales Data step and Write to Database steps.

   2. Create a hop between the Read Sales Data step and the Filter Rows step.

   3. Create a hop between the Filter Rows step and the Write to Database step.

   4. In the dialog box that appears, select Result is TRUE.

      

      Hop dialog set to Result is True
3. Double-click the Filter Rows step. The Filter Rows window appears.

4. In the Step Name field, enter Filter Missing Zips.

5. Click in The condition field to open the Fields window.

6. In the Fields window, select POSTALCODE and click OK.

7. Click the comparison operator field. It is set to = by default. The Functions window appears.

8. Select IS NOT NULL, then click OK.

   

   Filter rows is set postalcode is not null
9. Click OK to exit the Filter Rows window.

   You will return to this step later.

   You will configure Send true data to step and Send false data to step.

10. Save your transformation.

Step 3: Resolve missing data

After completing Step 2: Filter for missing codes, you are ready to resolve the missing postal codes.

You will use a second text file that lists cities, states, and postal codes.

You will use it to look up postal codes for records with missing values.

This work uses the false branch of your Filter rows step.

First, use a Text file input step to read from the source file.

Then, use a Stream lookup step to bring the resolved postal codes into the stream.

Finally, use a Select values step to rename fields and remove unnecessary fields.

Retrieve data from your lookup file

Follow these steps to retrieve data from your lookup file.

1. Add a new Text File Input step to your transformation.

   This step retrieves the records from your lookup file.

   Do not add a hop yet.

   

   Add Text File Input step to canvas
2. Open the Text File Input step window, then enter Read Postal Codes in the Step name property.

3. Click Browse to navigate to the Zipssortedbycitystate.csv source file located in the directory ...\design-tools\data-integration\samples\transformations\files.

4. Change File type to *.csv, select Zipsortedbycitrystate.csv, and click OK.

   The path to the source file appears in the File or directory field.

5. Click Add.

   The path to the file appears under Selected files.

View the contents of the sample file

Follow these steps to view the contents of the sample file.

1. Click the Content tab, then set the Format field to Unix​.

2. Click the File tab again and click the Show file content near the bottom of the window.

3. The Number of lines(0=all lines) window appears. Click the OK button to accept the default.

4. The Content of first file window shows the file.

   Examine the file to see how the input file is delimited.

   Check what enclosure character is used.

   Check whether a header row is present.

5. Click Close to close the window.

Edit and save the transformation

Follow these steps to edit and save your transformation.

1. In the Content tab, change the Separator character to a comma (,) and confirm that the Enclosure setting is a quotation mark (").

   Verify that the Header option is selected.

2. Under the Fields tab, click Get Fields to retrieve the data from your CSV file.

3. The Number of lines to sample window appears. Enter 0 in the field, then click OK.

   

   Results from Get Fields in the Fields tab
4. If the Scan Result window displays, click Close to close it.

5. Click Preview rows to verify that your entries are correct.

   1. When prompted to enter the preview size, click OK.

   2. Review the information in the window, then click Close.

6. Click OK to exit the Text File input window.

7. Save the transformation.

Resolve missing zip code information

Follow these steps to resolve the missing postal code information.

1. Add a Stream Lookup step to your transformation by clicking the Design tab, expanding the Lookup folder, then selecting Stream Lookup.

2. Draw a hop from the Filter Missing Zips to the Stream lookup step. In the dialog box that appears, select Result is FALSE.

3. Create a hop from the Read Postal Codes step to the Stream lookup step.

   

   Add a hop from Read Postal Codes to Stream Lookup
4. Double-click the Stream lookup step to open the Stream Value Lookup window.

5. Rename Stream Lookup to Lookup Missing Zips.

6. From the Lookup step drop-down box, select Read Postal Codes as the lookup step. Perform the following:

   1. In the key(s) to look up the value(s) table, define the CITY and STATE fields.

   2. In row #1, open the drop-down menu in the Field column and select CITY.

   3. Click in the LookupField column and select CITY.

   4. In row #2, open the drop-down menu in the Field column and select STATE.

   5. Click in the LookupField column and select STATE.

      

      Stream value lookup example
7. Click Get Lookup Fields to pull the three fields from the Read Postal Code step.

8. POSTALCODE is the only field you want to retrieve.

   To delete the CITY and STATE lines, right-click in the line and select Delete Selected Lines.

9. In the New Name field, change the name POSTALCODE to ZIP_RESOLVED and verify that Type is set to String.

10. Select Use sorted list (i.s.o. hashtable).

    

    Value lookup example
11. Click OK to close the Stream Value Lookup edit properties dialog box.

12. Save your transformation.

Preview your transformation

Follow these steps to preview your transformation.

1. To preview the data, select and right-click the Lookup Missing Zips step. From the menu that appears, select Preview.

2. In the Transformation debug dialog window, click Quick Launch to preview the data flowing through this step.

3. In the Examine preview data window that appears, note that the new field, ZIP_RESOLVED, has been added to the stream containing your resolved postal codes.

   

   Examine ZIP_RESOLVED field
4. Click Close to close the window.

5. If the Select the preview step window appears, click Close.

The execution results near the bottom of the PDI window show updated metrics in the Step Metrics tab.

Apply formatting to your transformation

Follow these steps to clean up the field layout on your lookup stream so that it matches the format and layout of the other stream going to the Write to Database step.

1. Add a Select Values step to your transformation by expanding the Transform folder and clicking Select Values.

2. Create a hop from the Lookup Missing Zips to the Select Values step.

   

   Add hop from Lookup Missing Zips to Select Values
3. Double-click the Select Values step to open its properties dialog box.

4. Rename the Select Values step to Prepare Field Layout.

5. Click Get fields to select to retrieve all fields and begin modifying the stream layout.

6. In the Fields list, find the # column and click the number for the ZIP_RESOLVED field.

   Use CTRL UP (macOS, COMMAND UP) to move ZIP_RESOLVED just below the POSTALCODE field.

   

   Move ZIP_RESOLVED field under POSTALCODES field
7. Select the old POSTALCODE field in the list (line 20), right-click in the line, and select Delete Selected Lines.

8. The original POSTALCODE field was formatted as a 9-character string.

   You must modify your new field to match the form.

9. Click the Meta-Data tab.

10. In the first row of the Fields to alter table the meta-data for section, click in the Fieldname column and select ZIP_RESOLVED. Perform the following steps:

11. Enter POSTALCODE in the Rename to column.

12. Select String in the Type column and enter 9 in the Length column.

    

    POSTALCODE String type and length
13. Click OK to exit the edit properties dialog box.

14. Draw a hop from the Prepare Field Layout (Select values) step to the Write to Database (Table output) step.

15. When prompted, select the Main output of the step option.

16. Save your transformation.

    

    Renaming fields workflow example

Step 4: Clean the data

After completing Step 3: Resolve missing data, you can further cleanse and categorize the data into buckets before loading it into a relational database.

In this section, you cleanse the COUNTRY field data by mapping United States to USA using the Value mapper step.

In addition, you use buckets for categorizing the SALES data using the Number range step.

Add a Value mapper step to the transformation

Follow these steps to add the Value mapper step to the transformation.

1. Delete both hops connected to the Write to Database step. For each hop, right-click and select Delete.

2. Create some extra space on the canvas. Drag the Write to Database step toward the right side of your canvas.

   

   Add space on canvas for Value mapper step
3. Add the Value mapper step to your transformation by expanding the Transform folder and choosing Value mapper.

4. Create a hop between the Filter Missing Zips and Value mapper steps. In the dialog box that appears, select Result is TRUE.

5. Create a hop between the Prepare Field Layout and Value mapper steps. When prompted, select the Main output of the step option.

   

   Add Value mapper step to the canvas

Set the properties in the Value Mapper step

Follow these steps to set the properties in the Value mapper step.

1. Double-click the Value mapper step to open its properties dialog box.

2. Click in the Fieldname to use field and select COUNTRY.

3. In the Field Values table, define the United States and USA field values.

   1. In row #1, click the field in the Source value column and enter United States.

   2. Then, click the field in the Target value column and enter USA.

      

      Set values for fields in the Value mapper step
4. Click OK.

5. Save your transformation.

Apply ranges

Follow these steps to apply ranges to your transformation.

1. Add a Number range step to your transformation by expanding the Transform folder and selecting Number range.

2. Create a hop between the Value mapper and Number range steps.

3. Create a hop between the Number range and Write to Database steps.

   When prompted, select the Main output of the step option.

   

   Add Number range step to the canvas
4. Double-click the Number range step to open its properties dialog box.

5. Click in the Input field and select SALES from the list.

6. In the Output field enter DEALSIZE.

7. In the Ranges (min <=x< max) table, define the ranges and bucket values.

   1. In row #1, click the field in the Upper Bound column and enter 3000.0. Then, click the field in the Value column and enter Small.

   2. In row #2, click the field in the Lower Bound column and enter 3000.0. Then, click the field in the Upper Bound column and enter 7000.0. Click the field in the Value column and enter Medium.

   3. In row #3, click the field in the Lower Bound column and enter 7000.0. Then, click the field in the Value column and enter Large.

      

      Set ranges in Number Range step
8. Click OK.

Execute the SQL statement

Your database table does not yet contain the field DEALSIZE.

Perform these steps to execute the SQL statement.

1. Double-click the Write to Database step to open its properties dialog box.

2. Click the SQL button at the bottom of the window to generate the new DDL.

   1. The Simple SQL editor window appears with the SQL statements needed to alter the table.

      

      Simple SQL editor to generate the DDL
   2. Click Execute to execute the SQL statement.

   3. The Results of the SQL statements window appears. Examine the results, then click OK to close the window.

   4. Click Close in the Simple SQL editor window to close it.

   5. Click OK to close the Write to Database window.

3. Save your transformation.

Step 5: Run the transformation

Pentaho Data Integration provides a number of deployment options.

In this section, you create a transformation using the Local run option.

1. Select Action > Run.

   The Run Options window appears.

2. Keep the default Pentaho local option for this exercise.

3. Click Run.

   The transformation executes.

   

   Transformation runs without errors

After the transformation runs, the Execution Results panel opens below the canvas.

View the execution results

Use the tabs in Execution Results to verify execution, troubleshoot errors, and monitor performance.

• Step Metrics

  Shows statistics for each step. It includes rows read, rows written, errors, and rows per second.

  This tutorial has no intentional errors. The transformation should run successfully.

  If errors occur, steps with errors are highlighted in red.

  In this example, the Lookup Missing Zips step caused an error.

  

  Error message display
• Logging

  Shows logging details for the most recent execution.

  Use it to locate errors. Error lines are highlighted in red.

  In this example, the Lookup Missing Zips step caused an error.

  It tried to look up POSTALCODE2, which does not exist in the lookup stream.

  

  Transformation logging display
• Execution History

  Shows step metrics and log information from previous executions.

  This works only if you configured database logging.

  Use the Logging tab in Transformation Settings.

• Performance Graph

  Analyzes step performance based on metrics like rows read, rows written, errors, and rows per second.

  This works only if you configured database logging.

  Use the Logging tab in Transformation Settings.

• Metrics

  Shows a Gantt chart after the transformation or job runs.

  It can include database connection time, SQL execution time, or transformation load time.

  

  Step metrics tab
• Preview Data

  Shows a preview of the data.

Step 6: Orchestrate with jobs

Jobs are used to coordinate ETL activities such as:

• Defining the flow and dependencies that control the linear order for the transformations to run.

• Preparing for execution by checking conditions such as, "Is my source file available?" or "Does a table exist?"

• Performing bulk load database operations.

• Assisting file management, such as posting or retrieving files using FTP, copying files, and deleting files.

• Sending success or failure notifications through email.

For this part of the tutorial, imagine that an external system is responsible for placing your sales_data.csv input in its source location every Saturday night at 9 p.m.

You want to create a job that will verify that the file has arrived and then run the transformation to load the records into the database.

The following steps assume that you have built a Getting Started transformation as described in Step 1: Extract and load data.

1. Go to File > New > Job.

   

   PDI job window
2. Expand the General folder and drag a Start job entry onto the canvas.

   The Start job entry defines where the execution will begin.

3. Expand the Conditions folder and add a File Exists job entry.

4. Draw a hop from the Start job entry to the File Exists job entry.

   

   Draw hop from Start to File exists
5. Double-click the File Exists job entry to open its properties dialog box.

   Click Browse and set the filter near the bottom of the window to All Files.

   Select the sales_data.csv from the following directory: ...\design-tools\data-integration\samples\transformations\files.

6. Click OK to exit the Open File window.

7. Click OK to exit the Check if a file exists window.

8. Expand the General folder and add a Transformation job entry.

9. Draw a hop between the File Exists and the Transformation job entries.

10. Double-click the Transformation job entry to open its properties dialog box.

11. Click Browse to open the Select repository object window.

    Browse to and select the Getting Started transformation.

12. Click OK to close the Transformation window.

13. Save your job as Sample Job.

14. Click Run in the toolbar.

    When the Run Options window appears, select Local environment type and click Run.

    The Execution Results panel should open showing job metrics and log information.

    

    Job sample

PDI job tutorial (standalone)

This tutorial overlaps with Step 6: Orchestrate with jobs.

Use it if you want a shorter, job-only walkthrough.

Jobs are used to coordinate ETL activities such as:

• Defining the flow and dependencies for what order transformations should be run.

• Preparing for execution by checking conditions such as, "Is my source file available?" or "Does a table exist?"

• Performing bulk load database operations.

• File management such as posting or retrieving files using FTP, copying files and deleting files.

• Sending success or failure notifications through email.

For this exercise, imagine that an external system is responsible for placing your sales_data.csv input in its source location every Saturday night at 9 p.m.

You want to create a job that will check to see that the file has arrived and run your transformation to load the records into the database.

To complete this exercise, complete the steps in Pentaho Data Integration (PDI) tutorial first.

1. Go to File > New > Job.

   

   PDI Job Window
2. Expand the General folder and drag a Start job entry onto the graphical workspace.

   The Start job entry defines where the execution will begin.

3. Expand the Conditions folder and add a File Exists job entry.

4. Draw a hop from the Start job entry to the File Exists job entry.

5. Double-click the File Exists job entry to open its Edit Properties dialog box.

   Click Browse and set the filter near the bottom of the window to All Files.

   Select the sales_data.csv from the following location: ...\design-tools\data-integration\samples\transformations\files.

6. Click OK to exit from the Open File window.

7. Click OK to exit from the Check if a file exists window.

8. In Spoon, expand the General folder and add a Transformation job entry.

9. Draw a hop between the File Exists and the Transformation job entries.

10. Double-click the Transformation job entry to open its edit Properties dialog box.

11. Click Browse to open the Select repository object window.

12. Expand the repository tree to find your sample transformation. Select it and click OK.

    

    Select repository object window
13. Save your job as Sample Job.

14. Click Run in the toolbar.

    When the Run Options window appears, choose Local environment type and click Run.

    The Execution Results panel should open showing job metrics and log information.

    

    Job Sample

More PDI documentation

These tutorials are designed to quickly demonstrate basic PDI features.

For more detailed information, see these topics in the Pentaho Data Integration document:

• Learn about the PDI Client

• Use Pentaho Repositories in PDI

• Schedule Perspective in the PDI Client