AnsweredAssumed Answered

Compare two files using AE scripting

Question asked by shravani_vedantham_6826 on Apr 10, 2018
Latest reply on Apr 17, 2018 by ShravaniVedantham613199

Like • Show 0 Likes0
Comment • 6

Hello all

I have a requirement to compare two files, File 1 and File 2 and create a new file with the records unique in File2.

The files are very huge and have millions of rows in them. I tried using shell script but it is taking hours to complete. Is there a way we could do this using AE scripting and if you can give me a hint , that would be great.

Thanks!

No one else has this question

Outcomes

6 Replies

Pete Wirfs Apr 10, 2018 4:43 PM
Mark Correct
Correct Answer
I don't think AE scripting would be a good choice. It would be very slow. The scripting language periodically pauses long running scripts so the rest of the AE responsibilities can be given the attention they deserve.

It would be better to have the AE execute an external solution that compares the files and writes a delta file.
Like • Show 0 Likes0
Actions
shravani_vedantham_6826 Apr 10, 2018 4:55 PM
Mark Correct
Correct Answer
@Pete Wirfs Hmm.. It is the same thing with Shell scripting. It is taking hours .
Thanks Pete. I will see if I can change the SQL itself to get the desired data.
Like • Show 0 Likes0
Actions
Pete Wirfs Apr 10, 2018 4:59 PM
Mark Correct
Correct Answer
To return unique rows from the 2nd result set;

Select *
from (resultset2)
where not exists
(select 1 from (resultset1)
where resultset1_key = resultset2_key)
Like • Show 0 Likes0
Actions
shravani_vedantham_6826 Apr 10, 2018 5:10 PM
Mark Correct
Correct Answer
Thank you. @Pete Wirfs
Like • Show 1 Like1
Actions
Carsten_Schmitz Apr 11, 2018 7:39 AM
Mark Correct
Correct Answer
Hi.

Unfortunately, you neglected to tell us about your platform.

First off, as petwir already said, AE scripting is a very bad choice if you want performance (I know, I've done benchmarks). What sort of shell script did you try?
- one, when it's just about comparing files, there's diff and sdiff and friends. These should compare files several gigabytes in size without major issues, at least on a UNIX OS. Have you looked into these?
- second, when people claim shell script is slow for file operations like comparisions, it's usually because they needlessly iterate over lines, or fork useless processes. Forking processes is expensive. Example: People do this:
  
  cat file | grep pattern1 | grep pattern2
  
  ... and say that's slow. Forking the greps is slow, and it's a useless use of "cat". This is much faster:
  
  grep -E "pattern1|pattern2" file
  
  if that still doesn't cut it, look into perl or python. Given sufficient RAM, I am positive you can diff gigabytes if not terrabytes of data without major issues if you choose the correct tool for it. Perl, especially, is good for this.
- Third, one thing I did once when I had to cross-reference several hundred gigabytes of data was to load the data into an ad hoc sqlite database, then operated on that with SQL. That offered great performance. Seems overkill for comparing two files though, you'd be fine when chosing the correct shell-adjacent utilities imho.
Hth,
Carsten
1 person found this helpful
Like • Show 1 Like1
Actions
ShravaniVedantham613199 Apr 17, 2018 9:05 PM
Mark Correct
Correct Answer
@Carsten : Thank you for all the information.
Our platform is Linux. I agree with all that you mentioned. I tried shell scripting using the second grep option above. it still took long time. diff, and sdiff wouldn't help in my case as I want a clean file with all the unique details. I would have to again edit to format the file.
And, I did not try perl , I am sure I can get a script online for this. I will give it a try. for now, I managed to change the SQL we were using. It will help me for my future developments
Like • Show 0 Likes0
Actions

Compare two files using AE scripting

Outcomes

Related Content

Recommended Content