Learning Pentaho Data Integration 8 CE（Third Edition）

Modifying the Kettle home in Unix-like systems

Modifying the Kettle home in Windows

Modifying the Kettle home directory

Deploying the project in different environments

Analyzing performance graphs

Analyzing Steps Metrics

Maximizing the performance

Designing and developing reusable jobs and transformations

Making the work portable

Styling your work

Best practices to design jobs and transformations

Defining and adopting a versioning system

Dealing with external resources

Defining a folder structure for the project

Setting up the local environment

Setting up a new project

Best Practices for Designing and Deploying a PDI Project

Summary

Automating the execution

Sending the output of executions to log files

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

Using command-line arguments

Supplying named parameters and variables

Checking the exit code

Running jobs and transformations

Using the Pan and Kitchen utilities

Launching Transformations and Jobs from the Command Line

Summary

Capturing the result filenames

Using variables and named parameters

Working with groups of data

Getting the results of the execution of the job

Configuring the executors with advanced settings

Using Job executors

Executing jobs in an iterative way

Introducing other ways to process the filelist

Copying moving and deleting files

Attaching files in an email

Using the filelist for different purposes

Maintaining a filelist

Working with filelists

Using the copied rows as parameters of a Job or Transformation

Using the copied rows to manage files in advanced ways

Accessing copied rows for different purposes

Using variables to name Job and Transformation folders

Using variables to name jobs and transformations

Using variables to create flexible processes

Using named parameters

Understanding the scope of variables

Running nested jobs

Enhancing your processes with the use of variables

Creating Advanced Jobs

Summary

Identifying use cases to implement metadata injection

Discovering metadata and injecting it

Injecting metadata

Creating a template Transformation

Explaining how metadata injection works

Introducing metadata injection

Implementing Metadata Injection

Summary

Continuing the flow after executing the inner transformation

Using variables and named parameters

Working with groups of data

Getting the results of the execution of the inner transformation

Configuring the executors with advanced settings

Using Transformation executors

Executing transformations in an iterative way

Transferring data using the copy/get rows mechanism

Making the data flow between transformations

Introducing more elaborate sub-transformations

Executing a sub-transformation

Creating and testing a sub-transformation

Creating and executing sub-transformations

Creating reusable Transformations

Using variables as fields of your stream

Defining and using named parameters

Setting variables with partial or total results of your flow

Setting a variable with a value unknown beforehand

Setting a variable with a constant value

Defining variables at runtime

Revisiting the kettle.properties file

Explaining predefined variables

Introducing all kinds of Kettle variables

Defining and using Kettle variables

Creating Portable and Reusable Transformations

Summary

Loading a fact table by overwriting snapshot data

Loading a fact table by inserting snapshot data

Loading a snapshot fact table

Loading a cumulative fact table

Obtaining the surrogate key for a time dimension

Obtaining the surrogate key for the junk dimension

Obtaining the surrogate key for Type II SCD

Obtaining the surrogate key for Type I SCD

Translating the business keys into surrogate keys

Deciding the level of granularity

Learning the basics about fact tables

Loading fact tables

Explaining degenerate dimensions

Loading junk dimensions

Loading a mini dimension

Loading other kinds of dimensions

Explaining and loading Type III SCD and Hybrid SCD

Loading a Type II SDC and verifying how history is kept

Loading a Type II SDC for the first time

Loading Type II SCDs with a dimension lookup/update step

Introducing and loading Type II slowly changing dimension

Loading a Type I SCD with a combination lookup/update step

Introducing and loading Type I slowly changing dimensions

Loading a time dimension

Understanding dimensions technical details

Learning the basics of dimensions

Loading dimensions with data

Introducing dimensional modeling

Creating the database and configuring the environment

Exploring the Jigsaw database model

Preparing the environment

Loading Data Marts with PDI

Summary

Looking for data using a Dynamic SQL row step

Looking for data using a Database join step

Performing complex database lookups

Making a performance difference when looking up data in a database

Doing simple lookups with the Database Value Lookup step

Looking up data in different ways

Verifying a connection running DDL scripts and doing other useful tasks

Performing CRUD operations with more flexibility

Deleting records of a database table with the Delete step

Inserting or updating data with the Insert / Update step

Inserting new data into a database table

Inserting updating and deleting data

Using Kettle variables in your queries

Adding parameters to your queries

Using the Table input step to run flexible queries

Getting data from the database with the Table input step

Previewing and getting data from a database

Exploring a database with the Database Explorer

Connecting with Relational Database Management Systems

Connecting to a database and exploring its content

Performing Basic Operations with Databases

Summary

Picking rows backward and forward with the Analytic Query step

Going forward and backward across rows

Modifying the dataset with a Row Normaliser step

Normalizing data

Aggregating data with a Row Denormaliser step

Converting row data to column data using the Row denormaliser step

Converting rows to columns

Summarizing the PDI steps that operate on sets of rows

Aggregating data

Working on groups of rows

Sorting a dataset with the sort rows step

Sorting data

Transforming the Dataset

Summary

Avoiding coding using purpose-built steps

Parsing JSON structures

Redirecting data to different target steps

Reading data from additional steps

Receiving parameters

Getting the most out of the Java Class step

Testing the Java Class using the Test class button

Controlling the flow with the putRow() function

Modifying fields

Adding fields

Data types equivalence

Learning to insert java code in a Java Class step

Inserting Java code using the Java Class step

Using the Java language in PDI

Doing simple tasks with the Java Class step

Parsing unstructured files with JavaScript

Testing the script using the Test script button

Controlling the flow using predefined constants

Organizing your code

Modifying fields

Adding fields

Inserting JavaScript code using the JavaScript step

Using the JavaScript language in PDI

Doing simple tasks with the JavaScript step

Manipulating Data by Coding

Summary

Fixing data that doesn't match the rules

Treating invalid data by splitting and merging streams

Introducing common validations and their implementation with PDI

Validating and reporting errors to the log

Validating data with PDI

Validating data

Deduplicating non-exact matches

Cleansing by doing a fuzzy search

Dealing with non-exact matches

Introducing PDI steps useful for cleansing data

Improving the quality of data

Standardizing information

Cleansing data by example

Cleansing data

Cleansing Validating and Fixing Data

Summary

Looking up data with a Stream lookup step

Looking up data

Customizing the way of merging streams

Merging two or more streams

Merging streams in several ways

Exploring PDI steps for splitting a stream based on conditions

Splitting a stream based on a simple condition

Splitting the stream based on conditions

Introducing partitioning and clustering

Distributing rows

Copying rows

Splitting streams unconditionally

Filtering data based on row numbers

Filtering rows by using the Java Filter step

Filtering unwanted rows with a Filter rows step

Reading a file and getting the list of words found in it

Filtering rows upon conditions

Filtering data

Controlling the Flow of Data

Summary

Parsing a JSON structure stored in a field

Reading a JSON file with the JSON input step

Parsing JSON structures with PDI

Getting familiar with the JSONPath notation

Introducing JSON terminology

Parsing JSON structures

PDI Transformation and Job files

Parsing an XML structure stored in a field

Reading an XML file with the Get data from XML step

Parsing XML structures with PDI

Getting familiar with the XPath notation

Introducing XML terminology

Working with XML

Working with complex structures

Modifying the metadata of streams

Getting the month names with a User Defined Java Class step

Performing other useful operations on dates

Using the Get System Info step

Getting information relative to the current date

Subtracting dates with the Calculator step

Performing simple operations on dates

Operating with dates

Doing some math with Numeric fields

Searching and replacing using regular expressions

Extracting parts of strings using regular expressions

Working with strings

Manipulating simple fields

Manipulating PDI Data and Metadata

Summary

Sending data to HDFS

Getting data from HDFS

Writing files to an AWS S3 instance

Reading files from an AWS S3 instance

Working with Big Data and cloud sources

Creating JSON files

Creating XML files

Creating spreadsheets

Creating text files

Creating the most common kinds of files

Formatting Numeric fields

Formatting Date fields

Describing the content of the output file

Creating several files whose name depend on the content of the file

Creating a file whose name is known only at runtime

Providing the name and location of an output file

Learning to create all kind of files and write data into them

Creating a simple file

Outputting data to files

Reading JSON files

Reading XML files

Reading spreadsheets

Reading text files

Reading the most common kinds of sources

Reading only a subset of the file

Reading Numeric fields

Reading Date fields

Describing the incoming fields

Reading a file whose name is known at runtime

Reading files that are compressed or located on a remote server

Reading several files at the same time

Specifying the name and location of the file

Learning to read all kind of files

Troubleshooting reading files

Reading a simple file

Reading data from files

Reading and Writing Files

Summary

How and when you can use variables

Understanding the kettle.properties file

Knowing the basics about Kettle variables

Customizing the file management

Summarizing the Job entries that deal with files

Working with regular expressions

Selecting files and folders

Creating a Job that moves some files

Managing files

Changing the execution to be synchronous

Forcing a status with an abort Job or success entry

Changing the flow of execution based on conditions

Understanding and changing the flow of execution

Using the Transformation Job Entry

Running transformations from a Job

Enriching your work by sending an email

The Job metrics tab

The Logging tab

Looking at the results in the Execution results window

Getting familiar with the job design process

Designing jobs

Revisiting the Spoon interface and the editing features

Designing and running jobs

Creating a Simple Job

Learning the basics about jobs

Introducing jobs

Creating Basic Task Flows

Summary

Customizing the error handling

Implementing the error handling functionality

Handling errors

Explaining the PDI data types

Adding or modifying fields by using different PDI steps

Understanding the PDI rowset

Understanding PDI data and metadata

Running transformations in an interactive fashion

The Step Metrics tab

The Logging tab

Looking at the results in the execution results pane

Previewing and fixing errors as they appear

Putting the editing features in practice

Designing transformations

Working with grids

Adding steps and creating hops

Using the mouseover assistance toolbar

Getting familiar with editing features

Designing and previewing transformations

Getting Started with Transformations

Summary

Installing useful related software

Previewing and running a Transformation

Designing a Transformation

Creating a Hello World! Transformation

The basics about transformations

Introducing transformations

Extending the PDI functionality through the Marketplace

Exploring the Spoon interface

Starting and customizing Spoon

Launching the PDI Graphical Designer - Spoon

Installing PDI

Integrating PDI along with other Pentaho tools

Exporting data

Migrating information

Data cleansing

Integrating data

Loading data warehouses or data marts

Using PDI in real-world scenarios

Introducing Pentaho Data Integration

Pentaho Data Integration and Pentaho BI Suite

Getting Started with Pentaho Data Integration

Questions

Piracy

Errata

Downloading the color images of this book

Downloading the example code

Customer support

Reader feedback

Conventions

Who this book is for

What you need for this book

What this book covers

Preface

Customer Feedback

Why subscribe?

www.PacktPub.com

About the Reviewers

About the Author

Credits

版权信息

封面

封面

版权信息

Credits

About the Author

About the Reviewers

www.PacktPub.com

Why subscribe?

Customer Feedback

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

Downloading the color images of this book

Errata

Piracy

Questions

Getting Started with Pentaho Data Integration

Pentaho Data Integration and Pentaho BI Suite

Introducing Pentaho Data Integration

Using PDI in real-world scenarios

Loading data warehouses or data marts

Integrating data

Data cleansing

Migrating information

Exporting data

Integrating PDI along with other Pentaho tools

Installing PDI

Launching the PDI Graphical Designer - Spoon

Starting and customizing Spoon

Exploring the Spoon interface

Extending the PDI functionality through the Marketplace

Introducing transformations

The basics about transformations

Creating a Hello World! Transformation

Designing a Transformation

Previewing and running a Transformation

Installing useful related software

Summary

Getting Started with Transformations

Designing and previewing transformations

Getting familiar with editing features

Using the mouseover assistance toolbar

Adding steps and creating hops

Working with grids

Designing transformations

Putting the editing features in practice

Previewing and fixing errors as they appear

Looking at the results in the execution results pane

The Logging tab

The Step Metrics tab

Running transformations in an interactive fashion

Understanding PDI data and metadata

Understanding the PDI rowset

Adding or modifying fields by using different PDI steps

Explaining the PDI data types

Handling errors

Implementing the error handling functionality

Customizing the error handling

Summary

Creating Basic Task Flows

Introducing jobs

Learning the basics about jobs

Creating a Simple Job

Designing and running jobs

Revisiting the Spoon interface and the editing features

Designing jobs

Getting familiar with the job design process

Looking at the results in the Execution results window

The Logging tab

The Job metrics tab

Enriching your work by sending an email

Running transformations from a Job

Using the Transformation Job Entry

Understanding and changing the flow of execution

Changing the flow of execution based on conditions

Forcing a status with an abort Job or success entry

Changing the execution to be synchronous

Managing files

Creating a Job that moves some files

Selecting files and folders

Working with regular expressions

Summarizing the Job entries that deal with files

Customizing the file management

Knowing the basics about Kettle variables

Understanding the kettle.properties file

How and when you can use variables

Summary

Reading and Writing Files

Reading data from files

Reading a simple file

Troubleshooting reading files

Learning to read all kind of files

Specifying the name and location of the file

Reading several files at the same time

Reading files that are compressed or located on a remote server

Reading a file whose name is known at runtime

Describing the incoming fields

Reading Date fields

Reading Numeric fields

Reading only a subset of the file

Reading the most common kinds of sources

Reading text files

Reading spreadsheets

Reading XML files

Reading JSON files

Outputting data to files

Creating a simple file

Learning to create all kind of files and write data into them

Providing the name and location of an output file

Creating a file whose name is known only at runtime

Creating several files whose name depend on the content of the file

Describing the content of the output file

Formatting Date fields

Formatting Numeric fields

Creating the most common kinds of files

Creating text files

Creating spreadsheets

Creating XML files

Creating JSON files

Working with Big Data and cloud sources

Reading files from an AWS S3 instance

Writing files to an AWS S3 instance

Getting data from HDFS

Sending data to HDFS

Summary

Manipulating PDI Data and Metadata

Manipulating simple fields

Working with strings

Extracting parts of strings using regular expressions

Searching and replacing using regular expressions

Doing some math with Numeric fields

Operating with dates

Performing simple operations on dates

Subtracting dates with the Calculator step

Getting information relative to the current date

Using the Get System Info step

Performing other useful operations on dates

Getting the month names with a User Defined Java Class step

Modifying the metadata of streams

Working with complex structures

Working with XML

Introducing XML terminology

Getting familiar with the XPath notation

Parsing XML structures with PDI

Reading an XML file with the Get data from XML step

Parsing an XML structure stored in a field

PDI Transformation and Job files

Parsing JSON structures

Introducing JSON terminology

Getting familiar with the JSONPath notation

Parsing JSON structures with PDI

Reading a JSON file with the JSON input step

Parsing a JSON structure stored in a field

Summary

Controlling the Flow of Data

Filtering data

Filtering rows upon conditions

Reading a file and getting the list of words found in it

Filtering unwanted rows with a Filter rows step

Filtering rows by using the Java Filter step

Filtering data based on row numbers

Splitting streams unconditionally

Copying rows

Distributing rows

Introducing partitioning and clustering

Splitting the stream based on conditions

Splitting a stream based on a simple condition

Exploring PDI steps for splitting a stream based on conditions

Merging streams in several ways

Merging two or more streams

Customizing the way of merging streams

Looking up data

Looking up data with a Stream lookup step

Summary

Cleansing Validating and Fixing Data

Cleansing data

Cleansing data by example

Standardizing information

Improving the quality of data

Introducing PDI steps useful for cleansing data

Dealing with non-exact matches

Cleansing by doing a fuzzy search

Deduplicating non-exact matches

Validating data

Validating data with PDI

Validating and reporting errors to the log

Introducing common validations and their implementation with PDI

Treating invalid data by splitting and merging streams

Fixing data that doesn't match the rules

Summary

Manipulating Data by Coding

Doing simple tasks with the JavaScript step

Using the JavaScript language in PDI

Inserting JavaScript code using the JavaScript step

Adding fields

Modifying fields

Organizing your code

Controlling the flow using predefined constants

Testing the script using the Test script button

Parsing unstructured files with JavaScript

Doing simple tasks with the Java Class step

Using the Java language in PDI

Inserting Java code using the Java Class step

Learning to insert java code in a Java Class step

Data types equivalence

Adding fields

Modifying fields

Controlling the flow with the putRow() function

Testing the Java Class using the Test class button

Getting the most out of the Java Class step

Receiving parameters

Reading data from additional steps

Redirecting data to different target steps

Parsing JSON structures

Avoiding coding using purpose-built steps

Summary

Transforming the Dataset

Sorting data

Sorting a dataset with the sort rows step

Working on groups of rows

Aggregating data

Summarizing the PDI steps that operate on sets of rows

Converting rows to columns

Converting row data to column data using the Row denormaliser step

Aggregating data with a Row Denormaliser step

Normalizing data

Modifying the dataset with a Row Normaliser step

Going forward and backward across rows

Picking rows backward and forward with the Analytic Query step

Summary

Performing Basic Operations with Databases

Connecting to a database and exploring its content

Connecting with Relational Database Management Systems

Exploring a database with the Database Explorer

Previewing and getting data from a database

Getting data from the database with the Table input step

Using the Table input step to run flexible queries

Adding parameters to your queries

Using Kettle variables in your queries

Inserting updating and deleting data

Inserting new data into a database table

Inserting or updating data with the Insert / Update step

Deleting records of a database table with the Delete step

Performing CRUD operations with more flexibility

Verifying a connection running DDL scripts and doing other useful tasks

Looking up data in different ways

Doing simple lookups with the Database Value Lookup step

Making a performance difference when looking up data in a database

Performing complex database lookups

Looking for data using a Database join step

Looking for data using a Dynamic SQL row step

Summary

Loading Data Marts with PDI

Preparing the environment

Exploring the Jigsaw database model

Creating the database and configuring the environment

Introducing dimensional modeling

Loading dimensions with data

Learning the basics of dimensions

Understanding dimensions technical details

Loading a time dimension

Introducing and loading Type I slowly changing dimensions

Loading a Type I SCD with a combination lookup/update step

Introducing and loading Type II slowly changing dimension

Loading Type II SCDs with a dimension lookup/update step

Loading a Type II SDC for the first time

Loading a Type II SDC and verifying how history is kept

Explaining and loading Type III SCD and Hybrid SCD

Loading other kinds of dimensions

Loading a mini dimension

Loading junk dimensions

Explaining degenerate dimensions

Loading fact tables

Learning the basics about fact tables

Deciding the level of granularity

Translating the business keys into surrogate keys

Obtaining the surrogate key for Type I SCD

Obtaining the surrogate key for Type II SCD

Obtaining the surrogate key for the junk dimension

Obtaining the surrogate key for a time dimension

Loading a cumulative fact table

Loading a snapshot fact table

Loading a fact table by inserting snapshot data

Loading a fact table by overwriting snapshot data

Summary

Creating Portable and Reusable Transformations

Defining and using Kettle variables

Introducing all kinds of Kettle variables

Explaining predefined variables

Revisiting the kettle.properties file

Defining variables at runtime

Setting a variable with a constant value

Setting a variable with a value unknown beforehand

Setting variables with partial or total results of your flow

Defining and using named parameters

Using variables as fields of your stream

Creating reusable Transformations

Creating and executing sub-transformations

Creating and testing a sub-transformation

Executing a sub-transformation

Introducing more elaborate sub-transformations

Making the data flow between transformations

Transferring data using the copy/get rows mechanism

Executing transformations in an iterative way

Using Transformation executors

Configuring the executors with advanced settings

Getting the results of the execution of the inner transformation

Working with groups of data

Using variables and named parameters

Continuing the flow after executing the inner transformation

Summary

Implementing Metadata Injection

Introducing metadata injection

Explaining how metadata injection works

Creating a template Transformation

Injecting metadata

Discovering metadata and injecting it

Identifying use cases to implement metadata injection

Summary

Creating Advanced Jobs

Enhancing your processes with the use of variables

Running nested jobs

Understanding the scope of variables

Using named parameters

Using variables to create flexible processes

Using variables to name jobs and transformations

Using variables to name Job and Transformation folders

Accessing copied rows for different purposes

Using the copied rows to manage files in advanced ways

Using the copied rows as parameters of a Job or Transformation

Working with filelists

Maintaining a filelist

Using the filelist for different purposes

Attaching files in an email

Copying moving and deleting files

Introducing other ways to process the filelist

Executing jobs in an iterative way

Using Job executors

Configuring the executors with advanced settings

Getting the results of the execution of the job

Working with groups of data

Using variables and named parameters

Capturing the result filenames

Summary

Launching Transformations and Jobs from the Command Line

Using the Pan and Kitchen utilities

Running jobs and transformations

Checking the exit code

Supplying named parameters and variables

Using command-line arguments

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

Sending the output of executions to log files

Automating the execution

Summary

Best Practices for Designing and Deploying a PDI Project

Setting up a new project

Setting up the local environment

Defining a folder structure for the project

Dealing with external resources

Defining and adopting a versioning system

Best practices to design jobs and transformations

Styling your work

Making the work portable

Designing and developing reusable jobs and transformations

Maximizing the performance

Analyzing Steps Metrics

Analyzing performance graphs

Deploying the project in different environments

Modifying the Kettle home directory

Modifying the Kettle home in Windows

Modifying the Kettle home in Unix-like systems