Book Details

ISBN 139781849515245

Paperback352 pages

Book Description

Pentaho Data Integration (PDI, also called Kettle), one of the data integration tools leaders, is broadly used for all kind of data manipulation such as migrating data between applications or databases, exporting data from databases to flat files, data cleansing, and much more. Do you need quick solutions to the problems you face while using Kettle?

Pentaho Data Integration 4 Cookbook explains Kettle features in detail through clear and practical recipes that you can quickly apply to your solutions. The recipes cover a broad range of topics including processing files, working with databases, understanding XML structures, integrating with Pentaho BI Suite, and more.

Pentaho Data Integration 4 Cookbook shows you how to take advantage of all the aspects of Kettle through a set of practical recipes organized to find quick solutions to your needs. The initial chapters explain the details about working with databases, files, and XML structures. Then you will see different ways for searching data, executing and reusing jobs and transformations, and manipulating streams. Further, you will learn all the available options for integrating Kettle with other Pentaho tools.

Pentaho Data Integration 4 Cookbook has plenty of recipes with easy step-by-step instructions to accomplish specific tasks. There are examples and code that are ready for adaptation to individual needs.

Table of Contents

Chapter 1: Working with Databases

Introduction

Connecting to a database

Getting data from a database

Getting data from a database by providing parameters

Getting data from a database by running a query built at runtime

Inserting or updating rows in a table

Inserting new rows where a simple primary key has to be generated

Inserting new rows where the primary key has to be generated based on stored values

Deleting data from a table

Creating or altering a database table from PDI (design time)

Creating or altering a database table from PDI (runtime)

Inserting, deleting, or updating a table depending on a field

Changing the database connection at runtime

Loading a parent-child table

Chapter 2: Reading and Writing Files

Introduction

Reading a simple file

Reading several files at the same time

Reading unstructured files

Reading files having one field by row

Reading files with some fields occupying two or more rows

Writing a simple file

Writing an unstructured file

Providing the name of a file (for reading or writing) dynamically

Using the name of a file (or part of it) as a field

Reading an Excel file

Getting the value of specific cells in an Excel file

Writing an Excel file with several sheets

Writing an Excel file with a dynamic number of sheets

Chapter 3: Manipulating XML Structures

Introduction

Reading simple XML files

Specifying fields by using XPath notation

Validating well-formed XML files

Validating an XML file against DTD definitions

Validating an XML file against an XSD schema

Generating a simple XML document

Generating complex XML structures

Generating an HTML page using XML and XSL transformations

Chapter 4: File Management

Introduction

Copying or moving one or more files

Deleting one or more files

Getting files from a remote server

Putting files on a remote server

Copying or moving a custom list of files

Deleting a custom list of files

Comparing files and folders

Working with ZIP files

Chapter 5: Looking for Data

Introduction

Looking for values in a database table

Looking for values in a database (with complex conditions or multiple tables involved)

Looking for values in a database with extreme flexibility

Looking for values in a variety of sources

Looking for values by proximity

Looking for values consuming a web service

Looking for values over an intranet or Internet

Chapter 6: Understanding Data Flows

Introduction

Splitting a stream into two or more streams based on a condition

Merging rows of two streams with the same or different structures

Comparing two streams and generating differences

Generating all possible pairs formed from two datasets

Joining two or more streams based on given conditions

Interspersing new rows between existent rows

Executing steps even when your stream is empty

Processing rows differently based on the row number

Chapter 7: Executing and Reusing Jobs and Transformations

Introduction

Executing a job or a transformation by setting static arguments and parameters

Executing a job or a transformation from a job by setting arguments and parameters dynamically

Executing a job or a transformation whose name is determined at runtime

Authors

Adrián Sergio Pulvirenti

Adrián Sergio Pulvirenti was born in Buenos Aires, Argentina, in 1972. He earned his Bachelor's degree in Computer Sciences at UBA, one of the most prestigious universities in South America.
He has dedicated more than 15 years to developing desktop and web-based software solutions. Over the last few years he has been leading integration projects and development of BI solutions.

María Carina Roldán

María Carina Roldán was born in Argentina; she earned her Bachelor's degree in Computer Science at the Universidad Nacional de La Plata (UNLP) and then moved to Buenos Aires where she has lived since 1994.

She has worked as a BI consultant for almost twenty years. She started working with Pentaho technology back in 2006. Over the last ten years she has been dedicated full time to developing BI solutions mainly as an ETL specialist using Pentaho tools. In all these years she worked for different companies including Assert Solutions, a Pentaho partner company based in Argentina, and Webdetails — a Portuguese company acquired by Pentaho in 2013. Currently, she works as an independent consultant.

Carina is the author of the first and second edition of Pentaho Data Integration Beginner's Book, Packt Publishing and the co-author of the first and second edition of Pentaho Data Integration Cookbook, Packt Publishing.

Table of Contents

Chapter 1: Working with Databases

Introduction

Connecting to a database

Getting data from a database

Getting data from a database by providing parameters

Getting data from a database by running a query built at runtime

Inserting or updating rows in a table

Inserting new rows where a simple primary key has to be generated

Inserting new rows where the primary key has to be generated based on stored values

Deleting data from a table

Creating or altering a database table from PDI (design time)

Creating or altering a database table from PDI (runtime)

Inserting, deleting, or updating a table depending on a field

Changing the database connection at runtime

Loading a parent-child table

Chapter 2: Reading and Writing Files

Introduction

Reading a simple file

Reading several files at the same time

Reading unstructured files

Reading files having one field by row

Reading files with some fields occupying two or more rows

Writing a simple file

Writing an unstructured file

Providing the name of a file (for reading or writing) dynamically

Using the name of a file (or part of it) as a field

Reading an Excel file

Getting the value of specific cells in an Excel file

Writing an Excel file with several sheets

Writing an Excel file with a dynamic number of sheets

Chapter 3: Manipulating XML Structures

Introduction

Reading simple XML files

Specifying fields by using XPath notation

Validating well-formed XML files

Validating an XML file against DTD definitions

Validating an XML file against an XSD schema

Generating a simple XML document

Generating complex XML structures

Generating an HTML page using XML and XSL transformations

Chapter 4: File Management

Introduction

Copying or moving one or more files

Deleting one or more files

Getting files from a remote server

Putting files on a remote server

Copying or moving a custom list of files

Deleting a custom list of files

Comparing files and folders

Working with ZIP files

Chapter 5: Looking for Data

Introduction

Looking for values in a database table

Looking for values in a database (with complex conditions or multiple tables involved)

Looking for values in a database with extreme flexibility

Looking for values in a variety of sources

Looking for values by proximity

Looking for values consuming a web service

Looking for values over an intranet or Internet

Chapter 6: Understanding Data Flows

Introduction

Splitting a stream into two or more streams based on a condition

Merging rows of two streams with the same or different structures

Comparing two streams and generating differences

Generating all possible pairs formed from two datasets

Joining two or more streams based on given conditions

Interspersing new rows between existent rows

Executing steps even when your stream is empty

Processing rows differently based on the row number

Chapter 7: Executing and Reusing Jobs and Transformations

Introduction

Executing a job or a transformation by setting static arguments and parameters

Executing a job or a transformation from a job by setting arguments and parameters dynamically

Executing a job or a transformation whose name is determined at runtime

Alerts & Offers

Series & Level

We understand your time is important. Uniquely amongst the major publishers, we seek to develop and publish the broadest range of learning and information products on each technology. Every Packt product delivers a specific learning pathway, broadly defined by the Series type. This structured approach enables you to select the pathway which best suits your knowledge level, learning style and task objectives.

Learning

As a new user, these step-by-step tutorial guides will give you all the practical skills necessary to become competent and efficient.

Beginner's Guide

Friendly, informal tutorials that provide a practical introduction using examples, activities, and challenges.

Essentials

Fast paced, concentrated introductions showing the quickest way to put the tool to work in the real world.

Cookbook

A collection of practical self-contained recipes that all users of the technology will find useful for building more powerful and reliable systems.

Blueprints

Guides you through the most common types of project you'll encounter, giving you end-to-end guidance on how to build your specific solution quickly and reliably.

Mastering

Take your skills to the next level with advanced tutorials that will give you confidence to master the tool's most powerful features.

Starting

Accessible to readers adopting the topic, these titles get you into the tool or technology so that you can become an effective user.

Progressing

Building on core skills you already have, these titles share solutions and expertise so you become a highly productive power user.