topic Re: [resolved] Handling large amount of data / 65535 bytes limit in Talend Studio

[resolved] Handling large amount of data / 65535 bytes limit

Anonymous — Sun, 23 Oct 2016 21:25:04 GMT

Hi, I'm encountering a Java problem with one of my jobs ... I have one table where I get data from and then from another table, I get a lot of data by lookup and enrich the resulting stream field by field. In fact, the second table is always the same, but each time it's a different key. Wordpress tables posts and postmeta, just in case you know them; I'm trying to extract woocommerce data from those two tables. It worked until a certain amount of fields.

The error I'm getting is:

Exception in thread "main" java.lang.Error: Unresolved compilation problem: 
	The code of method tMysqlInput_1Process(Map<String,Object>) is exceeding the 65535 bytes limit
	at academia.rechnungsexportv3_0_1.RechnungsExportv3.tMysqlInput_1Process(RechnungsExportv3.java:54426)
	at academia.rechnungsexportv3_0_1.RechnungsExportv3.tMysqlConnection_1Process(RechnungsExportv3.java:2961)
	at academia.rechnungsexportv3_0_1.RechnungsExportv3.runJobInTOS(RechnungsExportv3.java:50183)
	at academia.rechnungsexportv3_0_1.RechnungsExportv3.main(RechnungsExportv3.java:50040)

This is caused by serializing 46 lookups like that.
Do you have any idea how this can be optimized?
Oh, and I might need even more data from other tables.
If I may speak in visual Basic terms, each of those is like a "Dlookup". And in fact, I only need one value from each lookup. I can imagine, this would save a lot of resources, if something like that would be implementable in a tJava or tJavaRow component. And honestly, I must admit that I find the above architecture of the job a bit blown up with it's 46 boring fields. 😉
I appreciate any advice

Re: [resolved] Handling large amount of data / 65535 bytes limit

Anonymous — Sun, 23 Oct 2016 22:29:57 GMT

If you only need 1 value from each of the lookups (wouldn't it be more like one value and at least one lookup key?) then you can write the SQL inside the db components to only return the required the columns. You will have to ensure you have your schema columns set up accordingly, but this will remove a lot of lines of Java code.

Re: [resolved] Handling large amount of data / 65535 bytes limit

Anonymous — Sun, 30 Oct 2016 12:14:39 GMT

If you only need 1 value from each of the lookups (wouldn't it be more like one value and at least one lookup key?) then you can write the SQL inside the db components to only return the required the columns. You will have to ensure you have your schema columns set up accordingly, but this will remove a lot of lines of Java code.

Thank you for your suggestion - I think that's a direction to try. What I'm doing with those tFilterRow components is definitively something which I could do within the SQL statement of the respective tMysqlInput component, because each of the criteria is "hardcoded". I will give it a try, although it will take me a time. I will provide feedback. 🙂

Re: [resolved] Handling large amount of data / 65535 bytes limit

vapukov — Sun, 30 Oct 2016 12:43:22 GMT

more - all of this logic possible to pack as SQL code and run before Talend
it good to use Talend for lookup if You need -
read from MySQL and Lookup in Oracle (as example)
but when all tables on same schema (or same server) - much easy do it all in SQL

Re: [resolved] Handling large amount of data / 65535 bytes limit

Anonymous — Sun, 30 Oct 2016 22:21:01 GMT

Thank you for your suggestions - I've got it resolved by eliminating the tFilterRows and putting the logic into SQL, it's working now. Brillant.