Cara Batch Impor CSV Besar dengan Cepat menggunakan PHP (Juta Catatan dalam Detik)

Saya tahu saya tahu! Bagaimana Anda bisa menempatkan PHP dan Fast di baris yang sama? Saya akan mengatakan, Anda terjebak di masa lalu. Anda harus mencoba PHP 7 dan mengalaminya sendiri. Akan ada banyak kritik untuk artikel ini. Silakan baca.

LOAD DATA INFILE adalah pilihan terbaik untuk mengimpor file CSV besar. Pada artikel ini kita akan melihat bagaimana kita akan membaca file CSV dan INSERT record melalui skrip PHP. Ini dapat digunakan dalam kasus khusus.

Secara umum PHP tidak akan memiliki kasus penggunaan seperti itu. Tapi Anda tidak pernah tahu, ketika Anda akan menemukan situasi seperti itu, kebutuhan klien bisa datang kapan saja. Ketika Anda seorang “programmer lepas” (tidak memainkan peran konsultan), Anda akan didorong dengan segala macam persyaratan gila.

Saya telah menulis artikel tentang semua hal CSV di PHP. Anda harus mempelajarinya untuk mempelajari cara menangani file CSV di PHP.

Saya sangat menyadari tentang micro-benchmarking dan perangkapnya. Jadi, saya tidak pergi ke arah itu untuk artikel ini. Itu sebabnya saya memberi judul hanya dalam hitungan detik. Ini adalah perkiraan. Ini bukan untuk orang yang akan melihat dalam mikro, nano detik.

Jika Anda ingin memasukkan sejuta catatan dalam beberapa detik, menggunakan skrip PHP secara terprogram, maka ini pasti akan membantu. Kasus penggunaan jarang terjadi dan begitulah solusinya. Anda mungkin harus melakukan beberapa penyesuaian di sana-sini dalam kode contoh ini agar sesuai dengan kasus Anda.

<?php

function file_get_contents_chunked($link, $file, $chunk_size, $queryValuePrefix, $callback)
{
    try {
        $handle = fopen($file, "r");
        $i = 0;
        while (! feof($handle)) {
            call_user_func_array($callback, array(
                fread($handle, $chunk_size),
                &$handle,
                $i,
                &$queryValuePrefix,
                $link
            ));
            $i ++;
        }
        fclose($handle);
    } catch (Exception $e) {
        trigger_error("file_get_contents_chunked::" . $e->getMessage(), E_USER_NOTICE);
        return false;
    }

    return true;
}
$link = mysqli_connect("localhost", "root", "pass", "huge-csv");
$success = file_get_contents_chunked($link, "sample-dataset.csv", 2048, '', function ($chunk, &$handle, $iteration, &$queryValuePrefix, $link) {
    $TABLENAME = 'tbl_lead';
    $chunk = $queryValuePrefix . $chunk;

    // split the chunk of string by newline. Not using PHP's EOF
    // as it may not work for content stored on external sources
    $lineArray = preg_split("/rn|n|r/", $chunk);
    $query = 'INSERT INTO ' . $TABLENAME . '(id, name, email) VALUES ';
    $numberOfRecords = count($lineArray);
    for ($i = 0; $i < $numberOfRecords - 2; $i ++) {
        // split single CSV row to columns
        $colArray = explode(',', $lineArray[$i]);
        $query = $query . '(' . $colArray[0] . ',"' . $colArray[1] . '","' . $colArray[2] . '"),';
    }
    // last row without a comma
    $colArray = explode(',', $lineArray[$i]);
    $query = $query . '(' . $colArray[0] . ',"' . $colArray[1] . '","' . $colArray[2] . '")';
    $i = $i + 1;

    // storing the last truncated record and this will become the
    // prefix in the next run
    $queryValuePrefix = $lineArray[$i];
    mysqli_query($link, $query) or die(mysqli_error($link));

    /*
     * {$handle} is passed in case you want to seek to different parts of the file
     * {$iteration} is the section of the file that has been read so
     * ($i * 4096) is your current offset within the file.
     */
});

if (! $success) {
    // It Failed
}

Dua hal utama yang perlu diperhatikan adalah,

1. Baca file dalam potongan (batch).
2. Sisipkan beberapa record dalam satu pernyataan insert.

Hal di atas adalah kunci dalam mempercepat proses secara keseluruhan. Membaca baris demi baris dan mengulangi melalui loop akan memperlambat proses. Jadi semuanya bermuara pada membaca potongan (batch) dan multi insert. Kemudian poin ketiga yang layak disebutkan adalah menggunakan fungsi asli PHP sedapat mungkin.

Saya telah menggunakan Regex untuk mengganti baris baru dalam file CSV. Jika Anda memiliki opsi yang lebih baik, silakan sarankan melalui bagian komentar di bawah.

Sekarang izinkan saya menelusuri kodenya.

PHP ketakutan memungkinkan untuk membaca dalam potongan string. Cobalah bereksperimen dengan ukuran potongan (batch) yang berbeda. Tidak ada ukuran tertentu yang tepat. Ada banyak variabel, konfigurasi server Anda, perangkat keras, pengaturan MySQL, dan banyak lagi.

Saya telah menggunakan contoh file CSV yang saya buat sendiri. Saya akan merinci prosesnya di bawah ini. Anda dapat menggunakan sampel Anda sendiri atau data nyata dan meneruskannya sebagai parameter.

file_get_contents_chunked melakukan pemrosesan file CSV dan ini memiliki fungsi panggilan balik sebagai argumen terakhir. Panggilan balik ini menangani penguraian catatan dengan pembatas (koma dalam contoh saya) dan membuat kueri multi-insert dan melakukan penyisipan yang sebenarnya.

Anda mungkin harus memodifikasi bagian kueri agar sesuai dengan format tabel database Anda. Struktur keseluruhan skrip menangani penguraian CSV dalam potongan (batch) dan panggilan balik. Terima kasih kepada RobertPitt.

Satu hal yang layak disebutkan adalah, alih-alih membaca baris demi baris, saya telah menggunakan potongan itu untuk meningkatkan kecepatan. Semua pengguna Internet telah menyarankan fgetcsv. Tapi saya telah melanjutkan dengan membaca chunk (batch) dari fread.

Fread ketika membaca sebagai chunk (batch) jelas akan memiliki bagian dari baris CSV yang terpotong. Saya telah menyimpan catatan terakhir yang terpotong itu di setiap potongan di $queryValuePrefix variabel. Itu dipertahankan dengan mendeklarasikan sebagai referensi melalui panggilan balik.

Contoh kumpulan data CSV

Saya menulis skrip PHP kecil untuk menghasilkan kumpulan data CSV yang diperlukan. Ada banyak sumber seperti catatan sensus Pemerintah, data Covid yang sekarang populer, data cuaca, dan banyak lagi.

Pada era Artificial Intelligence saat ini, di mana Data Mining lebih populer daripada Twinkle Twinkle Little Star, mendapatkan sampel file CSV besar hanya dengan sekali klik.

Tapi tetap untuk fleksibilitas, saya menulis skrip PHP sendiri.

<?php
$file = fopen('s-sample-dataset.csv', 'w');

for ($i = 1; $i <= 1000000; $i ++) {
    echo $i;
    $line[] = $i;
    $line[] = uniqid();
    $line[] = uniqid();
    fputcsv($file, $line);
    $line = null;
}
fclose($file);

Ini adalah cara yang kejam untuk menghasilkan data sampel 🙂 Pada saat ini Faker layak disebut. Ini adalah perpustakaan PHP yang bagus untuk menghasilkan data palsu yang bagus. Ini akan terlihat sangat dekat dengan nyata.

Terakhir, untuk para nerd. Skrip ini menjalankan dan mengimpor satu juta (1000000) catatan dalam 9 detik. Mesin saya adalah MacBookPro dengan Catalina, 2,3 GHz i5, RAM 16 GB dan PHP 7.4. Ini hanya untuk ide dan tolong jangan membuat banyak dari ini.

Tuangkan semua sudut pandang Anda dan saran untuk perbaikan di komentar di bawah. Selamat mem-parsing semua CSV Anda, potonglah!

Kembali ke Atas