Sometimes queries return nil, second execution returns results

[vanetten]

Thank you BTW.

I've been using BigBroda for several months against VERY large BigQuery databases.

I have an issue where queries occasionally return nil.

If I reload the same query, it works fine the second time.
My gut tells me this is a BigQuery "too big" or "timeout" issue.
Where the second execution, BigQuery has cached the result.
Have you ever seen this?
Any workarounds?

Bill

[michelson]

Its hard to tell what might be the problem , but it could be the cache thing that you mention
how large is your dataset ?
ps: I'm sorry by the delay , I'm on vacations right now and my response time could be slow

[vanetten]

The largest table is 3TB.
As I mentioned, occasionally queries return nil, then return what they're supposed to on reload.
I can deal with that, but I would like to rescue from it somewhere.
Can't figure out what to code to catch the error.
Hate to place a rescue around every single big BigQuery query.

[michelson]

yeah , but it seems to be not an exception to catch, is a nil object instead. so a retry with a timeout is needed.

this behavior occurs only for the first query or this occurs for the subsequents ones?

[vanetten]

Found a solution to my issue!!!
Retry on result['jobComplete'] == false.
I will fork and generate a pull request.

class JobIncomplete < Exception
end
def exec_query(sql, name = nil, binds = [])
log(sql, name, binds) do
result = nil
Retriable.retriable on: JobIncomplete, tries: 10 do
result = BigBroda::Jobs.query(@config[:project], {"query"=> sql })
unless result['jobComplete']
log("BigQuery Job Incomplete, trying again, #{Time.now}", nil) {}
raise JobIncomplete
end
end
cols = result["schema"]["fields"].map{|o| o["name"] }
records = result["totalRows"].to_i.zero? ? [] : result["rows"].map{|o| o["f"].map{|k,v| k["v"]} }
stmt = records
ActiveRecord::Result.new(cols, stmt)
end
end

[michelson]

that's great @vanetten. I'll be looking for that PR.

cheers