質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.12%

シェルスクリプトによるCSVデータ(件数不確定)のDB挿入とphpでechoした値の受け取りについて

解決済

回答 4

投稿

  • 評価
  • クリップ 0
  • VIEW 817

jun_1989

score 15

お世話になります。

数万件〜数百万件になるCSVファイルのDBへのinsert及びupdateに関して質問させてください。

現在、AWSのS3に定期的にアップされるCSVファイルをダウンロードしてローカルに保存し、そのCSVのデータをDBに挿入・更新するphpのプログラム(get-csv.php)を書いています。

CSVファイルのデータ件数が数万件〜数百万件(毎回件数は違います)あるので、一回で処理しようとするとメモリが飛んでしまいます。

ですのでget-csv.phpでは、メモリ節約のため一回の処理数を1万件までにして、シェルスクリプトを実行する際に

for i in `seq 1 100`
do
 php get-csv.php $i
done


という具合に、get-csv.phpに引数を渡して引数が1の時は1件目〜1万件目、2の時は1万件目〜2万件目、3の時は2万件目〜3万件目・・・という処理をphp側で書いています。

本当はforではなくwhileなどを使ってループを制御したいのですが、無知のためやり方がわかりません。

whileで回したget-csv.phpがechoする値(NULLか否か)を使ってwhileの条件判定をするのだろうとは思いますが、phpで吐いた値をシェルスクリプトで受け取る方法が恥ずかしながらわかりません。

質問と致しましては

①phpのプログラムでechoした値をシェルスクリプトで受け取る方法(シェルスクリプトのwhileの条件式で使うためです)

②そもそも件数の多いCSVファイルの処理として、私が行っている方法は適切かどうか。他に考えられる処理の仕方があるのかどうか。

の2点です。

どちらか1点でも構いませんので、わかる方がいらっしゃいましたらご教授のほどよろしくお願い申し上げます。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 4

checkベストアンサー

0

SQLの種別は何を予定されているのでしょうか?
たとえばMySQLだとしたら基本的にはCSVをSQL文に変換した上で
INSERT INTO ~ VALUES(x,x,x),(y,y,y),(Z,Z,Z)・・・・
というバルク形式のデータとして取り込むのが効率的です。
一度に取り込む量は1万件程度でまとめるとトラブル時の検証も
やりやすいと思います。

具体的には
INSERT INTO ~ ・・・・;
INSERT INTO ~ ・・・・;
INSERT INTO ~ ・・・・;
INSERT INTO ~ ・・・・;

のようなSQL文を何万行とテンポラリで保持しておき、
まずは1万行単位でsplitする
その上で月末の;をとり、2行目以降のINSERT INTO・・・VALUES
を除外すると1万件のバルクINSERT文ができあがります。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/07/31 15:19

    ご回答ありがとうございます。説明不足で申し訳ございません。SQLはmysqlを使用しております。現状、foreachにてCSVのデータを一件ずつinsertしております。教えていただいた方法だと、シェルでforを回すのではなく、一回でCSVのデータを全部取得して、phpで分割して挿入するという認識でよろしいでしょうか。お手数ですが、可能でしたらご回答よろしくお願いします。

    キャンセル

  • 2017/07/31 15:24

    ごめんなさい、ちょっとこちらの認識がちがうのかもしれません。
    今回の目的はCSVデータを一括で読めないのをなんとかしたい
    また、高速に処理したいということではないのでしょうか?

    CSVデータは1行読み出してはINSERT形式に変換してテンポラリに
    書き出していくというイメージです。
    最終的にINSERT文が大量にできあがりますが、INSERT文を
    乱発するよりバルク形式のINSERTの方が数十~数百倍は速いと
    思います。

    逆にCSVで出力するのではなくdumpデータとして吐き出してもらえれば
    もっと効率的でしょう

    キャンセル

  • 2017/07/31 15:48

    いえ、ご認識の通りです。大量のCSVデータを高速に処理したいというのが望みです。現状、phpでCSVデータを一件ずつinsertしていたので、教えていただいたバルク形式の挿入を試してみたいと思います。

    >逆にCSVで出力するのではなくdumpデータとして吐き出してもらえればもっと効率的でしょう

    そうなんですね!勉強になります。ただ今回の案件ではCSV出力を指定されていますので、また別の機会に試してみたいと思います。

    キャンセル

0

seq 1 $(wc -l source.csv|awk '{print int($1/10000)}')|xargs -n1 php get-csv.php


でどうでしょうか?csvの件数(行数)を気にしなくてよく、phpの戻り値も不要になります。
上記1行でfor〜doneの4行分に相当します。

  • 件数不確定とのことなので、wc -l source.csvで件数(行数)を調べます

  • 1万件づつの処理にするために分割数を算出します awk '{print int($1/10000)}'

  • 連番を作成しますseq 1 $(wc -l source.csv|awk '{print int($1/10000)}')

  • get-cdv.phpの引数に値を渡します。|xargs -n1 php get-csv.php

試しに、以下のコマンドを実行していけば、どんなことをしているかわかるはずです。(phpの代わりにechoを使ってテストしてます)

  1. wc -l source.csv
  2. wc -l source.csv|awk '{print int($1/10000)}'
  3. seq 1 $(wc -l source.csv|awk '{print int($1/10000)}')|xargs echo get-csv.php
  4. seq 1 $(wc -l source.csv|awk '{print int($1/10000)}')|xargs -n1 echo get-csv.php

個々のコマンドは調べてください

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/07/31 15:36

    ご回答ありがとございます。初めて目にした文で勉強になります。可能でしたらご教授いただきたいのですが、そちらの文はforの中で使用するという認識でよろしいでしょうか。またprint int($1/10000)の部分はMAXで1万回forを回すという認識でよろしいでしょうか。恐縮ですが、よろしくお願いします。

    キャンセル

  • 2017/07/31 16:22

    1行ですべてです。forは使いません

    キャンセル

  • 2017/07/31 17:12

    ご丁寧に解説頂き本当に感謝申し上げます。1~4の命令を実行して、何をしているかがわかりました!forの回数を求めていたんですね。勉強になります。早速使用させていただきます。

    キャンセル

0

①phpのプログラムでechoした値をシェルスクリプトで受け取る方法(シェルスクリプトのwhileの条件式で使うためです)

phpからの戻り値を得る方法として2つの方法を考えました。どちらも終了条件に使えます

 RC=$(php -r "echo 'AAA';")
 echo $RC

 php -r "exit(2);"
 rc=$?
 echo $rc

②そもそも件数の多いCSVファイルの処理として、私が行っている方法は適切かどうか。他に考えられる処理の仕方があるのかどうか。

大して違わないですが入力ファイルを分割する方法(5個並列)

 split -l 10000 data.csv out_
 jj=1
 for ff in out_*
 do
    php -r "echo '$ff';" & #入力ファイルをパラメータでまたはSTDINで
    if [ $(( jj % 5 )) -eq 0 ]; then
        wait
    fi
    ((jj++))
 done
 rm out_*

並列実行はinsert時にdeadlockが発生しないテーブルの場合ですが・・・

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/08/04 20:37

    ご回答ありがとうございます!phpもbashも勉強中なので参考になります!

    キャンセル

0

LOAD DATA INFILEを使用するのはどうでしょう?
insertやupdateで行うよりも高速だと思います。
データ量により一括で処理できない場合は、CSVファイルを分割する必要はありますが。

MySQL 5.6 リファレンスマニュアル 13.2.6 LOAD DATA INFILE 構文

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/08/04 20:34

    ご回答ありがとうございます!「LOAD DATA INFILE」初めて聞きました!勉強になります。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.12%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる
  • トップ
  • PHPに関する質問
  • シェルスクリプトによるCSVデータ(件数不確定)のDB挿入とphpでechoした値の受け取りについて