3

I am out of my depth a bit perhaps but submit this query hoping someone has some experience importing large (11.2GB) tab delimited text file (2.2GB tar.gz file) into either MySQL 5.5.41 or PostgreSQL 9.3.6. Double precision for the fields will be required as it is spatial data (Latitude, Longitude and Elevation).

I have MySQL and PostgreSQL setup on Ubuntu 14.04 using phpMyAdmin and phpPGAdmin to interact with the servers. However I realise that command line interaction will probably be better.

I have read a bit and there seems to be a way to split data for MySQL (SQLDumpSplitter2 - http://www.rusiczki.net/2007/01/24/sql-dump-file-splitter/).

Any suggestions or alternative options greatly appreciated.

Thanks Paul

1

For PostgreSQL, I would recommend using pgloader.

pgloader leaves the rows that it cannot import in a separate text file, so you can take a look at what went wrong, fix the errors, and finish importing the data. COPY simply crashed when it encounters a row it didn't expect, which is likely to happen several times for very big files.

pgloader can also be set up to load data in parallel, but I'm not sure how much help it would be if you're just loading a single table.

For MySQL, the standard LOAD DATA LOCAL INFILE is as fast as you're going to get (as far as I know). It spits out a warning for each row that it cannot import, so you can go back and look at that data later.

Other than that, 11.2 GB isn't too bad. Be sure to remove all existing indexes on the table that you're importing, and recreate the indexes afterwards.

I've imported text files of comparable size into both databases without a problem. Loading data and creating indexes might take a few hours, so just leave it overnight.

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.