Creating mtcars table in Big Query using DBI returns type not found: FLOAT error

mihagazvoda · May 18, 2020, 5:17pm

Cross-posted: https://stackoverflow.com/questions/61851640/creating-table-in-big-query-using-dbi-returns-type-not-found-float-error

I'm trying to create mtcars table in Big Query as it is described in a documentation:

con <- DBI::dbConnect(
  bigrquery::bigquery(), 
  project = project, 
  dataset = dataset, 
  billing = billing
)

DBI::dbWriteTable(con, "mtcars", mtcars)

However, I get an error:

Running job '...
Error: Job '...' failed
x Type not found: FLOAT at [2:9] [invalidQuery]
Run `rlang::last_error()` to see where the error occurred.
In addition: Warning message:
In class(obj) <- c("scalar", class(obj)) :
  Setting class(x) to multiple strings ("scalar", "SQL", ...); result will no longer be an S4 object

What am I doing wrong?

EconomiCurtis · May 18, 2020, 5:18pm

I'm curious what last_error returned?

mihagazvoda · May 18, 2020, 5:39pm

Sure:

<error/rlang_error>
Job '...' failed
x Type not found: FLOAT at [2:9] [invalidQuery]
Backtrace:
     █
  1. ├─DBI::dbCreateTable(con, "mtcars", mtcars)
  2. └─DBI::dbCreateTable(con, "mtcars", mtcars)
  3.   ├─DBI::dbExecute(conn, query)
  4.   └─DBI::dbExecute(conn, query)
  5.     ├─DBI::dbSendStatement(conn, statement, ...)
  6.     └─DBI::dbSendStatement(conn, statement, ...)
  7.       ├─DBI::dbSendQuery(conn, statement, ...)
  8.       └─bigrquery::dbSendQuery(conn, statement, ...)
  9.         └─bigrquery:::BigQueryResult(conn, statement, ...)
 10.           └─bigrquery::bq_job_wait(job, quiet = conn@quiet)

Blair09M · May 18, 2020, 6:00pm

@mihagazvoda I'm not seeing any errors:

con <- DBI::dbConnect(
  bigrquery::bigquery(),
  project = "bigrquery-test-277617",
  dataset = "cars"
)

DBI::dbWriteTable(con, "mtcars", mtcars)
DBI::dbReadTable(con, "mtcars")
#> # A tibble: 32 x 11
#>     carb    am    vs  qsec    wt  drat   cyl  disp    hp  gear   mpg
#>    <int> <int> <int> <dbl> <dbl> <dbl> <int> <dbl> <int> <int> <dbl>
#>  1     2     0     1  20    3.19  3.69     4  147.    62     4  24.4
#>  2     2     0     1  22.9  3.15  3.92     4  141.    95     4  22.8
#>  3     1     0     1  20.0  2.46  3.7      4  120.    97     3  21.5
#>  4     1     0     1  19.4  3.22  3.08     6  258    110     3  21.4
#>  5     1     0     1  20.2  3.46  2.76     6  225    105     3  18.1
#>  6     4     0     1  18.3  3.44  3.92     6  168.   123     4  19.2
#>  7     4     0     1  18.9  3.44  3.92     6  168.   123     4  17.8
#>  8     2     0     0  17.0  3.44  3.15     8  360    175     3  18.7
#>  9     4     0     0  15.8  3.57  3.21     8  360    245     3  14.3
#> 10     3     0     0  17.4  4.07  3.07     8  276.   180     3  16.4
#> # … with 22 more rows
DBI::dbRemoveTable(con, "mtcars")
DBI::dbDisconnect(con)

^{Created on 2020-05-18 by the reprex package (v0.3.0)}

Session info

sessionInfo()
#> R version 4.0.0 (2020-04-24)
#> Platform: x86_64-apple-darwin17.0 (64-bit)
#> Running under: macOS Catalina 10.15.4
#> 
#> Matrix products: default
#> BLAS:   /Library/Frameworks/R.framework/Versions/4.0/Resources/lib/libRblas.dylib
#> LAPACK: /Library/Frameworks/R.framework/Versions/4.0/Resources/lib/libRlapack.dylib
#> 
#> locale:
#> [1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8
#> 
#> attached base packages:
#> [1] stats     graphics  grDevices utils     datasets  methods   base     
#> 
#> loaded via a namespace (and not attached):
#>  [1] Rcpp_1.0.4.6     knitr_1.28       magrittr_1.5     bit_1.1-15.2    
#>  [5] R6_2.4.1         rlang_0.4.6      fansi_0.4.1      stringr_1.4.0   
#>  [9] highr_0.8        httr_1.4.1       tools_4.0.0      xfun_0.13       
#> [13] utf8_1.1.4       cli_2.0.2        DBI_1.1.0        withr_2.2.0     
#> [17] askpass_1.1      htmltools_0.4.0  ellipsis_0.3.0   openssl_1.4.1   
#> [21] yaml_2.2.1       bit64_0.9-7      assertthat_0.2.1 digest_0.6.25   
#> [25] gargle_0.5.0     tibble_3.0.1     lifecycle_0.2.0  crayon_1.3.4    
#> [29] fs_1.4.1         vctrs_0.2.4      curl_4.3         glue_1.4.0      
#> [33] evaluate_0.14    rmarkdown_2.1    stringi_1.4.6    bigrquery_1.3.1 
#> [37] compiler_4.0.0   pillar_1.4.4     jsonlite_1.6.1   pkgconfig_2.0.3

Could you run sessioninfo::session_info() and respond with the results here? This will help determine if there are outdated packages or other factors that may be influencing the observed error.

mihagazvoda · May 18, 2020, 6:24pm

Here's session_info() :

─ Session info ──────────────────────────────────────────────────────────
 setting  value                       
 version  R version 4.0.0 (2020-04-24)
 os       macOS Catalina 10.15.2      
 system   x86_64, darwin17.0          
 ui       RStudio                     
 language (EN)                        
 collate  en_US.UTF-8                 
 ctype    en_US.UTF-8                 
 tz       Europe/Ljubljana            
 date     2020-05-18                  

─ Packages ──────────────────────────────────────────────────────────────
 package     * version  date       lib source        
 askpass       1.1      2019-01-13 [1] CRAN (R 4.0.0)
 assertthat    0.2.1    2019-03-21 [1] CRAN (R 4.0.0)
 bigrquery   * 1.3.0    2020-05-08 [1] CRAN (R 4.0.0)
 bit           1.1-15.2 2020-02-10 [1] CRAN (R 4.0.0)
 bit64         0.9-7    2017-05-08 [1] CRAN (R 4.0.0)
 cli           2.0.2    2020-02-28 [1] CRAN (R 4.0.0)
 crayon        1.3.4    2017-09-16 [1] CRAN (R 4.0.0)
 curl          4.3      2019-12-02 [1] CRAN (R 4.0.0)
 DBI         * 1.1.0    2019-12-15 [1] CRAN (R 4.0.0)
 ellipsis      0.3.0    2019-09-20 [1] CRAN (R 4.0.0)

Blair09M · May 18, 2020, 8:34pm

It looks like bigrquery 1.3.1 was released a few days ago. Can you update that package - install.packages("bigrquery") - and try again?

mihagazvoda · May 19, 2020, 5:20am

I updated the packages and still doesn't work. I tried really simple example:

t <- tibble::tibble(
  x = 1:10,
  y = letters[1:10]
)

dbCreateTable(con, "mtcars", mtcars)

I also checked query history in BQ:

CREATE TABLE `t` (
  `x` INTEGER,
  `y` STRING
)

And it seems when creating table R types are used (INTEGER is not available in BQ).
When creating mtcars all fields were FLOAT which also doesn't exist in BQ.

Blair09M · May 19, 2020, 9:06pm

Hmm interesting. Can you post a reprex, including output, of the following:

library(bigrquery)

con <- dbConnect(
  bigquery(),
  project = "bigrquery-test-277617",
  dataset = "cars"
)

dbWriteTable(con,
             "foo",
             tibble::tibble(
               x = 1:10,
               y = letters[1:10]
             ))
dbReadTable(con, "foo")
#> # A tibble: 10 x 2
#>    y         x
#>    <chr> <int>
#>  1 a         1
#>  2 b         2
#>  3 c         3
#>  4 d         4
#>  5 e         5
#>  6 f         6
#>  7 g         7
#>  8 h         8
#>  9 i         9
#> 10 j        10
dbRemoveTable(con, "foo")
dbDisconnect(con)

^{Created on 2020-05-19 by the reprex package (v0.3.0)}

Session info

sessionInfo()
#> R version 4.0.0 (2020-04-24)
#> Platform: x86_64-apple-darwin17.0 (64-bit)
#> Running under: macOS Catalina 10.15.4
#> 
#> Matrix products: default
#> BLAS:   /Library/Frameworks/R.framework/Versions/4.0/Resources/lib/libRblas.dylib
#> LAPACK: /Library/Frameworks/R.framework/Versions/4.0/Resources/lib/libRlapack.dylib
#> 
#> locale:
#> [1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8
#> 
#> attached base packages:
#> [1] stats     graphics  grDevices utils     datasets  methods   base     
#> 
#> other attached packages:
#> [1] bigrquery_1.3.1
#> 
#> loaded via a namespace (and not attached):
#>  [1] Rcpp_1.0.4.6     knitr_1.28       magrittr_1.5     bit_1.1-15.2    
#>  [5] R6_2.4.1         rlang_0.4.6      fansi_0.4.1      stringr_1.4.0   
#>  [9] highr_0.8        httr_1.4.1       tools_4.0.0      xfun_0.13       
#> [13] utf8_1.1.4       cli_2.0.2        DBI_1.1.0        withr_2.2.0     
#> [17] askpass_1.1      htmltools_0.4.0  ellipsis_0.3.0   openssl_1.4.1   
#> [21] yaml_2.2.1       bit64_0.9-7      assertthat_0.2.1 digest_0.6.25   
#> [25] gargle_0.5.0     tibble_3.0.1     lifecycle_0.2.0  crayon_1.3.4    
#> [29] fs_1.4.1         vctrs_0.2.4      curl_4.3         glue_1.4.0      
#> [33] evaluate_0.14    rmarkdown_2.1    stringi_1.4.6    compiler_4.0.0  
#> [37] pillar_1.4.4     jsonlite_1.6.1   pkgconfig_2.0.3

mihagazvoda · May 21, 2020, 6:48pm

It works! Also the previous examples! No idea what changed....

library(DBI)
library(bigrquery)

con <- dbConnect(
  bigquery(),
  project = "mihagazvoda-playground",
  dataset = "test",
  billing = "mihagazvoda-playground"
)

dbWriteTable(con,
             "foo",
             tibble::tibble(
               x = 1:10,
               y = letters[1:10]
             ))
#> Using an auto-discovered, cached token.
#> To suppress this message, modify your code or options to clearly consent to the use of a cached token.
#> See gargle's "Non-interactive auth" vignette for more details:
#> https://gargle.r-lib.org/articles/non-interactive-auth.html
#> The bigrquery package is using a cached token for xxx@gmail.com.
dbReadTable(con, "foo")
#> # A tibble: 10 x 2
#>    y         x
#>    <chr> <int>
#>  1 a         1
#>  2 b         2
#>  3 c         3
#>  4 d         4
#>  5 e         5
#>  6 f         6
#>  7 g         7
#>  8 h         8
#>  9 i         9
#> 10 j        10

dbRemoveTable(con, "foo")
dbDisconnect(con)

^{Created on 2020-05-21 by the reprex package (v0.3.0)}

Blair09M · May 21, 2020, 8:30pm

@mihagazvoda how interesting! I'm not sure what could have changed If you discover what enabled the expected behavior, update here so that others can benefit from the resolution