Add new column to BigQuery

Add new column to BigQuery

h2oのログにUesr-Agent情報も追加した。ログのフォーマットで%{HEADERNAME}iとすると、任意のレスポンスヘッダを出力させることが出来る。

access-log:
    path: /var/log/h2o/access.log
    format: "%h %l %u %{%Y/%m/%d:%H:%M:%S}t \"%r\" %s %b \"%{User-agent}i\""

bigqueryにh2oのログを送っているので、td-agentとbigquery側でも対応する。

既存のテーブルにUser-Agent用のカラムを追加する

まず既存のテーブルのフォーマットを取得する。jqでフィールド情報のみを抽出すると便利。以下私のbigqueryの例。

bq --format=prettyjson show h2o_access_log.2016_10_12 | jq '.schema.fields' > /tmp/table.json

table.jsonの中身はこんな感じ。

[
  {
    "mode": "NULLABLE",
    "name": "remote_address",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "remote_logname",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "remote_user",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "request_time",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "request_line",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "status_code",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "size_of_the_response_body",
    "type": "STRING"
  }
]

ここに新しくuser_agentカラムを追加する。

     "mode": "NULLABLE",
     "name": "size_of_the_response_body",
     "type": "STRING"
+  },
+  {
+    "mode": "NULLABLE",
+    "name": "user_agent",
+    "type": "STRING"
   }
 ]

追加したらbqコマンドで反映させる。

$ bq update h2o_access_log.2016_10_12 /tmp/table.json
Table 'ponpokopon-123456:h2o_access_log.2016_10_12' successfully updated.

増えた!そしてこのページからもフィールド増やせたんじゃないか説に気づく...。

td-agentの設定

スキーマファイルを修正。

    {
      "name": "size_of_the_response_body",
      "type": "STRING"
 +  },
 +  {
 +    "name": "user_agent",
 +    "type": "STRING"
    }
  ]

シミュレータで再度フォーマット作り直し。

 -  format /(?<remote_address>[^ ]*) (?<remote_logname>[^ ]*) (?<remote_user>[^ ]*) (?<request_time>[^\]]*) (?<request_line>[^ ]* +\S* +\S*) (?<status_code>[^ ]*) (?<size_of_the_response_body>[^ ]*)/
 +  format /(?<remote_address>[^ ]*) (?<remote_logname>[^ ]*) (?<remote_user>[^ ]*) (?<request_time>[^\]]*) (?<request_line>[^ ]* +\S* +\S*) (?<status_code>[^ ]*) (?<size_of_the_response_body>[^ ]*) "(?<user_agent>[^\"]*)"/

bqに適当にクエリ発行してuser agent情報が格納されているのを確認。

$ bq query 'select request_line,user_agent from [h2o_access_log.2016_10_12] where user_agent contains "Firefox" limit 10'
Waiting on bqjob_r5627c7efc30c844a_00000157b9758655_1 ... (0s) Current status: DONE
+------------------------------------------------------------------------+--------------------------------------------------------------------------+
|                              request_line                              |                                user_agent                                |
+------------------------------------------------------------------------+--------------------------------------------------------------------------+
| "GET /content/images/2015/01/----------2015-01-15-21-52-50.png HTTP/2" | Mozilla/5.0 (Windows NT 6.1; WOW64; rv:49.0) Gecko/20100101 Firefox/49.0 |
| "GET /assets/css/screen.css?v=5b8f99b2aa HTTP/2"                       | Mozilla/5.0 (Windows NT 6.1; WOW64; rv:49.0) Gecko/20100101 Firefox/49.0 |
| "GET /content/images/2015/01/c9e42240.jpeg HTTP/2"                     | Mozilla/5.0 (Windows NT 6.1; WOW64; rv:49.0) Gecko/20100101 Firefox/49.0 |
| "GET /content/images/2015/01/IMG_1352.png HTTP/2"                      | Mozilla/5.0 (Windows NT 6.1; WOW64; rv:49.0) Gecko/20100101 Firefox/49.0 |
| "GET /assets/js/jquery.fitvids.js?v=5b8f99b2aa HTTP/2"                 | Mozilla/5.0 (Windows NT 6.1; WOW64; rv:49.0) Gecko/20100101 Firefox/49.0 |
| "GET /assets/fonts/casper-icons.woff?v=1 HTTP/2"                       | Mozilla/5.0 (Windows NT 6.1; WOW64; rv:49.0) Gecko/20100101 Firefox/49.0 |
| "GET /assets/js/prism.js?v=5b8f99b2aa HTTP/2"                          | Mozilla/5.0 (Windows NT 6.1; WOW64; rv:49.0) Gecko/20100101 Firefox/49.0 |
| "GET /content/images/2015/01/----------2015-01-15-21-50-41.png HTTP/2" | Mozilla/5.0 (Windows NT 6.1; WOW64; rv:49.0) Gecko/20100101 Firefox/49.0 |
| "GET /content/images/2016/02/--.png HTTP/2"                            | Mozilla/5.0 (Windows NT 6.1; WOW64; rv:49.0) Gecko/20100101 Firefox/49.0 |
| "GET /assets/js/index.js?v=5b8f99b2aa HTTP/2"                          | Mozilla/5.0 (Windows NT 6.1; WOW64; rv:49.0) Gecko/20100101 Firefox/49.0 |
+------------------------------------------------------------------------+--------------------------------------------------------------------------+

良さげ☆

ちなみにuser_agentカラム追加前のスキーマではuser_agentカラムはNULLになる。