"Table exists" step in Pentaho Kettle

Question

I want to use "Table exists" step to check if certain table exists if not then create one

The transformation I have created (in order to copy data from input database into output database)

Table Input ----->  Table exists  ----> Table output

The properties in "Table exists" are

Step name:
connection:
Schema name:

Tablename field:
Result fieldname:

The first three are clear. The last two

What does "Tablename field" mean ? It is a drop list with limited options which are column names of input table. Should it be the table name I want to check in connection ?

What is Result fieldname ? How can I use that ?

Answer 1

• Tablename field is the table it checks for.
• Result fieldname The name of the field that will contain the boolean result flag in the output of this step.

Use the following sequence to check and create table if not exist.

Table Input - Get the data in, from the database.

Generate rows - Specify tablenamefield as name and table_name_to_check as value and String as type. Later we can use the field tablenamefield in Table exists step

Table Exist - Select tablenamefield which appears in the Tablename field drop down. Write any name as the Result fieldname i.e result

Switch/ Case - Type Y in case values and Table Output as the target step. Make execute SQL statement as Default target step. field name to switch is result

Execute SQL statement - Write your table creation SQL query.

Table Output - Writes to the new table.

NB: If you don't want to check whether the table exist for each row. you can use a job and check for the table existence in first transformation and Table input and output steps in second transformation which I recommend for performance.

Answer 2

the Tablename field is the field that contains the name of the table(s) you want to check. If you only want to check a single table, you only need a single row with the table name in one of the fields.

The Result fieldname is the name of a new field the step is going to add to the step output. That new field contains the result of the check. It is a boolean field you can then use in later steps, for instance to send this row into a branch that creates the table. The field value is true if the table exists and false if the table doesn't exist.