Error 409 : "JSON fetch error: Already Exists: "
Closed this issue · 22 comments
Hi,
I create an script to export data from BigQuery with library BigQueryR.
When I use bqr_query, the script returns results.
When I use bqr_query_asynch, the script fails and return an error :
"Auto-refreshing stale OAuth token.
Request Status Code: 409
Error in checkGoogleAPIError(req) :
JSON fetch error: Already Exists: Job trim-saga-436:0yUe56n5wGynh5f "
This job doesn't exist. I check with : bqr_list_jobs(projectId, allUsers = FALSE, projection = c("full","minimal"), stateFilter = c("done", "pending", "running")).
Have you an idea ?
Thank you in advance.
Best regards
Thanks, may I just check if you are using the github or CRAN version?
I install and use this package :
library(bigQueryR)
library(googleCloudStorageR)
Ok but where was it installed from? Easiest would be to load your libraries then run sessionInfo()
R version 3.3.2 (2016-10-31)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows >= 8 x64 (build 9200)
locale:
[1] LC_COLLATE=French_France.1252 LC_CTYPE=French_France.1252 LC_MONETARY=French_France.1252 LC_NUMERIC=C LC_TIME=French_France.1252
attached base packages:
[1] stats graphics grDevices utils datasets methods base
other attached packages:
[1] googleCloudStorageR_0.2.0 bigQueryR_0.2.0 RMySQL_0.10.9 DBI_0.5-1
loaded via a namespace (and not attached):
[1] Rcpp_0.12.9 crayon_1.3.2 digest_0.6.12 withr_1.0.2 mime_0.5 R6_2.2.0 xtable_1.8-2 magrittr_1.5
[9] httr_1.2.1 testthat_1.0.2 googleAuthR_0.4.0 devtools_1.12.0 tools_3.3.2 shiny_1.0.0 httpuv_1.3.3 memoise_1.0.0
[17] htmltools_0.3.5
Thanks :)
Bit hard to track down this one - could you instal the github version:
if(!require("ghit")){
install.packages("ghit")
}
ghit::install_github("cloudyr/bigQueryR")
and rerun your query, but turn the verbose logging on first:
options(googleAuthR.verbose = 1)
and if it errors again paste the output here.
Please also run
googleAuthR::gar_token_info()
I believe this is an authentication issue, that should be clearer to debug if you load the github version now.
Hi,
When i try to launch this command :
if(!require("ghit")){
install.packages("ghit")
}
ghit::install_github("cloudyr/bigQueryR")
R return :
Version requirements for 'Suggests' dependencies currently ignored.
Error in read.dcf(file = tmpf) : cannot open the connection
In addition: Warning message:
In read.dcf(file = tmpf) :
cannot open compressed file 'C:\Users\..\AppData\Local\Temp\RtmpCirjzf/ghitdrat/src/contrib/PACKAGES', probable reason 'No such file or directory'
Have you an idea?
It's an authentification issue.
When i change account, the job is ok but the first account is unusable with R.
Thanks
Try installing devtools
from CRAN, then:
devtools::install_github("cloudyr/bigQueryR")
I install bigQueryR from git.
I launch
bqr_auth(new_user = TRUE)
R return :
Removing old credentials ... .httr-oauth
Waiting for authentication in browser...
Press Esc/Ctrl + C to abort
Authentication complete.
Token cache file: .httr-oauth
Scopes: https://www.googleapis.com/auth/devstorage.full_control
Hash: xxxxxxxxxxxxxxxxxxxxxxxxxxx
App key: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.apps.googleusercontent.com
Method: new_token
I launch my function : "executeQueryFileBQ(folder_export,paramThem)"
R return :
Token cache file: .httr-oauth
Scopes: https://www.googleapis.com/auth/devstorage.full_control
Hash: xxxxxxxxxxxxxxxxxxxxxxxxxxx
App key: xxxxxxxxxxxxxxxxxxxxxxxxxxx.apps.googleusercontent.com
Method: new_token
Set default bucket name to 'export_temporaire'
[1] "Debut du traitement"
[1] "2017-04-18 15:10:53 CEST"
[1] "-------------------"
[1] "Construction de la requete SQL"
Error in check_bq_auth() :
Not authenticated with Google BigQuery. Needs to be one of https://www.googleapis.com/auth/cloud-platform https://www.googleapis.com/auth/bigquery
4. stop("Not authenticated with Google BigQuery. Needs to be one of ",
paste(cloud_scopes, collapse = " "))
3. check_bq_auth()
2. bqr_query_asynch(project_id, dataset_id, bqQuery, destinationTableId = destinationTableId,
writeDisposition = "WRITE_TRUNCATE") at function_extract.r#113
1. executeQueryFileBQ(folder_export, paramThem)
You have the scope set for Google Cloud Storage, but not BigQuery
https://www.googleapis.com/auth/devstorage.full_control
vs https://www.googleapis.com/auth/bigquery
If you make sure this is set before authentication and loading the library, it should do it, as it allows both:
options(googleAuthR.scopes.selected = "https://www.googleapis.com/auth/cloud-platform")
once authenticated, when i launch this request :
job <- bqr_query_asynch(project_id,dataset_id,"SELECT * FROM [xxxxxxx.xxxxxxxx1.__TABLES__]
where table_id contains 'ga_sessions_intraday' and table_id contains '201705'
order by table_id desc limit 2"
,destinationTableId = "bigResultTable")
R returns :
Token exists.
Valid local token
Request: https://www.googleapis.com/bigquery/v2/projects/xxxxxxxxxx/jobs/
Body JSON parsed to: {"jobReference":{"projectId":"xxxxxxxxxx","jobId":"XZPE1g8xBmDiZCq"},"configuration":{"query":{"allowLargeResults":true,"defaultDataset":{"datasetId":"analyse","projectId":"xxxxxxxxxx"},"destinationTable":{"datasetId":"analyse","projectId":"xxxxxxxxxx","tableId":"bigResultTable"},"query":"SELECT * FROM [xxxxxxxxxx.xxxxxxxxxx.__TABLES__] where table_id contains 'ga_sessions_intraday' and table_id contains '201705' order by table_id desc limit 2","useLegacySql":true,"writeDisposition":["WRITE_EMPTY","WRITE_TRUNCATE","WRITE_APPEND"]}}}
-> POST /bigquery/v2/projects/xxxxxxxxxx/jobs/ HTTP/2
-> Host: www.googleapis.com
-> User-Agent: googleAuthR/0.5.1 (gzip)
-> Accept: application/json, text/xml, application/xml, */*
-> Content-Type: application/json
-> Accept-Encoding: gzip
-> Authorization: Bearer xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-> Content-Length: 548
->
>> {"jobReference":{"projectId":"xxxxxxxxxx","jobId":"XZPE1g8xBmDiZCq"},"configuration":{"query":{"allowLargeResults":true,"defaultDataset":{"datasetId":"analyse","projectId":"xxxxxxxxxx"},"destinationTable":{"datasetId":"analyse","projectId":"xxxxxxxxxx","tableId":"bigResultTable"},"query":"SELECT * FROM [xxxxxxxxxx.xxxxxxxxxx.__TABLES__] where table_id contains 'ga_sessions_intraday' and table_id contains '201705' order by table_id desc limit 2","useLegacySql":true,"writeDisposition":["WRITE_EMPTY","WRITE_TRUNCATE","WRITE_APPEND"]}}}
<- HTTP/2 409
<- vary: Origin
<- vary: X-Origin
<- content-type: application/json; charset=UTF-8
<- content-encoding: gzip
<- date: Fri, 05 May 2017 08:33:09 GMT
<- expires: Fri, 05 May 2017 08:33:09 GMT
<- cache-control: private, max-age=0
<- x-content-type-options: nosniff
<- x-frame-options: SAMEORIGIN
<- x-xss-protection: 1; mode=block
<- content-length: 164
<- server: GSE
<- alt-svc: quic=":443"; ma=2592000; v="37,36,35"
<-
Request Status Code: 409
JSON fetch error: Already Exists: Job xxxxxxxxxx:XZPE1g8xBmDiZCq
No retry attempted: Already Exists: Job xxxxxxxxxx:XZPE1g8xBmDiZCq
Error in checkGoogleAPIError(req) :
JSON fetch error: Already Exists: Job xxxxxxxxxx:XZPE1g8xBmDiZCq
In addition: Warning message:
In if (!getOption("googleAuthR.scopes.selected") %in% cloud_scopes) { :
the condition has length > 1 and only the first element will be used
Hmm ok thanks, I can't replicate it so I'll see what else to try.
Ok this works for me, could you see if the same? The error is weird, I think it may be something else other than jobs.
Get latest version of bigQueryR from github:
devtools::install_github("cloudyr/bigQueryR")
## Authenticate via JSON file on autoload or auth - make sure this has bigQuery access
library(bigQueryR)
#bqr_auth()
# set default project and dataset
bq_global_dataset("datasetId")
bq_global_project("projectId")
q = 'SELECT * FROM [projectId:datasetId.tableId]'
## an async job returns a job object
job <- bqr_query_asynch(query = q, destinationTableId = "test_table")
## wait for job to finish
bqr_wait_for_job(job)
## check if table has been created
tables <- bqr_list_tables()
## should be TRUE
"test_table" %in% tables$tableId
Either that or its a really long running job or something? Try looking through this list and seeing if any are still RUNNING
bqr_list_jobs()
I try your script
devtools::install_github("cloudyr/bigQueryR")
## Authenticate via JSON file on autoload or auth - make sure this has bigQuery access
library(bigQueryR)
#bqr_auth()
# set default project and dataset
bq_global_dataset("analyse_gch")
bq_global_project("trim-saga-436")
q = 'SELECT * FROM [trim-saga-436:analyse_gch.Identification_device_campaign]'
## an async job returns a job object
job <- bqr_query_asynch(query = q, destinationTableId = "test_table")
## wait for job to finish
bqr_wait_for_job(job)
## check if table has been created
tables <- bqr_list_tables()
## should be TRUE
"test_table" %in% tables$tableId
bqr_list_jobs()
R returns :
Request Status Code: 409
JSON fetch error: Already Exists: Job trim-saga-436:dLoyDtk1QDveZxY
No retry attempted: Already Exists: Job trim-saga-436:dLoyDtk1QDveZxY
Error in checkGoogleAPIError(req) :
JSON fetch error: Already Exists: Job trim-saga-436:dLoyDtk1QDveZxY
In addition: Warning message:
In if (!getOption("googleAuthR.scopes.selected") %in% cloud_scopes) { :
the condition has length > 1 and only the first element will be used
bqr_wait_for_job(job)
Error in inherits(x, "bqr_job") : object 'job' not found
>
> ## check if table has been created
> tables <- bqr_list_tables()
Token exists.
Valid local token
Request: https://www.googleapis.com/bigquery/v2/projects/yyyyyyyyyyy/datasets/analyse_gch/tables/?maxResults=1000&pageToken=
-> GET /bigquery/v2/projects/yyyyyyyyyyy/datasets/analyse_gch/tables/?maxResults=1000&pageToken= HTTP/2
-> Host: www.googleapis.com
-> User-Agent: googleAuthR/0.5.1 (gzip)
-> Accept: application/json, text/xml, application/xml, */*
-> Accept-Encoding: gzip
-> Authorization: Bearer zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
->
<- HTTP/2 200
<- expires: Fri, 05 May 2017 12:19:32 GMT
<- date: Fri, 05 May 2017 12:19:32 GMT
<- cache-control: private, max-age=0, must-revalidate, no-transform
<- etag: "qqqqqqqqqqqqqqqqqqqqqqqqqqq/qqqqqqqqqqqqqqqqqqqqq"
<- vary: Origin
<- vary: X-Origin
<- content-type: application/json; charset=UTF-8
<- content-encoding: gzip
<- x-content-type-options: nosniff
<- x-frame-options: SAMEORIGIN
<- x-xss-protection: 1; mode=block
<- content-length: 353
<- server: GSE
<- alt-svc: quic=":443"; ma=2592000; v="37,36,35"
<-
Warning message:
In if (!getOption("googleAuthR.scopes.selected") %in% cloud_scopes) { :
the condition has length > 1 and only the first element will be used
>
> ## should be TRUE
> "test_table" %in% tables$tableId
[1] FALSE
> bqr_list_jobs()
Token exists.
Valid local token
Request: https://www.googleapis.com/bigquery/v2/projects/yyyyyyyyyyy/jobs/?allUsers=FALSE&projection=full&stateFilter=done
-> GET /bigquery/v2/projects/yyyyyyyyyyy/jobs/?allUsers=FALSE&projection=full&stateFilter=done HTTP/2
-> Host: www.googleapis.com
-> User-Agent: googleAuthR/0.5.1 (gzip)
-> Accept: application/json, text/xml, application/xml, */*
-> Accept-Encoding: gzip
-> Authorization: Bearer zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
->
<- HTTP/2 200
<- expires: Fri, 05 May 2017 12:20:15 GMT
<- date: Fri, 05 May 2017 12:20:15 GMT
<- cache-control: private, max-age=0, must-revalidate, no-transform
<- etag: "qqqqqqqqqqqqqqqqqqqqqqqqqqq/qqqqqqqqqqqqqqqqqqqqq"
<- vary: Origin
<- vary: X-Origin
<- content-type: application/json; charset=UTF-8
<- content-encoding: gzip
<- x-content-type-options: nosniff
<- x-frame-options: SAMEORIGIN
<- x-xss-protection: 1; mode=block
<- server: GSE
<- alt-svc: quic=":443"; ma=2592000; v="37,36,35"
<-
[[1]]
==Google BigQuery Job==
JobID: job_AUtgQ02ZXfkR42xfk3zXsrXJK3E
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-05 12:57:48
Start: 2017-05-05 12:59:35
End: 2017-05-05 12:59:37
## View job configuration via job$configuration
[[2]]
==Google BigQuery Job==
JobID: job_oACOiRGeYLTzCMkI9eViAJGSE1g
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-05 12:55:02
Start: 2017-05-05 12:56:55
End: 2017-05-05 12:57:01
## View job configuration via job$configuration
[[3]]
==Google BigQuery Job==
JobID: job_Gza8cXio8K8xSgVBPJ9RWU19_XI
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-05 12:53:13
Start: 2017-05-05 12:54:56
End: 2017-05-05 12:55:01
## View job configuration via job$configuration
[[4]]
==Google BigQuery Job==
JobID: job_t16qCYCS5hkk_c2wJMcak5pDQX8
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-05 12:49:48
Start: 2017-05-05 12:51:47
End: 2017-05-05 12:52:01
## View job configuration via job$configuration
[[5]]
==Google BigQuery Job==
JobID: job_7NXOmniJNeYZhl-A2lhnh5iYVJE
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-05 12:47:41
Start: 2017-05-05 12:49:31
End: 2017-05-05 12:49:40
## View job configuration via job$configuration
[[6]]
==Google BigQuery Job==
JobID: job_Wwhng2u66XwHyGXC9l4ZpUe399A
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-05 12:45:50
Start: 2017-05-05 12:47:38
End: 2017-05-05 12:47:40
## View job configuration via job$configuration
[[7]]
==Google BigQuery Job==
JobID: job_nKEuWAsc-M1Ju_vWRGPLnVGFvfY
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-05 10:15:40
Start: 2017-05-05 10:17:21
End: 2017-05-05 10:17:23
## View job configuration via job$configuration
[[8]]
==Google BigQuery Job==
JobID: job_v8KKOSVXGI85mO5uYoVJvgANZt4
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-05 10:13:48
Start: 2017-05-05 10:15:27
End: 2017-05-05 10:15:32
## View job configuration via job$configuration
[[9]]
==Google BigQuery Job==
JobID: job_at8IG3369ufJkNx2U9moI37o6-k
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-05 10:12:11
Start: 2017-05-05 10:13:46
End: 2017-05-05 10:13:48
## View job configuration via job$configuration
[[10]]
==Google BigQuery Job==
JobID: job_-8i3BHPijH0EjLYlym2P8S4IhNM
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-05 10:10:49
Start: 2017-05-05 10:12:33
End: 2017-05-05 10:12:38
## View job configuration via job$configuration
[[11]]
==Google BigQuery Job==
JobID: job_ZDTpfe7VaZ6pQtTJXVKe6UvPYTQ
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-05 10:08:01
Start: 2017-05-05 10:10:01
End: 2017-05-05 10:10:13
## View job configuration via job$configuration
[[12]]
==Google BigQuery Job==
JobID: job_08FBpVE8t5psEmLhAbRu6aLXdPY
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-05 10:05:49
Start: 2017-05-05 10:07:58
End: 2017-05-05 10:08:00
## View job configuration via job$configuration
[[13]]
==Google BigQuery Job==
JobID: job_1WFV2mnlbDdLU5mDZGSlIVKdoJc
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-05 09:59:02
Start: 2017-05-05 10:00:51
End: 2017-05-05 10:00:53
## View job configuration via job$configuration
[[14]]
==Google BigQuery Job==
JobID: job_liQPD44-f6uCZwizBxyl9Do7XD0
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-05 09:57:15
Start: 2017-05-05 09:58:52
End: 2017-05-05 09:58:54
## View job configuration via job$configuration
[[15]]
==Google BigQuery Job==
JobID: job_xzrxlu80XrvCckQvwASoyEVSOXA
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-05 09:54:46
Start: 2017-05-05 09:56:17
End: 2017-05-05 09:56:20
## View job configuration via job$configuration
[[16]]
==Google BigQuery Job==
JobID: job_24VlMLAiFJQe7VFRwUmaIRyr5o4
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-05 09:53:07
Start: 2017-05-05 09:54:43
End: 2017-05-05 09:54:44
## View job configuration via job$configuration
[[17]]
==Google BigQuery Job==
JobID: job_na4nvGz1uzsWGm4T7SBXLXM258M
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-05 09:49:49
Start: 2017-05-05 09:51:41
End: 2017-05-05 09:51:53
## View job configuration via job$configuration
[[18]]
==Google BigQuery Job==
JobID: job_rtyCr7U2zESx9IujjYJR_MRGU1A
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-05 09:47:38
Start: 2017-05-05 09:49:32
End: 2017-05-05 09:49:40
## View job configuration via job$configuration
[[19]]
==Google BigQuery Job==
JobID: job_c3KOKUG75Zyo5DgE8pX8d8VX-k4
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-05 09:45:51
Start: 2017-05-05 09:47:35
End: 2017-05-05 09:47:36
## View job configuration via job$configuration
[[20]]
==Google BigQuery Job==
JobID: job_Hd6xMi8jCDbuVUXiE2EF5Z7E-9A
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-04 12:57:20
Start: 2017-05-04 12:58:57
End: 2017-05-04 12:59:00
## View job configuration via job$configuration
[[21]]
==Google BigQuery Job==
JobID: job_pbUYmvHXV_NqS9aCm-A1NsuSZlo
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-04 12:54:52
Start: 2017-05-04 12:56:31
End: 2017-05-04 12:56:35
## View job configuration via job$configuration
[[22]]
==Google BigQuery Job==
JobID: job_tzUexppgTR3CBsygrgs4_Mr5Zw8
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-04 12:52:50
Start: 2017-05-04 12:54:49
End: 2017-05-04 12:54:50
## View job configuration via job$configuration
[[23]]
==Google BigQuery Job==
JobID: job_VlFPKEWMh2MOeYHDw0YywXnEvc0
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-04 12:49:33
Start: 2017-05-04 12:51:27
End: 2017-05-04 12:51:38
## View job configuration via job$configuration
[[24]]
==Google BigQuery Job==
JobID: job_KBlMseRP3Q-U71HAtUPVWXGHyzA
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-04 12:47:33
Start: 2017-05-04 12:49:16
End: 2017-05-04 12:49:24
## View job configuration via job$configuration
[[25]]
==Google BigQuery Job==
JobID: job_vrT2upnp990Ygl_6rOOaKxgc0ao
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-04 12:45:52
Start: 2017-05-04 12:47:30
End: 2017-05-04 12:47:31
## View job configuration via job$configuration
[[26]]
==Google BigQuery Job==
JobID: job_jKAwdAcWbDp5-lxyPD5Q1klnvsE
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-04 11:57:02
Start: 2017-05-04 11:58:35
End: 2017-05-04 11:58:50
## View job configuration via job$configuration
[[27]]
==Google BigQuery Job==
JobID: job_iQ3VY9_LDWMN_EFGi0m-TPQUYxQ
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-04 09:58:22
Start: 2017-05-04 10:00:00
End: 2017-05-04 10:00:03
## View job configuration via job$configuration
[[28]]
==Google BigQuery Job==
JobID: job_5K73MMtn6VVzXRzwm5xxGL2ZhIQ
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-04 09:56:32
Start: 2017-05-04 09:58:14
End: 2017-05-04 09:58:16
## View job configuration via job$configuration
[[29]]
==Google BigQuery Job==
JobID: job_x7o_RSVjdlHoLiRL_oaD8FOrRSY
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-04 09:54:10
Start: 2017-05-04 09:55:45
End: 2017-05-04 09:55:47
## View job configuration via job$configuration
[[30]]
==Google BigQuery Job==
JobID: job_PG_CNyG8T8XlG9HkV5u5wjSZLDc
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-04 09:52:34
Start: 2017-05-04 09:54:08
End: 2017-05-04 09:54:09
## View job configuration via job$configuration
[[31]]
==Google BigQuery Job==
JobID: job_nc1z5YwCD93n8EvCl6kq4GxaVFA
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-04 09:49:31
Start: 2017-05-04 09:51:13
End: 2017-05-04 09:51:26
## View job configuration via job$configuration
[[32]]
==Google BigQuery Job==
JobID: job_bxnpgO1BatPUixifkg6smFDQDo0
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-04 09:47:44
Start: 2017-05-04 09:49:15
End: 2017-05-04 09:49:22
## View job configuration via job$configuration
[[33]]
==Google BigQuery Job==
JobID: job_zxh1EQT9AgPMWF7hGj25ds5L0_A
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-04 09:45:56
Start: 2017-05-04 09:47:42
End: 2017-05-04 09:47:43
## View job configuration via job$configuration
[[34]]
==Google BigQuery Job==
JobID: job_SN43YKEyWZTmds-vt7qVU3DJJ7c
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-03 14:56:24
Start: 2017-05-03 14:58:10
End: 2017-05-03 14:58:12
## View job configuration via job$configuration
[[35]]
==Google BigQuery Job==
JobID: job_oALpZjmV_YOmChBK1CSSgiqtl2o
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-03 12:54:39
Start: 2017-05-03 12:56:26
End: 2017-05-03 12:56:32
## View job configuration via job$configuration
[[36]]
==Google BigQuery Job==
JobID: job_knpBmOZkcpbFwowMub_h2Vvalk4
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-03 12:52:42
Start: 2017-05-03 12:54:37
End: 2017-05-03 12:54:38
## View job configuration via job$configuration
[[37]]
==Google BigQuery Job==
JobID: job_Jc0bjXwc80ylS7a6jket0NR2WNA
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-03 12:49:40
Start: 2017-05-03 12:51:13
End: 2017-05-03 12:51:27
## View job configuration via job$configuration
[[38]]
==Google BigQuery Job==
JobID: job_o9z5T5jEc38XmFN3E02-trgf9YY
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-03 12:47:44
Start: 2017-05-03 12:49:19
End: 2017-05-03 12:49:28
## View job configuration via job$configuration
[[39]]
==Google BigQuery Job==
JobID: job__Vb8ciyl6MF89Mv1ALp3xg0_uoc
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-03 12:45:56
Start: 2017-05-03 12:47:42
End: 2017-05-03 12:47:43
## View job configuration via job$configuration
[[40]]
==Google BigQuery Job==
JobID: job_P5bejL2-WE8_fgr2Vi_jY3t0rs0
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-03 11:53:08
Start: 2017-05-03 11:54:46
End: 2017-05-03 11:55:44
## View job configuration via job$configuration
[[41]]
==Google BigQuery Job==
JobID: job_zCAfjX-Qc_nY3FntoTeEh59dhjI
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-03 10:23:55
Start: 2017-05-03 10:25:40
End: 2017-05-03 10:26:02
## View job configuration via job$configuration
[[42]]
==Google BigQuery Job==
JobID: job_oBKI7buccNPRXNZUBgJsHK4_Qhs
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-03 09:59:32
Start: 2017-05-03 10:01:19
End: 2017-05-03 10:01:21
## View job configuration via job$configuration
[[43]]
==Google BigQuery Job==
JobID: job_-i3V_5IoEsgIW_AN9X-IwPblncQ
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-03 09:57:41
Start: 2017-05-03 09:59:22
End: 2017-05-03 09:59:25
## View job configuration via job$configuration
[[44]]
==Google BigQuery Job==
JobID: job_rw8OiANSAABZyfQvtgaExSj5k5c
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-03 09:55:01
Start: 2017-05-03 09:56:52
End: 2017-05-03 09:56:54
## View job configuration via job$configuration
[[45]]
==Google BigQuery Job==
JobID: job_dHM7YXgK0VkliPl_mh-XFPbPOe4
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-03 09:53:23
Start: 2017-05-03 09:54:58
End: 2017-05-03 09:55:00
## View job configuration via job$configuration
[[46]]
==Google BigQuery Job==
JobID: job_5m5Ye0h0wokqpun8RoM_Lth6kUY
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-03 09:50:25
Start: 2017-05-03 09:52:03
End: 2017-05-03 09:52:27
## View job configuration via job$configuration
[[47]]
==Google BigQuery Job==
JobID: job_rtef_n7s9C6zi7j-xzSeHgyPP6Q
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-03 09:50:04
Start: 2017-05-03 09:51:59
End: 2017-05-03 09:52:16
## View job configuration via job$configuration
[[48]]
==Google BigQuery Job==
JobID: job_6-eCpZ36-3cRsKPE34WPc4xvHMo
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-03 09:47:40
Start: 2017-05-03 09:49:47
End: 2017-05-03 09:49:55
## View job configuration via job$configuration
[[49]]
==Google BigQuery Job==
JobID: job_31mL200hl96ACdPqTT2xXIcojis
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-03 09:45:48
Start: 2017-05-03 09:47:38
End: 2017-05-03 09:47:39
## View job configuration via job$configuration
[[50]]
==Google BigQuery Job==
JobID: job_Wn00z6VuSnTUGE4Wx1miIOTc7b0
ProjectID: yyyyyyyyyyy
Status: DONE
User: xxxxxxxxxx@gmail.com
Created: 2017-05-03 09:33:26
Start: 2017-05-03 09:35:10
End: 2017-05-03 09:36:11
## View job configuration via job$configuration
I don't find the job which is mentionned by R (trim-saga-436:dLoyDtk1QDveZxY) in the list of jobs.
An idea ?
Very strange! The log is missing the call that failed, could you show them for this line:
sessionInfo()
options(googleAuthR.verbose = 1)
## an async job returns a job object
job <- bqr_query_asynch(query = q, destinationTableId = "test_table")
Token exists.
Valid local token
Request: https://www.googleapis.com/bigquery/v2/projects/yyyyyyyyyyy/jobs/
Body JSON parsed to: {"jobReference":{"projectId":"yyyyyyyyyyy","jobId":"YcqSKpoCY0TG8kc"},"configuration":{"query":{"allowLargeResults":true,"defaultDataset":{"datasetId":"analyse_gch","projectId":"yyyyyyyyyyy"},"destinationTable":{"datasetId":"analyse_gch","projectId":"yyyyyyyyyyy","tableId":"test_table"},"query":"SELECT * FROM [yyyyyyyyyyy:analyse_gch.Identification_device_campaign]","useLegacySql":true,"writeDisposition":"WRITE_EMPTY"}}}
-> POST /bigquery/v2/projects/yyyyyyyyyyy/jobs/ HTTP/2
-> Host: www.googleapis.com
-> User-Agent: googleAuthR/0.5.1 (gzip)
-> Accept: application/json, text/xml, application/xml, */*
-> Content-Type: application/json
-> Accept-Encoding: gzip
-> Authorization: Bearer xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-> Content-Length: 432
->
>> {"jobReference":{"projectId":"yyyyyyyyyyy","jobId":"YcqSKpoCY0TG8kc"},"configuration":{"query":{"allowLargeResults":true,"defaultDataset":{"datasetId":"analyse_gch","projectId":"yyyyyyyyyyy"},"destinationTable":{"datasetId":"analyse_gch","projectId":"yyyyyyyyyyy","tableId":"test_table"},"query":"SELECT * FROM [yyyyyyyyyyy:analyse_gch.Identification_device_campaign]","useLegacySql":true,"writeDisposition":"WRITE_EMPTY"}}}
<- HTTP/2 409
<- vary: Origin
<- vary: X-Origin
<- content-type: application/json; charset=UTF-8
<- content-encoding: gzip
<- date: Fri, 05 May 2017 12:44:39 GMT
<- expires: Fri, 05 May 2017 12:44:39 GMT
<- cache-control: private, max-age=0
<- x-content-type-options: nosniff
<- x-frame-options: SAMEORIGIN
<- x-xss-protection: 1; mode=block
<- content-length: 163
<- server: GSE
<- alt-svc: quic=":443"; ma=2592000; v="37,36,35"
<-
Request Status Code: 409
JSON fetch error: Already Exists: Job yyyyyyyyyyy:YcqSKpoCY0TG8kc
No retry attempted: Already Exists: Job yyyyyyyyyyy:YcqSKpoCY0TG8kc
Error in checkGoogleAPIError(req) :
JSON fetch error: Already Exists: Job yyyyyyyyyyy:YcqSKpoCY0TG8kc
In addition: Warning message:
In if (!getOption("googleAuthR.scopes.selected") %in% cloud_scopes) { :
Ok thanks, I'm going to have to ask for help on this one!
The job id that is getting generated for the job already exists. It looks like bq_query_asynch calls a utility function idempotency() that generates a random job id. This uses sample() to get a random string for the job. However, sample uses the r global random seed, so if someone is trying to get repeatable randomness by calling set.seed(), then it will generate identical job ids.
The fix for this would be to not pass in your own job id, or to use a source of randomness that wasn't tied to the random seed.
With your fix, the script which you sent me runs correctly.
Thanks