2種類のCSVを1つにまとめてMySQLにアップロード

はじめに

「csvで保存された売上データをデータベースにアップロードする」
という案件をこなしたのですが，このcsvが「売上情報等のcsv」と「メニューのcsv」というような感じで，二つに分けて保存されており，それらをまとめる必要がありました．
多分そこまで難しいことではないのですが，今回やったことを忘れないためにもまとめていきたいと思います．
僕自身今回初めてシェルを書いたこともあり，なるべく詳しく書いていきたいと思います．

案件で使ったデータを使用するわけにはいかないので，今回はこちらの疑似個人情報データ生成サービスのサイトを使用させて頂き，サンプルデータを作成しました．

サンプルデータ及び，書いたコードはGitHubに上げましたので参照してください．

設計概要

ディレクトリ構造と実行時の流れ

ディレクトリ構造は下記のようにしました．

$ tree
.
├── BackUp
├── CSV
│   └── 20180103
│       ├── blue.csv
│       ├── blue_record.csv
│       ├── red.csv
│       ├── red_record.csv
│       ├── yellow.csv
│       └── yellow_record.csv
└── upload_to_mysql.sh

upload_to_mysql.sh を実行すると，CSV/(実行日) にあるファイルに対して処理を実行しMySQLにデータをアップロードします．無事にアップロードされればcsvをBackUpに移動させ，CSV/(実行日)ディレクトリが空であれば削除します．

csvをアップロードする際に LOAD DATA INFILE を使用したのですが，二つのcsvをまとめてアップロードする方法が見つからなかったため，成績に関するcsvを一旦別のデータベース(information_about_records)にアップロードし，それからメインのデータベース(information_about_students)にアップロードするようにしました．

csvとデータベース設計

csv

あまりいいサンプルが思いつかなかったので結構てきとうな感じで作ってしまいましたが，
「クラスごとに生徒の情報をまとめた，(クラス名).csv」(e.g. red.csv)
と
「クラスごとに出席番号，成績がまとめられた，(クラス名)_record.csv」(e.g. red_record.csv)
としました．

(クラス名).csv

カラム名
出席番号
氏名
氏名(カタカナ)
性別
電話番号
出身地
血液型

(クラス名)_record.csv

カラム名
出席番号
数学
国語
英語
社会
理科

データベース

データベース名は information_about_schoolとしました．
テーブルは下記のように設計しました．
information_about_records

カラム名	型	説明
id	int(11)	primary key, auto_increment
class	varchar(255)	クラス名
number	int(11)	出席番号
record_of_math	varchar(255)	数学の点数
record_of_japanese	varchar(255)	国語の点数
record_of_english	varchar(255)	英語の点数
record_of_society	varchar(255)	社会の点数
record_of_science	varchar(255)	理科の点数

information_about_students

カラム名	型	説明
id	int(11)	primary key, auto_increment
class	varchar(255)	クラス名
number	int(11)	出席番号
name	varchar(255)	氏名
katakana_of_name	varchar(255)	氏名(カタカナ)
sex	varchar(255)	性別
telephone_number	int(11)	電話番号
birthplace	varchar(255)	出身地
blood_type	varchar(255)	血液型
record_of_math	varchar(255)	数学の点数
record_of_japanese	varchar(255)	国語の点数
record_of_english	varchar(255)	英語の点数
record_of_society	varchar(255)	社会の点数
record_of_science	varchar(255)	理科の点数

ちなみにですが，

mysql -uUSER -pPASS -Ns 'DB名' -e "DESC TABLE_NAME;" | cut -f1

とすることでテーブルのカラム名を取得することができます．

コード説明

以上までである程度概観を見れたと思うので，いよいよ本題に入りたいと思います．コードの部分部分を説明していきます．コード全体はGitHubでご確認ください．

アクセス情報を保存

CURRENT=$(cd $(dirname $0) && pwd)  # シェルスクリプトがあるディレクトリ
DBUSER=root                         # データベースユーザー名
PASS="PASSWORD"                     # データベースパスワード
DBNAME=information_about_school     # データベース名

# 今日の日付を取得
DATE=$(date "+%Y%m%d")

# MySQLをバッチモードで実行するコマンド
CMD_MYSQL="mysql --local-infile=1 -u${DBUSER} ${DBNAME}"

データベースにアクセスするための情報を保存しています．
CURRENT=\$(cd \$(dirname \$0) && pwd)についてはこちらで詳しく説明しましたのでご参照ください．
簡単に説明すると，どこから実行しても結果が同じようになるためのもので，シェルのディレクトリの場所をCURRENTに保存します．
--local-infile=1はLOAD DATA LOCAL INFILEを使用するのに必要なオプション設定です．

バックアップディレクトリを作成

# バックアップ先ディレクトリを作成
backup_directory_path=$CURRENT/BackUp/${DATE}
if [ ! -e $backup_directory_path ]; then
    mkdir $backup_directory_path
fi

実行日の日付のディレクトリがBackUpになければ作成します．
-eでディレクトリがあるかチェックしています．

information_about_recordsを空に

# information_about_recordsを空に
MYSQL_PWD=${PASS} $CMD_MYSQL <<-EOF
TRUNCATE TABLE information_about_records;
EOF

information_about_recordsは一時的にデータを保存するデータベースなので，永続的に保存する必要がありません．むしろデータが残っていると処理が面倒になるので， TRUNCATE することでテーブルを初期化しています．
EOF で囲んでいるのはヒアドキュメントというものです．
<< EOFと書いた場合，行頭のタブを無視してくれないので， <<- EOFとします．

成績ファイルに対しての処理

# --------------成績ファイルに対しての処理----------------
# ディレクトリ内にあるファイルの絶対パスを取得
for csvpath in $(pwd)/*; do
    # ファイル名を取得
    filename=$(basename ${csvpath})

    # "record"が含まれているファイルだけを処理
    if [ $(echo ${filename} | grep 'record') ]; then
        # クラスを取得
        class=${filename%_*}

        # 出席番号や成績などがまとめられたテーブルを作成する．
        MYSQL_PWD=${PASS} $CMD_MYSQL <<-EOF
        LOAD DATA LOCAL INFILE "${csvpath}"
        INTO TABLE information_about_records
        FIELDS
            TERMINATED BY ","
            OPTIONALLY ENCLOSED BY '"'
        LINES
            TERMINATED BY "\r\n"
        IGNORE 1 LINES
            (@出席番号, @数学, @国語, @英語, @社会, @理科)
        SET
            class = "${class}",
            number = @出席番号,
            record_of_math = @数学,
            record_of_japanese = @国語,
            record_of_english = @英語,
            record_of_society = @社会,
            record_of_science = @理科;
        EOF

        # エラーが発生しなければファイルをバックアップディレクトリに移動
        if [ $? -gt 0 ]; then
            # エラー処理
                echo "エラーが発生しました．"
        else
            #正常終了
            echo "正常に終了しました．"
            mv $csvpath $backup_directory_path
        fi
    fi
done
# -------------------------------------------------------------------

\$(basename \${ファイル名})で拡張子を除いたファイル名を取得できます．

if [ $(echo ${filename} | grep 'record') ]; then
 ．．．
fi

これで${filename}に'record'がが含まれるか判定します．これによって成績ファイルに対してのみ処理することで，先に成績のデータベースを作成します．先に成績のデータベースを作成することで，後でメインのデータベースを作成する際にデータを取り出すことができます．

# 出席番号や成績などがまとめられたテーブルを作成する．
MYSQL_PWD=${PASS} $CMD_MYSQL <<-EOF
．．．
EOF

mysqlはmysql -uUSER -pPASSとしてもアクセスすることができるのですが，「パスワードをコマンドラインに入力するのは危険だよ」といったエラーメッセージが表示されてしまいます．MYSQL_PWD=${PASS}とすることでそのエラーメッセージが表示されないようにします．
これもコマンドラインに入力してしまっているので安全ではないのですが，自分で試しに使ってみる程度であれば大丈夫だと思います．安全なアクセスにするためにはconfigなどに書いたりしてアクセスするようにするといいみたいです．
<<- EOFの前をcatとすると実行したSQL文を表示することができます．@数学などはmysql上で展開されるので表示することはできませんが，ある程度参考にはなると思います．

生徒情報ファイルに対しての処理

# ----------生徒情報ファイルに対しての処理-------------
for csvpath in $(pwd)/*; do
    # ファイル名を取得
    filename=$(basename ${csvpath})

    # grepは-vを指定すると否定
    # recordを含まないファイルのみ処理
    if [ $(echo ${filename} | grep -v 'record') ]; then
        # クラスを取得
        class=${filename%.*}

        # 出席番号と成績を紐付けるテーブルを作成する．
        MYSQL_PWD=${PASS} $CMD_MYSQL <<-EOF
        LOAD DATA LOCAL INFILE "${csvpath}"
        INTO TABLE information_about_students
        FIELDS
            TERMINATED BY ","
            OPTIONALLY ENCLOSED BY '"'
        LINES
            TERMINATED BY "\r\n"
        IGNORE 1 LINES
            (@出席番号, @氏名, @カタカナ, @性別, @電話番号, @出身地, @血液型)
        SET
            class = "${class}",
            number = @出席番号,
            name = @氏名,
            katakana_of_name = @カタカナ,
            sex = @性別,
            telephone_number = @電話番号,
            birthplace = @出身地,
            blood_type = @血液型,
            record_of_math = (SELECT information_about_records.record_of_math FROM information_about_records WHERE information_about_records.class = "${class}" AND information_about_records.number = @出席番号),
            record_of_japanese = (SELECT information_about_records.record_of_japanese FROM information_about_records WHERE information_about_records.class = "${class}" AND information_about_records.number = @出席番号),
            record_of_english = (SELECT information_about_records.record_of_english FROM information_about_records WHERE information_about_records.class = "${class}" AND information_about_records.number = @出席番号),
            record_of_society = (SELECT information_about_records.record_of_society FROM information_about_records WHERE information_about_records.class = "${class}" AND information_about_records.number = @出席番号),
            record_of_science = (SELECT information_about_records.record_of_science FROM information_about_records WHERE information_about_records.class = "${class}" AND information_about_records.number = @出席番号);
        EOF

        # エラーが発生しなければファイルをバックアップディレクトリに移動
        if [ $? -gt 0 ]; then
            # エラー処理
                echo "エラーが発生しました．"
        else
            #正常終了
            echo "正常に終了しました．"
            mv $csvpath $backup_directory_path
        fi
    fi
done
# --------------------------------------------------------------------

やっていることはほとんど成績ファイルに対する処理と変わりませんが，record_of_mathなどにデータを入れる際にSELECT文を使っています．()で囲む必要があるので注意してください．

後処理

# バックアップが成功し，ディレクトリが空であれば元のディレクトリを削除
if [ -z "$(ls $directory)" ]; then
    rmdir ${CURRENT}/CSV/${DATE}
fi

ファイルをバックアップディレクトリに無事に移動できて，元のディレクトリが空になれば空のディレクトリが増えるのを防ぐために削除します．

まとめ・感想など

以上で二つのcsvを一つのデータベースにまとめて保存することができました．「csvファイルを処理して一つにまとめてからデータベースにアップロードする」なども考えたのですが，こっちの方が簡単に実装できそうだったのでこのやり方で実装しました．
他にもっといい方法があれば教えてくださるとありがたいです！

できるだけ詳しく書くと書いておきながらそこまで詳しくは説明できてませんね，，すみません ;　;
プログラミングってわからないときは簡単なこともわからないのに，わかってからは出来ることが当たり前になってしまって丁寧な解説が難しくなってしまう気がします．丁寧にわかりやすい解説をしているサイトや人などをみると，すごいなぁと感心してしまいます．

中身とは関係ないのですが，mysqlのauto_incrementって値が飛ぶことがあるみたいです．動作を確認しているとidが飛んでいて驚きました．それだけですが．

また何か作ったりすればちょくちょく書いていくと思います．

質問等あれば気軽にコメント等してください！