Hitachi Vantara Pentaho Community Wiki
Skip to end of metadata
Go to start of metadata


This step is used to read information from a database, using a connection and SQL. Basic SQL statements can be generated automatically by clicking Get SQL select statement.




Step name

Name of the step;the name has to be unique in a single transformation


The database connection from which to read data


The SQL statement used to read information from the database connection. You can also click Get SQL select statement... to browse tables and automatically generate a basic select statement.

Enable lazy conversion

When enabled, lazy conversion avoids unnecessary data type conversions and can result in a significant performance improvements.

Replace variables in script?

Enable to replace variables in the script; this feature was provided to allow you to test with or without performing variable substitutions.

Insert data from step

Specify the input step name where Pentaho? can expect information to come from. This information can then be inserted into the SQL statement. The locators where Pentaho? inserts information is indicated by ? (question marks).

Execute for each row?

Enable to perform the data insert for each individual row.

Limit size

Sets the number of lines that is read from the database; zero (0) means read all lines.

Metadata Injection Support

You can use the Metadata Injection supported fields with ETL Metadata Injection step to pass metadata to your transformation at runtime. The following Option fields of the Table Input step support metadata injection:

  • Options: SQL, Enable Lazy Conversion, Replace Variables in Script?, Insert Data from Step, and Limit Size


Below is a sample SQL statement:

SELECT * FROM customers WHERE changed_date BETWEEN ? AND ?

This statement needs two dates that are read on the Insert data from step.Note: The dates can be provided using the Get System Info step type. For example, if you want to read all customers that have had their data changed yesterday, you may get the range for yesterday and read the customer data.


Preview allows you to preview the step. This is accomplished by preview of a new transformation with two steps: this one and a
Dummy step. To see a detailed log of the execution, click Logs in the Preview window.


Variables are not getting substituted

Q: I'm feeding parameters via a input hop to a table input, but they're not being replaced properly. My input row contains e.g. the fieldname "input_param". The following is my query in Table input step:

 WHERE PONO = ${input_param};

the input_param does not get replaced. How to do this?

A:The reason it fails is that there's a difference between variables and field substitution. The above syntax would work if "input_param" would be a variable. However to use the field values of the incoming rows as arguments you need to use ? Parameters. The query would need to become:


These parameter markers are filled in positionally.

Parameters are working, but not as part of a tablename

Q: I'm using parameters in a table input step. This works when I use the parameter in a where clause as:


If 1 argument would be passed to the table input step from the previous step. However when I use the parameter marker as part of a tablename as:


it fails. The intention of the above query would be e.g. to access multiple tables CO_VEHICLES_1, CO_VEHICLES_2, ... where the number would be passed as a parameter. This always fails, why?

A:The reason it fails is that JDBC does not allow parameter markers in a table. The reason for this is simple: the tablename determines the columns you can get retrieve, the access rights, ... These things are checked once for parametrized SQL statements and afterwards only the parameters are filled in to execute the query.

If parameter markers would be allowed in tablenames the SQL statement would have to be prepared every time, hence parameter markers in tablenames are not supported.

There is a way around it: you can use (kettle) variables as part of a tablename as in:


What PDI does for these queries (if you enable "variable replacement" in the table input step) is:

  • Replace the variables in the SQL statements;
  • Bind the parameters per execution;

This way JDBC is not aware of the changing tablename (because it will be filled in by PDI before the SQL is sent over to the database), it will however result in different SQL statements being prepared by the database. There's also another section in is document on the setting of variables.


  1. The "Enable lazy conversion" flag appears to be quite lazy with regard to utf8-encoded strings, treating a two-byte character as two separate one-byte characters. I haven't explored the behaviour here, but if you are seeing issues with utf8 decoding consider switching off lazy-conversion.

  2. Hello Team,

    I wanted to pass two fields retrieved from Table input as a search parameter in next table input step. But while doing this I got an error. If I retrieved this search condition step and process my job it runs fine. But for my business purpose I wanted to used this condition.

    Please assist on this.

    For Example:

    1st Table Input Step

    Select '142501' as Min_Value, '142510' as Max_Value

    From Dual(Table Name);

    I wanted to used this two values in my next table table input step

    Select * from table_name

    where column name >= ? and column_name < ?

    ----Where Min_Value as my 1st value and Max_Value as 2nd one. But at the time of specifying such condition I got an error as interpreter not identify to which value assign to whom?

    Please give a valid solution on this