DBのデータをTDに流し込む話第二弾

#はじめに
私は現在[dots.](http://eventdots.jp/)で開発をしています。
[前回の記事](http://qiita.com/little_85/items/c0b71075c8a19a3eb8ef)でDBからTDにデータを流し込む方法を調査しました。
今回は実際にBulk Importを使ってどのようにデータを流しこんだかの話です。

#環境
td-agentのバージョン

```bash
$ rpm -qi td-agent
Name        : td-agent                     Relocations: /
Version     : 2.3.0
・・・
```

td commandのバージョン

```bash
$ td --version
0.13.0
```

java

```bash
$ java -version
openjdk version "1.8.0_71"
OpenJDK Runtime Environment (build 1.8.0_71-b15)
OpenJDK 64-Bit Server VM (build 25.71-b15, mixed mode)
```

#やること
1日1回MysqlのDBの特定のデータをTDに流しこむために以下のことをやります。

1. Mysqlからデータとってくる！
2. TDにデータなげる！

以上！

っていうのは嘘です。
それぞれについて詳しく話します

## Mysqlからデータとってくる
前回の記事に書いたように[blob型のカラムが存在するテーブルはMysqlから直接importできませんでした](http://qiita.com/little_85/items/c0b71075c8a19a3eb8ef#unsupported-jdbc-type--4)。(残念)
直接importできるテーブルはtdコマンドが全部やってくれます。
こんな感じのコマンドを叩きます

```bash
td import:auto \
           --auto-create td_db_name.td_table_name \
           --format mysql \
           --db-url jdbc:mysql://dots_hostname/dots_db_name \
           --db-user dots_user \
           --time-column created_at \
           --db-password　dots_db_password \
           table_name
```
じゃあ、直接Importできないテーブルはどうやったか。
mysqlコマンドでデータを取得してtsvにします。
(第一弾の記事ではcsvにすると言ってましたが、試行錯誤の途中でtsvになりました。今思えばcsvのままでも問題なかったです。)
こんなかんじ

```sql
mysql -u user_name -D db_name -h host_name -p
-e "SELECT col1,col2,col3,created_at FROM table_name
INTO OUTFILE "outfile.tsv" FIELDS TERMINATED BY '\t'"
``` 
outfileはこんな感じ↓

```bash
$ cat outfile.tsv
xxx hoge fuga
XXX HOGE FUGA
```

## TDにデータなげる
mysqlから直接importできるテーブルは↑で記載したtdコマンドでそのまま流し込まれます。

tsvにしたデータをTDになげる方法

```bash
td import:auto \
           --auto-create td_db_name.td_table_name \
           --format tsv \
           --columns col1,col2,col3 \
           --time-column created_at \
           --time-format "%Y-%m-%d %H:%M:%S" \
           --db-password　dots_db_password \
           outfile.tsv
```
こんなコマンドを書きます
そう難しいことはないです、このままです

### 困ったこと(1)
そう、難しくはなかったのですが扱っているデータ上イベントタイトル等でゴミが入ってくる場合がありました。
そのゴミのせいでtsvファイルにしたらうまくimportできるわけではありませんでした。
今回出てきたゴミのタグいいとしては

* ダブルクォートの囲みが前は全角なのに後ろは半角になっている
	* ダブルクォートが閉じられてないと判定されてエラーになる
	* 例)楽しい”PHP"
* イベントタイトル後方に謎のタブ文字
	* タブ文字が入るせいで間違ったところでカラムが1つ増えてしまいカラム数の整合性がとれずエラーになる

#### 解決策
今回の流し込みは分析につかうための流し込みなので、タイトルがオリジナルと完全である必要がありませんでした。
そのため、「"」とタブ文字をREPLACEしてしまいました。
mysqlコマンドはこんな感じに変更します。
col1にイベントタイトル等が入るとします。

```sql
mysql -u user_name -D db_name -h host_name -p
-e "SELECT REPLACE(REPLACE(col1,"\t"," "), "\"", "") as col1,col2,col3,created_at FROM table_name
INTO OUTFILE "outfile.tsv" FIELDS TERMINATED BY '\t'"
```
**「"」=> 消す**
**タブ文字=>半角スペース**
に各々RPLACEしています。

### 困ったこと(2)
importをするときSchemeの型指定をしないと、サンプルのデータで勝手に型を判定してカラムの型指定をしてくれるみたいです。

```bash
sample row: {"col1":"テスト","col2":"NULL","col3",0,"created_at":"2016-02-10 11:27:23"}
```
これでいうと、col1とcol2とcreated_atはstring型,col3はlong型になります。

MysqlのtableでNULLを許可してるint型のカラムは
サンプルで数字が入ってるレコードを拾われるとTD側のtableの型指定をlongにされてしまいます。

ここで困るのが *NULL* の扱い。
NULLはstring型で判定されるので、long型のカラムにはINSERTできずエラーになってしまいそのレコードはimportできません。

#### 解決策
td import:autoのオプションで型指定してあげましょう！！！

```bash
--column-type col3:string
```
これつけるだけ、簡単だ。

##その他
### 日毎のデータの入れ替え
日ごとにTDの同一テーブルのデータを入れ替えています。
どのような方法をとっているか記載しておきます。

`table`
というテーブルのデータを入れ替える場合

1. `table_Ymd`テーブルをcreateしてデータをimport
2. `table`と`table_Ymd`をswap
3. `table_Ymd`をdelete

これで`table`が最新の状態になりました。
深夜に入れ替えている＆サービスに関係しているところではないので寸断など気にせずGOGOしてます。

### リモートDBのデータはINTO OUTFILEできない
すごい当たり前のことなんですけど、本番で動かしてみて発覚しました。 **恥**
stg環境まではローカルのDBにつなぎにいくので気づきませんでした。
本番はリモートなので、うまく動かず・・。
出力したものをリダイレクトしてファイルに書き出すよう変更しました。

```sql
mysql -u user_name -D db_name -h host_name -p
-e "SELECT REPLACE(REPLACE(col1,"\t"," "), "\"", "") as col1,col2,col3,created_at FROM table_name"
> outfile.tsv
```

# さいごに
今まで書いてきたことをscriptに詰め込んで毎日cronで動かしています。
エンジニア4年間やってきましたが、無知すぎでした。
試行錯誤しすぎて不必要なことまでやってるかもしれないので、何か美しい方法があれば教えて下さい。