bigQueryR
bigQueryR copied to clipboard
cloudyr
DELETE ROW-- Wrote diagnostic object to 'gar_parse_error.rds'
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
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.
Thanks Mark for your quick response. I will test as you propose and I'm back to you.