EmbulkでGoogleFormに投稿されたデータをMySQLに吐き出すだけ。

この記事はEmbulkのカレンダー | Advent Calendar 2015 - Qiita の5日目として書かれています。

こんばんは @ara_ta3 です。
この前GoogleFormから投稿されたデータをMySQLに突っ込んで集計したいという思いがあったので、今回はそれをやってみようと思います。
GoogleFormを利用するとGoogleSpreadSheetにそのデータが追記されていくので下記２つのpluginを利用します。

github.com

インストール

$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 さんによる記事です。
お楽しみに