Pentaho中如何对比两表找差异行？Merge Rows能否无合并取差异？

Hey there! Let's break down your two Pentaho data comparison questions with practical, actionable steps— I’ve worked through these exact scenarios more times than I can count.

The core here is to first align rows using a unique identifier (like a primary key) and then check for mismatches across other columns. Here are two reliable approaches:

• Approach 1: Use a Join + Filter/Calculator

  1. Drag two Table Input components onto the canvas, one for each table. Make sure each pulls in the unique key column plus all columns you want to compare.
  2. Add an Inner Join component, connect both table inputs to it. Configure the join to match rows on your unique identifier (e.g., table1.id = table2.id).
  3. Add a Calculator component after the join. For every pair of columns you want to compare (e.g., table1.name vs table2.name), create a new field with an expression like IF(table1.name <> table2.name, 'Mismatch', 'Match').
  4. Use a Filter Rows component to keep only rows where at least one of the mismatch fields equals 'Mismatch'. This gives you all rows with inconsistent content.

• Approach 2: Use Merge Rows (diff) Tool

  1. Again, start with two Table Input components for your tables. Ensure both include the unique key and all columns to compare.
  2. Add a Sort Rows component after each table input, sorting by the unique identifier (and any other columns that help align rows consistently).
  3. Connect both sorted streams to a Merge Rows (diff) component. Set the "Main step" to your baseline table, and the "Compare step" to the table you're checking against.
  4. The tool will add a flag column with values like identical, changed, new, deleted. Use a Filter Rows component to keep only rows where flag = 'changed'—these are your inconsistent rows.

Absolutely—you don’t have to write the merged data back to the target table. The Merge Rows (diff) tool is perfect for this; you just need to focus on extracting the difference rows instead of performing a merge write. Here's how:

1. Set up the base streams: As above, use Table Input for both tables, add Sort Rows to each (critical for the diff tool to work correctly), and connect them to Merge Rows (diff).
2. Configure the diff tool:
   • Set "Main step" to your original/target table, "Compare step" to the table you're comparing against.
   • Under "Fields to compare", select all columns you want to check for differences.
3. Extract only the difference rows:
   • Add a Filter Rows component right after Merge Rows (diff). Configure it to keep rows where the flag column is either changed (rows that exist in both but have mismatched content) or new (rows present in the compare table but not the target) — adjust based on which differences you care about.
4. Output the results: Instead of connecting to a Table Output component that writes to the target table, you can send the filtered rows to a Text File Output, Excel Output, or even just a Preview step to inspect the differences. No data is written back to your original target table unless you explicitly add that step.

Pro tip: If you only care about rows that are missing from the target table or exist only in the target, you can also filter for new or deleted flags respectively.

内容的提问来源于stack exchange，提问作者Bommu