Exporting Data from TreasureData to Gmail by using Result Export to HTTP

TreasureData provides a lot of way to export data. Sometimes, we want to send email with summary KPIs to own.

This section describes how to export data from TreasureData through email.

Note: This is most simple way. If you have any concerns about security, TreasureData has 2 options; SSL and StaticIPs for result output(paid option).

System Archtecture Image

image

Installations

For Message Parsing Server

1. Setup Ruby

1.1. Install rbenv

$ sudo apt-get install git build-essential libssl-dev libreadline-dev
$ git clone https://github.com/sstephenson/rbenv.git ~/.rbenv
$ git clone https://github.com/sstephenson/ruby-build.git ~/.rbenv/plugins/ruby-build

Add the followings to ~/.profile

export PATH="$HOME/.rbenv/bin:$PATH"
eval "$(rbenv init -)"

$ source ~/.profile

1.2. Install Ruby

$ rbenv install 2.2.2
$ rbenv global 2.2.2
$ ruby --version
ruby 2.2.2p95 (2015-04-13 revision 50295) [x86_64-linux]

2. Setup td2mail

2.1. Edit Configuratons

Download td2email as template.

$ git clone https://github.com/kzk/td2mail
via SendGrid

If you have a account on Sendgrid, you need to export environment variables.

$ export SENDGRID_USERNAME=<Your User Name>
$ export SENDGRID_PASSWORD=<Your Password>
via Gmail

Edit web.rb if you want to use Gmail instead of Sendgrid.

NOTE: You need to enable 2-Step Verification to get App-specific password.

Before:

Pony.options = {
  :via => :smtp,
  :via_options => {
    :address => 'smtp.sendgrid.net',
    :port => '587',
    :domain => 'heroku.com',
    :user_name => ENV['SENDGRID_USERNAME'],
    :password => ENV['SENDGRID_PASSWORD'],
    :authentication => :plain,
    :enable_starttls_auto => true
  }
}

After:

Pony.options = {
  :via => :smtp,
  :via_options => {
    :address => 'smtp.gmail.com',
    :port => '587',
    :domain => 'smtp.gmail.com',
    :user_name => ENV['GMAIL_USERNAME'],
    :password => ENV['GMAIL_PASSWORD'],
    :authentication => :login,
    :enable_starttls_auto => true
  }
}

You need to export environment variables for gmail.

$ export GMAIL_USERNAME=<Your User Name>
$ export GMAIL_PASSWORD=<Your App-specific password>

After setting Pony.options, you need to edit from setting.

  • from: appears as sender in email 
      from: "td2mail <[email protected]>",

2.2. Install dependency libraries.

$ cd td2mail/
$ gem install bundler
$ bundle install

2.3. Run server

$ bundle exec ruby web.rb -p 8080 -o 0.0.0.0

For TreasureData

3. Create Query with Result Export to HTTP

3.1. Create Query

Write new query on https://console.treasuredata.com/query_forms/new

The following query means that mail would send if error code happened too much between now and 1 hour ago.

SELECT status, count(1) as cnt
FROM nginx_access
WHERE TD_TIME_RANGE(time, TD_TIME_ADD(TD_SCHEDULED_TIME(), '-1h', 'UTC'), TD_SCHEDULED_TIME(), 'UTC')
AND status != '200'
Group by status
HAVING count(1) >= 1

3.2. Set Result Export to HTTP

Add Result Export Setting.

Especially, you need to add email setting to path

If you want to send email to [email protected], path should be /[email protected]

Results

result

Good to have

  • Feature to deploy this app to Heroku by Heroku Button
  • Feature to display body with table format by parsing data

Related

td2slack is a similar tool. It is a little Sinatra app that bridges between Treasure Data's HTTP PUT result output functionality and Slack's Incoming Webhook API.