Execute Row SQL Script
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:
| Field | Description |
| Step name | Specify the unique name of the Execute row SQL script 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 list. |
| Edit | Click to edit your current database connection. |
| New | Click to establish a new database connection. |
| Wizard | Click to open a new database connection using the Wizard. |
| Commit | Specify the number of rows to commit at a time. The default is 1. |
| Send a single statement | Clear if you do not want to group rows into a single statement. |
| SQL field name | Specify the field that contains either the SQL statement or the path to the SQL file to execute. |
| Read SQL from file | Select to specify that the SQL field name is the path to an SQL file. |
Output fields
The Execute row SQL step features the following output field options for collecting statistics about the script processing:
| Option | Description |
| Field to contain insert stats | Specify 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 Update | Specify 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 stats | Specify 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 stats | Specify 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.