On Mon, Oct 22, 2012 at 2:57 PM, Dave Aronson &l= t;rubyta= lk2dave@davearonson.com> wrote:

On Mon, Oct 22, 2012 at 2:= 21 PM, Ruby Student <ruby.stud= ent@gmail.com> wrote:

> Every week I get a large file, over 50 millions records

The big question is... are these files SORTED, preferably on some
UNIQUE key, or at least some order that will remain the same from week
to week? =A0If yes, then you can use the same sort of techniques as in
the "diff" utility found on every Unix-derived system and many ot= hers.
=A0(Windows has something similar but the name escapes me at the moment. =A0IIRC, in an ironic twist, this is one of those cases where the
Windows command has a *more* cryptic name than its Unix cognate.) =A0How to make a "diff" type program has been covered in gazillions of blog/magazine articles, textbooks, etc., so I won't go into detail.
If you're lucky, you might even be able to just use the ones existing on your system, with some shell scripting for glue.

On the other claw, if the records are in random order, then you've got<= br> a much more serious problem. =A0In that case, ASSUMING that the keys,
and number of updated/duplicated records, are both quite small, off
the top of my head I think I'd:

- Extract the keys from last week's file
- Ditto for this week's
- Sort those, assuming the keys are sufficiently smaller that this is reaso= nable
- Diff them.
- Extract the actual records from both weeks for any matching keys.
- Sort and diff, under the same assumption.

Or, if the above data sets are not small enough to make sorting
reasonable, but the potential dups might at least fit in RAM:

- Extract last week's keys into a Set
- Initialize a "Needs Further Inspection" (NFI) Set
- Iterate over this week's records:
=A0 =3D Try to find the key in last week's Set of keys
=A0 =3D If seen, remove from last weeks and add to NFI Set
=A0 =3D Else process as an Insertion
- Anything left in last week's Set is a Removal
- (You can now get rid of last week's Set of keys)
- Extract last week's full records matching NFI keys,
=A0 putting them in a hash keyed by the key
- Extract this week's records matching NFI keys,
=A0 looking them up in the hash
- Compare the entire records, processing as either
=A0 Duplicate or Update as needed

-Dave

--
Dave Aronson, the T. Rex of Codosaurus LLC,
secret-cleared freelance software developer
taking contracts in or near NoVa or remote.
See information at ht= tp://www.Codosaur.us/.