Book Image

Pentaho Data Integration Cookbook - Second Edition - Second Edition

Book Image

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.

Related Content you might be interested in

Current Title:

Small book image
Pentaho Data Integration Cookbook - Second Edition - Second Edition
Dec, 2013 | 462 pages
No titles found
Table of Contents (21 chapters)
Pentaho Data Integration Cookbook Second Edition
Pentaho Data Integration Cookbook Second Edition
Credits
Credits
About the Author
About the Author
About the Reviewers
About the Reviewers
www.PacktPub.com
www.PacktPub.com
Preface
Preface
Free Chapter
1
Working with Databases
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
2
Reading and Writing Files
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
3
Working with Big Data and Cloud Sources
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
4
Manipulating XML Structures
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
5
File Management
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
6
Looking for Data
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
7
Understanding and Optimizing Data Flows
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
8
Executing and Re-using Jobs and Transformations
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
9
Integrating Kettle and the Pentaho Suite
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
10
Getting the Most Out of Kettle
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
11
Utilizing Visualization Tools in Kettle
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
12
Data Analytics
Data Analytics
Introduction
Reading data from a SAS datafile
Studying data via stream statistics
Building a random data sample for Weka
Data Structures
Data Structures
Books data structure
museums data structure
outdoor data structure
Steel Wheels data structure
Lahman Baseball Database
References
References
Books
Online
Index
Index

Writing a simple file

In this recipe, you will learn the use of the Text file output step for writing text files.

Let's assume that you have a database with outdoor products and you want to export a catalog of products to a text file.

Getting ready

For this recipe, you will need a database with outdoor products with the structure explained in Appendix A, Data Structures.

How to do it...

Carry out the following steps:

  1. Create a new transformation.

  2. Drop a Table input step into the canvas. Enter the following SQL statement:

    SELECT innerj.desc_product, categories.category, innerj.price FROM products innerj
INNER JOIN categories
ON innerj.id_category = categories.id_category

  3. From the Output category, add a Text file output step.

  4. In the Filename textbox under the File tab, type or browse to the name of the destination file.

  5. In the Extension textbox, leave the default value txt.

  6. Check the Do not create file at start checkbox. This checkbox prevents the creation of the file when there is no data to write to...

Previous Section
End of Section 8
Next Section