Restarting R session... > # --- > # > # KeyringSetup.R > # > # --- > > # Install keyring - one time operation --------- > # install.packages("keyring") > > # --- R Version --------------------- > R.Version() $platform [1] "x86_64-w64-mingw32" $arch [1] "x86_64" $os [1] "mingw32" $crt [1] "ucrt" $system [1] "x86_64, mingw32" $status [1] "" $major [1] "4" $minor [1] "3.1" $year [1] "2023" $month [1] "06" $day [1] "16" $`svn rev` [1] "84548" $language [1] "R" $version.string [1] "R version 4.3.1 (2023-06-16 ucrt)" $nickname [1] "Beagle Scouts" > # --- Java Version ------------------ > system("java -version") java version "11.0.11" 2021-04-20 LTS Java(TM) SE Runtime Environment 18.9 (build 11.0.11+9-LTS-194) Java HotSpot(TM) 64-Bit Server VM 18.9 (build 11.0.11+9-LTS-194, mixed mode) [1] 0 > # ----------------------------------- > > # > # functions to get databricks token (user will be prompted for keyring password) > # > > getToken <- function () { + return ( + keyring::backend_file$new()$get( + service = "databricks", + user = "token", + keyring = "databricks_keyring" + ) + ) + } > > getUrl <- function () { + url <- "jdbc:databricks://nachc-databricks.cloud.databricks.com:443/default;transportMode=http;ssl=1;httpPath=sql/protocolv1/o/3956472157536757/0123-223459-leafy532;AuthMech=3;UseNativeQuery=1;UID=token;PWD=" + return ( + paste(url, getToken(), sep = "") + ) + } > > connectionDetails <- DatabaseConnector::createConnectionDetails ( + dbms = "spark", + connectionString = getUrl(), + pathToDriver="D:\\_YES_2023-05-28\\workspace\\SosExamples\\_COVID\\02-data-diagnostics\\drivers\\databricks\\" + ) > > # --- start test of connectionDetails ----------------------------------------------------------- > testConnection <- DatabaseConnector::connect(connectionDetails) Connecting using Spark JDBC driver > testConnection nachc-databricks.cloud.databricks.com> DatabaseConnector::querySql(testConnection, "show tables in demo_cdm") DATABASE TABLENAME ISTEMPORARY 1 demo_cdm attribute_definition 0 2 demo_cdm care_site 0 3 demo_cdm cdm_source 0 4 demo_cdm cohort 0 5 demo_cdm cohort_censor_stats 0 6 demo_cdm cohort_definition 0 7 demo_cdm cohort_inclusion 0 8 demo_cdm cohort_inclusion_result 0 9 demo_cdm cohort_inclusion_stats 0 10 demo_cdm cohort_summary_stats 0 11 demo_cdm concept 0 12 demo_cdm concept_ancestor 0 13 demo_cdm concept_class 0 14 demo_cdm concept_relationship 0 15 demo_cdm concept_synonym 0 16 demo_cdm condition_era 0 17 demo_cdm condition_occurrence 0 18 demo_cdm cost 0 19 demo_cdm death 0 20 demo_cdm device_exposure 0 21 demo_cdm domain 0 22 demo_cdm dose_era 0 23 demo_cdm drug_era 0 24 demo_cdm drug_exposure 0 25 demo_cdm drug_strength 0 26 demo_cdm fact_relationship 0 27 demo_cdm location 0 28 demo_cdm measurement 0 29 demo_cdm metadata 0 30 demo_cdm note 0 31 demo_cdm note_nlp 0 32 demo_cdm observation 0 33 demo_cdm observation_period 0 34 demo_cdm payer_plan_period 0 35 demo_cdm person 0 36 demo_cdm procedure_occurrence 0 37 demo_cdm provider 0 38 demo_cdm relationship 0 39 demo_cdm source_to_concept_map 0 40 demo_cdm specimen 0 41 demo_cdm visit_detail 0 42 demo_cdm visit_occurrence 0 43 demo_cdm vocabulary 0 > DatabaseConnector::disconnect(testConnection) > # --- end test of connectionDetails ------------------------------------------------------------- > > if (Sys.getenv("STRATEGUS_KEYRING_PASSWORD") == "") { + # set keyring password by adding STRATEGUS_KEYRING_PASSWORD='sos' to renviron + usethis::edit_r_environ() + # then add STRATEGUS_KEYRING_PASSWORD='sos', save and close + # Restart your R Session to confirm it worked + stop("Please add STRATEGUS_KEYRING_PASSWORD='sos' to your .Renviron file + via usethis::edit_r_environ() as instructed, save and then restart R session") + } > > # Provide your environment specific values ------ > dbms <- "redshift" > connectionString <- "jdbc:databricks://nachc-databricks.cloud.databricks.com:443/default;transportMode=http;ssl=1;httpPath=sql/protocolv1/o/3956472157536757/0123-223459-leafy532;AuthMech=3;UseNativeQuery=1" > username <- "token" > password = getToken() > > > > # Run the rest to setup keyring ---------- > ################################## > # DO NOT MODIFY BELOW THIS POINT > ################################## > keyringName <- "sos-challenge" > keyringPassword <- "sos" # This password is simply to avoid a prompt when creating the keyring > > # Create the keyring if it does not exist. > # If it exists, clear it out so we can re-load the keys > allKeyrings <- keyring::keyring_list() > if (keyringName %in% allKeyrings$keyring) { + if (keyring::keyring_is_locked(keyring = keyringName)) { + keyring::keyring_unlock(keyring = keyringName, password = keyringPassword) + } + # Delete all keys from the keyring so we can delete it + message(paste0("Delete existing keyring: ", keyringName)) + keys <- keyring::key_list(keyring = keyringName) + if (nrow(keys) > 0) { + for (i in 1:nrow(keys)) { + keyring::key_delete(keys$service[i], keyring = keyringName) + } + } + keyring::keyring_delete(keyring = keyringName) + } Delete existing keyring: sos-challenge > keyring::keyring_create(keyring = keyringName, password = keyringPassword) > > # Store the the user-specific configuration ----- > keyring::key_set_with_value("dbms", password = dbms, keyring = keyringName) > keyring::key_set_with_value("connectionString", password = connectionString, keyring = keyringName) > keyring::key_set_with_value("username", password = username, keyring = keyringName) > keyring::key_set_with_value("password", password = password, keyring = keyringName) > > # Print the values to confirm the configuration > message("Keyring values set as:") Keyring values set as: > keys <- c("dbms", "connectionString", "username", "password") > for (i in seq_along(keys)) { + if(keys[i] != "password") { + message(paste0(" - ", keys[i], ": ", keyring::key_get(keys[i], keyring = keyringName))) + } + } - dbms: redshift - connectionString: jdbc:databricks://nachc-databricks.cloud.databricks.com:443/default;transportMode=http;ssl=1;httpPath=sql/protocolv1/o/3956472157536757/0123-223459-leafy532;AuthMech=3;UseNativeQuery=1 - username: token > # --- > # > # StrategusCodeToRun.R > # > # --- > > # install the network package > # install.packages('remotes') > # remotes::install_github("OHDSI/Strategus", ref="results-upload") > library(Strategus) Loading required package: CohortGenerator Loading required package: DatabaseConnector Loading required package: R6 > > ##=========== START OF INPUTS ========== > connectionDetailsReference <- "Jmdc" > workDatabaseSchema <- 'scratch_fluoroquinolone' > cdmDatabaseSchema <- 'demo_cdm' > outputLocation <- 'D:/_YES_2023-05-28/workspace/_OHDSI_STUDIES/_EXTERNAL/Fluoroquinalone/_GITHUB/output' > minCellCount <- 5 > cohortTableName <- "sos_fq_aa" > > # > # SEE KeyRingSetup.R FOR CREATION OF connectionDetails > # > > # the keyring entry should correspond to what you selected in KeyringSetup.R > # connectionDetails = DatabaseConnector::createConnectionDetails( > # dbms = keyring::key_get("dbms", keyring = "sos-challenge"), > # connectionString = keyring::key_get("connectionString", keyring = "sos-challenge"), > # user = keyring::key_get("username", keyring = "sos-challenge"), > # password = keyring::key_get("password", keyring = "sos-challenge") > # ) > > ##=========== END OF INPUTS ========== > ################################## > # DO NOT MODIFY BELOW THIS POINT > ################################## > analysisSpecifications <- ParallelLogger::loadSettingsFromJson( + fileName = "inst/analysisSpecification.json" + ) > > storeConnectionDetails( + connectionDetails = connectionDetails, + connectionDetailsReference = connectionDetailsReference, + keyringName = "sos-challenge" + ) > > executionSettings <- createCdmExecutionSettings( + connectionDetailsReference = connectionDetailsReference, + workDatabaseSchema = workDatabaseSchema, + cdmDatabaseSchema = cdmDatabaseSchema, + cohortTableNames = CohortGenerator::getCohortTableNames(cohortTable = cohortTableName), + workFolder = file.path(outputLocation, connectionDetailsReference, "strategusWork"), + resultsFolder = file.path(outputLocation, connectionDetailsReference, "strategusOutput"), + minCellCount = minCellCount + ) > > # Note: this environmental variable should be set once for each compute node > Sys.setenv("INSTANTIATED_MODULES_FOLDER" = file.path(outputLocation, "StrategusInstantiatedModules")) > > # --- start test of connectionDetails ----------------------------------------------------------- > testConnection <- DatabaseConnector::connect(connectionDetails) Connecting using Spark JDBC driver > testConnection nachc-databricks.cloud.databricks.com> DatabaseConnector::querySql(testConnection, "show tables in demo_cdm") DATABASE TABLENAME ISTEMPORARY 1 demo_cdm attribute_definition 0 2 demo_cdm care_site 0 3 demo_cdm cdm_source 0 4 demo_cdm cohort 0 5 demo_cdm cohort_censor_stats 0 6 demo_cdm cohort_definition 0 7 demo_cdm cohort_inclusion 0 8 demo_cdm cohort_inclusion_result 0 9 demo_cdm cohort_inclusion_stats 0 10 demo_cdm cohort_summary_stats 0 11 demo_cdm concept 0 12 demo_cdm concept_ancestor 0 13 demo_cdm concept_class 0 14 demo_cdm concept_relationship 0 15 demo_cdm concept_synonym 0 16 demo_cdm condition_era 0 17 demo_cdm condition_occurrence 0 18 demo_cdm cost 0 19 demo_cdm death 0 20 demo_cdm device_exposure 0 21 demo_cdm domain 0 22 demo_cdm dose_era 0 23 demo_cdm drug_era 0 24 demo_cdm drug_exposure 0 25 demo_cdm drug_strength 0 26 demo_cdm fact_relationship 0 27 demo_cdm location 0 28 demo_cdm measurement 0 29 demo_cdm metadata 0 30 demo_cdm note 0 31 demo_cdm note_nlp 0 32 demo_cdm observation 0 33 demo_cdm observation_period 0 34 demo_cdm payer_plan_period 0 35 demo_cdm person 0 36 demo_cdm procedure_occurrence 0 37 demo_cdm provider 0 38 demo_cdm relationship 0 39 demo_cdm source_to_concept_map 0 40 demo_cdm specimen 0 41 demo_cdm visit_detail 0 42 demo_cdm visit_occurrence 0 43 demo_cdm vocabulary 0 > DatabaseConnector::disconnect(testConnection) > # --- end test of connectionDetails ------------------------------------------------------------- > > execute( + analysisSpecifications = analysisSpecifications, + executionSettings = executionSettings, + executionScriptFolder = file.path(outputLocation, connectionDetailsReference, "strategusExecution"), + keyringName = "sos-challenge" + ) Connecting using Spark JDBC driver Error in rJava::.jcall(jdbcDriver, "Ljava/sql/Connection;", "connect", : java.sql.SQLException: [Databricks][DatabricksJDBCDriver](500593) Communication link failure. Failed to connect to server. Reason: HTTP Response code: 401, Error message: Unknown. >