Search code examples
rdplyrmonetdb

Dplyr backend is not able to find applicable method for 'sql_translate_env'

I try to run a simple dplyr command, on an monetdb SQL-backend. The connection itself seems to work, but the dplyr call fails. I restarted the RSession and tried to reinstall the MonetDB.R package again, without success.

library(dplyr)
db <- MonetDB.R::src_monetdb("ai_db", user="analyst", host="monetdb.local", password="mypassword")

## works!
DBI::dbGetQuery(db$con, "SELECT count(*) from users")

## fails
db %>% tbl("users") %>% mutate(n= n())

Source: query [?? x 7] Database: MonetDB 11.25.5 (Dec2016-SP1)

Error in UseMethod("sql_translate_env") : no applicable method for 'sql_translate_env' applied to an object of class "MonetDBConnection"

I installed dplyr using the default repository/settings (packages.install("dplyr")) in a docker container (rocker/verse:3.3.2).

UPDATE 1: MonetDBLite instead od MonetDB.R

Now I installed MonetDBLite and I use the following code to create a connection: 

con <- mc(dbname="ai_db", user="analyst", password="mypassword", host="monetdb.local",
          timeout=86400000)
db <- MonetDBLite::src_monetdb("ai_db",con=con)

I set a quite high timeout, because of a error message indicating a to low one (see below). However, the command fails again and I don't think that it is related to the timeout, because it fails immediately.

## fails
db %>% tbl("users") %>% mutate(n= n())

Source: query [?? x 7] Database: MonetDB 11.25.5 (Dec2016-SP1)

Error in .mapiRead(conObj@connenv$socket) : Empty response from MonetDB server, probably a timeout. You can increase the time to wait for responses with the 'timeout' parameter to 'dbConnect()'.

The dataset is really small (just 5000 entries) so this should not be a big deal. MonetDB is installed ont he same server and works great, also the DBI interface works (and returns the result within a second):

DBI::dbGetQuery(con, "SELECT count(*) from videos")

UPDATE 2:

Now I tried both, the dev-version of MonetdbLite and the stable CRAN-version from the repository. Both fail, but with different errors.

packages.install("MonetDBLite")
db <- MonetDBLite::src_monetdb("ai_db", user="analyst", password="mypassword", host="monetdb.local")
db %>% tbl("users") %>% mutate(n= n())

Source: query [?? x 7] Database: MonetDB 11.25.5 (Dec2016-SP1)

Error in .mapiRead(conObj@connenv$socket) : Empty response from MonetDB server, probably a timeout. You can increase the time to wait for responses with the 'timeout' parameter to 'dbConnect()'.

devtools::install_github( "hannesmuehleisen/MonetDBLite" )
db <- MonetDBLite::src_monetdb("ai_db", user="analyst", password="mypassword", host="monetdb.local")
db %>% tbl("users") %>% mutate(n= n())

Source: query [?? x 7] Error in inherits(con_acquire(x), "MonetDBEmbeddedConnection") : could not find function "con_acquire"

Solution

  • Please use the MonetDBLite package instead, it contains the code to connect to standalone servers, too