Talend community forum / Efficiently load and compare data

williamwang · 2012-05-13 05:38:54

Hi,
This is what I want to achieve.
I load in a set of csv files and try to find out if any row exists in one of the tables (LOOKUP TABLE) in MYSQL.
Presume the lookup table has millions of rows, if I load everything using tMySQLInput, I am worried about the performance. Is that possible for me to feed the csv file entries as part of the QUERY string and let MySQL do the process which might be more efficient? If yes, how do I do that?

It will also be much appreciated if any one can suggest different ways of doing this.
Please see my solution attached.

Thank you very much.

William

Uploaded Images

pedro · 2012-05-14 04:32:06

Hi William

Without considering this job logic, for large tables, you might use 'Store on disk' feature of tMap and 'Enable stream' of tMysqlInput for better performance.

Besides, I know you want to feed the csv file entries to narrow down the rows from mysql.
But it might be a little difficult and cost extra memory.
If you can give the range of ids directly, it will be fine.

Regards,
Pedro

williamwang · 2012-05-14 04:53:51

Thank you very much for your reply, Pedro.
When you say give a range of IDs directly, do you mean I build a SELECT QUERY dynamically?
If yes, what components should I use? I believe tMySQLInput will be required with a WHERE clause.
How can I construct or build the WHERE clause in the query as a parameter? Do I have to use Context?
Thanks.

Regards,
William

pedro wrote:
Hi William

Without considering this job logic, for large tables, you might use 'Store on disk' feature of tMap and 'Enable stream' of tMysqlInput for better performance.

Besides, I know you want to feed the csv file entries to narrow down the rows from mysql.
But it might be a little difficult and cost extra memory.
If you can give the range of ids directly, it will be fine.

Regards,
Pedro

pedro · 2012-05-14 04:57:53

Hi William

I mean maybe all entries from csv files are between 1 and 1000000.
So we can edit the query of tMysqlInput.

Code:

Select * from table where id between 1 and 1000000

Regards,
Pedro

saukema · 2012-05-14 08:50:36

I remember that we once did a compare by dumping the MySQL table data to disk an make a compare on OS level

Announcement

Unanswered posts

#1 2012-05-13 05:38:54

Efficiently load and compare data

#2 2012-05-14 04:32:06

Re: Efficiently load and compare data

#3 2012-05-14 04:53:51

Re: Efficiently load and compare data

pedro wrote:

#4 2012-05-14 04:57:53

Re: Efficiently load and compare data

Code:

#5 2012-05-14 08:50:36

Re: Efficiently load and compare data

Board footer