Pentaho Data Integration Cookbook - Second Edition

Pentaho Data Integration Cookbook - Second Edition - Second Edition

Overview of this book

Pentaho Data Integration is the premier open source ETL tool, providing easy, fast, and effective ways to move and transform data. While PDI is relatively easy to pick up, it can take time to learn the best practices so you can design your transformations to process data faster and more efficiently. If you are looking for clear and practical recipes that will advance your skills in Kettle, then this is the book for you. Pentaho Data Integration Cookbook Second Edition guides you through the features of explains the Kettle features in detail and provides easy to follow recipes on file management and databases that can throw a curve ball to even the most experienced developers. Pentaho Data Integration Cookbook Second Edition provides updates to the material covered in the first edition as well as new recipes that show you how to use some of the key features of PDI that have been released since the publication of the first edition. You will learn how to work with various data sources ‚Äì from relational and NoSQL databases, flat files, XML files, and more. The book will also cover best practices that you can take advantage of immediately within your own solutions, like building reusable code, data quality, and plugins that can add even more functionality. Pentaho Data Integration Cookbook Second Edition will provide you with the recipes that cover the common pitfalls that even seasoned developers can find themselves facing. You will also learn how to use various data sources in Kettle as well as advanced features.

Pentaho Data Integration Cookbook Second Edition

Credits

About the Author

About the Reviewers

www.PacktPub.com

Preface

Free Chapter

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

Building SQL queries via database metadata

Performing repetitive database design tasks from PDI

Reading and Writing Files

Introduction

Reading a simple file

Reading several files at the same time

Reading semi-structured files

Reading files having one field per row

Reading files with some fields occupying two or more rows

Writing a simple file

Writing a semi-structured 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

Reading data from an AWS S3 Instance

Working with Big Data and Cloud Sources

Introduction

Loading data into Salesforce.com

Getting data from Salesforce.com

Loading data into Hadoop

Getting data from Hadoop

Loading data into HBase

Getting data from HBase

Loading data into MongoDB

Getting data from MongoDB

Manipulating XML Structures

Introduction

Reading simple XML files

Specifying fields by using the Path 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

Reading an RSS Feed

Generating an RSS Feed

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

Encrypting and decrypting files

Looking for Data

Introduction

Looking for values in a database table

Looking for values in a database with complex conditions

Looking for values in a database with dynamic queries

Looking for values in a variety of sources

Looking for values by proximity

Looking for values by using a web service

Looking for values over intranet or the Internet

Validating data at runtime

Understanding and Optimizing 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

Adding checksums to verify datasets

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

Processing data into shared transformations via filter criteria and subtransformations

Altering a data stream with Select values

Processing multiple jobs or transformations in parallel

Executing and Re-using Jobs and Transformations

Introduction

Launching jobs and transformations

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

Executing part of a job once for every row in a dataset

Executing part of a job several times until a condition is true

Moving part of a transformation to a subtransformation

Using Metadata Injection to re-use transformations

Integrating Kettle and the Pentaho Suite

Introduction

Creating a Pentaho report with data coming from PDI

Creating a Pentaho report directly from PDI

Configuring the Pentaho BI Server for running PDI jobs and transformations

Executing a PDI transformation as part of a Pentaho process

Executing a PDI job from the Pentaho User Console

Generating files from the PUC with PDI and the CDA plugin

Populating a CDF dashboard with data coming from a PDI transformation

Getting the Most Out of Kettle

Introduction

Sending e-mails with attached files

Generating a custom logfile

Running commands on another server

Programming custom functionality

Generating sample data for testing purposes

Working with JSON files

Getting information about transformations and jobs (file-based)

Getting information about transformations and jobs (repository-based)

Using Spoon's built-in optimization tools

Utilizing Visualization Tools in Kettle

Introduction

Managing plugins with the Marketplace

Data profiling with DataCleaner

Visualizing data with AgileBI

Using Instaview to analyze and visualize data

Data Analytics

Introduction

Reading data from a SAS datafile

Studying data via stream statistics

Building a random data sample for Weka

Data Structures

Books data structure

museums data structure

outdoor data structure

Steel Wheels data structure

Lahman Baseball Database

References

Books

Online

Index

Customer Reviews

5 star

4 star

3 star

2 star

1 star

Introduction

Databases are broadly used by organizations to store and administer transactional data such as customer service history, bank transactions, purchases, sales, and so on. They are also used to store data warehouse data used for Business Intelligence solutions.

In this chapter, you will learn to deal with databases in Kettle. The first recipe tells you how to connect to a database, which is a prerequisite for all the other recipes. The rest of the chapter teaches you how to perform different operations and can be read in any order according to your needs.

Note

The focus of this chapter is on relational databases (RDBMS). Thus, the term database is used as a synonym for relational database throughout the recipes.

Sample databases

Through the chapter you will use a couple of sample databases. Those databases can be created and loaded by running the scripts available at the book's website. The scripts are ready to run under MySQL.

Note

If you work with a different DBMS, you may have to modify the scripts slightly.

For more information about the structure of the sample databases and the meaning of the tables and fields, please refer to Appendix A, Data Structures. Feel free to adapt the recipes to different databases. You could try some well-known databases; for example, Foodmart (available as part of the Mondrian distribution at http://sourceforge.net/projects/mondrian/) or the MySQL sample databases (available at http://dev.mysql.com/doc/index-other.html).

Pentaho BI platform databases

As part of the sample databases used in this chapter you will use the Pentaho BI platform Demo databases. The Pentaho BI Platform Demo is a preconfigured installation that lets you explore the capabilities of the Pentaho platform. It relies on the following databases:

Database name	Description
`hibernate`	Administrative information including user authentication and authorization data.
`Quartz`	Repository for Quartz; the scheduler used by Pentaho.
`Sampledata`	Data for Steel Wheels, a fictional company that sells all kind of scale replicas of vehicles.

By default, all those databases are stored in Hypersonic (HSQLDB). The script for creating the databases in HSQLDB can be found at http://sourceforge.net/projects/pentaho/files. Under Business Intelligence Server | 1.7.1-stable look for pentaho_sample_data-1.7.1.zip. While there are newer versions of the actual Business Intelligence Server, they all use the same sample dataset.

These databases can be stored in other DBMSs as well. Scripts for creating and loading these databases in other popular DBMSs for example, MySQL or Oracle can be found in Prashant Raju's blog, at http://www.prashantraju.com/projects/pentaho.

Beside the scripts you will find instructions for creating and loading the databases.

Note

Prashant Raju, an expert Pentaho developer, provides several excellent tutorials related to the Pentaho platform. If you are interested in knowing more about Pentaho, it's worth taking a look at his blog.

Pentaho Data Integration Cookbook - Second Edition - Second Edition

Pentaho Data Integration Cookbook - Second Edition - Second Edition

Overview of this book

Related Content you might be interested in

Current Title:

Pentaho Data Integration Cookbook - Second Edition - Second Edition

Introduction

Note

Sample databases

Note

Pentaho BI platform databases

Note