この記事はEmbulk Advent Calendar 2015 - Qiita の5日目として書かれています。
こんばんは @ara_ta3 です。
この前GoogleFormから投稿されたデータをMySQLに突っ込んで集計したいという思いがあったので、今回はそれをやってみようと思います。
GoogleFormを利用するとGoogleSpreadSheetにそのデータが追記されていくので下記２つのpluginを利用します。
インストール
$gem install embulk Fetching: jruby-jars-9.0.0.0.gem (100%) Successfully installed jruby-jars-9.0.0.0 Fetching: embulk-0.7.10.gem (100%) Successfully installed embulk-0.7.10 Parsing documentation for jruby-jars-9.0.0.0 Installing ri documentation for jruby-jars-9.0.0.0 Parsing documentation for embulk-0.7.10 Installing ri documentation for embulk-0.7.10 Done installing documentation for jruby-jars, embulk after 0 seconds 2 gems installed $embulk --version embulk 0.7.10 $embulk gem install embulk-input-google_spreadsheets 2015-12-05 07:59:33.904 +0000: Embulk v0.7.10 Fetching: embulk-input-google_spreadsheets-0.1.0.gem (100%) Successfully installed embulk-input-google_spreadsheets-0.1.0 1 gem installed $embulk gem install embulk-output-mysql 2015-12-05 08:02:45.172 +0000: Embulk v0.7.10 Fetching: embulk-output-mysql-0.4.1.gem (100%) Successfully installed embulk-output-mysql-0.4.1 1 gem installed
簡単！！
embulk-input-google_spreadsheets の設定
ダミーのフォームを作ってみました。
フォームに投稿があればSpreadSheetも同時に作成されます。
https://docs.google.com/forms/d/1fPhGSBV1ahoLkfmrutKjA6PPyqFdNfQf3ac5B596raU/viewform
in: type: google_spreadsheets service_account_email: 'aaaaa@bbbbb.iam.gserviceaccount.com' p12_keyfile: '/path/to/keyfile.p12' spreadsheet_id: '{spread sheet id}' #URLが https://docs.google.com/spreadsheets/d/hogehogefugafuga/edit の場合 hogehogefugafuga がid columns: - {name: timestamp, type: string} - {name: ans, type: string}
tips
Q. timestampはなんでstring?
A. timestamp型にしてformatを指定したけどうまく行かなかったからです。previewしたら1970/01/01 とかになりました。つらい。MySQL側に突っ込む際にぱーすできるので、とりあえずはstringで取得します。
$embulk preview config.yml 2015-12-05 17:35:19.151 +0900: Embulk v0.7.10 2015-12-05 17:35:20.305 +0900 [INFO] (preview): Loaded plugin embulk-input-google_spreadsheets (0.1.0) +---------------------+------------+ | timestamp:string | ans:string | +---------------------+------------+ | 2015/12/05 17:27:07 | はい | | 2015/12/05 17:27:14 | はい | | 2015/12/05 17:27:22 | はい | | 2015/12/05 17:27:27 | はい | +---------------------+------------+
みなさんEmbulk大好きですね。
embulk-output-mysql の設定
最終的な設定はこんな感じです。
2015/12/05 現在、embulk-input-google_spreadsheets側で差分取得などは出来ないので、ここでは全部入れ替えるmode (replace) にしています。
（他のプラグインとかはどこまで取得したとかを覚えておいてとかできるのかな）
out: type: mysql host: localhost user: root password: "" database: test default_timezone: "Asia/Tokyo" table: embulkadvent mode: replace column_options: timestamp: {type: "DATETIME", value_type: string, timestamp_format: "%Y/%m/%d %H:%M:%S"} ans: {type: "VARCHAR(255)"}
tips
Q. Modeは他になにがあるの
A. https://github.com/embulk/embulk-output-jdbc/tree/v0.4.1/embulk-output-mysql を見ると、insert, insert_direct, truncate_insert, merge, merge_direct, replace の6こあるらしいです。
- insert まず初めにいくつかの中間テーブルに書き込み、それらをUNION ALLしたものを対象のテーブルに書き込む
- insert_direct 直接対象のテーブルに書き込む。トランザクションが貼られてるわけではないので、途中で失敗しても、一部のデータが書き込まれている状態になる。
- truncate_insert 挙動自体はinsertと同じ。ただし、対象のテーブルに書き込む直前にTruncate Tableが走る。
- merge 発行されるクエリはほぼInsertと同じ。ただし、最後にON DUPLICATE KEY UPDATEがつく。なので、UNIQUEキー等がなければinsertと同様。
- merge_direct insert_directとinsertの関係と同様
- replace 挙動自体はinsertと同様。ただし、対象のテーブルに書き込むのではなく、全てのデータが書き込まれたテーブルを対象のテーブルにALTER TABLEによってリネームする。
という感じでした。
Q. truncate_insertとreplaceの違いは？
A. truncate_insertとreplaceが同じ説明に見えましたが、truncate_insertはTRUNCATEを行う一方で、replaceはALTER TABLEを行うという点で異なっていました。
また、truncate_insertの場合、既にテーブルが作られていないといけませんが、replaceにした場合は設定ファイルに書かれたcolumn_optionsを元にテーブルが自動的に作成されました。
まとめ
少し前に雑務でGoogleFormに投稿されたものをどこかに吐き出そうと思ってGoogleAppScriptを組んだりとか、CSVで吐き出して手動で入れたりとかしてめんどくさかったですが、Embulk使ってConfig書くだけで出来てとても楽でした。
この程度の雑務にEmbulk利用するのすごいリッチすぎる気もしますが・・・ｗ
明日のEmbulkアドベントカレンダー2015 は@hiroysato さんによる記事です。
お楽しみに
