book

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

Name: Pentaho® Kettle Solutions: Building Open Source ETL Solutions with Pentaho Data Integration
ISBN: 9780470635179

by Matt Casters, Roland Bouman, Jos van Dongen

September 2010

Intermediate to advanced

720 pages

18h 50m

English

Wiley

Read now

Unlock full access

Copyright
About the Authors
Credits
Acknowledgments
Introduction
The Origins of KettleThe Design of KettleKettle Gets Some TractionKettle Goes Open SourceAbout This BookWho Should Read This BookWhat You Will Need to Use This BookWhat You Will Learn from This BookHow This Book Is OrganizedPart I: Getting StartedETLPart III: Management and DeploymentPart IV: Performance and ScalabilityPart V: Advanced TopicsAppendixesPrerequisitesJavaMySQLSQL Power ArchitectEclipseOn the WebsiteFurther Resources
I. Getting Started
1. ETL Primer
1.1. OLTP versus Data Warehousing1.2. What Is ETL?1.2.1. The Evolution of ETL Solutions1.2.2. ETL Building Blocks1.3. ETL, ELT, and EII1.3.1. ELT1.3.2. EII: Virtual Data Integration1.4. Data Integration Challenges1.4.1. Methodology: Agile BI1.4.2. ETL Design1.4.3. Data Acquisition1.4.3.1. Beware of Spreadsheets1.4.3.2. Design for Failure1.4.3.3. Change Data Capture1.4.4. Data Quality1.4.4.1. Data Profiling1.4.4.2. Data Validation1.5. ETL Tool Requirements1.5.1. Connectivity1.5.2. Platform Independence1.5.3. Scalability1.5.4. Design Flexibility1.5.5. Reuse1.5.6. Extensibility1.5.7. Data Transformations1.5.8. Testing and Debugging1.5.9. Lineage and Impact Analysis1.5.10. Logging and Auditing1.6. Summary
2. Kettle Concepts
2.1. Design Principles2.2. The Building Blocks of Kettle Design2.2.1. Transformations2.2.1.1. Steps2.2.1.2. Transformation Hops2.2.1.3. Parallelism2.2.1.4. Rows of Data2.2.1.5. Data Conversion2.2.1.5.1. Date to String Conversion2.2.1.5.2. Numeric to String Conversion2.2.1.5.3. Other Conversions2.2.2. Jobs2.2.2.1. Job Entries2.2.2.2. Job Hops2.2.2.3. Multiple Paths and Backtracking2.2.2.4. Parallel Execution2.2.2.5. Job Entry Results2.2.3. Transformation or Job Metadata2.2.4. Database Connections2.2.4.1. Special Options2.2.4.2. The Power of the Relational Database2.2.4.3. Connections and Transactions2.2.4.4. Database Clustering2.2.5. Tools and Utilities2.2.6. Repositories2.2.7. Virtual File Systems2.3. Parameters and Variables2.3.1. Defining Variables2.3.2. Named Parameters2.3.3. Using Variables2.4. Visual Programming2.4.1. Getting Started2.4.2. Creating New Steps2.4.3. Putting It All Together2.5. Summary
3. Installation and Configuration
3.1. Kettle Software Overview3.1.1. Integrated Development Environment: Spoon3.1.2. Command-Line Launchers: Kitchen and Pan3.1.3. Job Server: Carte3.1.4. Encr.bat and encr.sh3.2. Installation3.2.1. Java Environment3.2.1.1. Installing Java Manually3.2.1.2. Using Your Linux Package Management System3.2.2. Installing Kettle3.2.2.1. Versions and Releases3.2.2.2. Archive Names and Formats3.2.2.3. Downloading and Uncompressing3.2.2.4. Running Kettle Programs3.2.2.4.1. Windows3.2.2.4.2. UNIX-like systems3.2.2.5. Creating a Shortcut Icon or Launcher for Spoon3.2.2.5.1. Adding a Windows Shortcut3.2.2.5.2. Creating a Launcher for the GNOME Desktop3.3. Configuration3.3.1. Configuration Files and the .kettle Directory3.3.1.1.3.3.1.1.1. .spoonrc3.3.1.1.2. jdbc.properties3.3.1.1.3. kettle.properties3.3.1.1.4. kettle.pwd3.3.1.1.5. repositories.xml3.3.1.1.6. shared.xml3.3.2. The Kettle Shell Scripts3.3.2.1. General Structure of the Startup Scripts3.3.2.2. Adding an Entry to the Classpath3.3.2.3. Changing the Maximum Heap Size3.3.3. Managing JDBC Drivers3.4. Summary
4. An Example ETL Solution—Sakila
4.1. Sakila4.1.1. The Sakila Sample Database4.1.1.1. DVD Rental Business Process4.1.1.2. Sakila Database Schema Diagram4.1.1.3. Sakila Database Subject Areas4.1.1.4. General Design Considerations4.1.1.5. Installing the Sakila Sample Database4.1.2. The Rental Star Schema4.1.2.1. Rental Star Schema Diagram4.1.2.2. Rental Fact Table4.1.2.3. Dimension Tables4.1.2.4. Keys and Change Data Capture4.1.2.5. Installing the Rental Star Schema4.2. Prerequisites and Some Basic Spoon Skills4.2.1. Setting Up the ETL Solution4.2.1.1. Creating Database Accounts4.2.2. Working with Spoon4.2.2.1. Opening Transformation and Job Files4.2.2.2. Opening the Step's Configuration Dialog4.2.2.3. Examining Streams4.2.2.4. Running Jobs and Transformations4.3. The Sample ETL Solution4.3.1. Static, Generated Dimensions4.3.1.1. Loading the dim_date Dimension Table4.3.1.1.1. Generate 10 years4.3.1.1.2. Day Sequence4.3.1.1.3. Calculate Dimension Attributes4.3.1.1.4. Load dim_date4.3.1.1.5. Running the Transformation4.3.1.2. Loading the dim_time Dimension Table4.3.1.2.1. Generating Hours, Minutes, and Seconds4.3.1.2.2. Cartesian Product4.3.1.2.3. Calculate Time and Table out dim_time4.3.1.2.4. Running the Transformation4.3.2. Recurring Load4.3.2.1. The load_rentals Job4.3.2.1.1. Start4.3.2.1.2. Various Transformation Job Entries4.3.2.1.3. Different Types of Hops and Flow of Execution4.3.2.1.4. Serial Execution4.3.2.1.5. Mail Job Entries4.3.2.1.6. Running the Job4.3.2.2. The load_dim_staff Transformation4.3.2.3. Database Connections4.3.2.3.1. Changed Data Capture and Extraction4.3.2.3.2. Converting and Recoding the Active Flag4.3.2.3.3. Loading the dim_staff Type 2 Slowly Changing Dimension Table4.3.2.4. The load_dim_customer Transformation4.3.2.4.1. The Fetch Customer Address SubTransformation4.3.2.5. The load_dim_store Transformation4.3.2.6. The fetch_address Subtransformation4.3.2.6.1. Address Data Lookup Cascade4.3.2.6.2. More Denormalization: Concatenating Address Lines4.3.2.6.3. Subtransformation Interface4.3.2.7. The load_dim_actor Transformation4.3.2.7.1. The Insert / Update Step Type4.3.2.8. The load_dim_film Transformation4.3.2.8.1. Splitting the special_features List4.3.2.8.2. Flattening Individual Special Features to Yes/No Flags4.3.2.8.3. Creating Flags for the Film Categories4.3.2.8.4. Loading the dim_film Table4.3.2.8.5. Loading the dim_film_actor_bridge Table4.3.2.9. The load_fact_rental Transformation4.3.2.9.1. Changed Data Capture4.3.2.9.2. Obtaining Date and Time Smart Keys4.3.2.9.3. Calculating Metrics4.3.2.9.4. Looking Up Keys of Regular Dimension Tables4.3.2.9.5. Looking Up Keys of Type 2 Slowly Changing Dimension Tables4.3.2.9.6. Loading the Fact Table4.4. Summary

II. ETL
5. ETL Subsystems
5.1. Introduction to the 34 Subsystems5.1.1. Extraction5.1.1.1. Subsystems 1–3: Data Profiling, Change Data Capture, and Extraction5.1.2. Cleaning and Conforming Data5.1.2.1. Subsystem 4: Data Cleaning and Quality Screen Handler System5.1.2.2. Subsystem 5: Error Event Handler5.1.2.3. Subsystem 6: Audit Dimension Assembler5.1.2.4. Subsystem 7: Deduplication System5.1.2.5. Subsystem 8: Data Conformer5.1.3. Data Delivery5.1.3.1. Subsystem 9: Slowly Changing Dimension Processor5.1.3.2. Subsystem 10: Surrogate Key Creation System5.1.3.3. Subsystem 11: Hierarchy Dimension Builder5.1.3.4. Subsystem 12: Special Dimension Builder5.1.3.5. Subsystem 13: Fact Table Loader5.1.3.6. Subsystem 14: Surrogate Key Pipeline5.1.3.7. Subsystem 15: Multi-Valued Dimension Bridge Table Builder5.1.3.8. Subsystem 16: Late-Arriving Data Handler5.1.3.9. Subsystem 17: Dimension Manager System5.1.3.10. Subsystem 18: Fact Table Provider System5.1.3.11. Subsystem 19: Aggregate Builder5.1.3.12. Subsystem 20: Multidimensional (OLAP) Cube Builder5.1.3.13. Subsystem 21: Data Integration Manager5.1.4. Managing the ETL Environment5.2. Summary
6. Data Extraction
6.1. Kettle Data Extraction Overview6.1.1. File-Based Extraction6.1.1.1. Working with Text Files6.1.1.2. Working with XML files6.1.1.3. Special File Types6.1.2. Database-Based Extraction6.1.3. Web-Based Extraction6.1.3.1. Text-Based Web Extraction6.1.3.2. HTTP Client6.1.3.3. Using SOAP6.1.4. Stream-Based and Real-Time Extraction6.2. Working with ERP and CRM Systems6.2.1. ERP Challenges6.2.2. Kettle ERP Plugins6.2.3. Working with SAP Data6.2.4. ERP and CDC Issues6.3. Data Profiling6.3.1. Using eobjects.org DataCleaner6.3.1.1. Adding Profile Tasks6.3.1.2. Adding Database Connections6.3.1.3. Doing an Initial Profile6.3.1.4. Working with Regular Expressions6.3.1.5. Profiling and Exploring Results6.3.1.6. Validating and Comparing Data6.3.1.7. Using a Dictionary for Column Dependency Checks6.3.1.8. Alternative Solutions6.3.1.9. Text Profiling with Kettle6.4. CDC: Change Data Capture6.4.1. Source Data–Based CDC6.4.2. Trigger-Based CDC6.4.3. Snapshot-Based CDC6.4.4. Log-Based CDC6.4.5. Which CDC Alternative Should You Choose?6.5. Delivering Data6.6. Summary
7. Cleansing and Conforming
7.1. Data Cleansing7.1.1. Data-Cleansing Steps7.1.2. Using Reference Tables7.1.2.1. Conforming Data Using Lookup Tables7.1.2.2. Conforming Data Using Reference Tables7.1.3. Data Validation7.1.3.1. Applying Validation Rules7.1.3.2. Validating Dependency Constraints7.2. Error Handling7.2.1. Handling Process Errors7.2.1.1. Transformation Errors7.2.2. Handling Data (Validation) Errors7.3. Auditing Data and Process Quality7.4. Deduplicating Data7.4.1. Handling Exact Duplicates7.4.2. The Problem of Non-Exact Duplicates7.4.3. Building Deduplication Transforms7.4.3.1. Step 1: Fuzzy Match7.4.3.2. Step 2: Select Suspects7.4.3.3. Step 3: Lookup Validation Value7.4.3.4. Step 4: Filter Duplicates7.5. Scripting7.5.1. Formula7.5.2. JavaScript7.5.3. User-Defined Java Expressions7.5.4. Regular Expressions7.6. Summary
8. Handling Dimension Tables
8.1. Managing Keys8.1.1. Managing Business Keys8.1.1.1. Keys in the Source System8.1.1.2. Keys in the Data Warehouse8.1.1.3. Business Keys8.1.1.4. Storing Business Keys8.1.1.5. Looking Up Keys with Kettle8.1.2. Generating Surrogate Keys8.1.2.1. The "Add sequence" Step8.1.2.1.1. Using Internal Counters for the "Add sequence" Step8.1.2.1.2. Generating Surrogate Keys Based on a Counter8.1.2.1.3. Dynamically Configuring the Sequence Offset8.1.2.1.4. Surrogate Keys Based on a Database Sequence8.1.2.2. Working with auto_increment or IDENTITY Columns8.1.2.3. Keys for Slowly Changing Dimensions8.2. Loading Dimension Tables8.2.1. Snowflaked Dimension Tables8.2.1.1. Top-Down Level-Wise Loading8.2.1.2. Sakila Snowflake Example8.2.1.3. Sample Transformation8.2.1.4. Database Lookup Configuration8.2.1.4.1. Connection, Lookup Schema, and Lookup Table8.2.1.4.2. Lookup Key8.2.1.4.3. Lookup Value8.2.1.4.4. Cache Configuration8.2.1.4.5. Lookup Failure8.2.1.5. Sample Job8.2.2. Star Schema Dimension Tables8.2.2.1. Denormalization8.2.2.2. Denormalizing to 1NF with the "Database lookup" Step8.2.2.3. Change Data Capture8.3. Slowly Changing Dimensions8.3.1. Types of Slowly Changing Dimensions8.3.2. Type 1 Slowly Changing Dimensions8.3.2.1. The Insert / Update Step8.3.2.1.1. Connection, Target Schema and Table, and Commit Size8.3.2.1.2. Target Key8.3.2.1.3. Update Fields8.3.3. Type 2 Slowly Changing Dimensions8.3.3.1. The "Dimension lookup / update" Step8.3.3.1.1. Specifying the Mode of Operation8.3.3.1.2. General Configuration8.3.3.1.3. Keys Tab Page8.3.3.1.4. Surrogate Key8.3.3.1.5. History Maintenance8.3.3.1.6. Lookup / Update Fields8.3.4. Other Types of Slowly Changing Dimensions8.3.4.1. Type 3 Slowly Changing Dimensions8.3.4.2. Hybrid Slowly Changing Dimensions8.4. More Dimensions8.4.1. Generated Dimensions8.4.1.1. Date and Time Dimensions8.4.1.2. Generated Mini-Dimensions8.4.2. Junk Dimensions8.4.3. Recursive Hierarchies8.5. Summary
9. Loading Fact Tables
9.1. Loading in Bulk9.1.1. STDIN and FIFO9.1.2. Kettle Bulk Loaders9.1.2.1. MySQL Bulk Loading9.1.2.2. LucidDB Bulk Loader9.1.2.3. Oracle Bulk Loader9.1.2.4. PostgreSQL Bulk Loader9.1.2.5. Table Output Step9.1.3. General Bulk Load Considerations9.2. Dimension Lookups9.2.1. Maintaining Referential Integrity9.2.2. The Surrogate Key Pipeline9.2.2.1. Using In-Memory Lookups9.2.2.2. Stream Lookups9.2.3. Late-Arriving Data9.2.3.1. Late-Arriving Facts9.2.3.2. Late-Arriving Dimensions9.3. Fact Table Handling9.3.1. Periodic and Accumulating Snapshots9.3.2. Introducing State-Oriented Fact Tables9.3.3. Loading Periodic Snapshots9.3.4. Loading Accumulating Snapshots9.3.5. Loading State-Oriented Fact Tables9.3.6. Loading Aggregate Tables9.4. Summary
10. Working with OLAP Data
10.1. OLAP Benefits and Challenges10.1.1. OLAP Storage Types10.1.2. Positioning OLAP10.1.3. Kettle OLAP Options10.2. Working with Mondrian10.3. Working with XML/A Servers10.4. Working with Palo10.4.1. Setting Up the Palo Connection10.4.2. Palo Architecture10.4.3. Reading Palo Data10.4.4. Writing Palo Data10.5. Summary
III. Management and Deployment
11. ETL Development Lifecycle
11.1. Solution Design11.1.1. Best and Bad Practices11.1.1.1. Data Mapping11.1.1.2. Naming and Commentary Conventions11.1.1.3. Common Pitfalls11.1.2. ETL Flow Design11.1.3. Reusability and Maintainability11.2. Agile Development11.3. Testing and Debugging11.3.1. Test Activities11.3.2. ETL Testing11.3.2.1. Test Data Requirements11.3.2.2. Testing for Completeness11.3.2.3. Testing Data Transformations11.3.2.4. Test Automation and Continuous Integration11.3.2.5. Upgrade Tests11.3.3. Debugging11.4. Documenting the Solution11.4.1. Why Isn't There Any Documentation?11.4.1.1. Myth 1: My Software Is Self-Explanatory11.4.1.2. Myth 2: Documentation Is Always Outdated11.4.1.3. Myth 3: Who Reads Documentation Anyway?11.4.2. Kettle Documentation Features11.4.3. Generating Documentation11.5. Summary
12. Scheduling and Monitoring
12.1. Scheduling12.1.1. Operating System–Level Scheduling12.1.1.1. Executing Kettle Jobs and Transformations from the Command Line12.1.1.1.1. Command-Line Parameters12.1.1.1.2. Running Jobs with Kitchen12.1.1.1.3. Running Transformations with Pan12.1.1.1.4. Using Custom Command-Line Parameters12.1.1.2. UNIX-Based Systems: cron12.1.1.3. Windows: The at utility and the Task Scheduler12.1.2. Using Pentaho's Built-in Scheduler12.1.2.1. Creating an Action Sequence to Run Kettle Jobs and Transformations12.1.2.2. Kettle Transformations in Action Sequences12.1.2.3. Creating and Maintaining Schedules with the Administration Console12.1.2.4. Attaching an Action Sequence to a Schedule12.2. Monitoring12.2.1. Logging12.2.1.1. Inspecting the Log12.2.1.2. Logging Levels12.2.1.3. Writing Custom Messages to the Log12.2.2. E-mail Notifications12.2.2.1. Configuring the Mail Job Entry12.2.2.1.1. Addresses Tab12.2.2.1.2. Server Tab12.2.2.1.3. EMail Message Tab12.2.2.1.4. Attached Files Tab12.3. Summary
13. Versioning and Migration
13.1. Version Control Systems13.1.1. File-Based Version Control Systems13.1.1.1. Organization13.1.1.2. Leading File-Based VCSs13.1.2. Content Management Systems13.2. Kettle Metadata13.2.1. Kettle XML Metadata13.2.1.1. Transformation XML13.2.1.2. Job XML13.2.1.3. Global Replace13.2.2. Kettle Repository Metadata13.2.2.1. The Kettle Database Repository Type13.2.2.2. The Kettle File Repository Type13.2.2.3. The Kettle Enterprise Repository Type13.3. Managing Repositories13.3.1. Exporting and Importing Repositories13.3.2. Upgrading Your Repository13.4. Version Migration System13.4.1. Managing XML Files13.4.2. Managing Repositories13.4.3. Parameterizing Your Solution13.5. Summary
14. Lineage and Auditing
14.1. Batch-Level Lineage Extraction14.2. Lineage14.2.1. Lineage Information14.2.2. Impact Analysis Information14.3. Logging and Operational Metadata14.3.1. Logging Basics14.3.2. Logging Architecture14.3.2.1. Setting a Maximum Buffer Size14.3.2.2. Setting a Maximum Log Line Age14.3.2.3. Log Channels14.3.2.4. Log Text Capturing in a Job14.3.3. Logging Tables14.3.3.1. Transformation Logging Tables14.3.3.1.1. The Transformation Log Table14.3.3.1.2. The Step Log Table14.3.3.1.3. The Performance Log Table14.3.3.1.4. The Logging Channels Log Table14.3.3.2. Job Logging Tables14.3.3.2.1. The Job Log Table14.3.3.2.2. The Job Entry Log Table14.4. Summary
IV. Performance and Scalability
15. Performance Tuning
15.1. Transformation Performance: Finding the Weakest Link15.1.1. Finding Bottlenecks by Simplifying15.1.2. Finding Bottlenecks by Measuring15.1.3. Copying Rows of Data15.2. Improving Transformation Performance15.2.1. Improving Performance in Reading Text Files15.2.1.1. Using Lazy Conversion for Reading Text Files15.2.1.2. Single-File Parallel Reading15.2.1.3. Multi-File Parallel Reading15.2.1.4. Configuring the NIO Block Size15.2.1.5. Changing Disks and Reading Text Files15.2.2. Improving Performance in Writing Text Files15.2.2.1. Using Lazy Conversion for Writing Text Files15.2.2.2. Parallel Files Writing15.2.2.3. Changing Disks and Writing Text Files15.2.3. Improving Database Performance15.2.3.1. Avoiding Dynamic SQL15.2.3.2. Handling Roundtrips15.2.3.2.1. Reducing Network Latency15.2.3.2.2. Network Speed15.2.3.3. Handling Relational Databases15.2.3.3.1. Commit Size15.2.3.3.2. Indexes15.2.3.3.3. Table Partitioning15.2.3.3.4. Constraints15.2.3.3.5. Triggers15.2.4. Sorting Data15.2.4.1. Sorting on the Database15.2.4.2. Sorting in Parallel15.2.5. Reducing CPU Usage15.2.5.1. Optimizing the Use of JavaScript15.2.5.2. Launching Multiple Copies of a Step15.2.5.3. Selecting and Removing Values15.2.5.4. Managing Thread Priorities15.2.5.5. Adding Static Data to Rows of Data15.2.5.6. Limiting the Number of Step Copies15.2.5.7. Avoiding Excessive Logging15.3. Improving Job Performance15.3.1. Loops in Jobs15.3.2. Database Connection Pools15.4. Summary
16. Parallelization, Clustering, and Partitioning
16.1. Multi-Threading16.1.1. Row Distribution16.1.2. Row Merging16.1.3. Row Redistribution16.1.4. Data Pipelining16.1.5. Consequences of Multi-Threading16.1.5.1. Database Connections16.1.5.2. Order of Execution16.1.5.2.1. The Execute SQL Step16.1.5.2.2. The Blocking Step16.1.6. Parallel Execution in a Job16.2. Using Carte as a Slave Server16.2.1. The Configuration File16.2.2. Defining Slave Servers16.2.3. Remote Execution16.2.4. Monitoring Slave Servers16.2.5. Carte Security16.2.6. Services16.3. Clustering Transformations16.3.1. Defining a Cluster Schema16.3.2. Designing Clustered Transformations16.3.3. Execution and Monitoring16.3.4. Metadata Transformations16.3.4.1. Rules16.3.4.2. Data Pipelining16.4. Partitioning16.4.1. Defining a Partitioning Schema16.4.2. Objectives of Partitioning16.4.3. Implementing Partitioning16.4.4. Internal Variables16.4.5. Database Partitions16.4.6. Partitioning in a Clustered Transformation16.5. Summary
17. Dynamic Clustering in the Cloud
17.1. Dynamic Clustering17.1.1. Setting Up a Dynamic Cluster17.1.2. Using the Dynamic Cluster17.2. Cloud Computing17.3. EC217.3.1. Getting Started with EC217.3.2. Costs17.3.3. Customizing an AMI17.3.4. Packaging a New AMI17.3.5. Terminating an AMI17.3.6. Running a Master17.3.7. Running the Slaves17.3.8. Using the EC2 Cluster17.3.9. Monitoring17.3.10. The Lightweight Principle and Persistence Options17.4. Summary
18. Real-Time Data Integration
18.1. Introduction to Real-Time ETL18.1.1. Real-Time Challenges18.1.2. Requirements18.2. Transformation Streaming18.2.1. A Practical Example of Transformation Streaming18.2.2. Debugging18.2.3. Third-Party Software and Real-Time Integration18.2.4. Java Message Service18.2.4.1. Creating a JMS Connection and Session18.2.4.2. Consuming Messages18.2.4.3. Producing Messages18.2.4.4. Closing Shop18.3. Summary
V. Advanced Topics
19. Data Vault Management
19.1. Introduction to Data Vault Modeling19.2. Do You Need a Data Vault?19.3. Data Vault Building Blocks19.3.1. Hubs19.3.2. Links19.3.3. Satellites19.3.4. Data Vault Characteristics19.3.5. Building a Data Vault19.4. Transforming Sakila to the Data Vault Model19.4.1. Sakila Hubs19.4.2. Sakila Links19.4.3. Sakila Satellites19.5. Loading the Data Vault: A Sample ETL Solution19.5.1. Installing the Sakila Data Vault19.5.2. Setting Up the ETL Solution19.5.3. Creating a Database Account19.5.4. The Sample ETL Data Vault Solution19.5.4.1. Sample Hub: hub_actor19.5.4.2. Sample Link: link_customer_store19.5.4.3. Sample Satellite: sat_actor19.5.5. Loading the Data Vault Tables19.6. Updating a Data Mart from a Data Vault19.6.1. The Sample ETL Solution19.6.2. The dim_actor Transformation19.6.3. The dim_customer Transformation19.6.4. The dim_film Transformation19.6.5. The dim_film_actor_bridge Transformation19.6.6. The fact_rental Transformation19.6.7. Loading the Star Schema Tables19.7. Summary
20. Handling Complex Data Formats
20.1. Non-Relational and Non-Tabular Data Formats20.2. Non-Relational Tabular Formats20.2.1. Handling Multi-Valued Attributes20.2.1.1. Using the Split Field to Rows Step20.2.2. Handling Repeating Groups20.2.2.1. Using the Row Normaliser Step20.3. Semi- and Unstructured Data20.3.1. Kettle Regular Expression Example20.3.1.1. Configuring the Regex Evaluation Step20.3.1.1.1. Settings Tag Page20.3.1.1.2. Fields Grid20.3.1.1.3. Content Tab Page20.3.1.2. Verifying the Match20.4. Key/Value Pairs20.4.1. Kettle Key/Value Pairs Example20.4.1.1. Text File Input20.4.1.2. Regex Evaluation20.4.1.3. Grouping Lines into Records20.4.1.4. Denormaliser: Turning Rows into Columns20.5. Summary
21. Web Services
21.1. Web Pages and Web Services21.1.1. Kettle Web Features21.1.1.1. General HTTP Steps21.1.1.2. Simple Object Access Protocol21.1.1.3. Really Simple Syndication21.1.1.4. Apache Virtual File System Integration21.2. Data Formats21.2.1. XML21.2.1.1. Kettle Steps for Working with XML21.2.1.2. Kettle Job Entries for XML21.2.2. HTML21.2.3. JavaScript Object Notation21.2.3.1. Syntax21.2.3.2. JSON, Kettle, and ETL/DI21.3. XML Examples21.3.1. Example XML Document21.3.1.1. XML Document Structure21.3.1.2. Mapping to the Sakila Sample Database21.3.2. Extracting Data from XML21.3.2.1. Overall Design: The import_xml_into_db Transformation21.3.2.2. Using the XSD Validator Step21.3.2.2.1. XML Source21.3.2.2.2. Output Fields21.3.2.2.3. XML Schema Definition21.3.2.2.4. Error Handling21.3.2.2.5. Checking the Validation Result21.3.2.3. Using the "Get Data from XML" Step21.3.2.3.1. The File Tab21.3.2.3.2. The Content Tab21.3.2.3.3. The Fields Tab21.3.2.3.4. Using Tokens21.3.3. Generating XML Documents21.3.3.1. Overall Design: The export_xml_from_db Transformation21.3.3.2. Generating XML with the Add XML Step21.3.3.2.1. The Content Tab21.3.3.2.2. The Fields Tab21.3.3.3. Using the XML Join Step21.3.3.3.1. Target21.3.3.3.2. Source21.3.3.3.3. Join Condition21.3.3.3.4. Result Stream21.3.3.3.5. Complex Join Conditions21.4. SOAP Examples21.4.1. Using the "Web services lookup" Step21.4.1.1. Configuring the "Web services lookup" Step21.4.1.1.1. The Web Services Tab21.4.1.1.2. The "in" Tab21.4.1.1.3. The Result Tab21.4.2. Accessing SOAP Services Directly21.5. JSON Example21.5.1. The Freebase Project21.5.1.1. Freebase Versus Wikipedia21.5.1.2. Freebase Web Services21.5.1.3. The Freebase Read Service21.5.1.4. The Metaweb Query Language21.5.2. Extracting Freebase Data with Kettle21.5.2.1. Generate Rows21.5.2.2. Issuing a Freebase Read Request21.5.2.3. Processing the Freebase Result Envelope21.5.2.4. Filtering Out the Original Row21.5.2.5. Storing to File21.6. RSS21.6.1. RSS Structure21.6.1.1. Channel21.6.1.2. Item21.6.2. RSS Support in Kettle21.6.2.1. RSS Input21.6.2.2. RSS Output21.6.2.2.1. Source Data21.6.2.2.2. Standard Feed21.6.2.2.3. Custom Feed21.7. Summary
22. Kettle Integration
22.1. The Kettle API22.1.1. The LGPL License22.1.2. The Kettle Java API22.1.2.1. Source Code22.1.2.2. Building Kettle22.1.2.3. Building javadoc22.1.2.4. Libraries and the Class Path22.2. Executing Existing Transformations and Jobs22.2.1. Executing a Transformation22.2.2. Executing a Job22.3. Embedding Kettle22.3.1. Pentaho Reporting22.3.2. Putting Data into a Transformation22.3.2.1.1. Passing Data with a Result Object22.3.2.1.2. Passing Data in a Streaming Fashion22.3.2.1.3. Setting Parameters and Variables22.3.3. Dynamic Transformations22.3.4. Dynamic Template22.3.5. Dynamic Jobs22.3.6. Executing Dynamic ETL in Kettle22.3.7. Result22.3.8. Replacing Metadata22.3.8.1. Direct Changes with the API22.3.8.2. Using a Shared Objects File22.4. OEM Versions and Forks22.4.1. Creating an OEM Version of PDI22.4.2. Forking Kettle22.5. Summary
23. Extending Kettle
23.1. Plugin Architecture Overview23.1.1. Plugin Types23.1.2. Architecture23.1.3. Prerequisites23.1.3.1. Kettle API Documentation23.1.3.2. Libraries23.1.3.3. Integrated Development Environment23.1.3.4. Eclipse Project Setup23.1.3.5. Examples23.2. Transformation Step Plugins23.2.1. StepMetaInterface23.2.1.1. Value Metadata23.2.1.2. Row Metadata23.2.2. StepDataInterface23.2.3. StepDialogInterface23.2.3.1. Eclipse SWT23.2.3.2. Form Layout23.2.3.3. Kettle UI Elements23.2.3.4. Hello World Example Dialog23.2.4. StepInterface23.2.4.1. Reading Rows from Specific Steps23.2.4.2. Writing Rows to Specific Steps23.2.4.3. Writing Rows to Error Handling23.2.4.4. Identifying a Step Copy23.2.4.5. Result Feedback23.2.4.6. Variable Substitution23.2.4.7. Apache VFS23.2.4.8. Step Plugin Deployment23.3. The User-Defined Java Class Step23.3.1. Passing Metadata23.3.2. Accessing Input and Fields23.3.3. Snippets23.3.4. Example23.4. Job Entry Plugins23.4.1. JobEntryInterface23.4.2. JobEntryDialogInterface23.5. Partitioning Method Plugins23.5.1. Partitioner23.6. Repository Type Plugins23.7. Database Type Plugins23.8. Summary
A. The Kettle Ecosystem
A.1. Kettle Development and VersionsA.2. The Pentaho Community WikiA.3. Using the ForumsA.4. JiraA.5. ##pentaho
B. Kettle Enterprise Edition Features
C. Built-in Variables and Properties Reference
C.1. Internal VariablesC.2. Kettle VariablesC.3. Variables for Configuring VFSC.4. Noteworthy JRE Variables

Content preview from Pentaho® Kettle Solutions: Building Open Source ETL Solutions with Pentaho Data Integration

Chapter 18. Real-Time Data Integration

In this chapter, we offer a closer look at how real-time data integration can be performed with Kettle. You'll start by exploring the main challenges and requirements to figure out when it will be useful for you to deploy this type of data integration.

After that, we explain why the transformation engine is a good match for streaming real-time BI solutions, and discuss the main pitfalls and considerations you need to keep in mind.

As an example, we include the full code for a (near) real-time Twitter client that continuously updates a database table for further consumption by a dashboard. Finally, we cover third-party software such as database log readers and we provide guidelines on how to implement your own Java Message Service (JMS) solutions in Kettle.

Introduction to Real-Time ETL

In a typical data integration setting, jobs and transformations are run at specific times. For example, it's quite typical to have nightly, weekly, or monthly batch runs in place. The term batch run comes from the fact that a whole batch of data or group of work is executed in sequence in one go. It is also referred to as batch processing. Usually the batch is scheduled to run at a time when computing resources are readily available. For example, most data warehouses are updated with batch processing during the night when there are few users on the operational systems.

Usually it's sufficient to have nightly jobs in place to satisfy your requirements. In fact, the ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Pentaho Data Integration Quick Start Guide

Publisher Resources

ISBN: 9780470635179Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

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

by Matt Casters, Roland Bouman, Jos van Dongen

Chapter 18. Real-Time Data Integration

Introduction to Real-Time ETL

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.