こんにちは! HANDAI JKの杉森です!もうすぐ今年が終わります!皆さんにとって、どんな年だったでしょうか?　来年に向けての抱負を立て始めるのもいいかもしれませんね!

今回は、「ちょっとしたレガシーと戦ってきた」の後編になります。前回の記事はこちらになります!

問題点の整理

前回の最後に挙げたCSVファイルをデータベースへ移行するための問題点は以下の4つです。

Excel内でセル内改行が行われていたため、1データが複数行に渡っている
データとデータ間にあるはずのカンマがたまに存在していない (例の30と31の間)
日付入力欄に「不明」という入力がある (型の不一致)
Excelの表示で分割されていた項目の統一が必要 (後に追加された仕様)

時系列で解決していったものを話していこうと思います。また、説明の短縮のために箇条書きの番号でどの問題を取り扱っているのかを説明します。(社内データを扱っているため、一部記述不足・表記の変更を行なっていますがご了承ください)

問題1のためのSQL文と問題2の出現

セル内改行されたために、ダブルクォーテーションで囲われているという性質をあえて生かしてデータを分けます。複数行にデータがまたがっているということから、一行ごとに分けるのではなく、データの最後にある「",」で分けることにしました。各データ内の分割はカンマで行われているため、SQL文はこうなります。

load data local infile "ファイルのパス" into table テーブル名 fields terminated by ',' enclosed by '",';

そのあと、データベースを覗くとこうなってました。(表示は番号のみで全体の一部)

|   29 |
|   30 |
|   32 |
|   33 |
|   34 |
|   35 |
|   36 |
|   37 |
|   38 |
|   39 |
...

31番がありませんね... こういったデータが複数存在していました。これで問題1については解決になりますが、問題2が顕著に現れました形になります。

仕様変更による問題4の発生と問題3の解決

ちょうどこのあたりで項目の統一が決まり、問題4が発生しました。幸い隣同士で項目が並んでいますが、面倒だなと思いました笑　ここからは問題3の解決になりますが、テーブルの定義で、すでにDate型でカラムを作っていたため、「不明」となっていた文字列はこうなっていました。

+------------+----------+
| 日付     | count(*) |
+------------+----------+
| 2016-03-08 |        1 |
| 2013-03-15 |        2 |
| 2014-12-01 |        2 |
| 0000-00-00 |       28 |
| 2016-03-11 |        6 |
| 2015-11-13 |        1 |
| 2015-04-01 |        1 |
| 2015-04-10 |        1 |
| 2015-09-29 |        1 |
| 2012-02-08 |        1 |
| 2014-08-12 |        1 |
…

0000-00-00となっていました。これは、MySQLの独自仕様であり、NOT NULL制約のカラムではNULLと等価であり、NULLではないという仕様です。データベースにある分には問題ないのですが、アプリケーションでこの値を扱うとなると対応していないことが多いです。また、PostgreSQLなどの他のRDBMSには移行しようとする際にエラーとなります。そのため、「不明」に該当するものはNULLとして扱うことに決定し、新たに追加される日程不明のものはNULLとすることになりました。そうなると、0000-00-00は邪魔になるので、データの更新を行います。

update テーブル名 set 日付 = NULL where 日付 = 0000-00-00;

+------------+----------+
| 日付     | count(*) |
+------------+----------+
| 2016-03-08 |        1 |
| 2013-03-15 |        2 |
| 2014-12-01 |        2 |
| NULL             |       28 |
| 2016-03-11 |        6 |
| 2015-11-13 |        1 |
| 2015-04-01 |        1 |
| 2015-04-10 |        1 |
| 2015-09-29 |        1 |
| 2012-02-08 |        1 |
| 2014-08-12 |        1 |
…

これで問題3の解決となりました! Date型を扱うが、カラムの設定をNOT NULLにしたい場合は、1000-01-01 をNULLの値として扱うのが良いようです。

問題2と4の解決 (物理)

ここまではそれっぽく解決してきましたが、ここからはCSVファイル自体を修正することとなりました。文字列処理のプログラムの作成も考えましたが、それをするよりもファイル自体に修正を加える方が速いと判断したためです。もっとデータ量が多かったら、コード書いて文字列処理が行われたCSVファイルを作成した方が速かったと思います。

問題4の解決

ここで使用するのがエディタのAtomになります。CSVファイルを覗いていると、統一する項目の間は、いずれも「","」で区切られていました。この規則性があるのならば、一括でその部分を空文字に置換してやればいい! そう考えてAtomを開き、エディタの下の方に出てくる「すべて置換」を使いました。一発ですべて変わるので、置換したくないものが含まれていないか確認しましょう。

f:id:handaijk:20191227085351p:plain