Mapping to a database table

For some reason, the creation of a mapping to a database table poses a problem for certain people.

This is how it’s done in PDI 3.2.0 or later in the “Table Output” step:

Ogg video available over here

Until next time,

4 thoughts on “Mapping to a database table”

  1. Well, if it only was that easy. I am trying to do a mapping from a transformation into a mysql table. I am only populating a few columns in this table. When I try to run it, the sql alters the structure of the destination table to the data types of the source columns, and also drops all the columns in the destination table that are not being mapped to. Also any mappings to columns do not preserve the destination name, but rather drop the destination column and recreate it as the source column. the SQL is rather ugly:

    ALTER TABLE products_staging DROP valuename

    ALTER TABLE products_staging MODIFY sku1 TINYTEXT
    ALTER TABLE products_staging MODIFY sku2 TINYTEXT
    ALTER TABLE products_staging MODIFY sku3 TINYTEXT
    ALTER TABLE products_staging MODIFY Product TINYTEXT
    ALTER TABLE products_staging MODIFY Liter TINYTEXT
    ALTER TABLE products_staging MODIFY `Size/Liter` TINYTEXT
    ALTER TABLE products_staging MODIFY Suggested TINYTEXT
    ALTER TABLE products_staging MODIFY Retailer TINYTEXT
    ALTER TABLE products_staging MODIFY ProductCase TINYTEXT
    ALTER TABLE products_staging MODIFY RetailerUnit TINYTEXT

    How do I stop Kettle from modyfing my table and only populating the columns that I specify in the mapping?

    Also, if the mapping is to a primary key, the transformation is not even working, because the sql tries to alter the pk column.

    It seems like I am seriously missing the point here, but that is maybe due to just starting out with pentaho.

    I simply want to move two fields (pk, and another field) into the destination table, where it needs to create records, and convert the values into the corresponding types. (string to integer).

    I am not quite understanding what is going on with all the alter statements. Someone said that you can modify the SQL that is being generated somewhere, but I dont see a place where that can be overriden. Any help would be appreciated.

  2. Paul, the SQL generated is a suggestion from PDI as to how it thinks the table should look like.
    First of all: you pressed the button, it’s a manual process. You don’t have to do that.
    Second, the SQL can indeed be modified to your liking if you want. Simply edit the SQL the first time you see it.
    I didn’t think it could get any easier.

  3. Hello Matt,

    I’m just started working with Pentaho (the entire suite) and was specially charmed of PDI. However, I disliked the use of the “Select Vaules” transformation in the previous version, although it does what it has to do. With the new “Table Output” functionality, it indeed has become a lot easier to map the values to the target table.

    @Paul: The SQL button might be handy when you are building a transformation and do not have the target table, yet. In that case, it gives you a possible SQL query to build the table. In your case, where the target table already exists in the database, there is no need to use the SQL option. Just click “Get Fields” or (maybe better in your case) “Enter Field Mapping” and you’re done. Good luck.


  4. Hi Gerald,

    Software evolves and PDI especially evolves pretty fast also because of the many community contributions.
    People sometimes think that things aren’t moving fast when they look at the ever growing stack of feature requests out there in JIRA.
    However, good ideas do tend to get picked up and over the course of a year for example, you see the changes.

    Have fun with PDI!


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.