Pentaho Kettle Delete步骤是否锁表？如何分块删除大量数据？

To avoid long-running locks, you’ll want to break the delete into smaller, manageable chunks. Here are three practical ways to do this in Pentaho:

1. Loop with Limit-Based Deletes #

This is the simplest approach if your database supports LIMIT (or equivalent):

• First, create a job that starts by calculating the total number of rows to delete. Use a Table Input step to run SELECT COUNT(*) FROM your_table WHERE [your_condition] and store this count in a job variable (e.g., ${TOTAL_ROWS}).
• Add a Loop component (use the Repeat job entry) that runs until the remaining rows to delete hit zero.
• Inside the loop, run a transform with a Delete step that uses a query like:

  DELETE FROM your_table WHERE [your_condition] LIMIT 1000;
  

  (Adjust the 1000 to a chunk size that works for your database—start with 1k-10k and tweak based on performance.)
• After each loop iteration, update the remaining row count (run another COUNT query) and adjust the loop condition accordingly.

2. Range-Based Deletes (Using Primary Keys) #

If your table has a sequential primary key (e.g., id), this method is more stable (especially for databases that don’t support LIMIT like Oracle):

• First, get the min and max values of your primary key for the rows to delete:

  SELECT MIN(id), MAX(id) FROM your_table WHERE [your_condition];
  

• Store these as variables (e.g., ${MIN_ID} and ${MAX_ID}).
• Calculate the number of chunks (e.g., each chunk covers 10,000 IDs).
• Loop through each ID range, running a delete like:

  DELETE FROM your_table WHERE [your_condition] AND id BETWEEN ${START_ID} AND ${END_ID};
  

• Update ${START_ID} and ${END_ID} after each iteration until you cover the full range.

3. Call a Database-Stored Procedure #

If you prefer to handle the chunking logic in the database (often more efficient), write a stored procedure that deletes in batches, then call it from Pentaho:

• Create a stored procedure (example for MySQL):

  DELIMITER //
  CREATE PROCEDURE DeleteLargeDataset()
  BEGIN
    DECLARE rows_deleted INT;
    REPEAT
      DELETE FROM your_table WHERE [your_condition] LIMIT 1000;
      SET rows_deleted = ROW_COUNT();
    UNTIL rows_deleted = 0 END REPEAT;
  END //
  DELIMITER ;
  

• In Pentaho, use an Execute SQL Script step to run CALL DeleteLargeDataset();.

• Index your delete conditions: Make sure the columns in your WHERE clause are indexed—this reduces the time each chunk takes to run and minimizes lock scope.
• Avoid large transactions: Each chunk should be a separate transaction (Kettle commits after each step by default, which helps here).
• Monitor locks: Use your database’s built-in tools (e.g., SHOW ENGINE INNODB STATUS for MySQL, V$LOCK for Oracle) to check lock behavior during testing.

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