概要
rubyで動的に生成したクエリをDBに対してパラメータを少しずつ変えて繰り返し実行したいけど、対象のDBはsshしないとアクセスできないんだよな〜って場合の解決方法です。（もっと良い方法あったらコメントでこっそり教えてください。）
まずはローカルで
まず、以下のような例でローカルのDBに対してクエリを繰り返し実行するrubyのプログラムを書いてみます。
ストーリー
- 発行されるクエリはバッチの対象者抽出クエリ
- 条件の日付を１日づつ変更して各日付でどれだけ抽出されるのか調べたい
test.rb
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
上記のものを実行すると以下のような感じで結果が出てきます。
sh
$ ruby test.rb
2021-06-01
0 アカウント
2021-06-02
1 アカウント
2021-06-03
0 アカウント
2021-06-04
0 アカウント
ssh越しのDBに対して実行できるように変更していく
ssh越しのDBに対して実行できるように変えたものが以下です。
test2.rb
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ダンプしてきてやればいいじゃんと思う方がいらっしゃるかもしれませんが、さっとダンプできるならその方がいいと自分も思います。
でも、バッチなどの抽出条件が複雑で複数テーブルをダンプしてこないといけなく、レコード数も膨大となるとダンプしてくるのも時間がかかります。
そういった場合に自分はこういった方法でやっています。
最後まで読んでいただきありがとうございました。