CODEWITHSUNDEEP
perlfile-processingfilemerge
sfactor
sfactor

Reputation: 13062

How to perform a SQL-like Join in Perl?

I have to process some data by combining two different files. Both of them have two columns that would form a primary key that I can use to match them side-by-side. The files in questions are huge (around 5GB with 20 million rows) so I would need an efficient code. How would I do this in Perl?

I give an example:

If File A contains columns 

id, name, lastname, dob, school

File B contains columns 

address, id, postcode, dob, email

I would need to join these two files by matching id and dob in the two files to have an output file that would have the columns:

 id, name, lastname, dob, school, address, postcode, email

Upvotes: 8

Views: 779

Answers (6)

KneLL
KneLL

Reputation: 551

Also, you can try DBD::AnyData

Upvotes: 0

Darren Duncan
Darren Duncan

Reputation: 1

You can also use my 3-year-old CPAN module Set::Relation which is designed to do things like this, letting you do all the SQL features such as join in Perl. Create a Set::Relation object for each file and then use the join() method. That said, this module as-implemented will keep all your operands and result in memory, so it is limited by your RAM. But you can still look at its source for how join() works and then implement a more efficient version for your purposes based on it.

Upvotes: 0

Øyvind Skaar
Øyvind Skaar

Reputation: 2328

I haven't actually tried this, but a more creative solution could be:

  1. Read each file once and create a map between the unique id+dob combinations and their positions in the file. Use tell().
  2. Create the map in perl
  3. Read the actual data from the files using the positions int he map and sysread()
  4. Write the data to a new file

Upvotes: 0

Bill Ruppert
Bill Ruppert

Reputation: 9016

The old fashioned way to do this is to use system utilities to sort both files in key sequence and then match them line by line. Read both files, if the keys match output the data. If they don't match, read the file with the lesser key until they do match. Set the key infinitely high for a file if it hits eof. When both keys are infinitely high, you're done.

Upvotes: 2

Alien Life Form
Alien Life Form

Reputation: 1944

Or, peruse this nice Techrepublic article - you are still liable to need 5G of memory, though. I wonder where using the unix/linux CLI sort/join utilities would take you, efficiencywise. Just a thought.

Upvotes: 0

Øyvind Skaar
Øyvind Skaar

Reputation: 2328

Think I would just create a new mysql/sqlite/whatever DB and insert the rows. Should be ~20 lines of perl.

This, of course, requires easy access to a DB..

Guess you could also sort the files by the interesting fields and then for each line in file1 find and print the matching lines in file2.

Upvotes: 7

Related Questions