411 Error when using dplyr verbs with bigrquery and dbplyr

[Hlynur]

Hi there,

I'm trying to use dbplyr with BigQuery as a backend through the bigrquery package, but while I can get the connection just fine, I end up not being able to use any dplyr verbs. When I do I end up with a 411 HTML error, indicating that the server refuses to accept the request without a defined Content-Length header.

I've never come across this problem before so I'm wondering if it has something to do with a recent version of the packages? All ideas welcome.

What I've tried doing:

• Running this on different accounts, different projects and different datasets. No change
• Update all packages and dependencies in question. No change

Many thanks in advance

Package versions

packageVersion("odbc")
#> [1] '1.3.0'
packageVersion("bigrquery")
#> [1] '1.3.2'
packageVersion("dplyr")
#> [1] '1.0.3'
packageVersion("dbplyr")
#> [1] '2.0.0.9000'

^{Created on 2021-01-28 by the reprex package (v0.3.0)}

Reprex follows

library(odbc)
library(bigrquery)
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
library(dbplyr)
#> 
#> Attaching package: 'dbplyr'
#> The following objects are masked from 'package:dplyr':
#> 
#>     ident, sql


con = dbConnect(
  bigrquery::bigquery(),
  project = "bigquery-public-data",
  dataset = "austin_311",
  billing = "My Billing Account"
)

test_tbl = tbl(con, "311_service_requests")
#> Suitable tokens found in the cache, associated with these emails:
#>  -----------------@gmail.com
#>  -----------------@gmail.com
#> The first will be used.
#> 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 -----------@gmail.com.

# This works fine
test_tbl
#> # Source:   table<311_service_requests> [?? x 24]
#> # Database: BigQueryConnection
#>    unique_key complaint_type complaint_descr… owning_departme… source status
#>    <chr>      <chr>          <chr>            <chr>            <chr>  <chr> 
#>  1 20-000827… ACLOANIM       Loose Animal No… Animal Services… Phone  Closed
#>  2 20-003801… ACCOYTE        Coyote Complain… Animal Services… Phone  Closed
#>  3 20-000805… ROADMARK       Road Markings/S… Transportation   Phone  Closed
#>  4 20-002347… ACTRAP         Animal Trapped … Animal Services… Phone  Closed
#>  5 19-004347… ACCOYTE        Coyote Complain… Animal Services… Phone  Closed
#>  6 19-004537… COAACDD        Dangerous/Vicio… Animal Services… Phone  Closed
#>  7 20-002164… ACCOYTE        Coyote Complain… Animal Services… Phone  Closed
#>  8 20-002669… AUSCODCO       Austin Code - C… Austin Code Dep… Web    Closed
#>  9 20-000736… COAACDD        Dangerous/Vicio… Animal Services… Phone  Closed
#> 10 20-003762… AUSCODCO       Austin Code - C… Austin Code Dep… Phone  Closed
#> # … with more rows, and 18 more variables: status_change_date <dttm>,
#> #   created_date <dttm>, last_update_date <dttm>, close_date <dttm>,
#> #   incident_address <chr>, street_number <chr>, street_name <chr>, city <chr>,
#> #   incident_zip <int>, county <chr>, state_plane_x_coordinate <chr>,
#> #   state_plane_y_coordinate <dbl>, latitude <dbl>, longitude <dbl>,
#> #   location <chr>, council_district_code <int>, map_page <chr>, map_tile <chr>

# This also works fine
test_tbl %>% 
  head()
#> # Source:   lazy query [?? x 24]
#> # Database: BigQueryConnection
#>   unique_key complaint_type complaint_descr… owning_departme… source status
#>   <chr>      <chr>          <chr>            <chr>            <chr>  <chr> 
#> 1 20-000827… ACLOANIM       Loose Animal No… Animal Services… Phone  Closed
#> 2 20-003801… ACCOYTE        Coyote Complain… Animal Services… Phone  Closed
#> 3 20-000805… ROADMARK       Road Markings/S… Transportation   Phone  Closed
#> 4 20-002347… ACTRAP         Animal Trapped … Animal Services… Phone  Closed
#> 5 19-004347… ACCOYTE        Coyote Complain… Animal Services… Phone  Closed
#> 6 19-004537… COAACDD        Dangerous/Vicio… Animal Services… Phone  Closed
#> # … with 18 more variables: status_change_date <dttm>, created_date <dttm>,
#> #   last_update_date <dttm>, close_date <dttm>, incident_address <chr>,
#> #   street_number <chr>, street_name <chr>, city <chr>, incident_zip <int>,
#> #   county <chr>, state_plane_x_coordinate <chr>,
#> #   state_plane_y_coordinate <dbl>, latitude <dbl>, longitude <dbl>,
#> #   location <chr>, council_district_code <int>, map_page <chr>, map_tile <chr>

# But any dplyr verb results in this weird error
test_tbl %>% 
  select(unique_key)
#> Error: HTTP error [411] <!DOCTYPE html>
#> <html lang=en>
#>   <meta charset=utf-8>
#>   <meta name=viewport content="initial-scale=1, minimum-scale=1, width=device-width">
#>   <title>Error 411 (Length Required)!!1</title>
#>   <style>
#>     *{margin:0;padding:0}html,code{font:15px/22px arial,sans-serif}html{background:#fff;color:#222;padding:15px}body{margin:7% auto 0;max-width:390px;min-height:180px;padding:30px 0 15px}* > body{background:url(//www.google.com/images/errors/robot.png) 100% 5px no-repeat;padding-right:205px}p{margin:11px 0 22px;overflow:hidden}ins{color:#777;text-decoration:none}a img{border:0}@media screen and (max-width:772px){body{background:none;margin-top:0;max-width:none;padding-right:0}}#logo{background:url(//www.google.com/images/branding/googlelogo/1x/googlelogo_color_150x54dp.png) no-repeat;margin-left:-5px}@media only screen and (min-resolution:192dpi){#logo{background:url(//www.google.com/images/branding/googlelogo/2x/googlelogo_color_150x54dp.png) no-repeat 0% 0%/100% 100%;-moz-border-image:url(//www.google.com/images/branding/googlelogo/2x/googlelogo_color_150x54dp.png) 0}}@media only screen and (-webkit-min-device-pixel-ratio:2){#logo{background:url(//www.google.com/images/branding/googlelogo/2x/googlelogo_color_150x54dp.png) no-repeat;-webkit-background-size:100% 100%}}#logo{display:inline-block;height:54px;width:150px}
#>   </style>
#>   <a href=//www.google.com/><span id=logo aria-label=Google></span></a>
#>   <p><b>411.</b> <ins>That’s an error.</ins>
#>   <p>POST requests require a <code>Content-length</code> header.  <ins>That’s all we know.</ins>

^{Created on 2021-01-28 by the reprex package (v0.3.0)}

[nirgrahamuk]

perhaps use show_query() to see what SQL was generated ?

[Hlynur]

Thanks so much for the suggestion!

Looking at it, there is nothing visibly wrong with the queries. Which I believe makes sense as they'd be in the body of the request, but not in the header? Right? I have no idea what's going on to be honest, but I've gotten co-workers to try and run a couple of example requests and everyone is getting the same 411 error.

So, maybe this is something that would warrant filing an issue with the maintainers of either bigrquery or dbplyr?

# This works fine, as shown above
test_tbl %>% 
  show_query()
#> <SQL>
#> SELECT *
#> FROM `311_service_requests`

# This works, as shown in the original reprex
test_tbl %>% 
  head() %>% 
  show_query()
#> <SQL>
#> SELECT *
#> FROM `311_service_requests`
#> LIMIT 6

# But this *doesn't* work, as shown in the original reprex
test_tbl %>% 
  select(unique_key) %>% 
  head() %>% 
  show_query()
#> <SQL>
#> SELECT `unique_key`
#> FROM `311_service_requests`
#> LIMIT 6

^{Created on 2021-01-28 by the reprex package (v0.3.0)}

[mfherman]

Have you tried making the connection with {odbc} instead of {bigrquery}?

According to the docs, both should work.

https://db.rstudio.com/databases/big-query/

[Hlynur]

Oooh, I have not tried that. I'm gonna try it now! Thank you so much!

[Hlynur]

It seems that the problem isn't even with dplyr verbs. This is also happening with regular old dbGetQuery(), so I figure I should file an issue on the bigrquerygithub page.