Pivoting a table and handling its output via tMySqlOutput
Hey,
I've been trying to create a pivot table from a normal table. What I want is to take a table like this one:
itemType itemId fieldType fieldValue
Table 1 Legs "4"
Table 2 Legs "3"
Chair 5 Legs "4"
Table 1 Color "Red"
Chair 4 Owner "John"
and pivot it into a table like this one:
itemType itemId Legs Color Owner
Table 1 "4" "Red"
Table 2 "3"
Chair 5 "4"
Chair 4 "John"
The tPivotToColumnsDelimited component does exactly that, but its output is a .csv file and I need this to go into a MySql database. I can't import the .csv file because I don't know how many item properties I'll have (besides 'Legs', 'Color', 'Owner' there might be new ones added from time to time) so I can't create a schema for it.
Are there any other components that could do what tPivotToColumnsDelimited does for me? (I'm using v4.1.2 but I can upgrade/downgrade if needed)
could you not start by splitting the input into datasets: table chair then defining their schema into something that talend will know how to handle - so that you can use these schemas to load data into MySql? table ( itemId, legs, owner ) chair (itemID, color ) if you do not know the output - then you *have* to export into a CSV file as it has no restriction on the schema - just writes it out.
Hi
First of all, use tPivotToColumnsDelimited component to get the csv file.
Then You are able to use tFileInputDelimited to read the records back again and set a dynamic schema on tFileInputDelimited.
But the 'dynamic schema' feature is only available on commercial version Talend Intergration Suite.
Best regards!
Pedro
nicolasdiogo, pedro, thanks a lot for your answers.
Would it help if I was interested in specific items, and had a table that contained their item properties? that is, if I knew I was looking for 'chair' items only and had a table which contained their property names, like in the following table:
item itemProperty
chair Legs
chair Owner
chair Color
table Legs
table Color
