DELETE ROW-- Wrote diagnostic object to 'gar_parse_error.rds'

[Tartomas]

Hello, thanks for your contribution. I'm facing the following error.

I'm working in a shiny app with bigqueryR where I need to create, erase and update client information, base in two variable: Name and User. For this, I create two function, one to append information and other one, to erase a row in BQ from R. The first one add client properly. But, the following function deleteBQ also works, but it give me the following parsing error from the API, where I do not know how to manage it. Then, I check it out the data in BQ console and was erased it properly.

Any idea what is behind this error?
Best regards
Tomás

deleteDB = function(client=NULL,user=NULL,table=table){ 
  query = paste0("DELETE FROM ",table," WHERE Name = '",client,"' AND User = '",user,"'") 
  QueryReturn <- try(bqr_query(projectId = project_id,
                               datasetId = datasetid, query,useLegacySql = FALSE)) 
}

>  deleteDB(client = client,user = user)

Error in matrix(unlist(unlist(x$rows)), ncol = length(schema$name), byrow = TRUE) : 
  'data' must be of a vector type, was 'NULL'
Error : API Data failed to parse.  
             Wrote diagnostic object to 'gar_parse_error.rds', use googleAuthR::gar_debug_parse('gar_parse_error.rds') to 
             debug the data_parse_function.
Warning message:
In bqr_query(projectId = cred$project_id, datasetId = cred$datasetid,  :
  API Data failed to parse.  Wrote diagnostic object to 'gar_parse_error.rds', use googleAuthR::gar_debug_parse('gar_parse_error.rds') to debug the data_parse_function.


> googleAuthR::gar_debug_parsing(filename = "gar_parse_error.rds")

2020-02-12 13:18:28> # When creating a GitHub issue, please include this output.
List of 3
 $ request       :List of 4
  ..$ req_url     : chr "https://www.googleapis.com/bigquery/v2/projects/'project'/queries"
  ..$ request_type: chr "POST"
  ..$ the_body    :List of 6
  .. ..$ kind          : chr "bigquery#queryRequest"
  .. ..$ query         : chr "DELETE FROM clientes WHERE Name = 'diego' AND User = 'uribe'"
  .. ..$ maxResults    : num 1000
  .. ..$ useLegacySql  : logi FALSE
  .. ..$ useQueryCache : logi TRUE
  .. ..$ defaultDataset:List of 2
  .. .. ..$ datasetId: chr "datasetid"
  .. .. ..$ projectId: chr "proyect"
  ..$ customConfig: NULL
 $ response      :List of 3
  ..$ data_parse_args: list()
  ..$ data_parse_func:function (x)  
  ..$ content        :List of 7
  .. ..$ kind               : chr "bigquery#queryResponse"
  .. ..$ schema             :List of 1
  .. .. ..$ fields:'data.frame':	2 obs. of  4 variables:
  .. .. .. ..$ name       : chr [1:2] "Name" "User"
  .. .. .. ..$ type       : chr [1:2] "STRING" "STRING"
  .. .. .. ..$ mode       : chr [1:2] "NULLABLE" "NULLABLE"
  .. .. .. ..$ description: chr [1:2] "" ""
  .. ..$ jobReference       :List of 3
  .. .. ..$ projectId: chr "proyect"
  .. .. ..$ jobId    : chr "job_1rpsitZzGa8ndzuDeBIN0eNBPCHA"
  .. .. ..$ location : chr "US"
  .. ..$ totalBytesProcessed: chr "587"
  .. ..$ jobComplete        : logi TRUE
  .. ..$ cacheHit           : logi FALSE
  .. ..$ numDmlAffectedRows : chr "0"
 $ authentication:List of 1
  ..$ token:Classes 'TokenServiceAccount', 'Token2.0', 'Token', 'R6' <TokenServiceAccount>
  Inherits from: <Token2.0>
  Public:
    app: NULL
    cache: function (path) 
    cache_path: FALSE
    can_refresh: function () 
    clone: function (deep = FALSE) 
    credentials: list
    endpoint: oauth_endpoint
    hash: function () 
    init_credentials: function () 
    initialize: function (endpoint, secrets, params) 
    load_from_cache: function () 
    params: list
    print: function (...) 
    private_key: NULL
    refresh: function () 
    revoke: function () 
    secrets: list
    sign: function (method, url) 
    validate: function ()  
 - attr(*, "class")= chr "gar_parse_error"
2020-02-12 13:18:29> - Attempting data parsing
$request
$request$req_url
[1] "https://www.googleapis.com/bigquery/v2/projects/'project'/queries"

$request$request_type
[1] "POST"

$request$the_body
$request$the_body$kind
[1] "bigquery#queryRequest"

$request$the_body$query
[1] "DELETE FROM clientes WHERE Name = 'diego' AND User = 'uribe'"

$request$the_body$maxResults
[1] 1000

$request$the_body$useLegacySql
[1] FALSE

$request$the_body$useQueryCache
[1] TRUE

$request$the_body$defaultDataset
$request$the_body$defaultDataset$datasetId
[1] "datasetID"

$request$the_body$defaultDataset$projectId
[1] "project"

$request$customConfig
NULL

$response
$response$data_parse_args
list()

$response$data_parse_func
function (x) 
{
    converter <- list(integer = as.integer, float = as.double, 
        boolean = as.logical, string = identity, timestamp = function(x) as.POSIXct(as.integer(x), 
            origin = "1970-01-01", tz = "UTC"), date = function(x) as.Date(x, 
            format = "%Y-%m-%d"))
    schema <- x$schema$fields
    data_f <- as.data.frame(matrix(unlist(unlist(x$rows)), ncol = length(schema$name), 
        byrow = TRUE), stringsAsFactors = FALSE)
    types <- tolower(schema$type)
    converter_funcs <- converter[types]
    for (i in seq_along(converter_funcs)) {
        data_f[, i] <- converter_funcs[[i]](data_f[, i])
    }
    names(data_f) <- schema$name
    out <- data_f
    out <- as.data.frame(out, stringsAsFactors = FALSE)
    attr(out, "jobReference") <- x$jobReference
    attr(out, "pageToken") <- x$pageToken
    out
}
<bytecode: 0x000001f4832d22b8>
<environment: namespace:bigQueryR>

$response$content
$response$content$kind
[1] "bigquery#queryResponse"

$response$content$schema
$response$content$schema$fields
  name   type     mode description
1 Name STRING NULLABLE            
2 User STRING NULLABLE            


$response$content$jobReference
$response$content$jobReference$projectId
[1] "project"

$response$content$jobReference$jobId
[1] "job_1rpsitZzGa8ndzuDeBIN0eNBPCHA"

$response$content$jobReference$location
[1] "US"


$response$content$totalBytesProcessed
[1] "587"

$response$content$jobComplete
[1] TRUE

$response$content$cacheHit
[1] FALSE

$response$content$numDmlAffectedRows
[1] "0"



$authentication
$authentication$token
<Token>
<oauth_endpoint>
 authorize: https://accounts.google.com/o/oauth2/auth
 access:    https://accounts.google.com/o/oauth2/token
 validate:  https://www.googleapis.com/oauth2/v1/tokeninfo
 revoke:    https://accounts.google.com/o/oauth2/revoke
NULL
<credentials> access_token, expires_in, token_type
---


attr(,"class")
[1] "gar_parse_error"

> sessionInfo()

R version 3.6.0 (2019-04-26)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 18362)

Matrix products: default

locale:
[1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United States.1252   
[3] LC_MONETARY=English_United States.1252 LC_NUMERIC=C                          
[5] LC_TIME=English_United States.1252    

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
 [1] sp_1.3-1                  bigQueryR_0.5.0           googleCloudStorageR_0.5.0
 [4] googlesheets4_0.1.0.9000  googledrive_1.0.0         DBI_1.0.0                
 [7] bigrquery_1.2.0           scales_1.0.0              ggthemes_4.2.0           
[10] shinybusy_0.2.0           fullcalendar_0.0.0.9000   htmlwidgets_1.3          
[13] plotly_4.9.0              ggplot2_3.2.1             jsonlite_1.6             
[16] dplyr_0.8.3               leaflet_2.0.2             shinyalert_1.0           
[19] shinyBS_0.61              shinycssloaders_0.2.0     shinyWidgets_0.4.9       
[22] shinyjs_1.0               shiny_1.3.2               shinydashboard_0.7.1     
[25] htmltools_0.3.6           crayon_1.3.4             

loaded via a namespace (and not attached):
 [1] Rcpp_1.0.1        lattice_0.20-38   lubridate_1.7.4   tidyr_1.0.0       assertthat_0.2.1 
 [6] zeallot_0.1.0     digest_0.6.19     mime_0.8          R6_2.4.0          cellranger_1.1.0 
[11] backports_1.1.4   httr_1.4.1        pillar_1.4.2      rlang_0.4.2       lazyeval_0.2.2   
[16] curl_4.3          rstudioapi_0.10   data.table_1.12.2 googleAuthR_1.1.1 stringr_1.4.0    
[21] bit_1.1-14        munsell_0.5.0     compiler_3.6.0    httpuv_1.5.1      pkgconfig_2.0.2  
[26] askpass_1.1       openssl_1.4.1     tidyselect_0.2.5  tibble_2.1.3      viridisLite_0.3.0
[31] withr_2.1.2       later_0.8.0       grid_3.6.0        xtable_1.8-4      gtable_0.3.0     
[36] lifecycle_0.1.0   magrittr_1.5      zip_2.0.3         stringi_1.4.3     fs_1.3.1         
[41] promises_1.0.1    vctrs_0.2.1       tools_3.6.0       bit64_0.9-7       glue_1.3.1       
[46] purrr_0.3.3       crosstalk_1.0.0   yaml_2.2.0        colorspace_1.4-1  gargle_0.4.0     
[51] memoise_1.1.0  

[MarkEdmondson1234]

Yes, the function was made before DELETE was available, so it is expecting a data.frame of your SQL, not just an empty response that I guess DELETE queries do. As you say the query is working, its just the API response is unexpected.

I think for delete functions you should use bq_query_asynch() instead - this will return a jobId whatever happens, and you can deal with it better. If its a Shiny app it will probably be a better experience if you use bq_query_asynch() then bqr_get_job() with the jobId as then you can send some feedback (such as a progress bar) back to the user as they wait.

[Tartomas]

Thanks Mark for your quick response. I will test as you propose and I'm back to you.