今川館

都内勤務の地味OLです

俺的embulkでMySQLにデータを入れるときの注意点

embulk

最近、embulkをいじる機会があって、色々と思うところがあったので書いておく。
今回わたしが使ったケースではembulkのINPUTはバッチが作ったJSONL形式のファイルで、OUTPUTがMySQLのDBである。
OUTPUT先のDBのデータはAPIから専ら参照用に作られ、APIがそのデータを更新することはない。

以上の背景から思ったことを以下書いていく。

INT AUTO_INCREMENTのサロゲートキーをやめた

一般に、テーブルにはINTやBIGINTのAUTO_INCREMENTを指定されたサロゲートキーを設けるのが定石とされる。
DjangoとかRuby on Railsなどを使っていると、サロゲートキーはごくありふれたものだろう。

だが、embulkで投入するテーブルにはサロゲートキーを設けない方が良いと思った。
特に、INT型で作るのはヤバイと思う。

なぜかというと、embulkは定期的に件数の多いデータを入れる道具なので、頻繁にデータ更新が行われ、AUTO_INCREMENTも頻繁に行われるのであっという間にINTの最大値を超えてしまうからである。

INTの最大値なんて2,147,483,647(21.4億)なので、100万レコードの挿入を5分おきに実行していたら7.4日程度でオーバーフローしてしまう（！）*1

UNIQUE制約の作成はafter_loadで行うようにした

embulk-output-mysqlプラグインではafter_loadという、OUTPUTの処理が行われた後に実行すべきSQL文を書いておくことができる。

参考: https://github.com/embulk/embulk-output-jdbc/tree/master/embulk-output-mysql#configuration

after_load: if set, this SQL will be executed after loading all records.

after_load: これが設定されると、すべてのデータを投入後に設定されたSQLが実行されます。

embulkで大量件数のデータを投入することを想定すると、CREATE TABLEのタイミングではUNIQUE制約を作らず、全データ投入後に作るようにした。
最初からUNIQUEインデックスがあると挿入のコストが嵩むので、全部データを入れた後に一回にまとめてUNIQUEインデックスを作った方がトータルのコストは安くつくからである。

なお、当初はBOOL型の列に(0 OR 1)のCHECK制約を作っていたのだが、そもそもCHECK制約自体をやめてしまった。
元々のINPUTの方でこれら値がクリーンであることが保証されている(または期待される)ならば、OUTPUTの方でいちいちチェックするコストを削ってしまおうと思ったからである。

でも主キーは作っておかないとダメ

UNIQUEインデックスやサロゲートキーを作らないのは良かったのだが、同じ発想で最初は主キーを作るのもやめていた。
ところがMySQLは主キーが指定されていないテーブルに対しては裏で独自の擬似主キー的なものを作って管理するので、結果的に主キーを指定しないことにコスト的な利点はないらしい。

参考: https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column containing row ID values.

そのテーブルに主キーもユニークインデックスも指定されていないと、InnoDBは内部で隠しクラスターインデックスを作ります。
それは既定の列の値を合成した値を使用し、GEN_CLUST_INDEXという名前がつけられます。

modeはreplaceとtruncate_insertのどちらが良いのか？

OUTPUTのmodeはいくつか種類があって、採用候補にしたのが以下の二つだった。

  • replaceモード: CREATE TABLE/ DROP TABLEを行い、テーブルを丸ごと入れ替える方法
  • truncate_insertモード: 予め一時テーブルに全データを挿入しておき、後で本物のテーブルをDELETE/ SELECT-INSERTする方法

どっちが良いのかは少しもやもやと悩んだ挙句、今回はreplaceモードを採用した。
理由は特にここで述べるほど強い根拠があるわけではなく、なんとなくという程度である。

それより、TRUNCATE文を実行しないなら「truncate_insert」という名前はやめたら？　と思ってしまった(小言

TRUNCATEしないならインデックスの一括クリアもAUTO_INCREMENT値のリセットも行われないし、ロールバックは可能なわけで、違う印象を持ってしまうので良くないと思う。


最後までお読みくださりありがとうございました。

*1:これ開発中にふとしたきっかけで気づいて本当に助かったと思う。マジでヤバイと思う。