bigquery using DBI

[javier-gracia-tabuenca-tuni]

continuing from

#288

[javier-gracia-tabuenca-tuni]

would it be possible to be able to use BigQuery in addition to the default JDBC driver
the DBI driver ?

At the moment this is possible if 2 additions are made

• change to insertTable.default
• ListTables with conn is "DatabaseConnectorDbiConnection"

[schuemie]

I see you closed this, but to answer your question: We could switch from the JDBC driver to the DBI driver, I don't have strong preferences one way or the other. It is against DatabaseConnector principles to support both. This is because that would require all downstream analytics packages to be tested with both drivers.

[javier-gracia-tabuenca-tuni]

Thanks, @schuemie
I'm currently testing the changes in v7 with bq DBI
I will create a proposal when I have ready.
pre v7 we have been working with bq DBI.
as advantages:

• the insert table is faster, but that could also be added as bulk load with JDBC
• not need to keep track of the JDBC drivers versions. We had several problems with this in our organization.

I can re open this is come back with a PR when ready

[vojtechhuser]

I am interested in bigquery as well.
Any method. either JDBC or DBI.

I plan to contribute writing instructions how to connect to bigquery.
The context is single sign on to GCP using enterprise login (single sign on) (I don't know exactly, to be honest).

So far I have

1. install driver
2. obtain validate that in console.cloud.google.com
3. use https://github.com/jdposada/BQJdbcConnectionStringR to build up connection string. (maybe get connection going using hints from https://cloud.google.com/sdk/gcloud/reference/auth/application-default/login

[javier-gracia-tabuenca-tuni]

Sorry for the delay. I've made the changes in a local branch and use them in production for several weeks now.

I propose:

• use DBI instead of JDBC for BigQuery
• this need to be able to work when project and billing project are different, and when tables may be in different projects
  (in our TRE we have different institutions. The read only CDM is common to all and is in a project, then each institution has a separate billing project were there is a readwrite schema )

Advantages of this is that

• Not need to manage JDBC drivers (we had multiple problems with versioning)
• Not need extra code to produce the connection string
• Faster InsertTable (with out need of bulk upload extra code)

Changes I have done:
see dummy PR: https://github.com/javier-gracia-tabuenca-tuni/DatabaseConnector/pull/2/files
I use the package https://github.com/r-dbi/bigrquery

• ListTables.R: if dbms is 'bigquery' uses bigrquery:: function to list tables
• InsertTable.R: if dbms is 'bigquery' and if dropTableIfExists deletes table before inserting, bigrquery:: is not doing that correctly
• DBI.R: for a DatabaseConnectorDbiConnection if dbms is 'bigquery' uses delayIfNecessaryForDdl and delayIfNecessaryForInsert, just like it does for JDBC
• Sql.R:
  • On the above delay function I had to fix the regex so that It works also when using .
  • If there is an error and dbms is 'bigquery' take the error message from err$body, that contains the real sql error

To do
At the moment I haven't defaulted the DatabaseConnector to use DBI. I use DBI with code:

# Set credentials 
bigrquery::bq_auth()

connectionDetails <- DatabaseConnector::createDbiConnectionDetails(
  dbms = "bigquery",
  drv = bigrquery::bigquery(),
  project = "atlas-development-270609",
  billing = "atlas-development-270609",
  bigint = "integer64"
)
connection <- DatabaseConnector::connect(connectionDetails)

Im not sure what would be the best way to modify the DatabaseConnector:::connectBigQuery function
Particularly how to pass the 'billing' parameter
And you prefer to have 'bigrquery::bq_auth' with in the DatabaseConnector:::connectBigQuery. bigrquery::bq_auth() uese different parameters depending on the environment.

If you agree to move forward and set IBD as default, I'm happy to work on these details and make a PR with the full feature.

[javier-gracia-tabuenca-tuni]

Hello, I'm back from holidays
Would you like me to work on this ??
tagging @schuemie

[konstjar]

@schuemie @javier-gracia-tabuenca-tuni

Personally, I believe that DBI is better choice for DatabaseConnector, but let's be pragmatic here and see pros and cons for switching between these 2 options:

DBI:

Pros:

• No Java dependency
• Actively maintained
• Addresses some issues that @javier-gracia-tabuenca-tuni listed

Cons:

• additional dependency for DatabaseConnector from third party package
• breaking changes in connection setup for existing community users
• need to support both DBI (DatabaseConnector) and JDBC (WebAPI, Rabbit) across OHDSI tools

JDBC

Pros:

• Official Google supported driver
• Standardised connectivity in DatabaseConnector with other dialects
• Already adopted by community users

Cons:

• Versions management hassle
• No native dplyr support

Feel free to correct or add more points.
My biggest concerns are about existing users with their codebase and need to support both solution across community tools.

[ablack3]

My biggest concerns are about existing users with their codebase and need to support both solution across community tools.

Indeed all the Atlas functionality would still need to be supported using the BigQuery JDBC driver.

[schuemie]

I am leaning towards DBI, just because that would make DatabaseConnector lighter. We currently have code dedicated to handling BigQuey's idiosyncrasies, like here that would be good to get rid of to improve maintainability (and fits with the overall goal of making DatabaseConnector lighter, and the idea of gradually swapping out JDBC drivers for DBI ones as @ablack3 and me have discussed in the past).

I do agree that WebAPI will need to continue to use JDBC. The most important overlap in functionality between WebAPI and HADES is cohort generation. But I think the overall trend is to offload more and more of the analytics from WebAPI to HADES, so to me that is a less important consideration.

@konstjar : I'm curious. Why did you say "Personally, I believe that DBI is better choice"?

[konstjar]

@schuemie Thinking as R developer: bigrquery is a lightweight, no Java dependency, better error messages, tidyverse-friendly. It's my private opinion :-)