CODEWITHSUNDEEP
pentahopentaho-spoonpentaho-data-integration
eponkratova
eponkratova

Reputation: 477

Add new column to existing table Pentaho

I have a table input and I need to add the calculation to it i.e. add a new column. I have tried:

  1. to do the calculation and then, feed back. Obviously, it stuck the new data to the old data.

  2. to do the calculation and then feed back but truncate the table. As the process got stuck at some point, I assume what happens is that I was truncating the table while the data was still getting extracted from it.

  3. to use stream lookup and then, feed back. Of course, it also stuck the data on the top of the existing data.
  4. to use stream lookup where I pull the data from the table input, do the calculation, at the same time, pull the data from the same table and do a lookup based on the unique combination of date and id. And use the 'Update' step.

As it is has been running for a while, I am positive it is not the option but I exhausted my options.

Upvotes: 0

Views: 5134

Answers (3)

Cristian Curti
Cristian Curti

Reputation: 1054

I'd say your issue is not ONLY in Pentaho implementation, there are somethings you can do before reaching Data Staging in Pentaho.

  1. 'Workin Hard' is correct when he says you shouldn't use the same table, but instead leave the input untouched, and just upload / insert the new values into a new table, doesn't have to be a new table EVERYTIME, but instead of truncating the original, you truncate the staging table (output table).

  2. How many 'new columns' will you need ? Will every iteration of this run create a new column in the output ? Or you will always have a 'C' Column which is always A+B or some other calculation ? I'm sorry but this isn't clear. If the case is the later, you don't need Pentaho for transformations, Updating 'C' Column with a math or function considering A+B, this can be done directly in most relational DBMS with a simple UPDATE clause. Yes, it can be done in Pentaho, but you're putting a lot of overhead and processing time.

Upvotes: 0

Kauann H. de Oliveira
Kauann H. de Oliveira

Reputation: 81

It's seems that you need to update the table where your data came from with this new field. Use the Update step with fields A and B as keys.

Upvotes: 1

Helping Hand..
Helping Hand..

Reputation: 2440

actully once you connect the hope, result of 1st step is automatically carried forward to the next step. so let's say you have table input step and then you add calculator where you are creating 3rd column. after writing logic right click on calculator step and click on preview you will get the result with all 3 columns

Upvotes: 0

Related Questions