How to make datas our friends

「エンジニアは発信していくことが責務である」という言葉に感化されて始めた勉強したことを書き留めていく備忘録的なやつ。

CSVファイルをローカルのMySqlDBに取り込む

0. 概要

AppsFlyer(計測ツール)から落としてきたCSVのローデータを、ローカルのMySqlDBに取り込むのに若干苦戦したので、無事成功するまでの過程を後世に残そうと思いメモ。かなり基礎的な技術だと思われるので、対象読者は初心者向けだと思われる。

今回ハマったポイント

CSVの1行目(ヘッダー部分)まで読み込んでしまい型が違うと怒られる。

・ローデータのCSV内にある文字列"null"をNULL値として扱ってもらえず、
 INTEGER型のカラムで型が違うと怒られる。

・LOAD DATA INFILEコマンドを実行しても
 1行目のデータしかテーブルにINSERTされない。

 

はい、見ての通りめちゃ初歩的なミスだと思います(ΦωΦ)

(だって初めてやったんだもん、大目に見て欲しい)

1. 前準備

ローカルDBにCSVを取り込むためにデータベースとテーブルを用意する。
今回はこんな感じで用意しました。

データベースを用意(`・ω・´)ゞ

-- 新しいデータベースを作成する
mysql> CREATE DATABASE localdb_jp;

 テーブルを用意(`・ω・´)ゞ

-- 新しいテーブルを作成する
mysql >
CREATE TABLE tmp_localdb_jp.appsflyer_data (
~~~ 省略 ~~~
);

2. CSVを用意したテーブルに読み込む

早速DLしてきたCSVファイルをMySQL側でロードさせてみる。


MySQLにはLOAD DATA INFILEコマンドという、CSVをさくっと読み込んでくれる超絶便利なコマンドがあるのでそれを使うことにする。
(INSERT文を作って読み込ませるよりめちゃ早い)

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

▼構文はこんな感じ

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name,...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

SET文を使えば色々融通が効きそうな予感、でも今回は使わない。

早速それっぽいものを作成して実行してみる。

▼クエリ

mysql >
LOAD DATA INFILE "/PATH/TO/data.csv"
INTO TABLE tmp_localdb_jp.appsflyer_data
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY "\n";

▼実行結果

ERROR 1292 (22007): Incorrect datetime value: 'attributed touch time' for column 'attributed_touch_time' at row 1 

早速怒られる。

そりゃそうだ、そんな簡単に成功しないのは想定内だぜ。

どうやら、1行目もデータとして読み込んでしまっているのが原因で、カラムの型と実際のデータ型が違うよとのこと。

よく見たら構文のところに一行目を飛ばして読み込むっぽいオプションがある。(フィーリングで書き始めないでちゃんと見ましょうねというオチ)

IGNORE number { LINES | ROWS}

早速、それっぽくクエリに足して再度実行してみる。

▼クエリ

mysql >
LOAD DATA INFILE "/PATH/TO/data.csv"
INTO TABLE tmp_localdb_jp.appsflyer_data
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY "\n"
IGNORE 1 LINES;

▼実行結果

ERROR 1366 (HY000): Incorrect integer value: 'null' for column 'event_revenue' at row 1

はい、また怒られたー(´^ω^)

今度はnullをNULL値として扱ってもらえずINTEGER型のカラムに文字列が入ってきてるぜ親分!的なエラーです。(その辺柔軟にやってくれよ)

あまり作業量は増やしたくないのですが、渋々CSV内のデータを置換するハメに。(もっといい方法あるなら教えて欲しいんだ)

とりあえず、シェルさんになんとかしてもらう。

# LC_ALL=C sed -i -e 's/null/NULL/g' data.csv

これでひとまずnullがNULLになったのでもう一回トライしてみる。

無事通りました(`・ω・´)ゞキリ

mysql> SELECT COUNT(*) FROM tmp_localdb_jp.appsflyer_data;

+----------+

| COUNT(*) |

+----------+

|     3006 |

+----------+

1 row in set (0.06 sec)

番外編:LOADしてもデータが1行分しか読み込まれない 

 実はここに至るまでに、実行しても1行文しかデータが読み込まれないという状況に悩まされていました。

 

原因は、文字コードの違いで(余計なことして改行コードが変わっていたっぽい)LINES TERMINATED BY "\n"の部分を\nから\rにすることで解決しました。

 

どちらを使えばいいかわからない人はCSVファイルをtxtエディタで一回開いて、コマンドF(検索)で両方検索してみるとどちらの改行コードが使われているかすぐにわかります。