Receiving curl error when using tbl(con, sql("string query"))

[mjbroerman]

Receiving curl error when using tbl(con, sql("string query"))

[talegari]

I have landed on the same issue. This is a serious blocker for writing some non-trivial queries which need UNNEST which cannot be written with a dplyr/dbplyr verb. @hadley

[talegari]

Here is a quick and temporary fix:

#' @name bq_tbl_from_query
#' @title Create a tbl object from a sql query for a bigquery connection
#' @description `tbl(con, sql(query))` should work, but fails on `bigquery
#'   (1.4.2)`. This is a interim solution to keep my work going. See the issue
#'   here: https://github.com/r-dbi/bigrquery/issues/540
#' @details
#'
#' 1. create a temporary table (expires in 1 day by default) using
#' `DBI::dbExecute`.
#'
#' 2. Create a `tbl` connection to the temporary table.
#'
#' Package dependencies: `bigrquery`, `DBI`, `dplyr`, `checkmate`, `cli`, `glue`
#'
#' @param con (object) of class 'BigQueryConnection'
#' @param query (string) query string
#' @param dataset (string) dataset where temporary table gets created
#' @param n_days_expiration (integerish) Number of days of expiry for the
#'   temporary table
bq_tbl_from_query = function(con,
                             query,
                             dataset = NULL, 
                             n_days_expiration = 1
                             ){
  
  checkmate::assert_class(con, "BigQueryConnection")
  checkmate::assert_string(query)
  checkmate::assert_string(dataset, null.ok = TRUE)
  checkmate::assert_integerish(n_days_expiration, lower = 1)
  
  # if dataset is NULL, try and extract 'dataset' from connection object
  if (is.null(con_ads@dataset)){
    if (is.null(dataset)) {
      stop("'con' does not have dataset. Please provide 'dataset' input.")
    }
  } else {
    if (is.null(dataset)) {
      dataset = con_ads@dataset
    }
  }
  dataset_exists_flag = bigrquery::bq_dataset_exists(
    bigrquery::bq_dataset(project = con@project, dataset = dataset)
    )
  checkmate::assert_true(dataset_exists_flag)
  
  # set expiration string
  expiration_string = 
    glue::glue("OPTIONS(expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL {n_days_expiration} DAY))")
  
  # pick a random table name and create it
  table_exists_flag = TRUE
  while(table_exists_flag){
    table_name = paste(sample(letters, 20), collapse = "")
    table_exists_flag = bigrquery::bq_table_exists(
      bigrquery::bq_table(project = con@project,
                          dataset = dataset,
                          table = table_name
                          )
      )
  }
  
  create_query_string =
    glue::glue("CREATE TABLE {dataset}.{table_name} {expiration_string} AS ({query})")
  
  exec_flag = (DBI::dbExecute(con, create_query_string) == 0)
  if (!exec_flag){
    stop("Query failed to run")
  }
  
  cli::cli_alert_info(glue::glue("Temporary table created at `{dataset}.{table_name}` will auto-expire in {n_days_expiration} days"))
  
  # create a tbl object
  res = dplyr::tbl(con, glue::glue("{dataset}.{table_name}"))
  
  return(res)
}

[hadley]

Somewhat more minimal reprex:

library(dbplyr)
library(dplyr, warn.conflicts = FALSE)
library(bigrquery)

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

tbl(con, dbplyr::sql("SELECT * FROM tags LIMIT 10"))
#> Warning: <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.
#> ! 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]'.
#> Error in curl::curl_fetch_memory(url, handle = handle): URL rejected: Malformed input to a URL function

^{Created on 2023-11-02 with reprex v2.0.2}

[hadley]

Interestingly wrapping in collect() or as.data.frame() makes the problem go away, so this is caused by the print() method?

Looks like the problem is caused by which branch of op_can_download(x) you travel down.

Looks like that's caused by head(): collect(head(tbl(con, dbplyr::sql("SELECT * FROM tags LIMIT 10")), n = 10)) gives a simpler call stack.