Pivoting a table and handling its output via tMySqlOutput

Highlighted
One Star

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)

Thanks a lot in advance.
Highlighted

Re: Pivoting a table and handling its output via tMySqlOutput

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.
Highlighted
One Star

Re: Pivoting a table and handling its output via tMySqlOutput

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
Highlighted
One Star

Re: Pivoting a table and handling its output via tMySqlOutput

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
Highlighted
One Star

Re: Pivoting a table and handling its output via tMySqlOutput

Hi
You may use tMap component and set the 'out1' expression row1.item.equals("chair").
Best regards!
Pedro

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog