Learning Pentaho Data Integration 8 CE

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