get github watch events in a month

create_mappings.sh

curl -XPUT 'http://localhost:9200/_template/github-watch' -d @github-watch-mappings.json

github-watch-mappings.json

{
  "template": "github-watch-*",
  "mappings": {
    "logs": {
      "properties": {
        "repo_url": {
          "type": "string",
          "fields": {
            "raw": {
              "type": "string",
              "index": "not_analyzed"
            }
          }
        }
      }
    }
  }
}

github_watch_monthly_bigquery.sql

select repository_url, actor, created_at
from [githubarchive:github.timeline]
where type="WatchEvent" and created_at >= '2014-08-01 00:00:00' and created_at < '2014-09-01 00:00:00'
group each by repository_url, actor, created_at
order by created_at desc;

logstash_github_watch.conf

input {
  file {
    path => [ "/home/jinzhou/Downloads/2014_08_repourl_actor_createdat.csv" ]
  }
}
filter {
  csv {
    columns => ["repo_url", "actor", "datetime" ]
  }
  date {
    locale => "en"
    match => [ "datetime", "yyyy-MM-dd HH:mm:ss" ]
    timezone => "UTC"
  }
}
output {
  #stdout { codec => rubydebug }
  elasticsearch {
    host => localhost
    index => "github-watch-%{+YYYY.MM.dd}"
  }
}