rotate_analytics.rb

#!/usr/bin/env ruby
# Dump mongodb data to MySQL

require 'time'
require 'aws-sdk'
require 'json'
require 'csv'

puts "Initializing..."
s3 = AWS::S3.new(
  :access_key_id => 'AWS_ACCESS_KEY_ID',
  :secret_access_key => 'AWS_SECRET_ACCESS_KEY')

current_time = Time.now
timestamp = (current_time - 60).to_i
current_date = (current_time - 60).strftime("%Y-%m-%d")

output_filename = "#{timestamp}.json"
output_dir = "/mnt/backups/mongodump"
output_filepath = File.join(output_dir, output_filename)
puts "Done."

puts "Taking backup..."
mongo_dump_cmd = 'mongoexport -d analytics -c analytics -q "{ timestamp: { \$lte: ' + timestamp.to_s + '}}" --jsonArray --out ' + output_filepath
system(mongo_dump_cmd)
puts "Done."

puts "Deleting imported data from mongo..."
mongo_delete_cmd = 'mongo analytics --eval "db.analytics.remove({timestamp: {\$lte: ' + timestamp.to_s + '}})"'
system(mongo_delete_cmd)
puts "Done."

puts "Uploading dumpfile #{output_filepath} to S3..."
s3_key = File.join(current_date, output_filename)
s3.buckets["hacker-analytics-backup"].objects[s3_key].write(Pathname.new(output_filepath))
puts "Done."

mysql_table_name = "actions"
csv_filename = "#{mysql_table_name}.csv"
csv_filepath = File.join(output_dir, csv_filename)

puts "Converting json to csv ..."
CSV.open(csv_filepath, "w") do |csv|
  json_data = JSON.parse(open(output_filepath).read)
  json_data.each do |datum|
    csv << [datum["_id"]["$oid"], datum["action"], datum["params"]["action_id"], datum["uid"], datum["params"].to_json, datum["timestamp"]]
  end
end
puts "Done."

puts "Exporting csv to mysql ..."
mysql_import_cmd = 'mysqlimport --local --fields-optionally-enclosed-by="\"" --fields-terminated-by=, --compress -h challenges.chvuux5tfzfx.us-east-1.rds.amazonaws.com -u analytics_user -pipostanalytics analytics ' + csv_filepath
system(mysql_import_cmd)
puts "Done."

puts "Wrapping up..."

puts "deleting csv file"
system("rm #{csv_filepath}")

puts "deleting json file"
system("rm #{output_filepath}")

puts "Done."