[SOLVED] How to use R DBI to create a view?

How to use R DBI to create a view?

I'm trying to use R's DBI library to create a view on an Athena database, connected via JDBC. The dbSentStatement command, which is supposed to submit and execute arbitrary SQL without returning a result, throws an error when no result set is returned:

DBI::dbSendStatement(athena_con, my_query)
Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set",  : 
  Unable to retrieve JDBC result set
  JDBC ERROR: [Simba][JDBC](11300) A ResultSet was expected but not generated from query <query repeated here>

In addition, the view is not created.

I've tried other DBI commands that seemed promising (dbExecute, dbGetQuery, dbSentQuery), but they all throw the same error. (Actually, I expect them all to - dbSendStatement is the one that, from the manual, should work.)

Is there some other way to create a view using DBI, dbplyr, etc.? Or am I doing this right and its a limitation of RJDBC or the driver?

Solution

RJDBC pre-dates the more recent DBI specification and uses a different function to access this functionality: RJDBC::dbSendUpdate(con, query) .

DBI's dbSendStatement() doesn't work here yet. For best compatibility, RJDBC could implement this method and forward it to its dbSendUpdate() .