0

I have a massive flat text file with ~4M records that I would like to read into MySQL using Ruby/Rails, Python, or any other method.

The flat file is roughly this format: business_name,address,city,state,zip,employee_name

If there are multiple employees at the business, there will be one row for each employee with the business name, address, etc. repeated.

I'd like to read this in to two tables structured with the employees table having a foreign key like so:

businesses (id int, name varchar, address varchar, city varchar, etc.)
employees (id int, name varchar, business_id int)

How do I go about efficiently reading this file in so that the employees/businesses are created and referenced as needed?

Note: it is NOT safe to assume that the file is sorted.

I've tried reading the file into SQL first and then using SQL to get a unique list of business_name/address/city/state combinations that I could then insert into the businesses table, but my server runs out of memory. And this is only with 1/4th of the records loaded.

migrated from stackoverflow.com May 10 '14 at 13:59

This question came from our site for professional and enthusiast programmers.

  • 1
    This is probably a better question for Database Administrators – Mike W May 10 '14 at 5:22
  • I considered reading into a NoSQL database first, since I think that would be much more efficient, and then programmaticaly dumping that to CSV files for importing (since I think that's probably faster than running millions of SQL INSERT statements). Thoughts? – jwoww May 10 '14 at 5:22
  • The format you provided is already CSV. – Shiplu Mokaddim May 10 '14 at 5:23
  • Yes, to clarify, I would dump two CSV files. One with a unique list of businesses to load (with their primary key IDs) and another with a list of employees and their business_ids. So basically transforming the original CSV into two separate CSVs that represent the relational tables. – jwoww May 10 '14 at 5:33
  • Are the id numbers already in the text file? – Mike Sherrill 'Cat Recall' May 10 '14 at 5:43
2

Try using LOAD DATA INFILE into an ISAM table with few or no indexes. ISAM should reduce memory consumption and get all data in there the fastest possible way. I have never run out of memory when doing this with larger data sets than yours, on pretty old servers with alot less memory than is used today.

Once the data is imported, use ALTER TABLE to add required indexes, in essence everything needed to uniquely identify a business (name, address, state etc). This may take some time.

Create another ISAM table having an auto increment primary key as well as all columns from the first table which are required to uniquely identify a business (e.g the ones that were indexed, but nothing related to employees). Add a UNIQUE key which includes ALL all columns in the table except the primary key.

Use REPLACE INTO to fill the second table with data from the first, and you will in the process generate unique numeric keys for all the businesses. Add the key column to the first table and UPDATE it using data from the second, which then can be dropped afterwards.

From there you should have a table where all businesses have unique, numeric and indexed keys and should easily be able to split the data into the (InnoDb) tables you require.

  • This seems like a great approach, leveraging the inherent capabilities of the database, but I didn't quite get it to work. I read up on REPLACE INTO and created the UNIQUE key, but when the query finished, none of the duplicates had been eliminated. What am I doing wrong? SQL snippet of my table structure: snipt.org/Ujmh9 Note that I put phone in the table but not in the unique key, because the phone numbers are unique to employees, but I wanted each business to have at least one phone number associated with it. – jwoww May 11 '14 at 5:08
  • Never mind. User error. Missed one of the columns in the transfer from one table to the other. Now working perfectly! – jwoww May 11 '14 at 5:36
  • Glad to hear that it worked out! – Dag Sondre Hansen May 11 '14 at 16:05
0

I would make two passes through the input the file in a script, and generate two output .sql files, one for business and one (or more) for employees.

On the first pass, just build inserts for the businesses table. During the processing keep track of names of businesses you've already inserted and skip them when they show up in subsequent rows.

On the second pass, generate inserts for the employees table from each row, setting the FK to the businesses already in the database. You could use a subselect to find the business row id by business name for the employee insert statements.

If you used the subselect approach the employee inserts would look like:

INSERT INTO employees (employeename, business_id) 
VALUES (
    'emp-name-from-row',
    (SELECT id FROM businesses WHERE busname='bus-name-from-row')
);

(I renamed your name columns just to avoid ambiguity).

If the file is too big to do this in one go, first generate the business insert .sql from the original file, then use the posix split utility to break the file into manageable chunks for the employee inserts.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.