はじめに

私は現在dots.で開発をしています。
前回の記事でDBからTDにデータを流し込む方法を調査しました。
今回は実際にBulk Importを使ってどのようにデータを流しこんだかの話です。

環境

td-agentのバージョン

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

td commandのバージョン

$ td --version
0.13.0

java

$ 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に流しこむために以下のことをやります。

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

以上！

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

Mysqlからデータとってくる

前回の記事に書いたようにblob型のカラムが存在するテーブルはMysqlから直接importできませんでした。(残念)
直接importできるテーブルはtdコマンドが全部やってくれます。
こんな感じのコマンドを叩きます

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のままでも問題なかったです。)
こんなかんじ

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はこんな感じ↓

$ cat outfile.tsv
xxx hoge fuga
XXX HOGE FUGA

TDにデータなげる

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

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

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にイベントタイトル等が入るとします。

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の型指定をしないと、サンプルのデータで勝手に型を判定してカラムの型指定をしてくれるみたいです。

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のオプションで型指定してあげましょう！！！

--column-type col3:string

これつけるだけ、簡単だ。

その他

日毎のデータの入れ替え

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

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

table_Ymdテーブルをcreateしてデータをimport
tableとtable_Ymdをswap
table_Ymdをdelete

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

リモートDBのデータはINTO OUTFILEできない

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

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年間やってきましたが、無知すぎでした。
試行錯誤しすぎて不必要なことまでやってるかもしれないので、何か美しい方法があれば教えて下さい。

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