bigrquery 411 Error: POST requests require a Content-length header

I was originally trying to use dbplyr to interact with BigQuery data using bigrquery. While the tbl() function would show the data and even

tbl(con, "311_service requests") %>%
  head()

would work, adding a dplyr verb would result in a 411 Error. I posted a reprex of that particular situation in this thread on the rstudio community board. But trying to work my way down the error, I believe the problem is more general as shown by the following reprex.

In essence, any sql query run with dbGetQuery() results in this 411 error saying that the server refuses to accept the request without a defined Content-Length header.

I have already tried:

Different accounts
Different projects
Different datasets
Changing between cran versions and dev versions of odbc and bigrquery
Getting colleagues to try and run these reprexes on their setup
Switching from R 4.0.3 to R 3.6.2

It all leads to the same 411 error from Google. So I ask if the issue is that BigQuery has changed how the headers are supposed to be and bigrquery has not been updated accordingly. Is that possible?

Best regards, Hlynur

Reprex follows

library(odbc)
library(bigrquery)

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

bq_auth()  
#> Suitable tokens found in the cache, associated with these emails:
#>   * [email protected]
#>   * [email protected]
#> 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 [email protected].

sql_query <- "SELECT * FROM `311_service_requests` LIMIT 10"

test <- dbGetQuery(con, sql_query)
#> 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>

packageVersion("odbc")
#> [1] '1.3.0.9000'
packageVersion("bigrquery")
#> [1] '1.3.2.9001'

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

Jan 29 '21 02:01 hlynurhallgrims

If bigrquery was broken for everyone, I think there would've been a lot more problem reports so it's likely to be something on your end. You can try using httr::with_verbose() to get more information about what's sent:

library(DBI)
bigrquery::bq_auth("[email protected]")  

con <- dbConnect(
  bigrquery::bigquery(),
  project = "bigquery-public-data",
  dataset = "austin_311",
  billing = bigrquery::bq_test_project()
)

httr::with_verbose(qry <- dbSendQuery(con, "SELECT unique_key FROM `311_service_requests` LIMIT 10"))

You might also check that you have the latest version of the gargle package.

Jan 29 '21 13:01 hadley

Given that your colleagues are also experiencing the problem, you might double check that this isn't because of a corporate firewall or proxy.

Jan 29 '21 13:01 hadley

Thanks so much for the reply Hadley! Apologies in advance for the long post but the behaviour is just so peculiar, and the results of httr::with_verbose() don't show up in the reprex. I say peculiar, because with dbplyr I can get it to work.

Basically, this works and interacts fine with the API.

tbl(con, "311_service_requests") %>% 
    head(n = 10)

But this doesn't work

tbl(con, "311_service_requests") %>% 
    select(unique_key) %>% 
    head(n = 10)

It is so much appreciated that you took a look at this. I've included the result of the call you showcased above. Both the reprex (without the results of the with_verbose call) and code run in a fresh R session (because that shows the result of with_verbose. A comparison of dbplyr with and without select is at the bottom.

I should add I've both done this on my home network and then VPN-ing into my work network but the results are the same. But it very much feels like it could be something like that, if this is not an issue for anyone else out there.

-> POST /bigquery/v2/projects/My Billing Account/jobs/?fields=jobReference HTTP/2
-> Host: www.googleapis.com
-> User-Agent: bigrquery/1.3.2.9001 (GPN:RStudio; ) gargle/0.5.0.9000 httr/1.4.2.9000
-> Accept-Encoding: deflate, gzip
-> Accept: application/json, text/xml, application/xml, */*
-> Content-Type: application/json
-> Authorization: Bearer ### I have removed the Bearer token ###
-> Content-Length: 314
-> 
>> {
>>   "configuration": {
>>     "query": {
>>       "query": "SELECT unique_key FROM `311_service_requests` LIMIT 10",
>>       "useLegacySql": false,
>>       "priority": "INTERACTIVE",
>>       "defaultDataset": {
>>         "projectId": "bigquery-public-data",
>>         "datasetId": "austin_311"
>>       }
>>     }
>>   },
>>   "quiet": null
>> }

<- HTTP/2 411 
<- date: Fri, 29 Jan 2021 18:09:29 GMT
<- content-type: text/html; charset=UTF-8
<- server: ESF
<- content-length: 1564
<- x-xss-protection: 0
<- x-frame-options: SAMEORIGIN
<- x-content-type-options: nosniff
<- alt-svc: h3-29=":443"; ma=2592000,h3-T051=":443"; ma=2592000,h3-Q050=":443"; ma=2592000,h3-Q046=":443"; ma=2592000,h3-Q043=":443"; ma=2592000,quic=":443"; ma=2592000; v="46,43"
<- 
#-#-# Same 411 error as in first post, removed for brevity

Here is the reprex.

Again, I don't know why only the error is displayed here but not the same code in a fresh R session :(

library(DBI)

bigrquery::bq_auth("[email protected]")  

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


httr::with_verbose(qry <- dbSendQuery(con, "SELECT unique_key FROM `311_service_requests` LIMIT 10"))
#-#-# Same 411 error as in first post, removed for brevity

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

dbplyr

Looking at how this works for the dbplyr approach until you use any dplyr verbs. I have the following reprex (and the queries below, that are not shown in the reprex.

library(odbc)
library(bigrquery)
#> Warning: replacing previous import 'vctrs::data_frame' by 'tibble::data_frame'
#> when loading 'dplyr'
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 = bigrquery::bq_test_project()
)

httr::with_verbose({tbl(con, "311_service_requests") %>% head(n = 10)})
#> Suitable tokens found in the cache, associated with these emails:
#>   * [email protected]
#>   * [email protected]
#> 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 [email protected].
#> # 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-000180… ACLOANIM       Loose Animal No… Animal Services… Phone  Closed
#>  2 20-002347… ACTRAP         Animal Trapped … Animal Services… Phone  Closed
#>  3 19-001594… DRFLOODG       Flooding  Curre… Watershed Prote… Phone  Closed
#>  4 19-004040… ACLOANIM       Loose Animal No… Animal Services… Phone  Closed
#>  5 20-002856… AUSCODCO       Austin Code - C… Austin Code Dep… Mobil… Closed
#>  6 19-003233… ZZANIMAL       Lost Item in St… Watershed Prote… Phone  Closed
#>  7 19-004023… PRGRDELC       Park Maintenanc… Parks & Recreat… Phone  Closed
#>  8 20-003306… DRVECTRL       Creek & Pond Ve… Watershed Prote… Phone  Resol…
#>  9 19-003921… ACLOANIM       Loose Animal No… Animal Services… Phone  Closed
#> 10 19-004394… ZZANIMAL       Lost Item in St… Watershed Prote… 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>


httr::with_verbose({tbl(con, "311_service_requests") %>% select(unique_key) %>% head(n = 10)})
#-#-# Same 411 error as in first post, removed for brevity

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

Here are the different results from httr::with_verbose, because they don't show in the reprex.

#This works
httr::with_verbose({tbl(con, "311_service_requests") %>% head(n = 10)})
#  -> GET /bigquery/v2/projects/bigquery-public-data/datasets/austin_311/tables/311_service_requests?fields=schema HTTP/2
#  -> Host: www.googleapis.com
#  -> User-Agent: bigrquery/1.3.2.9001 (GPN:RStudio; ) gargle/0.5.0.9000 httr/1.4.2.9000
#  -> Accept-Encoding: deflate, gzip
#  -> Accept: application/json, text/xml, application/xml, */*
#    -> Authorization: Bearer ### I have removed Bearer token ###
#  -> 
#    <- HTTP/2 200 
#  <- etag: L8aEVYIuVkpPwOVxkq2iOQ==
#      <- content-type: application/json; charset=UTF-8
#      <- vary: Origin
#      <- vary: X-Origin
#      <- vary: Referer
#      <- content-encoding: gzip
#      <- date: Fri, 29 Jan 2021 18:35:36 GMT
#      <- server: ESF
#      <- cache-control: private
#      <- content-length: 825
#      <- x-xss-protection: 0
#      <- x-frame-options: SAMEORIGIN
#      <- x-content-type-options: nosniff
#      <- alt-svc: h3-29=":443"; ma=2592000,h3-T051=":443"; ma=2592000,h3-Q050=":443"; ma=2592000,h3-Q046=":443"; ma=2592000,h3-Q043=":443"; #ma=2592000,quic=":443"; ma=2592000; v="46,43"
#      <- 
#        # Source:   lazy query [?? x 24]
#        # Database: BigQueryConnection
#        unique_key complaint_type complaint_descr… owning_departme… source status status_change_date 
#      <chr>      <chr>          <chr>            <chr>            <chr>  <chr>  <dttm>             
#        1 20-000180… ACLOANIM       Loose Animal No… Animal Services… Phone  Closed 2020-01-15 11:03:51
#      2 20-002347… ACTRAP         Animal Trapped … Animal Services… Phone  Closed 2020-06-14 22:34:15
#      3 19-001594… DRFLOODG       Flooding  Curre… Watershed Prote… Phone  Closed 2019-12-13 12:47:20
#      4 19-004040… ACLOANIM       Loose Animal No… Animal Services… Phone  Closed 2019-10-20 14:43:59
#      5 20-002856… AUSCODCO       Austin Code - C… Austin Code Dep… Mobil… Closed 2020-07-15 08:51:06
#      6 19-003233… ZZANIMAL       Lost Item in St… Watershed Prote… Phone  Closed 2020-02-24 00:03:51
#      7 19-004023… PRGRDELC       Park Maintenanc… Parks & Recreat… Phone  Closed 2019-11-12 10:49:03
#      8 20-003306… DRVECTRL       Creek & Pond Ve… Watershed Prote… Phone  Resol… 2020-08-14 12:19:52
#      9 19-003921… ACLOANIM       Loose Animal No… Animal Services… Phone  Closed 2019-10-11 21:20:24
#      10 19-004394… ZZANIMAL       Lost Item in St… Watershed Prote… Phone  Closed 2020-05-17 01:01:33
#      # … with 17 more variables: 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 with select it gives me both a 200 message and then the 411 error. 
httr::with_verbose({tbl(con, "311_service_requests") %>% select(unique_key) %>% head(n = 10)})
#      -> GET /bigquery/v2/projects/bigquery-public-data/datasets/austin_311/tables/311_service_requests?fields=schema HTTP/2
#      -> Host: www.googleapis.com
#      -> User-Agent: bigrquery/1.3.2.9001 (GPN:RStudio; ) gargle/0.5.0.9000 httr/1.4.2.9000
#      -> Accept-Encoding: deflate, gzip
#      -> Accept: application/json, text/xml, application/xml, */*
#        -> Authorization: Bearer ### I have removed the bearer token ###
#      -> 
#        <- HTTP/2 200 
#      <- etag: L8aEVYIuVkpPwOVxkq2iOQ==
#        <- content-type: application/json; charset=UTF-8
#      <- vary: Origin
#      <- vary: X-Origin
#      <- vary: Referer
#      <- content-encoding: gzip
#      <- date: Fri, 29 Jan 2021 18:35:38 GMT
#      <- server: ESF
#      <- cache-control: private
#      <- content-length: 825
#      <- x-xss-protection: 0
#      <- x-frame-options: SAMEORIGIN
#      <- x-content-type-options: nosniff
#      <- alt-svc: h3-29=":443"; ma=2592000,h3-T051=":443"; ma=2592000,h3-Q050=":443"; ma=2592000,h3-Q046=":443"; ma=2592000,h3-Q043=":443"; #ma=2592000,quic=":443"; ma=2592000; v="46,43"
#      <- 
#-#-# Same 411 error as in first post, removed for brevity

Jan 29 '21 18:01 hlynurhallgrims

Hi again @hadley. Sorry to bother you again, but I'm becoming more convinced that there really is a problem with the bigrquery package. I recently ran this reprex on cloud.rstudio.com and it didn't work there either. Connecting to the data and printing the first 10 rows works fine, but once you try any type of manipulation, (be it with dplyr verbs or DBI::dbGetQuery) you get an error. This time around it's "Your client has issued a malformed or illegal request"

library(DBI)
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
library(bigrquery)
library(reprex)
library(rstudioapi)

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

#This works fine
tbl(con, "311_service_requests")
#> 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 [email protected].
#> # Source:   table<311_service_requests> [?? x 24]
#> # Database: BigQueryConnection
#>    unique_key  complaint_type complaint_descript… owning_departme… source status
#>    <chr>       <chr>          <chr>               <chr>            <chr>  <chr> 
#>  1 19-00290879 MOWINGME       Mowing Medians      Public Works     Phone  Closed
#>  2 20-00307666 HHENSECO       Public Health - En… Public Health    Phone  Closed
#>  3 20-00210212 ACLOANIM       Loose Animal Not D… Animal Services… Phone  Closed
#>  4 19-00447848 ACCOYTE        Coyote Complaints   Animal Services… Phone  Closed
#>  5 20-00349773 ACCOYTE        Coyote Complaints   Animal Services… Phone  Closed
#>  6 20-00018007 ACLOANIM       Loose Animal Not D… Animal Services… Phone  Closed
#>  7 20-00369761 STREETL1       Street Lights New   Austin Energy D… Phone  Closed
#>  8 19-00159650 DRFLOODG       Flooding  Current … Watershed Prote… Spot3… Closed
#>  9 20-00160196 ACLOANIM       Loose Animal Not D… Animal Services… Phone  Closed
#> 10 20-00238565 ACLOANIM       Loose Animal Not D… Animal Services… 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>

#But this results in an error. In fact any dplyr verb does
tbl(con, "311_service_requests") %>% 
  filter(complaint_type == "ACCOYTE")
#> Error: HTTP error [400] <!DOCTYPE html>
#> <html lang=en>
#>   <meta charset=utf-8>
#>   <meta name=viewport content="initial-scale=1, minimum-scale=1, width=device-width">
#>   <title>Error 400 (Bad Request)!!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>400.</b> <ins>That’s an error.</ins>
#>   <p>Your client has issued a malformed or illegal request.  <ins>That’s all we know.</ins>

#This also results in an error
DBI::dbGetQuery(con, 
                "SELECT * FROM `bigquery-public-data.austin_311.311_service_requests` LIMIT 10")
#> Error: HTTP error [400] <!DOCTYPE html>
#> <html lang=en>
#>   <meta charset=utf-8>
#>   <meta name=viewport content="initial-scale=1, minimum-scale=1, width=device-width">
#>   <title>Error 400 (Bad Request)!!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>400.</b> <ins>That’s an error.</ins>
#>   <p>Your client has issued a malformed or illegal request.  <ins>That’s all we know.</ins>

^{Created on 2021-03-03 by the reprex package (v1.0.0)}

Mar 03 '21 15:03 hlynurhallgrims

Hi, I currently face a similar problem for the first sql request. I have a BigQuery dataset with mtcars inside, names "my_data_seb".

If I run `tbl()` with the table directly it works

dplyr::tbl(con, "my_data_seb")

The output

# Source:   table<my_data_seb> [?? x 11]
# Database: BigQueryConnection
      am  carb    vs  qsec    wt  drat  disp    hp   cyl  gear   mpg
   <int> <int> <int> <dbl> <dbl> <dbl> <dbl> <int> <int> <int> <dbl>
 1     0     2     1  20    3.19  3.69  147.    62     4     4  24.4
 2     0     2     1  22.9  3.15  3.92  141.    95     4     4  22.8
 3     0     1     1  20.0  2.46  3.7   120.    97     4     3  21.5
 4     0     1     1  19.4  3.22  3.08  258    110     6     3  21.4
 5     0     1     1  20.2  3.46  2.76  225    105     6     3  18.1
 6     0     4     1  18.3  3.44  3.92  168.   123     6     4  19.2
 7     0     4     1  18.9  3.44  3.92  168.   123     6     4  17.8
 8     0     2     0  17.0  3.44  3.15  360    175     8     3  18.7
 9     0     4     0  15.8  3.57  3.21  360    245     8     3  14.3
10     0     3     0  17.4  4.07  3.07  276.   180     8     3  16.4
# ℹ more rows
# ℹ Use `print(n = ...)` to see more rows

If I run `tbl()` with a `sql()` command, I get the `Error 400 (Bad Request)`:

httr::with_verbose(
    dplyr::tbl(con, sql("SELECT * FROM my_data_seb"))
)

The full error message:

-> POST /bigquery/v2/projects/<my_private_project>/jobs/?fields=statistics%28query%28schema%28fields%29%29%29 HTTP/1.1
-> Host: www.googleapis.com
-> User-Agent: bigrquery/1.4.2.9000 (GPN:RStudio; ) gargle/1.4.0 httr/1.4.4
-> Accept-Encoding: deflate, gzip
-> Accept: application/json, text/xml, application/xml, */*
-> Content-Type: application/json
-> Authorization: Bearer XXXXX
-> Content-Length: 261
-> 
>> {
>>   "configuration": {
>>     "query": {
>>       "query": "SELECT * FROM my_data_seb",
>>       "useLegacySql": false,
>>       "defaultDataset": {
>>         "projectId": "<my_private_project>",
>>         "datasetId": "<my_private_dataset>"
>>       }
>>     },
>>     "dryRun": true
>>   }
>> }

<- HTTP/1.1 200 OK
<- ETag: tyG4iv+rZQb/FLnrnP2W2A==
<- Content-Type: application/json; charset=UTF-8
<- Vary: Origin
<- Vary: X-Origin
<- Vary: Referer
<- Content-Encoding: gzip
<- Date: Thu, 27 Apr 2023 10:34:28 GMT
<- Server: ESF
<- Cache-Control: private
<- X-XSS-Protection: 0
<- X-Frame-Options: SAMEORIGIN
<- X-Content-Type-Options: nosniff
<- Transfer-Encoding: chunked
<- 
Error: HTTP error [400] <!DOCTYPE html>
<html lang=en>
  <meta charset=utf-8>
  <meta name=viewport content="initial-scale=1, minimum-scale=1, width=device-width">
  <title>Error 400 (Bad Request)!!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%;-m

it works if I had a dplyr command after

dplyr::tbl(con, sql("SELECT * FROM my_data_seb")) %>% select(am, carb, vs, qsec)

Complete
Billed: 10.49 MB
Downloading first chunk of data.
First chunk includes all requested rows.
# Source:   SQL [?? x 4]
# Database: BigQueryConnection
      am  carb    vs  qsec
   <int> <int> <int> <dbl>
 1     0     2     1  20  
 2     0     2     1  22.9
 3     0     1     1  20.0
 4     0     1     1  19.4
 5     0     1     1  20.2
 6     0     4     1  18.3
 7     0     4     1  18.9
 8     0     2     0  17.0
 9     0     4     0  15.8
10     0     3     0  17.4
# ℹ more rows
# ℹ Use `print(n = ...)` to see more rows

it works if I download directly with {DBI}:

tb <-bq_dataset_query(ds, query = "SELECT * FROM my_data_seb")
#> Complete
#> Billed: 0 B
bq_table_fields(tb)
#> <bq_fields>
#>  am <INTEGER>
#>   carb <INTEGER>
#>   vs <INTEGER>
#>   qsec <FLOAT>
#>   wt <FLOAT>
#>   drat <FLOAT>
#>   disp <FLOAT>
#>   hp <INTEGER>
#>   cyl <INTEGER>
#>   gear <INTEGER>
#>   mpg <FLOAT>

The full verbose with httr::with_verbose(bq_dataset_query(ds, query = "SELECT * FROM my_data_seb")) being:

-> POST /bigquery/v2/projects/<my_private_project>/jobs/?fields=jobReference HTTP/1.1
-> Host: www.googleapis.com
-> User-Agent: bigrquery/1.4.2.9000 (GPN:RStudio; ) gargle/1.4.0 httr/1.4.4
-> Accept-Encoding: deflate, gzip
-> Accept: application/json, text/xml, application/xml, */*
-> Content-Type: application/json
-> Authorization: Bearer xxxx
-> Content-Length: 274
-> 
>> {
>>   "configuration": {
>>     "query": {
>>       "query": "SELECT * FROM my_data_seb",
>>       "useLegacySql": false,
>>       "priority": "INTERACTIVE",
>>       "defaultDataset": {
>>         "projectId": "<my_private_project>",
>>         "datasetId": "<my_private_dataset>"
>>       }
>>     }
>>   }
>> }

<- HTTP/1.1 200 OK
<- ETag: Rs4OglbFkBH6lGrF2sb6mQ==
<- Content-Type: application/json; charset=UTF-8
<- Vary: Origin
<- Vary: X-Origin
<- Vary: Referer
<- Content-Encoding: gzip
<- Date: Thu, 27 Apr 2023 10:49:53 GMT
<- Server: ESF
<- Cache-Control: private
<- X-XSS-Protection: 0
<- X-Frame-Options: SAMEORIGIN
<- X-Content-Type-Options: nosniff
<- Transfer-Encoding: chunked
<- 
-> GET /bigquery/v2/projects/<my_private_project>/jobs/job_EuspGUEH6z-BAmJ6oRG4tGBPccJq?location=EU&fields=status HTTP/1.1
-> Host: www.googleapis.com
-> User-Agent: bigrquery/1.4.2.9000 (GPN:RStudio; ) gargle/1.4.0 httr/1.4.4
-> Accept-Encoding: deflate, gzip
-> Accept: application/json, text/xml, application/xml, */*
-> Authorization: Bearer xxxx
-> 
<- HTTP/1.1 200 OK
<- ETag: Rs4OglbFkBH6lGrF2sb6mQ==
<- Content-Type: application/json; charset=UTF-8
<- Vary: Origin
<- Vary: X-Origin
<- Vary: Referer
<- Content-Encoding: gzip
<- Date: Thu, 27 Apr 2023 10:49:53 GMT
<- Server: ESF
<- Cache-Control: private
<- X-XSS-Protection: 0
<- X-Frame-Options: SAMEORIGIN
<- X-Content-Type-Options: nosniff
<- Transfer-Encoding: chunked
<- 
Complete
-> GET /bigquery/v2/projects/<my_private_project>/jobs/job_EuspGUEH6z-BAmJ6oRG4tGBPccJq?location=EU&fields=statistics HTTP/1.1
-> Host: www.googleapis.com
-> User-Agent: bigrquery/1.4.2.9000 (GPN:RStudio; ) gargle/1.4.0 httr/1.4.4
-> Accept-Encoding: deflate, gzip
-> Accept: application/json, text/xml, application/xml, */*
-> Authorization: Bearer xxxx
-> 
<- HTTP/1.1 200 OK
<- ETag: Rs4OglbFkBH6lGrF2sb6mQ==
<- Content-Type: application/json; charset=UTF-8
<- Vary: Origin
<- Vary: X-Origin
<- Vary: Referer
<- Content-Encoding: gzip
<- Date: Thu, 27 Apr 2023 10:49:53 GMT
<- Server: ESF
<- Cache-Control: private
<- X-XSS-Protection: 0
<- X-Frame-Options: SAMEORIGIN
<- X-Content-Type-Options: nosniff
<- Transfer-Encoding: chunked
<- 
Billed: 0 B
-> GET /bigquery/v2/projects/<my_private_project>/jobs/job_EuspGUEH6z-BAmJ6oRG4tGBPccJq?location=EU&fields=configuration%28query%28destinationTable%29%29 HTTP/1.1
-> Host: www.googleapis.com
-> User-Agent: bigrquery/1.4.2.9000 (GPN:RStudio; ) gargle/1.4.0 httr/1.4.4
-> Accept-Encoding: deflate, gzip
-> Accept: application/json, text/xml, application/xml, */*
-> Authorization: Bearer xxxx
-> 
<- HTTP/1.1 200 OK
<- ETag: Rs4OglbFkBH6lGrF2sb6mQ==
<- Content-Type: application/json; charset=UTF-8
<- Vary: Origin
<- Vary: X-Origin
<- Vary: Referer
<- Content-Encoding: gzip
<- Date: Thu, 27 Apr 2023 10:49:53 GMT
<- Server: ESF
<- Cache-Control: private
<- X-XSS-Protection: 0
<- X-Frame-Options: SAMEORIGIN
<- X-Content-Type-Options: nosniff
<- Transfer-Encoding: chunked
<- 
<bq_table> <my_private_project>-xxxx
> httr::with_verbose(bq_dataset_query(ds, query = "SELECT * FROM my_data_seb"))
-> POST /bigquery/v2/projects/<my_private_project>/jobs/?fields=jobReference HTTP/1.1
-> Host: www.googleapis.com
-> User-Agent: bigrquery/1.4.2.9000 (GPN:RStudio; ) gargle/1.4.0 httr/1.4.4
-> Accept-Encoding: deflate, gzip
-> Accept: application/json, text/xml, application/xml, */*
-> Content-Type: application/json
-> Authorization: Bearer xxxx
-> Content-Length: 274
-> 
>> {
>>   "configuration": {
>>     "query": {
>>       "query": "SELECT * FROM my_data_seb",
>>       "useLegacySql": false,
>>       "priority": "INTERACTIVE",
>>       "defaultDataset": {
>>         "projectId": "<my_private_project>",
>>         "datasetId": "<my_private_dataset>"
>>       }
>>     }
>>   }
>> }

<- HTTP/1.1 200 OK
<- ETag: HuEK9W2FBSHwqNg5/uyMzw==
<- Content-Type: application/json; charset=UTF-8
<- Vary: Origin
<- Vary: X-Origin
<- Vary: Referer
<- Content-Encoding: gzip
<- Date: Thu, 27 Apr 2023 10:50:32 GMT
<- Server: ESF
<- Cache-Control: private
<- X-XSS-Protection: 0
<- X-Frame-Options: SAMEORIGIN
<- X-Content-Type-Options: nosniff
<- Transfer-Encoding: chunked
<- 
-> GET /bigquery/v2/projects/<my_private_project>/jobs/job_jC32b2DgFuPvCRVQiXjkMdx4_xFq?location=EU&fields=status HTTP/1.1
-> Host: www.googleapis.com
-> User-Agent: bigrquery/1.4.2.9000 (GPN:RStudio; ) gargle/1.4.0 httr/1.4.4
-> Accept-Encoding: deflate, gzip
-> Accept: application/json, text/xml, application/xml, */*
-> Authorization: Bearer xxxx
-> 
<- HTTP/1.1 200 OK
<- ETag: HuEK9W2FBSHwqNg5/uyMzw==
<- Content-Type: application/json; charset=UTF-8
<- Vary: Origin
<- Vary: X-Origin
<- Vary: Referer
<- Content-Encoding: gzip
<- Date: Thu, 27 Apr 2023 10:50:32 GMT
<- Server: ESF
<- Cache-Control: private
<- X-XSS-Protection: 0
<- X-Frame-Options: SAMEORIGIN
<- X-Content-Type-Options: nosniff
<- Transfer-Encoding: chunked
<- 
Complete
-> GET /bigquery/v2/projects/<my_private_project>/jobs/job_jC32b2DgFuPvCRVQiXjkMdx4_xFq?location=EU&fields=statistics HTTP/1.1
-> Host: www.googleapis.com
-> User-Agent: bigrquery/1.4.2.9000 (GPN:RStudio; ) gargle/1.4.0 httr/1.4.4
-> Accept-Encoding: deflate, gzip
-> Accept: application/json, text/xml, application/xml, */*
-> Authorization: Bearer xxxx
-> 
<- HTTP/1.1 200 OK
<- ETag: HuEK9W2FBSHwqNg5/uyMzw==
<- Content-Type: application/json; charset=UTF-8
<- Vary: Origin
<- Vary: X-Origin
<- Vary: Referer
<- Content-Encoding: gzip
<- Date: Thu, 27 Apr 2023 10:50:32 GMT
<- Server: ESF
<- Cache-Control: private
<- X-XSS-Protection: 0
<- X-Frame-Options: SAMEORIGIN
<- X-Content-Type-Options: nosniff
<- Transfer-Encoding: chunked
<- 
Billed: 0 B
-> GET /bigquery/v2/projects/<my_private_project>/jobs/job_jC32b2DgFuPvCRVQiXjkMdx4_xFq?location=EU&fields=configuration%28query%28destinationTable%29%29 HTTP/1.1
-> Host: www.googleapis.com
-> User-Agent: bigrquery/1.4.2.9000 (GPN:RStudio; ) gargle/1.4.0 httr/1.4.4
-> Accept-Encoding: deflate, gzip
-> Accept: application/json, text/xml, application/xml, */*
-> Authorization: Bearer xxxx
-> 
<- HTTP/1.1 200 OK
<- ETag: HuEK9W2FBSHwqNg5/uyMzw==
<- Content-Type: application/json; charset=UTF-8
<- Vary: Origin
<- Vary: X-Origin
<- Vary: Referer
<- Content-Encoding: gzip
<- Date: Thu, 27 Apr 2023 10:50:32 GMT
<- Server: ESF
<- Cache-Control: private
<- X-XSS-Protection: 0
<- X-Frame-Options: SAMEORIGIN
<- X-Content-Type-Options: nosniff
<- Transfer-Encoding: chunked
<- 
<bq_table> <my_private_project>-xxxx

My package versions:

> packageVersion("dbplyr")
[1] ‘2.3.1’
> packageVersion("bigrquery")
[1] ‘1.4.2.9000’
> packageVersion("dplyr")
[1] ‘1.1.2’

Apr 27 '23 11:04 statnmap

Is this still a problem for folks?

Nov 03 '23 13:11 hadley

No, not a problem; for me at least, it's working perfectly 🥳 🎉

I tried running the old reprex script from when I originally posted this (having updated dbplyr since then, but probably not bigrquery), and was prompted with the following:

Warning message:
<BigQueryConnection> uses an old dbplyr interface
ℹ Please install a newer version of the package or contact the maintainer
This warning is displayed once every 8 hours. 
> tbl(con, "311_service_requests") |> 
+   select(unique_key) |> 
+   head(n = 10)
Error in curl::curl_fetch_memory(url, handle = handle) : 
  URL rejected: Malformed input to a URL function

But after installing the latest CRAN version of bigrquery everything's a smashing success. Thank you! 😄

Nov 06 '23 14:11 hlynurhallgrims

bigrquery bigrquery copied to clipboard

411 Error: POST requests require a Content-length header

Reprex follows

dbplyr

Here are the different results from httr::with_verbose, because they don't show in the reprex.

If I run tbl() with the table directly it works

If I run tbl() with a sql() command, I get the Error 400 (Bad Request):

it works if I had a dplyr command after

it works if I download directly with {DBI}:

bigrquery
bigrquery copied to clipboard

If I run `tbl()` with the table directly it works

If I run `tbl()` with a `sql()` command, I get the `Error 400 (Bad Request)`: