Pentaho Kettle Solutions: Building Open Source ETL Solutions with Pentaho Data Integration

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