Pentaho 3.2 Data Integration: Beginner's Guide

Book Image

Pentaho 3.2 Data Integration: Beginner's Guide

Book Image

Pentaho 3.2 Data Integration: Beginner's Guide

Overview of this book

Pentaho Data Integration (a.k.a. Kettle) is a full-featured open source ETL (Extract, Transform, and Load) solution. Although PDI is a feature-rich tool, effectively capturing, manipulating, cleansing, transferring, and loading data can get complicated.This book is full of practical examples that will help you to take advantage of Pentaho Data Integration's graphical, drag-and-drop design environment. You will quickly get started with Pentaho Data Integration by following the step-by-step guidance in this book. The useful tips in this book will encourage you to exploit powerful features of Pentaho Data Integration and perform ETL operations with ease.Starting with the installation of the PDI software, this book will teach you all the key PDI concepts. Each chapter introduces new features, allowing you to gradually get involved with the tool. First, you will learn to work with plain files, and to do all kinds of data manipulation. Then, the book gives you a primer on databases and teaches you how to work with databases inside PDI. Not only that, you'll be given an introduction to data warehouse concepts and you will learn to load data in a data warehouse. After that, you will learn to implement simple and complex processes.Once you've learned all the basics, you will build a simple datamart that will serve to reinforce all the concepts learned through the book.

Pentaho 3.2 Data Integration Beginner's Guide

Pentaho 3.2 Data Integration Beginner's Guide

Credits

Foreword

The Kettle Project

The Kettle Project

About the Author

About the Author

About the Reviewers

About the Reviewers

Preface

Free Chapter

Getting Started with Pentaho Data Integration

Getting Started with Pentaho Data Integration

Pentaho Data Integration and Pentaho BI Suite

Pentaho Data Integration

Time for action – installing PDI

Launching the PDI graphical designer: Spoon

Time for action – starting and customizing Spoon

Time for action – creating a hello world transformation

Time for action – running and previewing the hello_world transformation

Installing MySQL

Time for action – installing MySQL on Windows

Time for action – installing MySQL on Ubuntu

Getting Started with Transformations

Getting Started with Transformations

Reading data from files

Time for action – reading results of football matches from files

Time for action – reading all your files at a time using a single Text file input step

Time for action – reading all your files at a time using a single Text file input step and regular expressions

Sending data to files

Time for action – sending the results of matches to a plain file

Getting system information

Time for action – updating a file with news about examinations

Time for action – running the examination transformation from a terminal window

Time for action – getting data from an XML file with information about countries

Basic Data Manipulation

Basic Data Manipulation

Basic calculations

Time for action – reviewing examinations by using the Calculator step

Time for action – reviewing examinations by using the Formula step

Calculations on groups of rows

Time for action – calculating World Cup statistics by grouping data

Time for action – counting frequent words by filtering

Looking up data

Time for action – finding out which language people speak

Controlling the Flow of Data

Controlling the Flow of Data

Splitting streams

Time for action – browsing new PDI features by copyinga dataset

Time for action – assigning tasks by distributing

Splitting the stream based on conditions

Time for action – assigning tasks by filtering priorities with the Filter rows step

Time for action – assigning tasks by filtering priorities with the Switch/ Case step

Merging streams

Time for action – gathering progress and merging all together

Time for action – giving priority to Bouchard by using Append Stream

Transforming Your Data with JavaScript Code and the JavaScript Step

Transforming Your Data with JavaScript Code and the JavaScript Step

Doing simple tasks with the JavaScript step

Time for action – calculating scores with JavaScript

Time for action – testing the calculation of averages

Enriching the code

Time for action – calculating flexible scores by using variables

Reading and parsing unstructured files

Time for action – changing a list of house descriptions with JavaScript

Avoiding coding by using purpose-built steps

Transforming the Row Set

Transforming the Row Set

Converting rows to columns

Time for action – enhancing a films file by converting rows to columns

Time for action – calculating total scores by performances by country

Normalizing data

Time for action – enhancing the matches file by normalizing the dataset

Generating a custom time dimension dataset by using Kettle variables

Time for action – creating the time dimension dataset

Time for action – getting variables for setting the default starting date

Validating Data and Handling Errors

Validating Data and Handling Errors

Capturing errors

Time for action – capturing errors while calculating the ageof a film

Time for action – aborting when there are too many errors

Time for action – treating errors that may appear

Avoiding unexpected errors by validating data

Time for action – validating genres with a Regex Evaluation step

Time for action – checking films file with the Data Validator

Working with Databases

Working with Databases

Introducing the Steel Wheels sample database

Time for action – creating a connection with the Steel Wheels database

Time for action – exploring the sample database

Querying a database

Time for action – getting data about shipped orders

Time for action – getting orders in a range of dates by using parameters

Time for action – getting orders in a range of dates by using variables

Sending data to a database

Time for action – loading a table with a list of manufacturers

Time for action – inserting new products or updating existent ones

Time for action – testing the update of existing products

Eliminating data from a database

Time for action – deleting data about discontinued items

Performing Advanced Operations with Databases

Performing Advanced Operations with Databases

Preparing the environment

Time for action – populating the Jigsaw database

Looking up data in a database

Time for action – using a Database lookup step to create a list of products to buy

Time for action – using a Database join step to create a list of suggested products to buy

Introducing dimensional modeling

Loading dimensions with data

Time for action – loading a region dimension with a Combination lookup/update step

Time for action – testing the transformation that loads the region dimension

Time for action – keeping a history of product changes with the Dimension lookup/update step

Time for action – testing the transformation that keeps a historyof product changes

Creating Basic Task Flows

Creating Basic Task Flows

Introducing PDI jobs

Time for action – creating a simple hello world job

Receiving arguments and parameters in a job

Time for action – customizing the hello world file with arguments and parameters

Running jobs from a terminal window

Time for action – executing the hello world job from a terminal window

Using named parameters and command-line arguments in transformations

Time for action – calling the hello world transformation with fixed arguments and parameters

Deciding between the use of a command-line argument and a named parameter

Running job entries under conditions

Time for action – sending a sales report and warning the administrator if something is wrong

Creating Advanced Transformations and Jobs

Creating Advanced Transformations and Jobs

Enhancing your processes with the use of variables

Time for action – updating a file with news about examinations by setting a variable with the name of the file

Enhancing the design of your processes

Time for action – generating files with top scores

Time for action – calculating the top scores with a subtransformation

Time for action – splitting the generation of top scores by copying and getting rows

Time for action – generating the files with top scores by nesting jobs

Iterating jobs and transformations

Time for action – generating custom files by executing a transformation for every input row

Developing and Implementing a Simple Datamart

Developing and Implementing a Simple Datamart

Exploring the sales datamart

Loading the dimensions

Time for action – loading dimensions for the sales datamart

Extending the sales datamart model

Loading a fact table with aggregated data

Time for action – loading the sales fact table by looking up dimensions

Getting facts and dimensions together

Time for action – loading the fact table using a range of dates obtained from the command line

Time for action – loading the sales star

Getting rid of administrative tasks

Time for action – automating the loading of the sales datamart

Taking it Further

Taking it Further

PDI best practices

Getting the most out of PDI

Integrating PDI and the Pentaho BI suite

PDI Enterprise Edition and Kettle Developer Support

Working with Repositories

Working with Repositories

Creating a repository

Time for action – creating a PDI repository

Working with the repository storage system

Time for action – logging into a repository

Examining and modifying the contents of a repository with the Repository explorer

Migrating from a file-based system to a repository-based system and vice-versa

Pan and Kitchen: Launching Transformations and Jobs from the Command Line

Pan and Kitchen: Launching Transformations and Jobs from the Command Line

Running transformations and jobs stored in files

Running transformations and jobs from a repository

Checking the exit code

Providing options when running Pan and Kitchen

Quick Reference: Steps and Job Entries

Quick Reference: Steps and Job Entries

Transformation steps

Spoon Shortcuts

Spoon Shortcuts

General shortcuts

Designing transformations and jobs

Introducing PDI 4 Features

Introducing PDI 4 Features

Visual improvements for designing transformations and jobs

Time for action – creating a hop with the mouse-over assistance

Enterprise features

Pop Quiz Answers

Pop Quiz Answers

Index

Customer Reviews

5 star

0

4 star

0

3 star

0

2 star

0

1 star

0

Running transformations and jobs from a repository

In order to run a transformation or job stored in a repository follow these steps:

Open a terminal window.
Go to the Kettle installation directory.

Run the proper command according to the following table:

Running a ...

Windows

Unix-like system

transformation

pan.bat /rep:<value>
		/user:<user>
		/pass:<value>
		/trans:<value>
		/dir:<value>

pan.sh /rep:<value>
       /user:<user>
       /pass:<value>
       /trans:<value>
       /dir:<value>

job

kitchen.bat /rep:<value>
            /user:<user>
            /pass:<value>
            /job:<value>
            /dir:<value>

kitchen.sh /rep:<value>
           /user:<user>
           /pass:<value>
           /job:<value>
           /dir:<value>

In this preceding table:

rep is the name of the repository to log into
user and pass are the credentials to log into the repository
trans and job are the names of the transformation or job to run
dir is the name of the directory where the transformation or job is located

The parameters are shown on different lines for you to clearly identify all the options.

Note

When you type the command, you have to write all the parameters on the same line.

Suppose that you work on Windows, you have a repository named MY_REPO, and you log into the repository with user PDI_USER and password 1234. To run a transformation named Hello located in a directory named MY_WORK in that repository, type the following:

pan.bat /rep:"MY_REPO" /user:"PDI_USER" /pass:"1234" /trans:"Hello" /dir:"/MY_WORK/"

Note

If you defined auto-login, you don't need to provide the repository information— the rep, user, and pass command line parameters—as part of the command.

Specifying command line options

In the examples provided in this appendix, all options are specified by using the /option:value syntax—for example, /trans:"Hello".

Instead of /, you can also use -. Between the name of the option and the value, you can also use =. This means the options /trans:"Hello" and -trans="Hello" are equivalents.

You may use any combination of /,-, :, and =.

Note

In Windows, the use of - and = may cause problems; it's recommended that you use the /option:value syntax.

If there are spaces in the values, you can use quotes ('') or double quotes ("") to keep the values together. If there are no spaces, the quotes are optional.