Pivoting a table and handling its output via tMySqlOutput

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.

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.
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
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
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

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER 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

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Downloads and Trials

Test drive Talend's enterprise products.

Downloads