Mysql – How to know the progress when importing data into MySQL using LOAD DATA INFILE

MySQL

I have a 1 billion record stored in csv file. I'm using LOAD DATA INFILE to import the record to the table.

create table weibo_bind
(
    column1 bigint
        primary key,
    column2 bigint
);

And my csv file is column1,column2.

LOAD DATA INFILE '/path/to/demo.csv' IGNORE INTO TABLE weibo_bind FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

But how can I debug or how can I check the progress of importing? Because it takes a lot of time to import.

Or is there any way to speed up?

I'm using

mysql Ver 8.0.22, Innodb

Best Answer

Sort the incoming file by the PRIMARY KEY; this will speed up the LOAD. Don't have any secondary keys during the load.

There is no way (that I know of) to watch the progress in 8.0.

The .ibd file size is not a reliable indication -- you can't easily predict how big it will eventually be. Datatypes, data values, PK, other indexes, etc, impact the size.

Also, that file will grow in bursts.

Having just two BIGINTs (8 bytes each), the table might be about 40-60 bytes per row. The smaller range might be about right if the data is in PK order; the larger if not.

I hope the query does not timeout.

The process is probably I/O-bound, indicating that nothing you could do would speed it up.

Related Solutions

Mysql – How to use variables in file paths in MySQL statements

You cannot keep the path in a variable because variables are interpreted by the server, and path names are interpreted by the mysql client. Related question:

https://stackoverflow.com/questions/14523816/load-data-infile-variable-into-infile-error

However, there are a couple of different work-arounds that will allow you to accomplish what you are trying to do, and they are explained in the article.

I'd like to add one more idea:

If you call the mysql script from a Bash script, copy your file into a temp file, so the script always calls the same file name, like "work_file.csv".

After the mysql script runs, you can delete "work_file.csv".

Your original file is kept intact, and the data from that file is now uploaded.

MySQL – How to Import CSV File with Custom Data Change

The easiest solution would be to write a small script that reads that file and executes some queries to import these data sets.

If you want to use PHP (just one example, more information here) it would be something like:

    <?php

    $db = new PDO($connectStr, $username, $password);
    $q = $db->prepare("INSERT INTO table (...) VALUES (...)");

    $file = file_get_contents(...);
    $lines = explode("\n", $file);

    foreach ($lines as $line) {
        $data = explode(";", $line);
        // manipulate password here, for PHP, use password_hash instead of md5
        $q->execute([$data]);
    }
    ?>

Another promising solution could be LOAD DATA INFILE. You should have a look at http://dev.mysql.com/doc/refman/5.1/en/load-data.html for more information.

Best Answer

Related Solutions

Mysql – How to use variables in file paths in MySQL statements

MySQL – How to Import CSV File with Custom Data Change

Related Question