OHDSI Home | Forums | Wiki | Github

RStudio Server and BigQuery Authentication DatabaseConnector

Developers
#1

Trying to use OAuth authentication from a RStudio Server web browser doesn’t seem to work. It throws an rJava error.

BigQueryError
BigQueryError882×79 5 KB

From the desktop app, it will launch a small java window with a URL we can copy/paste a link into a browser, get a one-time token, then paste into the java window.

We had a similar problem with the bigrquery R package but they allow us to override the OAuth app and use the commandline in RStudio instead (https://bigrquery.r-dbi.org/) (Generating OAuth tokens for a server using httr – RStudio Support)

That doesn’t seem to work for the DatabaseConnector. Has anyone else found a workaround for RStudio Server (or maybe cloud) with one-off authentications?

#2

Hi Will

DatabaseConnector uses JDBC driver for BigQuery. That’s why you have authentication options provided by driver. Please check that you specified OAuthType=1; per documentation:
https://www.simba.com/products/BigQuery/doc/JDBC_InstallGuide/content/jdbc/bq/authenticating/useraccount.htm

Here is example of authentication using pre-generated token:

library(DatabaseConnector)
bqDriverPath <- "./bigquery-driver-directory"

connectionString <- "jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=<project_id>;DefaultDataset=<my_bigquery_database>;OAuthType=2;OAuthClientId=<account_service_id>.apps.googleusercontent.com;OAuthRefreshToken=<refresh_token>OAuthClientSecret=<secret_key>;Timeout=1000;AllowLargeResults=0;EnableHighThroughputAPI=1;UseQueryCache=1;"

connectionDetails <- createConnectionDetails(dbms="bigquery",
connectionString=connectionString,
user="",
password='',
pathToDriver = bqDriverPath)


# Create a connection
connection <- connect(connectionDetails)

# Test with a sql query to the concept table
sql <- "select * from <my_bigquery_database>.my_table limit 10;"
queryResult <- querySql(connection, sql)
print(queryResult)
#3

Hi Konstantin,

Yeah I have OAuth set to 1. I can use the same set of commands with RStudio (non-server edition) and it runs fine and I get the little Java app to get the URL and paste the token. When I try the same thing from RStudio Server, I get the error which I assume has to do with trying to launch that Java window. I don’t have a service account to use with this particular datamart.

#4

Hi Will,

I will reach you by email. I think it’s better to setup some session to see the problem.

t