Inserting foreign keys by Kettle

Question

Inserting foreign keys by Kettle

Navigation

#1 by (1 votes)

0

I need to load some data that is in spreadsheets into a relational database, but I have been facing some challenges with regard to inserting foreign keys.

In these images you can see that I enter the project data first, the auto increment of the bank generates a key, I retrieve it in the table input and then I call this step with Excel Input of the relief data worksheet. >

I have only one project and several reliefs related to it.

The project data is in one worksheet and the relief data is in a different worksheet.

Attemptingtoinsertanyerrorsisreturned,butthereliefdataisnotincluded.It'saseeminglysimpleproblem,butI'mstartingtounderstandKettlenow.

Thankyouinadvance!

EDIT2:

chave-estrangeira pentaho-kettle pentaho

asked by anonymous 27.08.2018 / 00:37

1 answer

Hour chart in Highcharts? Count amount of element of an array of a page

score 1 · Accepted Answer

You are using multiple input steps in sequence:

Excel input - Table input - Table input - Excel input - Table input

Each time an input step is used in the flow, the table resulting from this input is reset, ie in the next step you only have what was added in the last input. What you need is for these 5 inputs to be made in separate flows in the same KTR, and unified by a common key, using the Join steps (I advise Multiway Merge Join, since there are more than 2 flows).

I also see that you are using the "Accept filenames from previous step" option in your Excel input. In this way Excel input will receive the absolute paths of the files by the table that is arriving in the input step, and will not use the desired path in the list of files / directories.

EDIT:

If no other update parameter is required, you can use the "Execute SQL statements" step. In this step you can perform queries with variable substitution, that is, table rows that are fed to this step will be part of Query. Inthequerythatyouwilluse,youmustputaquestionmarkintheattributesofthequery,these"?" will be replaced in the same order as the parameter list.

Eg: If a table of this way arrives in the step:

Withaquerylikethis:

Thequerywillbeexecuted2times,changingthe"?" in the order id_project, name and location.