Best Way to apply SQL logic to a txt file

Four Stars

Best Way to apply SQL logic to a txt file

Hello, 

I have joined 2 .txt files in talend using tmap into new .txt file.  I would now like to apply very specific logic as well to this new .txt file and the current logic is in SQL.  (My goal here is to use Talend instead of SSIS).  See job snapshot below.

 

https://www.screencast.com/t/VwDsPsTY7Z7A

 

Anyone have suggestions on the best way to apply this logic?  Looking to what component(s) I should use.

Thanks!

 

concat(A,B) = (select concat(B,C) )

from (select D, MAX(H)) DATE 

from (SELECT U,V)

FROM xyz

WHERE

S= A and

F= G AND

V <= R

) X

 

 

 

 

Community Manager

Re: Best Way to apply SQL logic to a txt file

I'm afraid your pseudocode does not make any sense. To start with, your brackets do not match up. Can you give us an example with data of what you want to achieve. You can do this sort of stuff, but I can't figure out exactly what it is you want to do.

Four Stars

Re: Best Way to apply SQL logic to a txt file

Hi there, 

Thanks for responding.  I was trying to insert letters as the alias field name and table names to avoid uploading private data, but I guess I made it more confusing.

The sql code shouldn't be as of concerning to my question.  

 

What I'm struggling with is how to take 1 large .txt files that I have created and then insert a sql query back to another .txt file.

So this 1st link below shows that I joined 2 text files that created the 1 large text file

https://www.screencast.com/t/lFmXPd2n5

 

Now I want to take that large text file and apply sql logic (as it's already done) BACK to the acctjoin file that is in the 1st snapshot.

 

Maybe I'm going about this wrong?

I'm trying not to use Java expression builder as I think it will take me longer to learn that than use a sql query.

Did I confuse you more or ................

HA

Please advise.  Thank you sir  :-)

 

 

 

 

 

Community Manager

Re: Best Way to apply SQL logic to a txt file

I see. I would highly recommend you learn to use Java if using Talend. It really does help with problems like this and is far more flexible than SQL when manipulating data. However, I understand that it can be quite daunting to learn a new language. Another way to get around this problem of yours is to write your data to a database and then read the data back in with your required SQL. 

 

Four Stars

Re: Best Way to apply SQL logic to a txt file

Ok yes, I plan to learn more java as it looks I seem to always have advanced logic that will require it in my jobs.

I did, however, attempt to create a table on our AS400, but the file was too large to create.  I used the tcreatetable component.

Is tcreatetable the component you would use to create databases?

Thanks again for your help.

Any suggestions help me at this point.............Our SSIS package is crashing and taking way too long and I think Talend could do this faster and more efficiently.

Community Manager

Re: Best Way to apply SQL logic to a txt file

How big is the file? You can create a table using the tAS400Output component to create a table. Look at the "Action on Table" option. You can select "Create table if does not exist" to create the table if it does not exist.

Four Stars

Re: Best Way to apply SQL logic to a txt file

The file has 1.4 Billion rows and is 132,338,514 KB.

I will try your other suggestion here and see if it will work from that component.

Thanks!

I'll advise.

 

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 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog