Handle table with 21 million records...

One Star

Handle table with 21 million records...

Hi

I already put post asking how can I pass "parameters" to queries and that I already know how to do. I just need to iterate some input component with the tDBInput and in the where clause inject the information I need,, thats simple.


I will describe my case scenario:

I have one directory with files that will be imported in the Data Base.
In this example I make a inner join with the data that's in the Data Base (21 million records). This is too slow and the memory crashes ...

(See image 1 in attach)

What I want ...

I want to do the same process with the advantage of passing the parameters to the tDBInput.
How can I do that? If I pass one "iterate" from the tFileInput to the tDBInput I can pass the parameters to the where clause, but my question is how I achive my "row1", that is my data flow... how can I pass the information of that line of the file to the tMap (as a main flow) and then pass to the same tMap one "lookupFlow" coming from the tDBInput. (see image 2 in attach)


Thanks in advance Smiley Happy
Employee

Re: Handle table with 21 million records...

Hi,

I'm not sure to understand your process. What is the purpose of joining files with your 21 million records table before loading them in the database ?

Do you want to check lines that match with rows in the DB ? Perhaps you'd better use files as lookup instead of having a 21 million rows lookup table.


Best Regards
One Star

Re: Handle table with 21 million records...

rbillerey wrote:
Hi,

I'm not sure to understand your process. What is the purpose of joining files with your 21 million records table before loading them in the database ?

Do you want to check lines that match with rows in the DB ? Perhaps you'd better use files as lookup instead of having a 21 million rows lookup table.


Best Regards

Yes my first approach was to pass the table to one CSV, and that file take a lot of time and memory to be loaded and used in the tMap.

What I need is (see image in attach)... I have this process, but this process need to load all the data in the table to be able to do the tMap. How can I only select in the tDBInput the records that i want, using an where clause? To do that I need to link an "iterate" to the component. I want to do that process line by line, and inject the where clause with data that is on the line of the file.

I hope you understand Smiley Happy
Employee

Re: Handle table with 21 million records...

Hi,

Having such a tDbInput as lookup cannot work. What kind of join do you process in the tMap ( an example is welcome ) ? Is it possible to make the join process in the SGBD ? In that case, we could use bulk components to load the files in the database.

Best Regards.
One Star

Re: Handle table with 21 million records...

The join is simple, I just see if the data that's coming from the file exists in the Data Base, so I join some fields of the file with some of the Data Base. (see image in attach)

In the select statement, if I could inject the where clause with the data coming from the file that will be great :-)

How are you suggesting that I use the bulk components?In my case is an Oracle Data Base, so I must use the tOracleInput or the tOracleRow?How will this be different?
One Star

Re: Handle table with 21 million records...

the image that's missing in the previous reply .... this image is tMAP
One Star

Re: Handle table with 21 million records...

hbap wrote:
the image that's missing in the previous reply .... this image is tMAP

Now the image is fine ... Smiley Happy
One Star

Re: Handle table with 21 million records...

hi hbap.
did you solve your problem? I have the same and interested to know some.

bye

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

Have you checked out Talend’s 2019 Summer release yet?

Find out about Talend's 2019 Summer release

Blog

Talend Summer 2019 – What’s New?

Talend continues to revolutionize how businesses leverage speed and manage scale

Watch Now

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog