doesn't work with `MonetDBLite`

[joelgombin]

Here is a reprex.
I guess it's an etl rather than airlines issue, though.

If MonetDBLite hasn't been implemented yet as a backend for etl, I'd be happy to help to the extent of my skills (they don't go very far in SQL, though). Maybe @hannesmuehleisen or @ajdamico might want to help as well?

library(MonetDBLite)
library(airlines)

conn <- src_monetdblite()

airlines <- etl("airlines", conn)

airlines %>%
  etl_create(years = 1987, months = 10)
#> Error in .local(conn, statement, ...): Unable to execute statement 'DROP VIEW IF EXISTS summary'.
#> Server says 'syntax error, unexpected IF in: "drop view if"
#> ' [#42000].

sessionInfo()
#> R version 3.3.1 (2016-06-21)
#> Platform: x86_64-pc-linux-gnu (64-bit)
#> Running under: Ubuntu 14.04.4 LTS
#> 
#> locale:
#>  [1] LC_CTYPE=fr_FR.UTF-8       LC_NUMERIC=C              
#>  [3] LC_TIME=fr_FR.UTF-8        LC_COLLATE=fr_FR.UTF-8    
#>  [5] LC_MONETARY=fr_FR.UTF-8    LC_MESSAGES=fr_FR.UTF-8   
#>  [7] LC_PAPER=fr_FR.UTF-8       LC_NAME=C                 
#>  [9] LC_ADDRESS=C               LC_TELEPHONE=C            
#> [11] LC_MEASUREMENT=fr_FR.UTF-8 LC_IDENTIFICATION=C       
#> 
#> attached base packages:
#> [1] stats     graphics  grDevices utils     datasets  methods   base     
#> 
#> other attached packages:
#> [1] reprex_0.0.0.9001   airlines_0.2.2.9009 etl_0.3.2.9000     
#> [4] dplyr_0.5.0         MonetDBLite_0.3.2  
#> 
#> loaded via a namespace (and not attached):
#>  [1] Rcpp_0.12.6         lubridate_1.5.6     codetools_0.2-14   
#>  [4] tidyr_0.5.1         digest_0.6.9        assertthat_0.1     
#>  [7] R6_2.1.2            DBI_0.4-1           formatR_1.4        
#> [10] magrittr_1.5        evaluate_0.9        stringi_1.1.1      
#> [13] lazyeval_0.2.0.9000 rmarkdown_1.0       tools_3.3.1        
#> [16] readr_0.2.2.9000    stringr_1.0.0       yaml_2.1.13        
#> [19] rsconnect_0.4.3     clipr_0.2.1         htmltools_0.3.5    
#> [22] knitr_1.13          tibble_1.1

[hannes]

monetdblite just does not support DROP VIEW IF EXISTS syntax. A tryCatch(dbSendQuery(con, "DROP VIEW...")) should do the trick

[beanumber]

Another underlying problem here is that DBI::dbRemoveTable doesn't remove VIEWs. I think the easiest fix is to just remove the VIEW from the creation script.

[beanumber]

@joelgombin can you confirm that the latest version fixes your problem?

[joelgombin]

Nope :(

library(MonetDBLite)
library(airlines)

conn <- src_monetdblite()

airlines <- etl("airlines", conn)

airlines %>%
  etl_create(years = 1987, months = 10)
#> Error in .local(conn, statement, ...): Unable to execute statement 'DROP VIEW IF EXISTS summary'.
#> Server says 'syntax error, unexpected IF in: "drop view if"
#> ' [#42000].

sessionInfo()
#> R version 3.3.1 (2016-06-21)
#> Platform: x86_64-pc-linux-gnu (64-bit)
#> Running under: Ubuntu 14.04.4 LTS
#> 
#> locale:
#>  [1] LC_CTYPE=fr_FR.UTF-8       LC_NUMERIC=C              
#>  [3] LC_TIME=fr_FR.UTF-8        LC_COLLATE=fr_FR.UTF-8    
#>  [5] LC_MONETARY=fr_FR.UTF-8    LC_MESSAGES=fr_FR.UTF-8   
#>  [7] LC_PAPER=fr_FR.UTF-8       LC_NAME=C                 
#>  [9] LC_ADDRESS=C               LC_TELEPHONE=C            
#> [11] LC_MEASUREMENT=fr_FR.UTF-8 LC_IDENTIFICATION=C       
#> 
#> attached base packages:
#> [1] stats     graphics  grDevices utils     datasets  methods   base     
#> 
#> other attached packages:
#> [1] reprex_0.0.0.9001   MonetDBLite_0.3.2   airlines_0.2.2.9010
#> [4] etl_0.3.2.9000      dplyr_0.5.0        
#> 
#> loaded via a namespace (and not attached):
#>  [1] Rcpp_0.12.6      codetools_0.2-14 lubridate_1.5.6  tidyr_0.5.1     
#>  [5] digest_0.6.9     assertthat_0.1   R6_2.1.2         DBI_0.4-1       
#>  [9] formatR_1.4      magrittr_1.5     evaluate_0.9     stringi_1.1.1   
#> [13] rmarkdown_1.0    tools_3.3.1      stringr_1.0.0    readr_0.2.2.9000
#> [17] clipr_0.2.1      htmltools_0.3.5  knitr_1.13       tibble_1.1

[joelgombin]

(but your commit affects scripts related to MySQL and PostgreSQL, I guess they're not called when using MonetDBLite, are they?)

[beanumber]

Right. I forgot to actually change the offending line. How about now?

[joelgombin]

Not yet, but now we have a different error:

library(MonetDBLite)
library(airlines)

conn <- src_monetdblite()

airlines <- etl("airlines", conn)

airlines %>%
  etl_create(years = 1987, months = 10)
#> Could not find schema initialization script
#> Parsed with column specification:
#> cols(
#>   Code = col_character(),
#>   Description = col_character()
#> )
#> Parsed with column specification:
#> cols(
#>   X1 = col_integer(),
#>   X2 = col_character(),
#>   X3 = col_character(),
#>   X4 = col_character(),
#>   X5 = col_character(),
#>   X6 = col_character(),
#>   X7 = col_double(),
#>   X8 = col_double(),
#>   X9 = col_integer(),
#>   X10 = col_integer(),
#>   X11 = col_character(),
#>   X12 = col_character()
#> )
#> Warning: 459 parsing failures.
#> row col               expected actual
#>  24 X10 no trailing characters     .5
#>  45 X10 no trailing characters     .5
#>  77 X10 no trailing characters     .5
#> 124 X10 no trailing characters     .5
#> 188 X10 no trailing characters     .5
#> ... ... ...................... ......
#> See problems(...) for more details.
#> Identifier(s) "year" are reserved SQL keywords and need(s) to be quoted in queries.
#> Downloading flight data...
#> Importing flight data from /tmp/Rtmp9z5hNU/load/flights_1987_10.csv to the database...
#> Treating character vector parameter as file name(s) for monetdb.read.csv()
#> Error in .local(conn, statement, ...): Unable to execute statement 'COPY OFFSET 2 INTO flights FROM '/tmp/Rtmp9z5hNU/load/flights_1987_10.csv' USING DELIMITERS ',','\n'...'.
#> Server says 'ParseException:SQLparser:42S02!COPY INTO: no such table 'flights''.

sessionInfo()
#> R version 3.3.1 (2016-06-21)
#> Platform: x86_64-pc-linux-gnu (64-bit)
#> Running under: Ubuntu 14.04.4 LTS
#> 
#> locale:
#>  [1] LC_CTYPE=fr_FR.UTF-8       LC_NUMERIC=C              
#>  [3] LC_TIME=fr_FR.UTF-8        LC_COLLATE=fr_FR.UTF-8    
#>  [5] LC_MONETARY=fr_FR.UTF-8    LC_MESSAGES=fr_FR.UTF-8   
#>  [7] LC_PAPER=fr_FR.UTF-8       LC_NAME=C                 
#>  [9] LC_ADDRESS=C               LC_TELEPHONE=C            
#> [11] LC_MEASUREMENT=fr_FR.UTF-8 LC_IDENTIFICATION=C       
#> 
#> attached base packages:
#> [1] stats     graphics  grDevices utils     datasets  methods   base     
#> 
#> other attached packages:
#> [1] reprex_0.0.0.9001   MonetDBLite_0.3.2   airlines_0.2.2.9010
#> [4] etl_0.3.2.9000      dplyr_0.5.0        
#> 
#> loaded via a namespace (and not attached):
#>  [1] Rcpp_0.12.6         codetools_0.2-14    lubridate_1.5.6    
#>  [4] tidyr_0.5.1         digest_0.6.9        assertthat_0.1     
#>  [7] R6_2.1.2            DBI_0.4-1           formatR_1.4        
#> [10] magrittr_1.5        evaluate_0.9        stringi_1.1.1      
#> [13] lazyeval_0.2.0.9000 rmarkdown_1.0       tools_3.3.1        
#> [16] stringr_1.0.0       readr_0.2.2.9000    clipr_0.2.1        
#> [19] htmltools_0.3.5     knitr_1.13          tibble_1.1

[beanumber]

Aha. So I am confirming the error. I see the same behavior. I have added this case to the tests.

> DBI::dbWriteTable(ontime_monet$con, "flights", "~/dumps/airlines/load/flights_1987_10.csv", append = TRUE)
Treating character vector parameter as file name(s) for monetdb.read.csv()
Error in .local(conn, statement, ...) : 
  Unable to execute statement 'COPY OFFSET 2 INTO flights FROM '/home/bbaumer/dumps/airlines/load/flights_1987_10.csv' USING DELIMI...'.
Server says 'ParseException:SQLparser:42S02!COPY INTO: no such table 'flights''.

Here we are just trying to use the DBI::dbWriteTable() method for importing a CSV into a table with no predefined schema. @hannesmuehleisen do you see why this doesn't work?

I guess I'm not sure if this is a bug in airlines or a bug in MonetDBLite.

[ajdamico]

hi, MonetDB's csv import is pretty strict. devtools::install_github("beanumber/airlines") fails for me on windows. could you give me a minimal reproducible example that creates flights_1987_10.csv in the same structure that's failing for you..or just post that dataset publicly? the code below works without issue. thanks

library(DBI)

tf <- tempfile()
download.file( "https://github.com/beanumber/airlines/blob/master/data/flights.rda?raw=true" , tf , mode = 'wb' )

load( tf )
write.csv( flights , tf , row.names = FALSE )

db <- dbConnect( MonetDBLite::MonetDBLite() )
dbWriteTable( db , 'flights' , tf )

[beanumber]

@ajdamico did you install the dev version of etl? See (#50)

If you can get that working, see line 33 of the (https://github.com/beanumber/airlines/blob/master/tests/testthat/test-airlines.R) file, or try:

library(airlines)
db <- MonetDBLite::src_monetdblite()
ontime_monet <- etl("airlines", db = db)
ontime_monet %>% etl_create(years = 1987, months = 10)

After that fails, try:

DBI::dbWriteTable(ontime_monet$con, "flights", paste0(attr(ontime_monet, "load_dir"), "/flights_1987_10.csv"), append = TRUE)

[ajdamico]

hi, how can i get the csv file that you are looking at? thanks

devtools::install_github("beanumber/etl")
devtools::install_github("beanumber/airlines")

library(DBI)
library(airlines)

db <- MonetDBLite::src_monetdblite()
ontime_monet <- etl("airlines", db = db)

# gives an expected error
ontime_monet %>% etl_create(years = 1987, months = 10)

csvfile <- normalizePath(paste0(attr(ontime_monet, "load_dir"), "/flights_1987_10.csv"))

readLines(csvfile,n=10)
# Error in file(con, "r") : cannot open the connection
# In addition: Warning message:
# In file(con, "r") :
  # cannot open file 'C:\Users\anthonyd\AppData\Local\Temp\RtmpsLXvqJ\load\flights_1987_10.csv': No such file or directory


# csv file doesn't exist yet
# dbWriteTable(ontime_monet$con, "flights", csvfile, append = TRUE)