Closed yusuko closed 5 years ago
行うことを分解する
1回目の実験結果(ループ50回)
@Playerapp_vbは11回目で取得(90行)
@Playerapp_yakyuは20回目で取得(578行)
@Playerapp_cbskは3回目で取得(54行)
@Playerapp_wbskは5回目で取得(109行)
@Playerapp_tnsは2回目で取得(23行)
@rugby_girl13は50回ループしたが取得はできなかった。
結果として下記のエラーがでた。 おそらくnilであるからでたエラーであると思う。
/Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/http_command.rb:218:in `check_status': Invalid request (Google::Apis::ClientError)
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/http_command.rb:183:in `process_response'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/http_command.rb:299:in `execute_once'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/http_command.rb:104:in `block (2 levels) in execute'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/retriable-3.1.2/lib/retriable.rb:61:in `block in retriable'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/retriable-3.1.2/lib/retriable.rb:56:in `times'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/retriable-3.1.2/lib/retriable.rb:56:in `retriable'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/http_command.rb:101:in `block in execute'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/retriable-3.1.2/lib/retriable.rb:61:in `block in retriable'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/retriable-3.1.2/lib/retriable.rb:56:in `times'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/retriable-3.1.2/lib/retriable.rb:56:in `retriable'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/http_command.rb:93:in `execute'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/base_service.rb:360:in `execute_or_queue_command'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/base_service.rb:252:in `http'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google_drive-2.1.12/lib/google_drive/api_client_fetcher.rb:49:in `request_raw'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google_drive-2.1.12/lib/google_drive/session.rb:623:in `block in request'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google_drive-2.1.12/lib/google_drive/session.rb:622:in `loop'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google_drive-2.1.12/lib/google_drive/session.rb:622:in `request'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google_drive-2.1.12/lib/google_drive/worksheet.rb:338:in `save'
from /Users/anzaiyuusuke/Desktop/rails/twitter-analytics-acquirer/google_sheet_client.rb:22:in `write_in_spreadsheet'
from script.rb:15:in `block in <main>'
from script.rb:10:in `each'
from script.rb:10:in `<main>'/Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/http_command.rb:218:in `check_status': Invalid request (Google::Apis::ClientError)
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/http_command.rb:183:in `process_response'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/http_command.rb:299:in `execute_once'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/http_command.rb:104:in `block (2 levels) in execute'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/retriable-3.1.2/lib/retriable.rb:61:in `block in retriable'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/retriable-3.1.2/lib/retriable.rb:56:in `times'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/retriable-3.1.2/lib/retriable.rb:56:in `retriable'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/http_command.rb:101:in `block in execute'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/retriable-3.1.2/lib/retriable.rb:61:in `block in retriable'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/retriable-3.1.2/lib/retriable.rb:56:in `times'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/retriable-3.1.2/lib/retriable.rb:56:in `retriable'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/http_command.rb:93:in `execute'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/base_service.rb:360:in `execute_or_queue_command'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/base_service.rb:252:in `http'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google_drive-2.1.12/lib/google_drive/api_client_fetcher.rb:49:in `request_raw'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google_drive-2.1.12/lib/google_drive/session.rb:623:in `block in request'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google_drive-2.1.12/lib/google_drive/session.rb:622:in `loop'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google_drive-2.1.12/lib/google_drive/session.rb:622:in `request'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google_drive-2.1.12/lib/google_drive/worksheet.rb:338:in `save'
from /Users/anzaiyuusuke/Desktop/rails/twitter-analytics-acquirer/google_sheet_client.rb:22:in `write_in_spreadsheet'
from script.rb:15:in `block in <main>'
from script.rb:10:in `each'
from script.rb:10:in `<main>'
retryの間隔ってどれくらいにしてる?
スパム判定防止のためにも、retryの間隔は空けたほうが良いかもね
特に気にしていなかったです。 以後気をつけます!
1時間毎くらいにしますね。
2回目の実験結果(ループ80回)
localで実行
@Playerapp_vbは4回目で取得(90行)
@Playerapp_yakyuは21回目で取得(578行)
@Playerapp_cbskは3回目で取得(54行)
@Playerapp_wbskは5回目で取得(109行)
@Playerapp_tnsは2回目で取得(23行)
@rugby_girl13は7回で取得
@hir0shima_carpは17回で取得
@Player_twiは75回目で取得
ただし、Player_twiに書き込む途中で下記のエラーがでた。(メモリ落ちのエラー)
/Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/httpclient-2.8.3/lib/httpclient/session.rb:524:in `rescue in query': HTTPClient::KeepAliveDisconnected: Broken pipe (HTTPClient::KeepAliveDisconnected)
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/httpclient-2.8.3/lib/httpclient/session.rb:514:in `query'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/httpclient-2.8.3/lib/httpclient/session.rb:177:in `query'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/httpclient-2.8.3/lib/httpclient.rb:1242:in `do_get_block'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/httpclient-2.8.3/lib/httpclient.rb:1019:in `block in do_request'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/httpclient-2.8.3/lib/httpclient.rb:1138:in `rescue in protect_keep_alive_disconnected'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/httpclient-2.8.3/lib/httpclient.rb:1131:in `protect_keep_alive_disconnected'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/httpclient-2.8.3/lib/httpclient.rb:1014:in `do_request'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/httpclient-2.8.3/lib/httpclient.rb:1104:in `follow_redirect'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/httpclient-2.8.3/lib/httpclient.rb:854:in `request'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/http_command.rb:291:in `execute_once'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/http_command.rb:104:in `block (2 levels) in execute'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/retriable-3.1.2/lib/retriable.rb:61:in `block in retriable'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/retriable-3.1.2/lib/retriable.rb:56:in `times'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/retriable-3.1.2/lib/retriable.rb:56:in `retriable'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/http_command.rb:101:in `block in execute'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/retriable-3.1.2/lib/retriable.rb:61:in `block in retriable'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/retriable-3.1.2/lib/retriable.rb:56:in `times'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/retriable-3.1.2/lib/retriable.rb:56:in `retriable'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/http_command.rb:93:in `execute'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/base_service.rb:360:in `execute_or_queue_command'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/base_service.rb:252:in `http'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google_drive-2.1.12/lib/google_drive/api_client_fetcher.rb:49:in `request_raw'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google_drive-2.1.12/lib/google_drive/session.rb:623:in `block in request'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google_drive-2.1.12/lib/google_drive/session.rb:622:in `loop'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google_drive-2.1.12/lib/google_drive/session.rb:622:in `request'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google_drive-2.1.12/lib/google_drive/worksheet.rb:413:in `save'
from /Users/anzaiyuusuke/Desktop/rails/twitter-analytics-acquirer/google_sheet_client.rb:22:in `write_in_spreadsheet'
from script.rb:15:in `block in <main>'
from script.rb:10:in `each'
from script.rb:10:in `<main>'
3回目の実験結果(2回目からは時間経ってます。)
/Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/httpclient-2.8.3/lib/httpclient/session.rb:524:in `rescue in query': HTTPClient::KeepAliveDisconnected: Broken pipe (HTTPClient::KeepAliveDisconnected)
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/httpclient-2.8.3/lib/httpclient/session.rb:514:in `query'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/httpclient-2.8.3/lib/httpclient/session.rb:177:in `query'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/httpclient-2.8.3/lib/httpclient.rb:1242:in `do_get_block'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/httpclient-2.8.3/lib/httpclient.rb:1019:in `block in do_request'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/httpclient-2.8.3/lib/httpclient.rb:1138:in `rescue in protect_keep_alive_disconnected'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/httpclient-2.8.3/lib/httpclient.rb:1131:in `protect_keep_alive_disconnected'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/httpclient-2.8.3/lib/httpclient.rb:1014:in `do_request'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/httpclient-2.8.3/lib/httpclient.rb:1104:in `follow_redirect'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/httpclient-2.8.3/lib/httpclient.rb:854:in `request'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/http_command.rb:291:in `execute_once'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/http_command.rb:104:in `block (2 levels) in execute'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/retriable-3.1.2/lib/retriable.rb:61:in `block in retriable'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/retriable-3.1.2/lib/retriable.rb:56:in `times'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/retriable-3.1.2/lib/retriable.rb:56:in `retriable'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/http_command.rb:101:in `block in execute'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/retriable-3.1.2/lib/retriable.rb:61:in `block in retriable'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/retriable-3.1.2/lib/retriable.rb:56:in `times'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/retriable-3.1.2/lib/retriable.rb:56:in `retriable'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/http_command.rb:93:in `execute'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/base_service.rb:360:in `execute_or_queue_command'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google-api-client-0.21.2/lib/google/apis/core/base_service.rb:252:in `http'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google_drive-2.1.12/lib/google_drive/api_client_fetcher.rb:49:in `request_raw'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google_drive-2.1.12/lib/google_drive/session.rb:623:in `block in request'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google_drive-2.1.12/lib/google_drive/session.rb:622:in `loop'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google_drive-2.1.12/lib/google_drive/session.rb:622:in `request'
from /Users/anzaiyuusuke/.rbenv/versions/2.4.4/lib/ruby/gems/2.4.0/gems/google_drive-2.1.12/lib/google_drive/worksheet.rb:413:in `save'
from /Users/anzaiyuusuke/Desktop/rails/twitter-analytics-acquirer/google_sheet_client.rb:22:in `write_in_spreadsheet'
from script.rb:16:in `block in <main>'
from script.rb:10:in `each'
from script.rb:10:in `<main>'
データ量が多すぎるとスプレッドシートに書き込む段階でエラーが発生する。(メモリ落ち)
のようにするとか。
直近1週間だけをリアルタイムで取ってくる。という処理に書き換えてもいいかもしれない。
@nafu @ladnack 上記処理についてどう思いますか?? 日時指定というよりは、エクセルの行数で区切る形になるかもしれませんが。。。
具体的な処理は、まだきちんと考えていないですがもしご意見等あればお願いします🙇♂️
取得期間を限定することでデータ量が減って、その結果、
データ欠損なく取得できれば目的達成だと思うので、 特に個人の好みはないでーす
お二方ともありがとうございます! では、分割取得するコードを実装する方向で動きます💪
殴りメモなので、あとでわかりやすくします。
1をcsvの取得に関して案だし 2はスプレッドシートの書き込みに関して案だしする。
csvは全体を取得。
数値は変更可能。
案Aの、csvの取得範囲を変えたVer
例) 11/15の時
月初にはエスケープが0になるようにする。これを5日間単位で繰り返す。
11/14(水) 9:00の定期実行結果
次の実装
csvを書き出す範囲を限定
stored csv
に、取得しない行数を保存11/10: 11/5までのcsvの行数を取得 DBに保存 翌日からは、11/6からのデータしか取らない。 エクセルには、DBに保存された行数+1から書き出す
To do
stored_csv_length
カラムをintで追加。
現在起こっている問題
Player_twiだけ重すぎるので、そこは手動という対応も選択肢の一つとしてはある。 期間限定取得しても取れない。。。
タイトルはわざわざ毎回渡す必要ないので、カット。 またdelete_rowsしなくても、更新する際には自然と新しいものに生まれ変わる仕様である。
既存のcsvを残したまま、データを更新できることは確認した。
下記のコードで 1.csvを若い順に取得
parsed_csv_without_header = CSV.parse(csv)[1..-1]
return unless parsed_csv_without_header
reversed_csv = parsed_csv_without_header.reverse
reversed_csv.each.with_index do |row, index|
for j in 1..row.count do
worksheet[index + 30, j] = row[j - 1]
end
end
案Bの実装を考えていたが、
ので、まずは単純にAの改変を実装してみる。 問題点はスプレッドシート書き込み中のエラーだったため、 CSVを全体取得して、処理を分けてスプレッドシートに書き込む。 別issueで対応する。 https://github.com/ookamiinc/twitter-analytics-acquirer/issues/16
csvは全体を取得。
数値は変更可能。
案Aの、csvの取得範囲を変えたVer
例) 11/15の時
月初にはエスケープが0になるようにする。これを5日間単位で繰り返す。
https://github.com/ookamiinc/twitter-analytics-acquirer/issues/16 にてデータの書き込みを分割したところ、localとherokuにて10月分のデータ取得&書き込みに成功した。
プロセスにおいて主に2つの点を改善した。
What
CSVのデータサイズが大きい時に、ループをかなり回す必要があるという仮説が生まれた。 その仮説のもと
等を確認する。
HerokuにてPlayer_twiのcsvデータが1ヶ月分取れたらissue closed
Summary
https://github.com/ookamiinc/twitter-analytics-acquirer/issues/16 にてデータの書き込みを分割したところ、localとherokuにて10月分のデータ取得&書き込みに成功した。
プロセスにおいて主に2つの点を改善した。