Read massive flat file into relational database

Question

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.

Answer 1

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.

Answer 2

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.