はじめに
通常データベースの内容をCSVなりExcelなりにエクスポートする場合、ひとつのカラムに対してひとつの列が割り当てられ、ひとつのレコードに対して行がひとつ、つまりレコード数が増えると見た目縦に伸びていくような構造に、通常はなると思います。
しかし今回お客様のご要望は縦横が逆で、カラムが縦に並び、1レコードが1列に対応するものでした。
| 姓 | 名 | 住所 |
|---|---|---|
| 山田 | 一郎 | 北海道 |
| 田中 | 二郎 | 沖縄県 |
| 姓 | 山田 | 田中 |
|---|---|---|
| 名 | 一郎 | 二郎 |
| 住所 | 北海道 | 沖縄県 |
単純に実装するとリソースや処理時間をかなり消費するので、どうにかする必要がありました。
困難
PHPのCSV関連メソッド(他の言語でもそうではないかと思いますが)は1行分のデータを配列に入れて渡す形になっています。
典型的なユーザーデータを今回の形式で出力するとして、一列分、例えば全ユーザーの名前を配列に入れてメソッドに渡す必要があります。しかし、DBレコードの取得は通常1レコードずつ行うものなので、単純には下記のどちらかになると思います。
- 全カラム分(全行分)の配列群に1レコードずつデータを追加していって、最終的にCSV全体のデータを作り上げてから出力する
- 1カラムごとに全ユーザーのデータをループして1行分のデータを作り上げて出力する
今回のケースでは、カラム数(行数)が2,600程度、レコード数(列数)が数千程度でした。
(カラムと書いていますが、一部を除いて実際にはJSONのデータでDB上は1カラムに入っています)
最初のやり方では、総セル数がレコード数5,000として13M個になり、1レコードあたりのデータ量はばらつきがありますがメモリー使用量がギガ単位になることは確実なので、避けたい状況です。
2番目のやり方ではメモリーは少なくて済みますが、データベースから取ってくるレコードの数が上記の総セル数と同じになるので、これもやはり避けたいです。
解決
解決すべき項目は2つあり、
- メモリー使用量を抑える
- 余計なレコードの取得をしない
です。
ひとつの解決法として挙がったのは、あるひとつカラムのデータだけ順に取得する、つまり
select first_name from users order by id;
select last_name from users order by id;
...
のようにして、1行分のデータを順に構築することでしたが、今回JSON内にあるデータが多く含まれ、技術的にはJSON内のデータをピックアップすることはもちろん可能なのですが、SQLのJSON独特の書き方を数千個分対応するのは大変そうなので諦めました。
今回採用したのは、SQLiteのDBを作って通常のCSVのような形でいったんデータを格納して、取り出す時に縦横逆転する、というものでした。
ひとつのセルに対応するようなテーブルをひとつ用意します。
create table summary_data (
row_no integer not null,
col_no integer not null,
value text not null
)
次に、通常よくある1レコード1行になるようにデータを格納します。
イメージコード：
$row_no = 0;
foreach ($users as $user) {
$col_no = 0;
foreach ($user->cols as $col) {
insert([
'row_no' => $row_no,
'col_no' => $col_no,
'value' => $col
]);
$col_no++;
}
$row_no++;
}
最後に、行番号と列番号の扱いを逆にして(つまり縦横逆転)データを取り出し、CSV化します。
イメージコード：
$row_no = 0;
for (;;) {
$data = fetch('select value from summary_data where col_no = ? order by row_no', $row_no);
if (!$data) break;
fputcsv($fp, $data);
$row_no++;
}
おわりに
この方法により、
- データはファイル格納にし、メモリー使用量を最小限にする
- レコード取得回数は一回だけ
- 仕組みはわりと単純でわかりやすい
という、まぁまぁの結果で実装することができました。
ただし、いったん全セル分のデータをSQLiteに格納するのでそれなりに時間はかかります。
他にもっと良い方法があれば教えて下さい。