目次

この記事はQiitaに投稿していたものです。 このブログに自身のアウトプットをまとめるための再投稿です。

概要

rubyで動的に生成したクエリをDBに対してパラメータを少しずつ変えて繰り返し実行したいけど、対象のDBはsshしないとアクセスできないんだよな〜って場合の解決方法です。（もっと良い方法あったらコメントでこっそり教えてください。）

まずはローカルで

まず、以下のような例でローカルのDBに対してクエリを繰り返し実行するrubyのプログラムを書いてみます。

ストーリー

  • 発行されるクエリはバッチの対象者抽出クエリ
  • 条件の日付を１日づつ変更して各日付でどれだけ抽出されるのか調べたい
require 'mysql2'
require 'date'

start_date = Date.new(2021, 6, 1)
end_date = Date.new(2021, 6, 5)
current_date = start_date

db = Mysql2::Client.new(host: "127.0.0.1", username: "hoge", password: '', :encoding => 'utf8', database: 'test')

query = <<~EOS
        SELECT
          count(id)
        FROM
          users
        where
          updated_at  = ?
        EOS

while current_date < end_date do
  request = db.prepare(query)
  result = request.execute(current_date)
  result.each { |c|
        puts "#{current_date}\n#{c["count(id)"]}アカウント"
    }
end

上記のものを実行すると以下のような感じで結果が出てきます。

$ ruby test.rb
2021-06-01
0 アカウント
2021-06-02
1 アカウント
2021-06-03
0 アカウント
2021-06-04
0 アカウント

ssh越しのDBに対して実行できるように変更していく

ssh越しのDBに対して実行できるように変えたものが以下です。

require 'mysql2'
require 'date'
require 'net/ssh/gateway'

gateway = Net::SSH::Gateway.new(
  'host', # SSHするホスト
  'user', # SSHするユーザー
  port: 22,
  keys: ['~/.ssh/id_rsa']
)

start_date = Date.new(2021, 6, 1)
end_date = Date.new(2021, 6, 5)
current_date = start_date
gateway.open('DBがあるサーバーのホスト', 3306) do | port |
  db = Mysql2::Client.new(host: "127.0.0.1", port: port, username: "hoge", password: '', :encoding => 'utf8', database: 'test')

  query = <<~EOS
        SELECT
          count(id)
        FROM
          users
        where
          updated_at  = ?
        EOS

  while current_date < end_date do
    request = db.prepare(query)
    result = request.execute(current_date)
    result.each { |c|
        puts "#{current_date}\n#{c["count(id)"]}アカウント"
    }
  end
end

最後に

例だと5日分ですが、１年分をやろうとすると、とても手作業でやる気にはならないのでこういった自動化が有用に思えます。 SSHしなくてもDBダンプしてきてやればいいじゃんと思う方がいらっしゃるかもしれませんが、さっとダンプできるならその方がいいと自分も思います。 でも、バッチなどの抽出条件が複雑で複数テーブルをダンプしてこないといけなく、レコード数も膨大となるとダンプしてくるのも時間がかかります。 そういった場合に自分はこういった方法でやっています。 最後まで読んでいただきありがとうございました。

参考