Skip to main content

Pentaho+ documentation has moved!

The new product documentation portal is here. Check it out now at docs.hitachivantara.com

 

Hitachi Vantara Lumada and Pentaho Documentation

Delete

 

Parent article

The Delete step permanently removes a row from a database so you can cleanse your data. In the Delete step, choose a field to compare against the values of incoming fields from another step. When the comparison requirements are satisfied, the database row is deleted. If multiple rows match, then all rows with that value are deleted from the database.

General

 
Delete step dialog box

The following table describes the general options for the Delete step.

 

Option Description
Step name Specifies the unique name of the Delete step on the canvas. You can customize the name or leave it as the default.
Connection Select the name of a connected database from the drop-down list. Alternately, you can:
  • Click Edit to revise your current database connection.
  • Click New to establish a new database connection.
  • Click Wizard to open a new database connection using the Wizard.
Target schema Specify the schema of the table to load from your database.
Target table Specify the name of the table in your database where you want to delete the data.
Commit size Specify the size of the commit batch. The size is the number of DELETE statements to perform before sending a COMMIT command to the database.

Depending on your connected database, commit sizes can affect step performance. If blank or set to 0, the database determines the size.

The default is 100.

The key(s) to look up the value(s) table

 

The columns in the following table define the key(s) used to map the row(s) to delete.

Column Description
Table field Specify the field name from the populated list of Target table columns for which you want to compare.
Comparator Specify the comparator you want to use. Note that case-sensitive comparisons are possible, depending on your connected database and Target table.

You can select one of the following comparators:

  • =
  • <>
  • <
  • <=
  • >
  • >=
  • LIKE
  • BETWEEN
  • IS NULL
  • IS NOT NULL
Stream field 1 Specify the name of the field from the incoming stream that contains the row you want to use for comparison against the Table field value.
Stream field 2 Specify the name of the field from the incoming stream that contains the row you want to use for the BETWEEN comparison against the Stream field 1.
Get fields Select this button to populate Stream field 1 and Stream field 2 from previous steps in your transformation.

For example, if the look up value of QUANTITYORDERED is less than or equal to the min_quantityordered, and PRODUCTLINE values map equally, then that QUANTITYORDERED row is deleted from the STG_SALES_DATA table.

Metadata injection support

 

All fields of this step support metadata injection. You can use this step with ETL metadata injection to pass metadata to your transformation at runtime.