Search code examples
mysqlinsertload-data-infile

MySQL, load data from file, into number of tables

My basic task is to import parts of data from one single file, into several different tables as fast as possible.

I currently have a file per table, and i manage to import each file into the relevant table by using LOAD DATA syntax.

Our product received new requirements from a client, he is no more interested to send us multiple files but instead he wants to send us single file which contains all the original records instead of maintaining multiple such files.

I thought of several suggestions:

  1. I may require the client to write a single raw before each batch of lines in file describing the table to which he want it to be loaded and the number of preceding lines that need to be imported. e.g. 

    Table2,500 
...
Table3,400

    Then i could try to apply LOAD DATA for each such block of lines discarding the Table and line number description. IS IT FEASIBLE?

  2. I may require each record to contain the table name as additional attribute, then i need to iterate each records and inserting it , although i am sure it is much slower vs LOAD DATA.

  3. I may also pre-process this file using for example Java and execute the LOAD DATA as statement in a for loop.

I may require almost any format changes i desire, but it have to be one single file and the import must be fast. (I have to say that what i mean by saying table description, it is actually a different name of a feature, and i have decided that all relevant files to this feature should be saved in different table name - it is transparent to the client)

What sounds as the best solution? is their any other suggestion?

Solution

  • It depends on your data file. We're doing something similar and made a small perl script to read the data file line by line. If the line has the content we need (for example starts with table1,) we know that it should be in table 1 so we print that line.

    Then you can either save that output to a file or to a named pipe and use that with LOAD DATA.

    This will probably have a much better performance that loading it in temporary tables and from there into new tables.

    The perl script (but you can do it in any language) can be very simple.