topic Re: Dynamic table and column names to one table and rows? in Talend Studio

Dynamic table and column names to one table and rows?

Anonymous — Mon, 16 May 2016 08:06:03 GMT

Hi all,
Newbie to Talend and have spent the last week on this one. I can't seem to figure this out and perhaps it's not even possible. The topics I've found here in the forum all seem to speak to fixed column names and pivoting to normal files. So truly any help is appreciated. All of this is pertains to MYSQL.
Tables hold the results from measurements. Each measurement table name is formatted like "analysis_"{analysis ID}. ie . analysis_12345. I can use a context variable to query the table.

What I can't seem to figure out is columns names within each table are structured according to the following schema:
analysis X group of question X question
...so a column name will look like "analysisXgroupingXquestion" or 12345X45X4. "X" is the separator.

Table "analysis_12345" holds these columns and response data->
ID|12345X45X3|12345X45X4|12345X65X12SQ1|12345X65X12SQ2
1|4|7|2|1|
2|6|3|4|7|
Table analysis_35748 for example might be ->
ID|35748X12X6SQ1|35748X12X6SQ1|35748X3X1SQ1|35748X3X1SQ2|35748X7X7SQ1|35748X7X7SQ2
1|1|5|7|3|6|4|
2|7|4|4|2|1|7|
Each analysis table can have very different column names.
I would like to pivot each analysis table into one table. So for analysis_12345:
Response|measurement|group|question|score
1|12345|45|3|4
1|12345|45|4|7
1|12345|65|12SQ1|2|
1|12345|65|12SQ2|1|
2|12345|45|3|6|
2|12345|45|4|3|
2|12345|65|12SQ1|4|
2|12345|65|12SQ2|7|
Or analysis_35748
Response|measurement|group|question|score
1|35748|12|6SQ1|1|
1|35748|12|6SQ1|5|
1|35748|3|1SQ1|7|
1|35748|3|1SQ2|3|
1|35748|7|7SQ1|6|
1|35748|7|7SQ2|4|
2|35748|12|6SQ1|7|
2|35748|12|6SQ1|4|
2|35748|3|1SQ1|4|
2|35748|3|1SQ2|2|
2|35748|7|7SQ1|1|
2|35748|7|7SQ2|7|
I wrote a few routines to extract the new columns names from the existing... like this for example...

   public static Integer get_measurementiID(String columnname){
       
        if (columnname != null) {
           String[] explode=columnname.split("X");
           return  Integer.parseInt(explode);
       }
            return null;
       }

then for group and question.. etc ...
but my dilemma is actually getting Talend to iterate through the columns and actually output the new table. I'm absolutely lost. If anyone has some ideas or at least help point me in the right direction, I would be grateful!
Thanks

Re: Dynamic table and column names to one table and rows?

Anonymous — Tue, 17 May 2016 04:18:56 GMT

Hi,
It seems that you are looking for converting columns to rows?
Have you already checked this KB article about:TalendHelpCenter:Converting columns to rows to see if it works?
Best regards
Sabrina

Re: Dynamic table and column names to one table and rows?

Anonymous — Tue, 17 May 2016 19:03:01 GMT

Hi Sabrina and thank you for your reply.
Yes, I think I've looked at every post I could find on the subject. The problem is, I have no idea what the table schema will look like ahead of time..each one is different. ie. How many columns, or column names. Just an understanding of the format of the columns and their names if that makes sense.
I almost need to loop through the schema storing the column names somewhere, then, iterate through that list while iterating through each row of the table itself to collect values and output all of that into a MySQLtable in a normalized sort of format.
The closets method I can find is http://bekwam.blogspot.com/2011/06/dynamic-schemas-in-talend-open-studio.html but idea if it will actually work.
Does that make sense?
Thanks,
Gary

Re: Dynamic table and column names to one table and rows?

Anonymous — Tue, 17 May 2016 22:30:32 GMT

If you're using Talend's Enterprise version, you can use the Dynamic column type to do what you want.
here's some sample code I use to generate table create statements for one of my dynamic table loaders:
in a tJavaFlex:
Begin:

// start part of your Java code
int ncount=0;
String columnName="";
DynamicMetadata column;

Boolean firstRow = true;
String create = "CREATE TABLE " + "\"" + context.TABLENAME + "\"" + " ( ";

ncount=dynamic_tFileInputDelimited_2.getColumnCount();
int counter = 0;

Main:

/* Empty */

End:


for( int columnIndex = 0; columnIndex < ncount; columnIndex++ ) {

	column=dynamic_tFileInputDelimited_3.getColumnMetadata(columnIndex);
	columnName=column.getName();
	if( context.ROW_LEVEL_LOGGING ) {
		System.out.println(columnName + " index: " + columnIndex + "counter: " + counter);
	}
	
	if( (null != columnName) && (!"".equals(columnName)) ) {
		if( firstRow ) { 
			create += "\"" +columnName + "\"" + " VARCHAR ";
			firstRow = false;
		}
		else { 
			create += " , " + "\"" + columnName + "\"" + " VARCHAR "; 
		}
	}
//	System.out.println("K:" + columnName + " V:" +columnValue);
		counter++;
}

create += " ) ";
if( context.ROW_LEVEL_LOGGING ) {
System.out.println("Generated DDL: " + create );
}
context.CREATE_TABLE_STATEMENT = create;

Re: Dynamic table and column names to one table and rows?

Anonymous — Wed, 18 May 2016 18:42:56 GMT

// start part of your Java code
int ncount=0;
String columnName="";
DynamicMetadata column;

Boolean firstRow = true;
String create = "CREATE TABLE " + "\"" + context.TABLENAME + "\"" + " ( ";

ncount=dynamic_tFileInputDelimited_2.getColumnCount();
int counter = 0;

Main:

/* Empty */

End:


for( int columnIndex = 0; columnIndex < ncount; columnIndex++ ) {

	column=dynamic_tFileInputDelimited_3.getColumnMetadata(columnIndex);
	columnName=column.getName();
	if( context.ROW_LEVEL_LOGGING ) {
		System.out.println(columnName + " index: " + columnIndex + "counter: " + counter);
	}
	
	if( (null != columnName) && (!"".equals(columnName)) ) {
		if( firstRow ) { 
			create += "\"" +columnName + "\"" + " VARCHAR ";
			firstRow = false;
		}
		else { 
			create += " , " + "\"" + columnName + "\"" + " VARCHAR "; 
		}
	}
//	System.out.println("K:" + columnName + " V:" +columnValue);
		counter++;
}

create += " ) ";
if( context.ROW_LEVEL_LOGGING ) {
System.out.println("Generated DDL: " + create );
}
context.CREATE_TABLE_STATEMENT = create;

Thanks so much John,
This looks pretty close to what I"m trying to accomplish. I think the wall I keep running into it that Open Studio can't work with dynamic schemas. I've now been playing with tMysqlColumnlist into a tSetGlobalVar trying to work around this limitation.
Have you ever been able to do something like this within the Open Studio product? With 900 connectors and even a tJavaFlex component, I seem to think it must be possible...haha

thanks again for sharing!
Gar

Re: Dynamic table and column names to one table and rows?

Anonymous — Wed, 18 May 2016 22:30:52 GMT

Talend has to give you some reason to buy it
with open studio you could do something similar, but you'd need to parse the file yourself and forego the use of schema's all together -- at that point you'd be writing your own program in a sense, so it may be a better idea to just do that anyway. You can still integrate it into jobs if you set it up as a Talend Routine.

Re: Dynamic table and column names to one table and rows?

Anonymous — Tue, 21 Jun 2016 07:42:32 GMT

// start part of your Java code
int ncount=0;
String columnName="";
DynamicMetadata column;

Boolean firstRow = true;
String create = "CREATE TABLE " + "\"" + context.TABLENAME + "\"" + " ( ";

ncount=dynamic_tFileInputDelimited_2.getColumnCount();
int counter = 0;

Main:

/* Empty */

End:


for( int columnIndex = 0; columnIndex < ncount; columnIndex++ ) {

	column=dynamic_tFileInputDelimited_3.getColumnMetadata(columnIndex);
	columnName=column.getName();
	if( context.ROW_LEVEL_LOGGING ) {
		System.out.println(columnName + " index: " + columnIndex + "counter: " + counter);
	}
	
	if( (null != columnName) && (!"".equals(columnName)) ) {
		if( firstRow ) { 
			create += "\"" +columnName + "\"" + " VARCHAR ";
			firstRow = false;
		}
		else { 
			create += " , " + "\"" + columnName + "\"" + " VARCHAR "; 
		}
	}
//	System.out.println("K:" + columnName + " V:" +columnValue);
		counter++;
}

create += " ) ";
if( context.ROW_LEVEL_LOGGING ) {
System.out.println("Generated DDL: " + create );
}
context.CREATE_TABLE_STATEMENT = create;

what is your input data and your workflow look like, John?
I can not imagine how the code can transform your file data.
thank you