Explains how to get Kettle solutions up and running, then follows the 34 ETL subsystems model, as created by the Kimball Group, to explore the entire ETL lifecycle, including all aspects of data wareho
Table of Contents
Introduction xxxi
Part I Getting Started 1
Chapter 1 ETL Primer 3
OLTP versus Data Warehousing 3
What Is ETL? 5
The Evolution of ETL Solutions 5
ETL Building Blocks 7
ETL, ELT, and EII 8
ELT 9
EII: Virtual Data Integration 10
Data Integration Challenges 11
Methodology: Agile BI 12
ETL Design 14
Data Acquisition 14
Beware of Spreadsheets 15
Design for Failure 15
Change Data Capture 16
Data Quality 16
Data Profiling 16
Data Validation 17
ETL Tool Requirements 17
Connectivity 17
Platform Independence 18
Scalability 18
Design Flexibility 19
Reuse 19
Extensibility 19
Data Transformations 20
Testing and Debugging 21
Lineage and Impact Analysis 21
Logging and Auditing 22
Summary 22
Chapter 2 Kettle Concepts 23
Design Principles 23
The Building Blocks of Kettle Design 25
Transformations 25
Steps 26
Transformation Hops 26
Parallelism 27
Rows of Data 27
Data Conversion 29
Jobs 30
Job Entries 31
Job Hops 31
Multiple Paths and Backtracking 32
Parallel Execution 33
Job Entry Results 34
Transformation or Job Metadata 36
Database Connections 37
Special Options 38
The Power of the Relational Database 39
Connections and Transactions 39
Database Clustering 40
Tools and Utilities 41
Repositories 41
Virtual File Systems 42
Parameters and Variables 43
Defining Variables 43
Named Parameters 44
Using Variables 44
Visual Programming 45
Getting Started 46
Creating New Steps 47
Putting It All Together 49
Summary 51
Chapter 3 Installation and Configuration 53
Kettle Software Overview 53
Integrated Development Environment: Spoon 55
Command-Line Launchers: Kitchen and Pan 57
Job Server: Carte 57
Encr.bat and encr.sh 58
Installation 58
Java Environment 58
Installing Java Manually 58
Using Your Linux Package Management System 59
Installing Kettle 59
Versions and Releases 59
Archive Names and Formats 60
Downloading and Uncompressing 60
Running Kettle Programs 61
Creating a Shortcut Icon or Launcher for Spoon 62
Configuration 63
Configuration Files and the .kettle Directory 63
The Kettle Shell Scripts 69
General Structure of the Startup Scripts 70
Adding an Entry to the Classpath 70
Changing the Maximum Heap Size 71
Managing JDBC Drivers 72
Summary 72
Chapter 4 An Example ETL Solution—Sakila 73
Sakila 73
The Sakila Sample Database 74
DVD Rental Business Process 74
Sakila Database Schema Diagram 75
Sakila Database Subject Areas 75
General Design Considerations 77
Installing the Sakila Sample Database 77
The Rental Star Schema 78
Rental Star Schema Diagram 78
Rental Fact Table 79
Dimension Tables 79
Keys and Change Data Capture 80
Installing the Rental Star Schema 81
Prerequisites and Some Basic Spoon Skills 81
Setting Up the ETL Solution 82
Creating Database Accounts 82
Working with Spoon 82
Opening Transformation and Job Files 82
Opening the Step’s Configuration Dialog 83
Examining Streams 83
Running Jobs and Transformations 83
The Sample ETL Solution 84
Static, Generated Dimensions 84
Loading the dim_date Dimension Table 84
Loading the dim_time Dimension Table 86
Recurring Load 87
The load_rentals Job 88
The load_dim_staff Transformation 91
Database Connections 91
The load_dim_customer Transformation 95
The load_dim_store Transformation 98
The fetch_address Subtransformation 99
The load_dim_actor Transformation 101
The load_dim_film Transformation 102
The load_fact_rental Transformation 107
Summary 109
Part II ETL 111
Chapter 5 ETL Subsystems 113
Introduction to the 34 Subsystems 114
Extraction 114
Subsystems 1–3: Data Profiling, Change Data Capture, and
Extraction 115
Cleaning and Conforming Data 116
Subsystem 4: Data Cleaning and Quality Screen
Handler System 116
Subsystem 5: Error Event Handler 117
Subsystem 6: Audit Dimension Assembler 117
Subsystem 7: Deduplication System 117
Subsystem 8: Data Conformer 118
Data Delivery 118
Subsystem 9: Slowly Changing Dimension Processor 118
Subsystem 10: Surrogate Key Creation System 119
Subsystem 11: Hierarchy Dimension Builder 119
Subsystem 12: Special Dimension Builder 120
Subsystem 13: Fact Table Loader 121
Subsystem 14: Surrogate Key Pipeline 121
Subsystem 15: Multi-Valued Dimension Bridge Table Builder 121
Subsystem 16: Late-Arriving Data Handler 122
Subsystem 17: Dimension Manager System 122
Subsystem 18: Fact Table Provider System 122
Subsystem 19: Aggregate Builder 123
Subsystem 20: Multidimensional (OLAP) Cube Builder 123
Subsystem 21: Data Integration Manager 123
Managing the ETL Environment 123
Summary 126
Chapter 6 Data Extraction 127
Kettle Data Extraction Overview 128
File-Based Extraction 128
Working with Text Files 128
Working with XML files 133
Special File Types 134
Database-Based Extraction 134
Web-Based Extraction 137
Text-Based Web Extraction 137
HTTP Client 137
Using SOAP 138
Stream-Based and Real-Time Extraction 138
Working with ERP and CRM Systems 138
ERP Challenges 139
Kettle ERP Plugins 140
Working with SAP Data 140
ERP and CDC Issues 146
Data Profiling 146
Using eobjects.org DataCleaner 147
Adding Profile Tasks 149
Adding Database Connections 149
Doing an Initial Profile 151
Working with Regular Expressions 151
Profiling and Exploring Results 152
Validating and Comparing Data 153
Using a Dictionary for Column Dependency Checks 153
Alternative Solutions 154
Text Profiling with Kettle 154
CDC: Change Data Capture 154
Source Data–Based CDC 155
Trigger-Based CDC 157
Snapshot-Based CDC 158
Log-Based CDC 162
Which CDC Alternative Should You Choose? 163
Delivering Data 164
Summary 164
Chapter 7 Cleansing and Conforming 167
Data Cleansing 168
Data-Cleansing Steps 169
Using Reference Tables 172
Conforming Data Using Lookup Tables 172
Conforming Data Using Reference Tables 175
Data Validation 179
Applying Validation Rules 180
Validating Dependency Constraints 183
Error Handling 183
Handling Process Errors 184
Transformation Errors 186
Handling Data (Validation) Errors 187
Auditing Data and Process Quality 191
Deduplicating Data 192
Handling Exact Duplicates 193
The Problem of Non-Exact Duplicates 194
Building Deduplication Transforms 195
Step 1: Fuzzy Match 197
Step 2: Select Suspects 198
Step 3: Lookup Validation Value 198
Step 4: Filter Duplicates 199
Scripting 200
Formula 201
JavaScript 202
User-Defined Java Expressions 202
Regular Expressions 203
Summary 205
Chapter 8 Handling Dimension Tables 207
Managing Keys 208
Managing Business Keys 209
Keys in the Source System 209
Keys in the Data Warehouse 209
Business Keys 209
Storing Business Keys 210
Looking Up Keys with Kettle 210
Generating Surrogate Keys 210
The “Add sequence” Step 211
Working with auto_increment or IDENTITY Columns 217
Keys for Slowly Changing Dimensions 217
Loading Dimension Tables 218
Snowflaked Dimension Tables 218
Top-Down Level-Wise Loading 219
Sakila Snowflake Example 219
Sample Transformation 221
Database Lookup Configuration 222
Sample Job 225
Star Schema Dimension Tables 226
Denormalization 226
Denormalizing to 1NF with the “Database lookup” Step 226
Change Data Capture 227
Slowly Changing Dimensions 228
Types of Slowly Changing Dimensions 228
Type 1 Slowly Changing Dimensions 229
The Insert / Update Step 229
Type 2 Slowly Changing Dimensions 232
The “Dimension lookup / update” Step 232
Other Types of Slowly Changing Dimensions 237
Type 3 Slowly Changing Dimensions 237
Hybrid Slowly Changing Dimensions 238
More Dimensions 239
Generated Dimensions 239
Date and Time Dimensions 239
Generated Mini-Dimensions 239
Junk Dimensions 241
Recursive Hierarchies 242
Summary 243
Chapter 9 Loading Fact Tables 245
Loading in Bulk 246
STDIN and FIFO 247
Kettle Bulk Loaders 248
MySQL Bulk Loading 249
LucidDB Bulk Loader 249
Oracle Bulk Loader 249
PostgreSQL Bulk Loader 250
Table Output Step 250
General Bulk Load Considerations 250
Dimension Lookups 251
Maintaining Referential Integrity 251
The Surrogate Key Pipeline 252
Using In-Memory Lookups 253
Stream Lookups 253
Late-Arriving Data 255
Late-Arriving Facts 256
Late-Arriving Dimensions 256
Fact Table Handling 260
Periodic and Accumulating Snapshots 260
Introducing State-Oriented Fact Tables 261
Loading Periodic Snapshots 263
Loading Accumulating Snapshots 264
Loading State-Oriented Fact Tables 265
Loading Aggregate Tables 266
Summary 267
Chapter 10 Working with OLAP Data 269
OLAP Benefits and Challenges 270
OLAP Storage Types 272
Positioning OLAP 272
Kettle OLAP Options 273
Working with Mondrian 274
Working with XML/A Servers 277
Working with Palo 282
Setting Up the Palo Connection 283
Palo Architecture 284
Reading Palo Data 285
Writing Palo Data 289
Summary 291
Part III Management and Deployment 293
Chapter 11 ETL Development Lifecycle 295
Solution Design 295
Best and Bad Practices 296
Data Mapping 297
Naming and Commentary Conventions 298
Common Pitfalls 299
ETL Flow Design 300
Reusability and Maintainability 300
Agile Development 301
Testing and Debugging 306
Test Activities 307
ETL Testing 308
Test Data Requirements 308
Testing for Completeness 309
Testing Data Transformations 311
Test Automation and Continuous Integration 311
Upgrade Tests 312
Debugging 312
Documenting the Solution 315
Why Isn’t There Any Documentation? 316
Myth 1: My Software Is Self-Explanatory 316
Myth 2: Documentation Is Always Outdated 316
Myth 3: Who Reads Documentation Anyway? 317
Kettle Documentation Features 317
Generating Documentation 319
Summary 320
Chapter 12 Scheduling and Monitoring 321
Scheduling 321
Operating System–Level Scheduling 322
Executing Kettle Jobs and Transformations from
the Command Line 322
UNIX-Based Systems: cron 326
Windows: The at utility and the Task Scheduler 327
Using Pentaho’s Built-in Scheduler 327
Creating an Action Sequence to Run Kettle Jobs and
Transformations 328
Kettle Transformations in Action Sequences 329
Creating and Maintaining Schedules with the
Administration Console 330
Attaching an Action Sequence to a Schedule 333
Monitoring 333
Logging 333
Inspecting the Log 333
Logging Levels 335
Writing Custom Messages to the Log 336
E‑mail Notifications 336
Configuring the Mail Job Entry 337
Summary 340
Chapter 13 Versioning and Migration 341
Version Control Systems 341
File-Based Version Control Systems 342
Organization 342
Leading File-Based VCSs 343
Content Management Systems 344
Kettle Metadata 344
Kettle XML Metadata 345
Transformation XML 345
Job XML 346
Global Replace 347
Kettle Repository Metadata 348
The Kettle Database Repository Type 348
The Kettle File Repository Type 349
The Kettle Enterprise Repository Type 350
Managing Repositories 350
Exporting and Importing Repositories 350
Upgrading Your Repository 351
Version Migration System 352
Managing XML Files 352
Managing Repositories 352
Parameterizing Your Solution 353
Summary 356
Chapter 14 Lineage and Auditing 357
Batch-Level Lineage Extraction 358
Lineage 359
Lineage Information 359
Impact Analysis Information 361
Logging and Operational Metadata 363
Logging Basics 363
Logging Architecture 364
Setting a Maximum Buffer Size 365
Setting a Maximum Log Line Age 365
Log Channels 366
Log Text Capturing in a Job 366
Logging Tables 367
Transformation Logging Tables 367
Job Logging Tables 373
Summary 374
Part IV Performance and Scalability 375
Chapter 15 Performance Tuning 377
Transformation Performance: Finding the Weakest Link 377
Finding Bottlenecks by Simplifying 379
Finding Bottlenecks by Measuring 380
Copying Rows of Data 382
Improving Transformation Performance 384
Improving Performance in Reading Text Files 384
Using Lazy Conversion for Reading Text Files 385
Single-File Parallel Reading 385
Multi-File Parallel Reading 386
Configuring the NIO Block Size 386
Changing Disks and Reading Text Files 386
Improving Performance in Writing Text Files 387
Using Lazy Conversion for Writing Text Files 387
Parallel Files Writing 387
Changing Disks and Writing Text Files 387
Improving Database Performance 388
Avoiding Dynamic SQL 388
Handling Roundtrips 388
Handling Relational Databases 390
Sorting Data 392
Sorting on the Database 393
Sorting in Parallel 393
Reducing CPU Usage 394
Optimizing the Use of JavaScript 394
Launching Multiple Copies of a Step 396
Selecting and Removing Values 397
Managing Thread Priorities 397
Adding Static Data to Rows of Data 397
Limiting the Number of Step Copies 398
Avoiding Excessive Logging 398
Improving Job Performance 399
Loops in Jobs 399
Database Connection Pools 400
Summary 401
Chapter 16 Parallelization, Clustering, and Partitioning 403
Multi-Threading 403
Row Distribution 404
Row Merging 405
Row Redistribution 406
Data Pipelining 407
Consequences of Multi-Threading 408
Database Connections 408
Order of Execution 409
Parallel Execution in a Job 411
Using Carte as a Slave Server 411
The Configuration File 411
Defining Slave Servers 412
Remote Execution 413
Monitoring Slave Servers 413
Carte Security 414
Services 414
Clustering Transformations 417
Defining a Cluster Schema 417
Designing Clustered Transformations 418
Execution and Monitoring 420
Metadata Transformations 421
Rules 422
Data Pipelining 425
Partitioning 425
Defining a Partitioning Schema 425
Objectives of Partitioning 427
Implementing Partitioning 428
Internal Variables 428
Database Partitions 429
Partitioning in a Clustered Transformation 430
Summary 430
Chapter 17 Dynamic Clustering in the Cloud 433
Dynamic Clustering 433
Setting Up a Dynamic Cluster 434
Using the Dynamic Cluster 436
Cloud Computing 437
EC2 438
Getting Started with EC2 438
Costs 438
Customizing an AMI 439
Packaging a New AMI 442
Terminating an AMI 442
Running a Master 442
Running the Slaves 443
Using the EC2 Cluster 444
Monitoring 445
The Lightweight Principle and Persistence Options 446
Summary 447
Chapter 18 Real-Time Data Integration 449
Introduction to Real-Time ETL 449
Real-Time Challenges 450
Requirements 451
Transformation Streaming 452
A Practical Example of Transformation Streaming 454
Debugging 457
Third-Party Software and Real-Time Integration 458
Java Message Service 459
Creating a JMS Connection and Session 459
Consuming Messages 460
Producing Messages 460
Closing Shop 460
Summary 461
Part V Advanced Topics 463
Chapter 19 Data Vault Management 465
Introduction to Data Vault Modeling 466
Do You Need a Data Vault? 466
Data Vault Building Blocks 467
Hubs 467
Links 468
Satellites 469
Data Vault Characteristics 471
Building a Data Vault 471
Transforming Sakila to the Data Vault Model 472
Sakila Hubs 472
Sakila Links 473
Sakila Satellites 474
Loading the Data Vault: A Sample ETL Solution 477
Installing the Sakila Data Vault 477
Setting Up the ETL Solution 477
Creating a Database Account 477
The Sample ETL Data Vault Solution 478
Sample Hub: hub_actor 478
Sample Link: link_customer_store 480
Sample Satellite: sat_actor 483
Loading the Data Vault Tables 485
Updating a Data Mart from a Data Vault 486
The Sample ETL Solution 486
The dim_actor Transformation 486
The dim_customer Transformation 488
The dim_film Transformation 492
The dim_film_actor_bridge Transformation 492
The fact_rental Transformation 493
Loading the Star Schema Tables 495
Summary 495
Chapter 20 Handling Complex Data Formats 497
Non-Relational and Non-Tabular Data Formats 498
Non-Relational Tabular Formats 498
Handling Multi-Valued Attributes 498
Using the Split Field to Rows Step 499
Handling Repeating Groups 500
Using the Row Normaliser Step 500
Semi- and Unstructured Data 501
Kettle Regular Expression Example 503
Configuring the Regex Evaluation Step 504
Verifying the Match 507
Key/Value Pairs 508
Kettle Key/Value Pairs Example 509
Text File Input 509
Regex Evaluation 510
Grouping Lines into Records 511
Denormaliser: Turning Rows into Columns 512
Summary 513
Chapter 21 Web Services 515
Web Pages and Web Services 515
Kettle Web Features 516
General HTTP Steps 516
Simple Object Access Protocol 517
Really Simple Syndication 517
Apache Virtual File System Integration 517
Data Formats 517
XML 518
Kettle Steps for Working with XML 518
Kettle Job Entries for XML 519
HTML 520
JavaScript Object Notation 520
Syntax 521
JSON, Kettle, and ETL/DI 522
XML Examples 523
Example XML Document 523
XML Document Structure 523
Mapping to the Sakila Sample Database 524
Extracting Data from XML 525
Overall Design: The import_xml_into_db Transformation 526
Using the XSD Validator Step 528
Using the “Get Data from XML” Step 530
Generating XML Documents 537
Overall Design: The export_xml_from_db Transformation 537
Generating XML with the Add XML Step 538
Using the XML Join Step 541
SOAP Examples 544
Using the “Web services lookup” Step 544
Configuring the “Web services lookup” Step 544
Accessing SOAP Services Directly 546
JSON Example 549
The Freebase Project 549
Freebase Versus Wikipedia 549
Freebase Web Services 550
The Freebase Read Service 550
The Metaweb Query Language 551
Extracting Freebase Data with Kettle 553
Generate Rows 554
Issuing a Freebase Read Request 555
Processing the Freebase Result Envelope 556
Filtering Out the Original Row 557
Storing to File 558
RSS 558
RSS Structure 558
Channel 558
Item 559
RSS Support in Kettle 560
RSS Input 561
RSS Output 562
Summary 567
Chapter 22 Kettle Integration 569
The Kettle API 569
The LGPL License 569
The Kettle Java API 570
Source Code 570
Building Kettle 571
Building javadoc 571
Libraries and the Class Path 571
Executing Existing Transformations and Jobs 571
Executing a Transformation 572
Executing a Job 573
Embedding Kettle 574
Pentaho Reporting 574
Putting Data into a Transformation 576
Dynamic Transformations 580
Dynamic Template 583
Dynamic Jobs 584
Executing Dynamic ETL in Kettle 586
Result 587
Replacing Metadata 588
Direct Changes with the API 589
Using a Shared Objects File 589
OEM Versions and Forks 590
Creating an OEM Version of PDI 590
Forking Kettle 591
Summary 592
Chapter 23 Extending Kettle 593
Plugin Architecture Overview 593
Plugin Types 594
Architecture 595
Prerequisites 596
Kettle API Documentation 596
Libraries 596
Integrated Development Environment 596
Eclipse Project Setup 597
Examples 598
Transformation Step Plugins 599
StepMetaInterface 599
Value Metadata 605
Row Metadata 606
StepDataInterface 607
StepDialogInterface 607
Eclipse SWT 607
Form Layout 607
Kettle UI Elements 609
Hello World Example Dialog 609
StepInterface 614
Reading Rows from Specific Steps 616
Writing Rows to Specific Steps 616
Writing Rows to Error Handling 617
Identifying a Step Copy 617
Result Feedback 618
Variable Substitution 618
Apache VFS 619
Step Plugin Deployment 619
The User-Defined Java Class Step 620
Passing Metadata 620
Accessing Input and Fields 620
Snippets 620
Example 620
Job Entry Plugins 621
JobEntryInterface 622
JobEntryDialogInterface 624
Partitioning Method Plugins 624
Partitioner 625
Repository Type Plugins 626
Database Type Plugins 627
Summary 628
Appendix A The Kettle Ecosystem 629
Kettle Development and Versions 629
The Pentaho Community Wiki 631
Using the Forums 631
Jira 632
##pentaho 633
Appendix B Kettle Enterprise Edition Features 635
Appendix C Built-in Variables and Properties Reference 637
Internal Variables 637
Kettle Variables 640
Variables for Configuring VFS 641
Noteworthy JRE Variables 642
Index 643
