hi @LinaSulieman ,
It looks like you are using this package?
It is a small package I wrote to help with the connection string creation using application default credentials
The error you may have is that when specifying a parameter such as cdmSchema you need backticks. Below a code that I always use to test if my connection is correct before running any network study
bqDriverPath <- bq_driver_folder
jsonPath <-paste0(gcloud_credentials_path_docker, "/application_default_credentials.json")
database_description_file <- 'starr_omop_description.txt'
work_project_id <- "som-nero-phi-rit"
work_dataset_id <- "mskai_ple_sample"
temp_dataset_id <- "mskai_ple_sample_temp"
cdm_project_id <- "som-rit-phi-starr-prod"
cdm_dataset_id <- "starr_omop_cdm5_deid_1pcent_latest"
cdmDatabaseSchema <- paste0("`", cdm_project_id, ".", cdm_dataset_id, "`")
cohortDatabaseSchema <- paste0("`", work_project_id, ".", work_dataset_id, "`")
cohortTable <- "mskaicohort"
minCellCount <- 10
# Database description
databaseId <- "STARROMOP"
databaseName <- "STAnford medicine Research data Repository (STARR-OMOP)"
databaseDescription <- readr::read_file(database_description_file)
connectionString <- BQJdbcConnectionStringR::createBQConnectionString(
projectId=work_project_id,
defaultDataset = temp_dataset_id,
timeOut = 3600,
authType = 2,
jsonCredentialsPath = jsonPath)
# testing the BQ connection
connectionDetails <- DatabaseConnector::createConnectionDetails(dbms="bigquery",
connectionString=connectionString,
user="",
password='',
pathToDriver = bqDriverPath)
connection <- DatabaseConnector::connect(connectionDetails)
sql <- "
SELECT *
FROM
{cdmDatabaseSchema}.location
LIMIT 2;"
sql <- glue::glue(sql,
cdmDatabaseSchema = cdmDatabaseSchema)
sql <- toString(sql)
print(sql)
df <- DatabaseConnector::querySql(connection, sql)
print(df)
DatabaseConnector::disconnect(connection)
I have a Youtube video that walks over the use of this in the context of Docker, but you can omit all the docker stuff and jump to the part where I explain the config part
