EXEC_JOB_EXECUTION_ERR when querying the Bigquery database

[IoannaNika]

The problem occurs when:

library(DatabaseConnector)

# downloadJdbcDrivers("bigquery")

connectionDetails <- createConnectionDetails(dbms="bigquery",
                                             connectionString=Sys.getenv("BIGQUERY_CONNECTION_STRING"),
                                             user="",
                                             password='')


con <- connect(connectionDetails)
#> Connecting using BigQuery driver

getTableNames(con, "synpuf_110k")
#>  [1] "attribute_definition"  "care_site"             "cdm_source"           
#>  [4] "cohort"                "cohort_attribute"      "cohort_definition"    
#>  [7] "concept"               "concept_ancestor"      "concept_class"        
#> [10] "concept_relationship"  "concept_synonym"       "condition_era"        
#> [13] "condition_occurrence"  "cost"                  "death"                
#> [16] "device_exposure"       "domain"                "dose_era"             
#> [19] "drug_era"              "drug_exposure"         "drug_strength"        
#> [22] "fact_relationship"     "location"              "measurement"          
#> [25] "metadata"              "note"                  "note_nlp"             
#> [28] "observation"           "observation_period"    "ofjfofzacohort_person"
#> [31] "payer_plan_period"     "person"                "procedure_occurrence" 
#> [34] "provider"              "relationship"          "source_to_concept_map"
#> [37] "specimen"              "t0iyq72bcohort_person" "test"                 
#> [40] "visit_detail"          "visit_occurrence"      "vocabulary"           
#> [43] "person_view"

querySql(connection = con, "SELECT COUNT(*) FROM synpuf_110k.cohort")
#> Error in `.createErrorReport()`:
#> ! Error executing SQL:
#> java.sql.SQLException: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: EXEC_JOB_EXECUTION_ERR
#> An error report has been created at  /private/var/folders/ny/8mfpdl611hz7by4z_3kfl3t00000gn/T/Rtmpurqf39/reprex-ba487dca9968-brave-mara/errorReportSql.txt

disconnect(con)

^{Created on 2024-11-25 with reprex v2.1.1}

Session info

sessioninfo::session_info()
#> ─ Session info ───────────────────────────────────────────────────────────────
#>  setting  value
#>  version  R version 4.4.1 (2024-06-14)
#>  os       macOS 15.0
#>  system   aarch64, darwin20
#>  ui       X11
#>  language (EN)
#>  collate  en_US.UTF-8
#>  ctype    en_US.UTF-8
#>  tz       Europe/Amsterdam
#>  date     2024-11-25
#>  pandoc   3.2 @ /Applications/RStudio.app/Contents/Resources/app/quarto/bin/tools/aarch64/ (via rmarkdown)
#> 
#> ─ Packages ───────────────────────────────────────────────────────────────────
#>  package           * version date (UTC) lib source
#>  backports           1.5.0   2024-05-23 [1] CRAN (R 4.4.0)
#>  bit                 4.5.0   2024-09-20 [1] CRAN (R 4.4.1)
#>  bit64               4.5.2   2024-09-22 [1] CRAN (R 4.4.1)
#>  checkmate           2.3.2   2024-07-29 [1] CRAN (R 4.4.0)
#>  cli                 3.6.3   2024-06-21 [1] CRAN (R 4.4.0)
#>  DatabaseConnector * 6.3.2   2023-12-11 [1] CRAN (R 4.4.0)
#>  DBI                 1.2.3   2024-06-02 [1] CRAN (R 4.4.0)
#>  digest              0.6.37  2024-08-19 [1] CRAN (R 4.4.1)
#>  evaluate            1.0.0   2024-09-17 [1] CRAN (R 4.4.1)
#>  fansi               1.0.6   2023-12-08 [1] CRAN (R 4.4.0)
#>  fastmap             1.2.0   2024-05-15 [1] CRAN (R 4.4.0)
#>  fs                  1.6.4   2024-04-25 [1] CRAN (R 4.4.0)
#>  glue                1.8.0   2024-09-30 [1] CRAN (R 4.4.1)
#>  htmltools           0.5.8.1 2024-04-04 [1] CRAN (R 4.4.0)
#>  knitr               1.48    2024-07-07 [1] CRAN (R 4.4.0)
#>  lifecycle           1.0.4   2023-11-07 [1] CRAN (R 4.4.0)
#>  pillar              1.9.0   2023-03-22 [1] CRAN (R 4.4.0)
#>  reprex              2.1.1   2024-07-06 [1] CRAN (R 4.4.0)
#>  rJava               1.0-11  2024-01-26 [1] CRAN (R 4.4.0)
#>  rlang               1.1.4   2024-06-04 [1] CRAN (R 4.4.0)
#>  rmarkdown           2.28    2024-08-17 [1] CRAN (R 4.4.0)
#>  rstudioapi          0.16.0  2024-03-24 [1] CRAN (R 4.4.0)
#>  sessioninfo         1.2.2   2021-12-06 [1] CRAN (R 4.4.0)
#>  SqlRender           1.19.0  2024-10-09 [1] CRAN (R 4.4.1)
#>  utf8                1.2.4   2023-10-22 [1] CRAN (R 4.4.0)
#>  vctrs               0.6.5   2023-12-01 [1] CRAN (R 4.4.0)
#>  withr               3.0.2   2024-10-28 [1] CRAN (R 4.4.1)
#>  xfun                0.48    2024-10-03 [1] CRAN (R 4.4.1)
#>  yaml                2.3.10  2024-07-26 [1] CRAN (R 4.4.0)
#> 
#>  [1] /Library/Frameworks/R.framework/Versions/4.4-arm64/Resources/library
#> 
#> ──────────────────────────────────────────────────────────────────────────────

[konstjar]

Could you please add BigQuery JDBC Driver version?

[IoannaNika]

I tried with version 1.3.2 and 1.6.1.

[konstjar]

Can you please check also errorReportSql.txt file content. It looks like permissions problem on GCP side. Your account needs BigQuery Job User role to be able to create jobs.

[IoannaNika]

The content of the errorReportSql.txt:

DBMS:
bigquery

Error:
java.sql.SQLException: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: EXEC_JOB_EXECUTION_ERR

SQL:
SELECT COUNT(*) FROM synpuf_110k.cohort

R version:
R version 4.4.1 (2024-06-14)

Platform:
aarch64-apple-darwin20

Attached base packages:
- stats
- graphics
- grDevices
- utils
- datasets
- methods
- base

Other attached packages:
- DatabaseConnector (6.3.2)

[konstjar]

Thank you. I do not know the connection string that is used in your case, but please double check that projectId is defined. Also I would recommend to enable logs for JDBC driver and see exactly where it fails.

[konstjar]

I reproduced the same in my environment I got this error:

Error:
java.sql.SQLException: [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: Transaction control statements are supported only in scripts or sessions

Adding additional parameter ;EnableSession=1 in the Connection String fixed the issue in my case.

[IoannaNika]

Thank you that worked! However, there is another issue:

visit_detail table not included in cdm because:
Error in `newOmopTable()`: ! visit_detail_type_concept_id is not present in table visit_detail 

From my understanding this is because there is a typo in the visit_detail_type_concept_id column in table synpuf_110k. visit_detail

It is written as visit_deatil_type_concept_id