Problem comparing 2 files with lot of data

Shell Programming and Scripting
1

Hello everyone, here's the scenario

I have two files, each one has around 1,300,000 lines and each line has a column (phone numbers). I have to get the phones that are in file1 but not in file2. I can get these phones trough Oracle but my boss does not want that so he gave me the files with the phone numbers (he said it will take hours to finish the query and that will reduce the server resources or something like that).

First I tried to solve the problem with some perl scripting but it took like 10 minutes just to read the files and because my poor programming skills i tried to do the search with a double foreach, something like this:

@file1 = <SOME1>;
@file2 = <SOME2>;
$n = 0;
$flag = true; #if $flag = false then the element is in file2

foreach $row1 (@file1)
{
foreach $row2 (@file2)
{
if($row1 == $row2)
$flag = false
}
if($flag)
{
$anArray[$n]\=$row1; #ignore the backslash please
$n++;
}
$flag = true;
}

if($n > 0)
{
foreach $row3 (@anArray)
{
print OUT_FILE "$row3\n";
}
}

The data from the files is like this:

FILE1
----------------------------
1234567890
0987654321
2345678901
9012345678

FILE2
----------------------------
1234567890
0987654321
2345678901

OUT_FILE must be
----------------------------
9012345678

but this solution wil take ages to finish so now i am thinking in using awk or another lenguage but i really don't know which one is better for this problem and what algorithm i should use (besides i have never used awk or shell scripting, I'm new using UNIX), I was thinking in sort the files and then do a binary search but i have some doubts about it so i feel really lost now

Thanks for your help

2

  1. sort both files using "sort".

  2. then use "diff" to show the differences.

3

You can try something like the below

your_path is the path where there is enough space to execute the sort command for your huge files.

cat FILE1 FILE2 | sort -T your_path | uniq -u 

(or) to avoid UUOC

sort -T your_path FILE1 FILE2 | uniq -u
4

Try grep..

$ head file[12]
==> file1 <==
1234567890
0987654321
2345678901
9012345678

==> file2 <==
1234567890
0987654321
2345678901
$ grep -v -f file2 file1
9012345678

Not sure of the performance on large files. I think an Oracle SQL query would be better, e.g. select num from tab1 where num not in (select num from tab2)

5

Thanks to porter lorcan and ygor

Porter, your solution was not possible to perform because it needs a lot of memory (diff uses 6 times the size of the file in memory), and bdiff didn�t get exact results because the fragmentation of the files.

Lorcan: your solution worked great, it takes a few minutes

Ygor: after the problem with diff i was afraid to use greo so i didn't try it :slight_smile:

The solution i found was this: first i had to take all the blank spaces out with awk, then i sort the files and then use COMM to get the diferences, comm worked great and get the results in a few seconds

Thanks again for your help :slight_smile: