Go to Qiita Advent Calendar 2024 Top
LoginSignup
31
32

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@PruneMazui

[Laravel5.4] 大容量CSVのダウンロード

Last updated at Posted at 2017-10-23

5.4の記事ばっかり書いてるけどLTSの5.5で書きたいのが本音。

前提

  • Laravel 5.4
  • PHP7.1
  • DBはUTF-8（MySQLならutf8mb4 + mysqlnd とする）

解決したいこと

  • Databaseから1000万行あるデータとかをCSVとして出力したい
    （普通に全件出力するとメモリオーバーする）
  • CSVはWindows Excel で文字化けせずに開きたい
  • レスポンスするファイル名が日本語だった場合の問題は今回は無視

解決方法

  • SQLのカーソルを開き、適当なレコード数毎に出力
  • Symfony のStreamedResponseを使う

全部取ってきて～ってやるとPHPのメモリにどかっと結果セットが乗ってしまうので、RDBのカーソル開いて1行ごとに出力を行う。

レスポンスの作成

StreamedResponseのコールバック関数を定義する。
Laravelって書いたけど、どっちかというとSymfonyだなこりゃ。

  • 出力先を標準出力にして SPLファイルオブジェクトを作成
  • UTF-8でもWindows Excelでも文字化けしないようにするため、BOMを書き込む（小ネタ）
  • ヘッダ行書き込み
  • CSVを1行ごとに出力
<?php
function makeDownloadResponse(array $header, string $filename)
{
    $response = new \Symfony\Component\HttpFoundation\StreamedResponse;();

    $response->setCallback(function () use ($header) {
        $file = new \SplFileObject('php://output', 'w');

        // BOMを書き込み
        $file->fwrite(pack('C*',0xEF,0xBB,0xBF));

        $file->fputcsv(array_values($header));
        flush();

       // カーソルをOpenにして1行読み込んでCSV書き込み
       // RDB毎にやり方違う（後述）
        while($row = $pdo->fetch()) {
            $file->fputcsv($row);
            flush();
        }
    });

    $response->headers->set('Content-Type', 'application/octet-stream');
    $response->headers->set('Content-Disposition', 'attachment; filename=' . $filename);

    return $response;
}

カーソル開いて～はRDB毎にやり方違うので後述。注意点としてはカーソル開いてると別のクエリ投げれない。

MySQL でカーソル

ここ に書いてるとおり、デフォルトではバッファクエリモードで動いてる。バッファクエリモードは（mysqlndなら）クエリ投げた時点でPHP側にどっさりメモリ乗っかるので非バッファクエリモードにしてやる必要がある。

※追記
後から知ったのだが、LaravelのEloquentには標準でカーソル使ったり、複数回に分けてクエリ投げたりする方法があるのでそちらを使ったほうがよい。

以下古い記事なので参考程度に。

ってなわけでこんな感じ。

<?php
function makeDownloadResponse(array $header, string $filename)
{
    $response = new \Symfony\Component\HttpFoundation\StreamedResponse;();

    $response->setCallback(function () use ($header) {
        $file = new \SplFileObject('php://output', 'w');

        // BOMを書き込み
        $file->fwrite(pack('C*',0xEF,0xBB,0xBF));

        $file->fputcsv(array_values($header));
        flush();

        // カーソルをOpenにして1行読み込んでCSV書き込み
        $pdo = new \PDO('mysql:host=localhost;dbname=dbname', 'user', 'pass');
        $pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
        
        $stmt = $pdo->prepare('SELECT * FROM hoge_table');
        $statement->setFetchMode(\PDO::FETCH_ASSOC);
        $stmt->execute();

        while($row = $stmt->fetch() ) {
            $file->fputcsv($row);
            flush();
        }
    });

    $response->headers->set('Content-Type', 'application/octet-stream');
    $response->headers->set('Content-Disposition', 'attachment; filename=' . $filename);

    return $response;
}

PostgreSQL でカーソル

Postgres はバッファクエリの文献が見つけれなかった／(^o^)＼

とりあえずこんな感じ。

<?php
function makeDownloadResponse(array $header, string $filename)
{
    $response = new \Symfony\Component\HttpFoundation\StreamedResponse;();

    $response->setCallback(function () use ($header) {
        $file = new \SplFileObject('php://output', 'w');

        // BOMを書き込み
        $file->fwrite(pack('C*',0xEF,0xBB,0xBF));

        $file->fputcsv(array_values($header));
        flush();

        // カーソルをOpenにして1行読み込んでCSV書き込み
        $pdo = new \PDO('pgsql:host=localhost;dbname=dbname', 'user', 'pass');
        $pdo->beginTransaction();

        $stmt = $pdo->prepare('DECLARE cursor_hoge CURSOR FOR SELECT * FROM hoge_table');
        $stmt->execute();

        $statement = $pdo->prepare('FETCH 1 FROM cursor_hoge');
        $statement->setFetchMode(\PDO::FETCH_ASSOC);

        while($statement->execute() && $row = $statement->fetch()) {
            $file->fputcsv($row);
            flush();
        }
        $pdo->rollBack();
    });

    $response->headers->set('Content-Type', 'application/octet-stream');
    $response->headers->set('Content-Disposition', 'attachment; filename=' . $filename);

    return $response;
}

カーソル使うのにbeginしてやる必要があるのが注意点。

今回実装したコード

ようやくLaravelらしくなった（気がする）。

Laravelのクエリビルダー使ってるので、インターフェイスとしては何も考えずそいつ渡したらレスポンス返せるようにしたい。今回使ってるRDBMSはPostgresでし。

app/Contracts/EloquentCsvBridge.php
<?php
namespace App\Contracts;

use Illuminate\Database\Eloquent\Builder;
use Symfony\Component\HttpFoundation\StreamedResponse;

class EloquentCsvBridge
{
    const FETCH_COUNT = 100;
    const CURSOR_NAME = 'csv_exporting_cursor';

    protected $builder;
    protected $header;
    protected $callbackRowFormat = null;

    public function __construct(Builder $builder, array $header, callable $callbackRowFormat = null)
    {
        $this->builder = $builder;
        $this->header = $header;
        $this->callbackRowFormat = $callbackRowFormat;
    }

    protected function getRowsetGenerator(): \Generator
    {
        $pdo = \DB::connection()->getPdo();
        $pdo->beginTransaction();

        $sql = $this->builder->toSql();

        $statement = $pdo->prepare(sprintf('DECLARE %s SCROLL CURSOR WITH HOLD FOR %s', self::CURSOR_NAME, $sql));
        $statement->execute($this->builder->getBindings());

        $statement = $pdo->prepare(sprintf('FETCH FORWARD %d FROM %s', self::FETCH_COUNT, self::CURSOR_NAME));
        $statement->setFetchMode(\PDO::FETCH_ASSOC);

        while (true) {
            $statement->execute();
            $row = $statement->fetch();

            if (empty($row)) {
                $pdo->rollBack();
                return;
            }

            yield $row;
            while ($row = $statement->fetch()) {
                yield $row;
            }
        }
    }

    protected function putCsv(\SplFileObject $file, array $row): void
    {
        // 多重配列の場合ネストをループ
        if (is_array(current($row))) {
            foreach ($row as $r) {
                $this->putCsv($file, $r);
            }
            return;
        }

        $cols = [];
        foreach ($this->header as $key => $dummy) {
            $cols[$key] = $row[$key] ?? "";
        }

        $file->fputcsv($cols);
        return;
    }

    public function makeDownloadResponse(string $filename)
    {
        $response = new StreamedResponse();

        $response->setCallback(function () {
            $file = new \SplFileObject('php://output', 'w');

            // BOMを書き込み
            $file->fwrite(pack('C*',0xEF,0xBB,0xBF));

            $file->fputcsv(array_values($this->header));
            flush();

            foreach ($this->getRowsetGenerator() as $row) {
                if (! is_null($this->callbackRowFormat)) {
                    $row = ($this->callbackRowFormat)($row);
                }

                $this->putCsv($file, $row);
                flush();
            }
        });

        $response->headers->set('Content-Type', 'application/octet-stream');
        $response->headers->set('Content-Disposition', 'attachment; filename=' . $filename);

        return $response;
    }
}
  • 1回あたりのカーソルの進む度合い設定した（今回は100）
  • foreach で回したいので行取得部分を Generator 使うようにした
  • DBのコネクション取ってるところがなんかダサい
  • DB1行で複数返したい場合があったのでputCsv()を再帰的に処理してる

使い方

HogeController.php
<?php
// ～略～
class HogeController
{
    public function index()
    {
        $builder = (new HogeModel())->newQuery();
        // $builder->join() ...etc

        $bridge = new EloquentCsvBridge($builder, [
            'id'         => 'ID',
            'name'       => '名前',
            'created_at' => '作成日'
        ], function ($row){
            // 行の整形したりとか
            $row['created_at'] = $row['created_at'] ? Carbon::create($row['created_at'])->format('Y-m-d') : "";
            return $row;
        });

        return $bridge->makeDownloadResponse('test.csv');
    }
}

$builder がIlluminate\Database\Eloquent\Builder インスタンスなんだけど、
Illuminate\Database\Eloquent\Query\Builderもあって、
MIXINされてるからどっちでもいいようにタイプヒンティング使わない方がいいかもしれない。

第二引数はCSVのヘッダ行＋キーで行をフィルタするようにしてる。

所感

書いてる人はMySQLおじさんなので？Postgresの文献見つけれなくて苦労した（見つかったとは言ってない）。
省略してるけどphpDocumentorちゃんと書こうね。

31
32
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
31
32

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?