CSVファイルをDBにインポートする #PHP

概要

容量の大きいCSVファイルをMySQLに流し込む処理を作ることがあり、調べた内容をメモしておく。

ミドルウェア構成

dockerで Apache + PHP + MadiaDB のローカル環境を作ってテストしてみる。

docker-compose.yml

version: '3'
services:
  httpd:
    image: local/apache:2.4
    build: ./docker/httpd/
    ports:
      - '8080:80'
    volumes:
      - app:/var/www/www.example.com:cached

  php:
    image: local/php:7.3
    build:
      context: ./
      dockerfile: ./docker/php/Dockerfile
    ports:
      - '9000:9000'
    volumes:
      - app:/var/www/www.example.com:cached
    depends_on:
      - mariadb

  mariadb:
    image: local/mariadb:10.2
    build: ./docker/mariadb/
    environment:
      - MYSQL_DATABASE=mysql
      - MYSQL_ROOT_PASSWORD=password
    ports:
      - '3306:3306'
    volumes:
      - mariadb-data:/var/lib/mysql

volumes:
  mariadb-data:
  app:
    driver_opts:
      type: none
      device: ${PWD}/
      o: bind

アプリケーションについて

インポートコマンドを作る際の方針は以下の通りです。

Laravelのコマンドとして構築する
一時テーブルを作って、そちらにデータを一括流し込みする
Load Data Local Infileを使ってインポートする
※ PHPで１行ごと読んで処理していると時間かかるため

準備

Laravelで LOAD DATA LOCAL INFILE を使うには、DBとLaravelの設定が必要なようです。

my.cnf

[server]
local_infile=true

config/database.php

'mysql' => [
    'driver'      => 'mysql',
    'host'        => env('DB_HOST', 'mariadb'),
    'port'        => env('DB_PORT', '3306'),
    'database'    => env('DB_DATABASE', 'example'),
    'username'    => env('DB_USERNAME', 'mysql'),
    'password'    => env('DB_PASSWORD', 'password'),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset'     => 'utf8mb4',
    'collation'   => 'utf8mb4_unicode_ci',
    'prefix'      => '',
    'strict'      => true,
    'engine'      => null,
    'options'     => [
        PDO::ATTR_EMULATE_PREPARES   => true,
        PDO::MYSQL_ATTR_LOCAL_INFILE => true,  // ← ここ!!
    ],
],

実装してみる

まず、コマンドを追加する。

$ php artisan make:command ImportCsv

サンプルなので、コマンドにすべて直書きしたいと思います。

<?php
namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;

class ImportCsv extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'import:csv';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'import data from csv file.';

    public function handle()
    {
        $this->info('start');

        // ファイルを指定する
        $file = '/var/www/www.example.com/hoge.csv';

        // 一時テーブルを作成する
        $this->createTemporaryTable();

        // 一次テーブルにCSVファイルのデータを流し込む
        $this->importData($file);

        // その他登録処理などを記述する
        // ...

        $this->info('complete');
    }

    private function createTemporaryTable()
    {
        Schema::create('tmp_hoge', function (Blueprint $table) {
            $table->bigIncrements('id')->comment('id');
            $table->string('title', 50)->comment('タイトル');
            $table->string('description', 255)->comment('説明');
            $table->integer('price')->comment('金額');
            $table->integer('tax_rate')->comment('税率');
            $table->string('memo')->comment('備考');
            $table->temporary();
        });
    }

    private function importData(string $file)
    {
        // エンコード指定 (sjis,cp932, utf8)
        DB::statement("SET CHARACTER_SET_DATABASE=cp932;"); 
        DB::statement("
            LOAD DATA LOCAL INFILE ?
            INTO TABLE `tmp_hoge`
            FIELDS TERMINATED BY ?
            ENCLOSED BY ?
            LINES TERMINATED BY ?
            (@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18,@19,@20,@21,@22,@23,@24,@25,@26,@27,@28,@29,@30)
            SET
              title=@1,
              description=@2,
              price=@5,
              tax_rate=@6,
              memo=@30
            ;
        ", [
            $file,
            ',',
            '"',
            "\r\n"
        ]);
    }
}

実行するときは、こんな感じでしょうか。

$ php artisan import:csv

こんな感じでインポートできました。

まとめ

ローカルのdocker環境で、800万件のデータを取り込んで見たときは、
だいたい90秒(1分30秒)ぐらいだったでしょうか。

入力検証などはできないかったりするので、その点はデメリットです。
しかし、信頼できるデータをまとめて大量に流し込む場合には便利かもしれませんね。

追記 2021/11/05

「 SJIS 」のエンコードだと、機種依存文字が「？」になって取り込めないので、
「 CP932 」のエンコードの方が良さそうに思われます。

参考サイト

以上