How to import "a lot" of data to MySQL with PHP and foreign keys?

Question

I have these tables:

create table person (
    person_id int unsigned auto_increment, 
    person_key varchar(40) not null, 
    primary key (person_id), 
    constraint uc_person_key unique (person_key)
) 
-- person_key is a varchar(40) that identifies an individual, unique 
-- person in the initial data that is imported from a CSV file to this table

create table marathon (
    marathon_id int unsigned auto_increment,  
    marathon_name varchar(60) not null, 
    primary key (marathon_id) 
)

create table person_marathon (
    person_marathon _id int unsigned auto_increment,  

    person_id int unsigned, 
    marathon_id int unsigned,

    primary key (person_marathon_id),
    foreign key person_id references person (person_id), 
    foreign key marathon_id references person (marathon_id),

    constraint uc_marathon_person unique (person_id, marathon_id)  
)

Person table is populated by a CSV that contains about 130,000 rows. This CSV contains a unique varchar(40) for each person and some other person data. There is no ID in the CSV.

For each marathon, I get a CSV that contains a list of 1k - 30k persons. The CSV contains essentially just a list of person_key values that show which people participated in that specific marathon.

What is the best way to import the data into the person_marathon table to maintain the FK relationship?

These are the ideas I can currently think of:

Pull the person_id + person_key information out of MySQL and merge the person_marathon data in PHP to get the person_id in there before inserting into the person_marathon table
Use a temporary table for insert... but this is for work and I have been asked to never use temporary tables in this specific database
Don't use a person_id at all and just use the person_key field but then I would have to join on a varchar(40) and that's usually not a good thing

Or, for the insert, make it look something like this (I had to insert the <hr> otherwise it wouldn't format the whole insert as code):

insert  into person_marathon 

select  p.person_id, m.marathon_id

from    ( select 'person_a' as p_name, 'marathon_a' as m_name union 
          select 'person_b' as p_name, 'marathon_a' as m_name ) 
          as imported_marathon_person_list 

        join person p 
           on p.person_name = imported_marathon_person_list.p_name

        join marathon m 
           on m.marathon_name = imported_marathon_person_list.m_name

The problem with that insert is that to build it in PHP, the imported_marathon_person_list would be huge because it could easily be 30,000 select union items. I'm not sure how else to do it, though.

have you looked into an ETL process for this? Pentaho PDI may be ? — Maximus2012
– Maximus2012, Commented Jul 22, 2013 at 17:05
I would prefer to just be able to write something with PHP. I am just not sure of the best way to structure the insert. I think I'm leaning towards pulling out the person_id + person_key and then merging it in PHP before the MySQL insert. This is a very small project and I'm not sure we need a new tool to handle it. — gloomy.penguin
– gloomy.penguin, Commented Jul 22, 2013 at 17:08
You should try Pentaho. I have done a lot of large imports (several GB) with languages (php, java), and I have tried Pentaho. Just try for 30 minutes and tell us. ;) — kmas
– kmas, Commented Jul 22, 2013 at 17:10
I'd go with either option 2 or 3. A 40 byte string, if indexed properly, wouldn't be terrible for joins - especially if most your queries will be performing lookups on that string anyway. Loading into a temporary is a decent alternative: why on earth have you been asked never to use them (they don't persist beyond the end of your database connection, so who cares?) — eggyal
– eggyal, Commented Jul 22, 2013 at 17:18
@eggyal so temp table? I can argue for that, if it's the best option. I just wasn't sure if there was anything else. Can Pentaho even be automated? A temp table would definitely be easier to argue for than a new tool. — gloomy.penguin
– gloomy.penguin, Commented Jul 22, 2013 at 17:27

Topher Hunt · Accepted Answer · 2013-07-22 17:41:18Z

2

I've dealt with similar data conversion problems, though at a smaller scale. If I'm understanding your problem correctly (which I'm not sure of), it sounds like the detail that makes your situation challenging is this: you're trying to do two things in the same step:

import a large number of rows from CSV into mysql, and
do a transformation such that the person-marathon associations work through person_id and marathon_id, rather than the (unwieldy and undesirable) varchar personkey column.

In a nutshell, I would do everything possible to avoid doing both of these things in the same step. Break it into those two steps - import all the data first, in tolerable form, and optimize it later. Mysql is a good environment to do this sort of transformation, because as you import the data into the persons and marathons tables, the IDs are set up for you.

Step 1: Importing the data

I find data conversions easier to perform in a mysql environment than outside of it. So get the data into mysql, in a form that preserves the person-marathon associations even if it isn't optimal, and worry about changing the association approach afterwards.
You mention temp tables, but I don't think you need any. Set up a temporary column "personkey", on the persons_marathons table. When you import all the associations, you'll leave person_id blank for now, and just import personkey. Importantly, ensure that personkey is an indexed column both on the associations table and on the persons table. Then you can go through later and fill in the correct person_id for each personkey, without worrying about mysql being inefficient.
I'm not clear on the nature of the marathons table data. Do you have thousands of marathons to enter? If so, I don't envy you the work of handling 1 spreadsheet per marathon. But if it's fewer, then you can perhaps set up the marathons table by hand. Let mysql generate marathon IDs for you. Then as you import the person_marathon CSV for each marathon, be sure to specify that marathon ID in each association relevant to that marathon.

Once you're done importing the data, you have three tables: * persons - you have the ugly personkey, as well as a newly generated person_id, plus any other fields * marathons - you should have a marathon_id at this point, right? either newly generated, or a number you've carried over from some older system. * persons_marathons - this table should have marathon_id filled in & pointing to the correct row in the marathons table, right? You also have personkey (ugly but present) and person_id (which is still null).

Step 2: Use personkey to fill in person_id for each row in the association table

Then you either use straight Mysql, or write a simple PHP script, to fill in person_id for each row in the persons_marathons table. If I'm having trouble getting mysql to do this directly, I'll often write a php script to deal with a single row at a time. The steps in this would be simple:

look up any 1 row where person_id is null but personkey is not null
look up that personkey's person_id
write that person_id in the associations table for that row

You can tell PHP to repeat this 100 times then end script, or 1000 times, if you keep getting timeout problems or anything like taht.

This transformation involves a huge number of lookups, but each lookup only needs to be for a single row. That's appealing because at no point do you need to ask mysql (or PHP) to "hold the whole dataset in its head".

At this point, your associations table should have person_id filled in for every row. It's now safe to delete the personkey column, and voila, you have your efficient foreign keys.

answered Jul 22, 2013 at 17:41

Topher Hunt

4,8833 gold badges32 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

gloomy.penguin Over a year ago

"on-marathon associations work through person_id and marathon_id, rather than the (unwieldy and undesirable) varchar personkey column" -- This is exactly the problem. There are about 20-40 marathons each month but this data is not received as a CSV... it's just kind of attached to the person_marathon data so it's fairly easy to deal with compared to everything else.

gloomy.penguin Over a year ago

It seems like it might be a little hard to automate and I'm concerned about sticking extra info into person_marathon with new and old data and then removing (even unnecessary) data from the table.

Topher Hunt Over a year ago

OK, thanks for the comments and best of luck with this, Gloomy. It sounds like you'll continue to receive new marathon data in this unwanted format (where the varchar personkey is the only person reference)? I had been responding as though that was a one-time challenge. But even so, I think I'd still start by considering my proposed solution: set up the persons_marathons table to have an extra column for the varchar, though it sounds like you canNOT eventually remove that personkey column as it will continue to be needed. Mysql can handle the redundancy.

Collectives™ on Stack Overflow

How to import "a lot" of data to MySQL with PHP and foreign keys?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related