Pentaho Data Integration Cookbook Second Edition -- Table of Contents -- Pentaho Data Integration Cookbook Second Edition -- Credits -- About the Author -- About the Reviewers -- www.PacktPub.com -- Support files, eBooks, discount offers and more -- Why Subscribe? -- Free Access for Packt account holders -- Preface -- What this book covers -- What you need for this book -- Who this book is for -- Conventions -- Reader feedback -- Customer support -- Downloading the example code -- Errata -- Piracy -- Questions -- 1. Working with Databases -- Introduction -- Sample databases -- Pentaho BI platform databases -- Connecting to a database -- Getting ready -- How to do it... -- How it works... -- There's more... -- Avoiding creating the same database connection over and over again -- Avoiding modifying jobs and transformations every time a connection changes -- Specifying advanced connection properties -- Connecting to a database not supported by Kettle -- Checking the database connection at runtime -- Getting data from a database -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Getting data from a database by providing parameters -- Getting ready -- How to do it... -- How it works... -- There's more... -- Parameters coming in more than one row -- Executing the SELECT statement several times, each for a different set of parameters -- See also -- Getting data from a database by running a query built at runtime -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Inserting or updating rows in a table -- Getting ready -- How to do it... -- How it works... -- There's more... -- Alternative solution if you just want to insert records -- Alternative solution if you just want to update rows -- Alternative way for inserting and updating -- See also.

Inserting new rows where a simple primary key has to be generated -- Getting ready -- How to do it... -- How it works... -- There's more... -- Using the Combination lookup/update for looking up -- See also -- Inserting new rows where the primary key has to be generated based on stored values -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Deleting data from a table -- Getting ready -- How to do it... -- How it works... -- See also -- Creating or altering a database table from PDI (design time) -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Creating or altering a database table from PDI (runtime) -- How to do it... -- How it works... -- There's more... -- See also -- Inserting, deleting, or updating a table depending on a field -- Getting ready -- How to do it... -- How it works... -- There's more... -- Insert, update, and delete all-in-one -- Synchronizing after merge -- See also -- Changing the database connection at runtime -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Loading a parent-child table -- Getting ready -- How to do it... -- How it works... -- See also -- Building SQL queries via database metadata -- Getting ready -- How to do It... -- How it works... -- See also -- Performing repetitive database design tasks from PDI -- Getting ready -- How to do It... -- How it works... -- See also -- 2. Reading and Writing Files -- Introduction -- Reading a simple file -- Getting ready -- How to do it... -- How it works... -- There's more... -- Alternative notation for a separator -- About file format and encoding -- About data types and formats -- Altering the names, order, or metadata of the fields coming from the file -- Reading files with fixed width fields -- Reading several files at the same time -- Getting ready.

How to do it... -- How it works... -- There's more... -- Reading semi-structured files -- Getting ready -- How to do it... -- How it works... -- There's more... -- Master/detail files -- Logfiles -- See also -- Reading files having one field per row -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Reading files with some fields occupying two or more rows -- Getting ready -- How to do it... -- How it works... -- See also -- Writing a simple file -- Getting ready -- How to do it... -- How it works... -- There's more... -- Changing headers -- Giving the output fields a format -- Writing a semi-structured file -- Getting ready -- How to do it... -- How it works... -- There's more... -- Providing the name of a file (for reading or writing) dynamically -- Getting ready -- How to do it... -- How it works... -- There's more... -- Get System Info -- Generating several files simultaneously with the same structure, but different names -- Using the name of a file (or part of it) as a field -- Getting ready -- How to do it... -- How it works... -- Reading an Excel file -- Getting ready -- How to do it... -- How it works... -- See also -- Getting the value of specific cells in an Excel file -- Getting ready -- How to do it... -- How it works... -- There's more... -- Looking for a given cell -- Writing an Excel file with several sheets -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Writing an Excel file with a dynamic number of sheets -- Getting ready -- How to do it... -- How it works... -- See also -- Reading data from an AWS S3 Instance -- Getting ready -- How to do it... -- How it works... -- See also -- 3. Working with Big Data and Cloud Sources -- Introduction -- Loading data into Salesforce.com -- Getting ready -- How to do it... -- How it works... -- See also.

Getting data from Salesforce.com -- Getting ready -- How to do it... -- How it works... -- See also -- Loading data into Hadoop -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Getting data from Hadoop -- Getting ready -- How to do it... -- How it works... -- See also -- Loading data into HBase -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Getting data from HBase -- Getting ready -- How to do it... -- How it works... -- See also -- Loading data into MongoDB -- Getting ready -- How to do it... -- How it works... -- See also -- Getting data from MongoDB -- Getting ready -- How to do it... -- How it works... -- See also -- 4. Manipulating XML Structures -- Introduction -- Reading simple XML files -- Getting ready -- How to do it... -- How it works... -- There's more... -- XML data in a field -- XML file name in a field -- See also -- Specifying fields by using the Path notation -- Getting ready -- How to do it... -- How it works... -- There's more... -- Getting data from a different path -- Getting data selectively -- Getting more than one node when the nodes share their Path notation -- Saving time when specifying Path -- Validating well-formed XML files -- Getting ready -- How to do it... -- How it works... -- See also -- Validating an XML file against DTD definitions -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Validating an XML file against an XSD schema -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Generating a simple XML document -- Getting ready -- How to do it... -- How it works... -- There's more... -- Generating fields with XML structures -- See also -- Generating complex XML structures -- Getting ready -- How to do it... -- How it works... -- See also.

Generating an HTML page using XML and XSL transformations -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Reading an RSS Feed -- Getting ready -- How to do it... -- How it works... -- See also -- Generating an RSS Feed -- Getting ready -- How to do it... -- How it works -- There's more... -- See also -- 5. File Management -- Introduction -- Copying or moving one or more files -- Getting ready -- How to do it... -- How it works... -- There's more... -- Moving files -- Detecting the existence of the files before copying them -- Creating folders -- See also -- Deleting one or more files -- Getting ready -- How to do it... -- How it works... -- There's more... -- Figuring out which files have been deleted -- See also -- Getting files from a remote server -- How to do it... -- How it works... -- There's more... -- Specifying files to transfer -- Some considerations about connecting to an FTP server -- Access via SFTP -- Access via FTPS -- Getting information about the files being transferred -- See also -- Putting files on a remote server -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Copying or moving a custom list of files -- Getting ready -- How to do it... -- How it works... -- See also -- Deleting a custom list of files -- Getting ready -- How to do it... -- How it works... -- See also -- Comparing files and folders -- Getting ready -- How to do it... -- How it works... -- There's more... -- Comparing folders -- Working with ZIP files -- Getting ready -- How to do it... -- How it works... -- There's more... -- Avoiding zipping files -- Avoiding unzipping files -- See also -- Encrypting and decrypting files -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- 6. Looking for Data -- Introduction.

Looking for values in a database table.