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

Execute Row SQL Script

Parent article

The Execute row SQL script step executes an SQL statement or file for every input row, allowing you to dynamically assemble SQL for the creation of indexes, partitions, and tables.

NotePrepared SQL statements are not used due to the scripting and dynamic operation of this step, which can slow transformation performance. For optimal performance, Pentaho recommends using dedicated steps like Table Output, Table Input, Update, Delete, etc.

General

The following fields are general to this transformation step:

FieldDescription
Step nameSpecify the unique name of the Execute row SQL script step on the canvas. You can customize the name or leave it as the default.
ConnectionSelect the name of a connected database from the list.
EditClick to edit your current database connection.
NewClick to establish a new database connection.
WizardClick to open a new database connection using the Wizard.
CommitSpecify the number of rows to commit at a time. The default is 1.
Send a single statementClear if you do not want to group rows into a single statement.
SQL field nameSpecify the field that contains either the SQL statement or the path to the SQL file to execute.
Read SQL from fileSelect to specify that the SQL field name is the path to an SQL file.

Output fields

Execute Row SQL Script Output Fields

The Execute row SQL step features the following output field options for collecting statistics about the script processing:

OptionDescription
Field to contain insert statsSpecify a field name to create a field in the stream that contains the statistic for the number of records that were inserted.
Field to contain UpdateSpecify a field name to create field in the stream that contains the statistic for the number of records that were updated.
Field to contain Delete statsSpecify a field name to create a field in the stream that contains the statistic for the number of records that were deleted.
Field to contain Read statsSpecify a field name to create a field in the stream that contains the statistic for the number of records that were read.

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.