Back

Load data infile - where to store? Use Envoy?


Hi,

This is not actually related to Laravel, but my hope is that it might be since that would mean there is a Laravel-way to do it ;).

So. I get large datasets monthly (.tsv-files), which I import to my database (MySQL). Mysql command line tool has the "load data infile", which is, in my case, about 250x faster then doing some PHP-import. The files are quite big (5gb), so I don't want to put them in my version control. I also use Forge, so my dream is to be able to create some Artisan command that will do the heavy lifting. Why? Because some things, for instance DB credentials is not the same in every environment, and since i already have this configured in Laravel i would rather not have one more place to change.

So my first question is: have anyone (successfully) been able to to "load data infile" from within Laravel?

The second question is: how do you store these kind of files? My best option right now is to simply symlink app/resources/data (or whatever the folder is called), but is there a better way?

Thanks in advance!

laravelian replied 2 years ago

I was just banging my head against the wall last night with this issue --

What I did was to write the import file to the system's temp folder (machine's, not something in storage_path() -- i used sys_get_temp_dir() -- may not be doable or safe on shared hosting.), run the import, then delete the file directly afterwards. That way MySQL doesn't have a permission issue.

The second step to getting around the permissions issue was to use LOAD DATA LOCAL INFILE ...

This all worked wonderfully on my dev machine, but on the server, i got the error that LOAD DATA LOCCAL INFILE forbidden. AGHHHH -- so this took me a while to figure out but finally what it came down to was that I needed to set an option in the PDO class in order to enable it. BUT, the option was required to be set at instantiation, and Laravel was already bootstrapping PDO on it's own.

The solution was so simple, yet not in the official documentation -- I needed to add an options key to my database connection array, like so:

'mysql' => [
    ...
    'options' => [
        PDO::MYSQL_ATTR_LOCAL_INFILE => true
    ],
],

And then it worked like a charm!


Sign in to participate in this thread!


We'd like to thank these amazing companies for supporting us