pass-through block parameter from dbGetQuery() to fetch()

[MegaByte]

The Amazon Athena driver (http://docs.aws.amazon.com/athena/latest/ug/connect-with-jdbc.html) fails with

Error in .jcall(rp, "I", "fetch", stride, block) :
java.sql.SQLException: The requested fetchSize is more than the allowed value in Athena. Please reduce the fetchSize and try again. Refer to the Athena documentation for valid fetchSize values.

RJDBC sets a fetch block size of 2048 by default (https://github.com/s-u/RJDBC/blob/master/R/class.R#L306) but Amazon Athena wants the size to be <= 999 (this number is not actually documented).

[RoyalTS]

Is there any way one can override the default and set the fetchSize to a lower level?

[MegaByte]

I did this as a temporary workaround (since I am using multiple databases at once):

setMethod("dbGetQuery", signature(conn="JDBCConnection", statement="character"),  def=function(conn, statement, ...) {
  r <- dbSendQuery(conn, statement, ...)
  on.exit(.jcall(r@stat, "V", "close"))
  if (conn@jc %instanceof% "com.amazonaws.athena.jdbc.AthenaConnection") fetch(r, -1, 999) # Athena can only pull 999 rows at a time
  else fetch(r, -1)
})

[s-u]

Right, you can simply use

q <- dbSendQuery(c, ...)
fetch(q, -1, block=999)

dbGetQuery is just a convenience to call dbSendQuery followed by fetch

I guess the issue here is that we could pass-through the block parameter from dbGetQuery() to fetch()

[RoyalTS]

The workaround works, thanks!

But yeah, it'd be great if dbGetQuery would pass this through or if one could set this as an option more globally?

[nfultz]

This is fixed in AWR.Athena for whatever it's worth.