0

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"

NaN
  • 3,501
  • 8
  • 44
  • 77
  • hi, is that syntax correct? https://cran.rstudio.com/web/packages/dplyr/vignettes/databases.html – Anthony Damico Mar 03 '17 at 15:13
  • passing test cases: https://github.com/hannesmuehleisen/MonetDBLite/blob/master/tests/testthat/test_03_dplyr.R you might need `devtools::install_github( "hannesmuehleisen/MonetDBLite" )` not sure – Anthony Damico Mar 03 '17 at 15:14
  • @AnthonyDamico I tried `src_monetdb("ai_db", user="analyst", password="mypassword", host="monetdb.local")` first, but then I had to rewrite it to set the timeout (I always got this annoying timeout error). I will try to install the dev-version within the next days, but I hope that I get the stable CRAN version to work (I am using a pre-build docker image and I would prefer a solution without the need to install all the monetdb dependencies (e.g., `libmonetdb5.so`)). – NaN Mar 03 '17 at 16:42
  • @AnthonyDamico ok, it was not a dependency problem, I just had to set the permissions correctly and then I was able install the package using devtools. The newest version also fails, but with a different error (I updated my post already). – NaN Mar 03 '17 at 19:03
  • Do you mean the `ajdamico/lodown` library? should this fix the missing `con_acquire` function or the CRAN monetDBLite version? I installed it and tried the MonetdbLite devtools installation again, but I still get the same error (`could not find function "con_acquire"`) – NaN Mar 04 '17 at 13:26
  • i had linked to the wrong comment, sorry. use `devtools::install_github("hannesmuehleisen/MonetDBLite",ref="fcb21f4c4223024922221d4ed1a8e78d647d8abe")` – Anthony Damico Mar 04 '17 at 16:42
  • oh thanks! But I think I found the problem. I used `src_monetdb("ai_db",...)` instead of `src_monetdb(dbname="ai_db",...)`. Now it seems to work. – NaN Mar 04 '17 at 17:22

1 Answers1

1

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

Hannes Mühleisen
  • 2,542
  • 11
  • 13