Search code examples
pentahopentaho-spoonpdi

Dynamic Table name generation results in "Table Not found"

Using Pentaho and Spoon. i have a combination of transforms that

  1. Creates a new table name variable (TABLENAME)

In a separate transformation I'm attempting to use this table name

  1. first step creates a list of column names (generate rows step)
  2. second step uses these table rows and the table name from the previous transformation (Table output step)

When i click the SQL button on the Table Output Step, it returns the correct create table statement. (although it doesn't replace the placeholder, it does return a CREATE TABLE....)

when i attempt to run these two transformations in-line, it complains that the table with my generated name does not exist. It appears that instead it must be trying to INSERT into this generated table, when the sql button returns a CREATE.

how do i make pentaho create a table with a generated name stored in a variable?

Solution

  • The Table Output step doesn't create the table, the SQL button is like a help button, it only allows you to copy the DML to create the table and paste it to wherever you want to run it, but that step doesn't run the create table order.

    You'll need a separate transformation or job to create the table before inserting into it. For jobs there's an action to run SQL statements, one similar step for transformations.