Can't execute "MSK Repair table" with AWR.athena version 2.0.6.1

[luc-kalaora]

Hi, i try to update library AWR.athena version 2.0.6-1 but some R commands don't work anymore : With the oldest version i can execute this command : dbExecute(con, "MSCK REPAIR TABLE db.mytable") without errors With the new version i got this error message : Unable to retrieve JDBC result set for MSCK REPAIR TABLE db.mytable([Simba]JDBC A ResultSet was expected but not generated from query "MSCK REPAIR TABLE db.mytable". Query not executed. ) I try also dbSendQuery, dbSendStatement but it doesn't work. i use AthenaJDBC42_2.0.7.jar driver. Thanks for your help.

[nfultz]

@Respondi can you rerun your example, and then run traceback() and sessionInfo() and post the output here?

[luc-kalaora]

Sorry for the delay Here the results of the 2 commands : traceback(): 5: stop(..., " (", .jcall(x, "S", "getMessage"), ")") 4: .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ", statement) 3: .local(conn, statement, ...) 2: dbSendQuery(con2, "MSCK REPAIR TABLE wakoopa.user_properties") 1: dbSendQuery(con2, "MSCK REPAIR TABLE wakoopa.user_properties")

sessionInfo() : R version 3.5.3 (2019-03-11) Platform: x86_64-redhat-linux-gnu (64-bit) Running under: Amazon Linux 2

Matrix products: default BLAS/LAPACK: /usr/lib64/R/lib/libRblas.so

locale: [1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C LC_TIME=en_US.UTF-8 LC_COLLATE=en_US.UTF-8 LC_MONETARY=en_US.UTF-8
[6] LC_MESSAGES=en_US.UTF-8 LC_PAPER=en_US.UTF-8 LC_NAME=en_US.UTF-8 LC_ADDRESS=en_US.UTF-8 LC_TELEPHONE=en_US.UTF-8
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=en_US.UTF-8

attached base packages: [1] stats graphics grDevices utils datasets methods base

other attached packages: [1] DBI_1.0.0 AWR.Athena_2.0.6-1 aws.s3_0.3.12

loaded via a namespace (and not attached): [1] httr_1.4.0 compiler_3.5.3 R6_2.4.0 tools_3.5.3 base64enc_0.1-3 Rcpp_1.0.1 aws.signature_0.5.0 [8] xml2_1.2.0 RJDBC_0.2-7.1 digest_0.6.19 rJava_0.9-11

[nfultz]

I think the root cause is that the driver has changed and now no longer returns how many rows were affected - this is likely because it doesn't want to read all the rows added (which would cause you to get billed, I believe).

I poked around the documentation a little, and found these two mentions in the documentation:

In dbSendQuery: #' This method is for SELECT queries only. Some backends may #' support data manipulation queries through this method for compatibility #' reasons. However, callers are strongly encouraged to use #' [dbSendStatement()] for data manipulation statements.

So dbSendStatement() is the "encouraged" way to do this AFAICT.

However in dbSendStatement():

#' [dbSendStatement()] comes with a default implementation that simply #' forwards to [dbSendQuery()], to support backends that only #' implement the latter.

OK. AWR.Athena is a subclass of RJDBC, and when you look at it's source code, there is no method for dbSendStatement, so it falls back to dbSendQuery, which requests the number of rows changed, which fails. I'll check with Simon, and see if he is open to me adding that method to RJDBC, which would fix this not only for AWR.Athena, but also AWR.Redshift and hiveuberjar.

[nfultz]

@Respondi I've uploaded the 2.0.7 version to CRAN - can you update.packages to install it and try again?

[luc-kalaora]

Hi, i still have the same error :( with dbSendStatement(con2, "MSCK REPAIR TABLE wakoopa.user_properties") Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ", : Unable to retrieve JDBC result set for MSCK REPAIR TABLE wakoopa.user_properties ([Simba]JDBC A ResultSet was expected but not generated from query "MSCK REPAIR TABLE wakoopa.user_properties". Query not executed. )

• traceback() 7: stop(..., " (", .jcall(x, "S", "getMessage"), ")") 6: .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ", statement) 5: .local(conn, statement, ...) 4: dbSendQuery(conn, statement, ...) 3: dbSendQuery(conn, statement, ...) 2: dbSendStatement(con2, "MSCK REPAIR TABLE wakoopa.user_properties") 1: dbSendStatement(con2, "MSCK REPAIR TABLE wakoopa.user_properties")

• sessionInfo() R version 3.5.3 (2019-03-11) Platform: x86_64-redhat-linux-gnu (64-bit) Running under: Amazon Linux 2

Matrix products: default BLAS/LAPACK: /usr/lib64/R/lib/libRblas.so

locale: [1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C LC_TIME=en_US.UTF-8 LC_COLLATE=en_US.UTF-8 LC_MONETARY=en_US.UTF-8
[6] LC_MESSAGES=en_US.UTF-8 LC_PAPER=en_US.UTF-8 LC_NAME=en_US.UTF-8 LC_ADDRESS=en_US.UTF-8 LC_TELEPHONE=en_US.UTF-8
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=en_US.UTF-8

attached base packages: [1] stats graphics grDevices utils datasets methods base

other attached packages: [1] DBI_1.0.0 AWR.Athena_2.0.7-0

loaded via a namespace (and not attached): [1] compiler_3.5.3 tools_3.5.3 RJDBC_0.2-7.1 rJava_0.9-11

[nfultz]

I believe this is an issue with Amazon's driver, not the R package - please raise an issue with them, for example at https://forums.aws.amazon.com/forum.jspa?forumID=242&start=0