Kettle '?' not working Table Input Step

3
votes

I want to get all the table names from the database and then get all the rows from the tables. So I created a transformation like this:

  1. Get Table Names: Added the database connection and stored the table name in a output field called "tablename".
  2. Table Input: Marked "Replace variables in script" and "Execute for each row". Added the first step in "Insert data from step". SQL is "SELECT * from ?".
    I have read up a lot of tutorials online, including the documentation.
    My problem is that everywhere it says that I my "?" should be replaced with the parameter. But this does not happen. Here are the logs:

    2013/06/22 03:33:25 - Get table names.0 - Starting to run...
    2013/06/22 03:33:25 - Postgres 9.1.9 RO - read :9 table names from db meta-data.
    2013/06/22 03:33:25 - Table input.0 - Query parameters found = [stackexchange2]
    2013/06/22 03:33:25 - Table input.0 - SQL query : SELECT * from ?
    2013/06/22 03:33:25 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unexpected error
    2013/06/22 03:33:25 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
    2013/06/22 03:33:25 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : An error occurred executing SQL:
    2013/06/22 03:33:25 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : SELECT * from ?
    2013/06/22 03:33:25 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : ERROR: syntax error at or near "$1"
    Position: 16
    2013/06/22 03:33:25 - Table input.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :

I am using Kettle 4.4. Downloaded the spoon client from here.

UPDATE
I just want to make this work. I am learning the tool right now, and it would be good to know how '?' works.

2
pentahokettle
Kettle is a metadata oriented ETL tool. Table metadata is initialized during transform initialization. You would never write one transform that operates on all tables in a database. Can you say more about what you're really trying to acccomplish? – Brian.D.Myers
Why not? I am testing the software. I want to be able to go through all the tables. My SQL statement might change to do something meaningful. But for now, I am following the tutorials, so this should work right? – SeeknInspYre
And what's the best way of going through the tables? – SeeknInspYre
Which tutorial are you following? – Brian.D.Myers
You can just see the documentation. Below they have an example where they show how '?' works. – SeeknInspYre

2 Answers

2
votes

To solve your situation, i prefer to work with jobs, please find {kettle_intalation_folder_path}/examples/jobs/process all tables/Process all tables.kjb, because your case is a simplification of that example.

0
votes

Actually you can do this and you absolutely would do this. Take a look at "Metadata Injection".

Also you can't parameterise a table name - jdbc doesnt allow this. However you can get away with using the SQL step rather than table input and generate the whole SQL string in a field if you really want to. Wouldnt really recommend that though.

As said above, please describe further exactly what you're trying to do. If you're just migrating a database without doing any transformation of the data, i.e. from one db to another, then dont bother with Kettle as thats not what it's for.