Learning Pentaho Data Integration 8 CE

Description

Preface......Page 42 What this book covers......Page 44 What you need for this book......Page 46 Who this book is for......Page 48 Conventions......Page 50 Reader feedback......Page 51 Customer support......Page 52 Downloading the example code......Page 53 Downloading the color images of this book......Page 55 Errata......Page 56 Piracy......Page 58 Questions......Page 59 Getting Started with Pentaho Data Integration......Page 60 Pentaho Data Integration and Pentaho BI Suite......Page 62 Introducing Pentaho Data Integration......Page 65 Using PDI in real-world scenarios......Page 68 Loading data warehouses or data marts......Page 69 Integrating data......Page 71 Data cleansing......Page 72 Migrating information......Page 73 Exporting data......Page 74 Integrating PDI along with other Pentaho tools......Page 75 Installing PDI......Page 76 Launching the PDI Graphical Designer - Spoon......Page 79 Starting and customizing Spoon......Page 80 Exploring the Spoon interface......Page 84 Extending the PDI functionality through the Marketplace......Page 86 Introducing transformations......Page 89 The basics about transformations......Page 90 Creating a Hello World! Transformation......Page 91 Designing a Transformation......Page 92 Previewing and running a Transformation......Page 96 Installing useful related software......Page 99 Summary......Page 100 Getting Started with Transformations......Page 101 Designing and previewing transformations......Page 103 Getting familiar with editing features......Page 104 Using the mouseover assistance toolbar......Page 106 Adding steps and creating hops......Page 108 Working with grids......Page 111 Designing transformations......Page 113 Putting the editing features in practice......Page 114 Previewing and fixing errors as they appear......Page 120 Looking at the results in the execution results pane......Page 124 The Logging tab......Page 125 The Step Metrics tab......Page 127 Running transformations in an interactive fashion......Page 130 Understanding PDI data and metadata......Page 135 Understanding the PDI rowset......Page 137 Adding or modifying fields by using different PDI steps......Page 140 Explaining the PDI data types......Page 143 Handling errors......Page 145 Implementing the error handling functionality......Page 147 Customizing the error handling......Page 151 Summary......Page 155 Creating Basic Task Flows......Page 157 Introducing jobs......Page 159 Learning the basics about jobs......Page 160 Creating a Simple Job......Page 162 Designing and running jobs......Page 166 Revisiting the Spoon interface and the editing features......Page 167 Designing jobs......Page 170 Getting familiar with the job design process......Page 171 Looking at the results in the Execution results window......Page 175 The Logging tab......Page 176 The Job metrics tab......Page 178 Enriching your work by sending an email......Page 179 Running transformations from a Job......Page 184 Using the Transformation Job Entry......Page 186 Understanding and changing the flow of execution......Page 190 Changing the flow of execution based on conditions......Page 192 Forcing a status with an abort Job or success entry......Page 194 Changing the execution to be synchronous......Page 196 Managing files......Page 197 Creating a Job that moves some files......Page 199 Selecting files and folders......Page 204 Working with regular expressions......Page 206 Summarizing the Job entries that deal with files......Page 209 Customizing the file management......Page 211 Knowing the basics about Kettle variables......Page 215 Understanding the kettle.properties file......Page 217 How and when you can use variables......Page 219 Summary......Page 221 Reading and Writing Files......Page 222 Reading data from files......Page 224 Reading a simple file......Page 225 Troubleshooting reading files......Page 230 Learning to read all kind of files......Page 232 Specifying the name and location of the file......Page 233 Reading several files at the same time......Page 234 Reading files that are compressed or located on a remote server......Page 237 Reading a file whose name is known at runtime......Page 240 Describing the incoming fields......Page 243 Reading Date fields......Page 244 Reading Numeric fields......Page 246 Reading only a subset of the file......Page 248 Reading the most common kinds of sources......Page 250 Reading text files......Page 251 Reading spreadsheets......Page 253 Reading XML files......Page 255 Reading JSON files......Page 257 Outputting data to files......Page 258 Creating a simple file......Page 259 Learning to create all kind of files and write data into them......Page 262 Providing the name and location of an output file......Page 263 Creating a file whose name is known only at runtime......Page 264 Creating several files whose name depend on the content of the file......Page 267 Describing the content of the output file......Page 270 Formatting Date fields......Page 271 Formatting Numeric fields......Page 272 Creating the most common kinds of files......Page 273 Creating text files......Page 274 Creating spreadsheets......Page 275 Creating XML files......Page 277 Creating JSON files......Page 279 Working with Big Data and cloud sources......Page 281 Reading files from an AWS S3 instance......Page 282 Writing files to an AWS S3 instance......Page 284 Getting data from HDFS......Page 286 Sending data to HDFS......Page 289 Summary......Page 291 Manipulating PDI Data and Metadata......Page 293 Manipulating simple fields......Page 295 Working with strings......Page 296 Extracting parts of strings using regular expressions......Page 298 Searching and replacing using regular expressions......Page 301 Doing some math with Numeric fields......Page 304 Operating with dates......Page 306 Performing simple operations on dates......Page 308 Subtracting dates with the Calculator step......Page 310 Getting information relative to the current date......Page 313 Using the Get System Info step......Page 314 Performing other useful operations on dates......Page 316 Getting the month names with a User Defined Java Class step......Page 317 Modifying the metadata of streams......Page 320 Working with complex structures......Page 322 Working with XML......Page 323 Introducing XML terminology......Page 324 Getting familiar with the XPath notation......Page 325 Parsing XML structures with PDI......Page 327 Reading an XML file with the Get data from XML step......Page 328 Parsing an XML structure stored in a field......Page 332 PDI Transformation and Job files......Page 335 Parsing JSON structures......Page 337 Introducing JSON terminology......Page 338 Getting familiar with the JSONPath notation......Page 339 Parsing JSON structures with PDI......Page 340 Reading a JSON file with the JSON input step......Page 341 Parsing a JSON structure stored in a field......Page 343 Summary......Page 345 Controlling the Flow of Data......Page 347 Filtering data......Page 349 Filtering rows upon conditions......Page 350 Reading a file and getting the list of words found in it......Page 352 Filtering unwanted rows with a Filter rows step......Page 355 Filtering rows by using the Java Filter step......Page 356 Filtering data based on row numbers......Page 358 Splitting streams unconditionally......Page 361 Copying rows......Page 364 Distributing rows......Page 367 Introducing partitioning and clustering......Page 369 Splitting the stream based on conditions......Page 371 Splitting a stream based on a simple condition......Page 372 Exploring PDI steps for splitting a stream based on conditions......Page 375 Merging streams in several ways......Page 377 Merging two or more streams......Page 378 Customizing the way of merging streams......Page 382 Looking up data......Page 384 Looking up data with a Stream lookup step......Page 385 Summary......Page 391 Cleansing, Validating, and Fixing Data......Page 393 Cleansing data......Page 395 Cleansing data by example......Page 397 Standardizing information......Page 398 Improving the quality of data......Page 401 Introducing PDI steps useful for cleansing data......Page 404 Dealing with non-exact matches......Page 406 Cleansing by doing a fuzzy search......Page 408 Deduplicating non-exact matches......Page 413 Validating data......Page 416 Validating data with PDI......Page 417 Validating and reporting errors to the log......Page 419 Introducing common validations and their implementation with PDI......Page 422 Treating invalid data by splitting and merging streams......Page 425 Fixing data that doesn't match the rules......Page 427 Summary......Page 429 Manipulating Data by Coding......Page 431 Doing simple tasks with the JavaScript step......Page 433 Using the JavaScript language in PDI......Page 434 Inserting JavaScript code using the JavaScript step......Page 436 Adding fields......Page 439 Modifying fields......Page 442 Organizing your code......Page 444 Controlling the flow using predefined constants......Page 447 Testing the script using the Test script button......Page 449 Parsing unstructured files with JavaScript......Page 451 Doing simple tasks with the Java Class step......Page 454 Using the Java language in PDI......Page 455 Inserting Java code using the Java Class step......Page 457 Learning to insert java code in a Java Class step......Page 459 Data types equivalence......Page 461 Adding fields......Page 463 Modifying fields......Page 467 Controlling the flow with the putRow() function......Page 468 Testing the Java Class using the Test class button......Page 470 Getting the most out of the Java Class step......Page 471 Receiving parameters......Page 472 Reading data from additional steps......Page 473 Redirecting data to different target steps......Page 474 Parsing JSON structures......Page 476 Avoiding coding using purpose-built steps......Page 478 Summary......Page 482 Transforming the Dataset......Page 483 Sorting data......Page 485 Sorting a dataset with the sort rows step......Page 486 Working on groups of rows......Page 490 Aggregating data......Page 491 Summarizing the PDI steps that operate on sets of rows......Page 498 Converting rows to columns......Page 501 Converting row data to column data using the Row denormaliser step......Page 502 Aggregating data with a Row Denormaliser step......Page 508 Normalizing data......Page 511 Modifying the dataset with a Row Normaliser step......Page 513 Going forward and backward across rows......Page 517 Picking rows backward and forward with the Analytic Query step......Page 519 Summary......Page 525 Performing Basic Operations with Databases......Page 527 Connecting to a database and exploring its content......Page 529 Connecting with Relational Database Management Systems......Page 530 Exploring a database with the Database Explorer......Page 535 Previewing and getting data from a database......Page 539 Getting data from the database with the Table input step......Page 540 Using the Table input step to run flexible queries......Page 543 Adding parameters to your queries......Page 544 Using Kettle variables in your queries......Page 547 Inserting, updating, and deleting data......Page 550 Inserting new data into a database table......Page 551 Inserting or updating data with the Insert / Update step......Page 553 Deleting records of a database table with the Delete step......Page 555 Performing CRUD operations with more flexibility......Page 556 Verifying a connection, running DDL scripts, and doing other useful tasks......Page 557 Looking up data in different ways......Page 560 Doing simple lookups with the Database Value Lookup step......Page 561 Making a performance difference when looking up data in a database......Page 564 Performing complex database lookups......Page 566 Looking for data using a Database join step......Page 568 Looking for data using a Dynamic SQL row step......Page 571 Summary......Page 574 Loading Data Marts with PDI......Page 576 Preparing the environment......Page 578 Exploring the Jigsaw database model......Page 579 Creating the database and configuring the environment......Page 582 Introducing dimensional modeling......Page 584 Loading dimensions with data......Page 586 Learning the basics of dimensions......Page 588 Understanding dimensions technical details......Page 590 Loading a time dimension......Page 593 Introducing and loading Type I slowly changing dimensions......Page 596 Loading a Type I SCD with a combination lookup/update step......Page 598 Introducing and loading Type II slowly changing dimension......Page 602 Loading Type II SCDs with a dimension lookup/update step......Page 604 Loading a Type II SDC for the first time......Page 606 Loading a Type II SDC and verifying how history is kept......Page 611 Explaining and loading Type III SCD and Hybrid SCD......Page 613 Loading other kinds of dimensions......Page 616 Loading a mini dimension......Page 617 Loading junk dimensions......Page 619 Explaining degenerate dimensions......Page 620 Loading fact tables......Page 621 Learning the basics about fact tables......Page 622 Deciding the level of granularity......Page 624 Translating the business keys into surrogate keys......Page 626 Obtaining the surrogate key for Type I SCD......Page 628 Obtaining the surrogate key for Type II SCD......Page 629 Obtaining the surrogate key for the junk dimension......Page 631 Obtaining the surrogate key for a time dimension......Page 633 Loading  a cumulative fact table......Page 635 Loading  a snapshot fact table......Page 637 Loading a fact table by inserting snapshot data......Page 639 Loading a fact table by overwriting snapshot data......Page 641 Summary......Page 643 Creating Portable and Reusable Transformations......Page 645 Defining and using Kettle variables......Page 647 Introducing all kinds of Kettle variables......Page 648 Explaining predefined variables......Page 649 Revisiting the kettle.properties file......Page 651 Defining variables at runtime......Page 653 Setting a variable with a constant value......Page 654 Setting a variable with a value unknown beforehand......Page 657 Setting variables with partial or total results of your flow......Page 660 Defining and using named parameters......Page 662 Using variables as fields of your stream......Page 665 Creating reusable Transformations......Page 669 Creating and executing sub-transformations......Page 670 Creating and testing a sub-transformation......Page 672 Executing a sub-transformation......Page 675 Introducing more elaborate sub-transformations......Page 678 Making the data flow between transformations......Page 681 Transferring data using the copy/get rows mechanism......Page 682 Executing transformations in an iterative way......Page 686 Using Transformation executors......Page 687 Configuring the executors with advanced settings......Page 691 Getting the results of the execution of the inner transformation......Page 692 Working with groups of data......Page 695 Using variables and named parameters......Page 696 Continuing the flow after executing the inner transformation......Page 698 Summary......Page 701 Implementing Metadata Injection......Page 702 Introducing metadata injection......Page 704 Explaining how metadata injection works......Page 706 Creating a template Transformation......Page 708 Injecting metadata......Page 710 Discovering metadata and injecting it......Page 715 Identifying use cases to implement metadata injection......Page 720 Summary......Page 722 Creating Advanced Jobs......Page 723 Enhancing your processes with the use of variables......Page 725 Running nested jobs......Page 726 Understanding the scope of variables......Page 727 Using named parameters......Page 729 Using variables to create flexible processes......Page 732 Using variables to name jobs and transformations......Page 733 Using variables to name Job and Transformation folders......Page 736 Accessing copied rows for different purposes......Page 738 Using the copied rows to manage files in advanced ways......Page 739 Using the copied rows as parameters of a Job or Transformation......Page 743 Working with filelists......Page 747 Maintaining a filelist......Page 748 Using the filelist for different purposes......Page 750 Attaching files in an email......Page 751 Copying, moving, and deleting files......Page 755 Introducing other ways to process the filelist......Page 757 Executing jobs in an iterative way......Page 759 Using Job executors......Page 760 Configuring the executors with advanced settings......Page 764 Getting the results of the execution of the job......Page 765 Working with groups of data......Page 768 Using variables and named parameters......Page 770 Capturing the result filenames......Page 771 Summary......Page 774 Launching Transformations and Jobs from the Command Line......Page 775 Using the Pan and Kitchen utilities......Page 777 Running jobs and transformations......Page 778 Checking the exit code......Page 779 Supplying named parameters and variables......Page 781 Using command-line arguments......Page 783 Deciding between the use of a command-line argument and named parameters......Page 787 Sending the output of executions to log files......Page 789 Automating the execution......Page 792 Summary......Page 796 Best Practices for Designing and Deploying a PDI Project......Page 798 Setting up a new project......Page 800 Setting up the local environment......Page 801 Defining a folder structure for the project......Page 803 Dealing with external resources......Page 804 Defining and adopting a versioning system......Page 805 Best practices to design jobs and transformations......Page 807 Styling your work......Page 808 Making the work portable......Page 811 Designing and developing reusable jobs and transformations......Page 813 Maximizing the performance......Page 815 Analyzing Steps Metrics......Page 816 Analyzing performance graphs......Page 818 Deploying the project in different environments......Page 820 Modifying the Kettle home directory......Page 822 Modifying the Kettle home in Windows......Page 824 Modifying the Kettle home in Unix-like systems......Page 825 Summary......Page 827