Database mode in Diagnostics Explorer

Gowtham Rao

2024-09-30

Database mode

We recommend the use of a database system, when file size of the cohort diagnostics output becomes large e.g. > 1GB, as using the premerged.RData may leading sluggish performance. Currently only postgres server is supported.

We use DatabaseConnector and DbPool to manage connections between the shiny application and postgres. The recommended method to launch a Shiny application is to provide the connection details to the ‘launchDiagnosticsExplorer()’ shiny application. Please review the documentation of ‘DatabaseConnector::createConnectionDetails()’ function in the DatabaseConnector package on how to connect to the server.

We have an example script on how to upload data to remote postgres server here. The ‘createResultsDataModel()’ function will execute a series of SQL statements to create empty target schema to hold the results of the Cohort Diagnostics.

To upload the results, we recommend that you use the ‘uploadResults()’ function, and provide it the path to your postgres driver.

Once the data is uploaded into the postgres server - you can execute the shiny application using the ‘launchDiagnosticsExplorer()’ function. Please provide the connection details, along with the schema that you have uploaded your results (resultsDatabaseSchema).

For deploying the server, a yaml file called config.yml should be placed in the DiagnosticsExplorer path. See the README.md file in this directory for more details on configuration. Example yaml settings are included below:

# Alter these configuration settings for usage with remote databases
connectionDetails:
  dbms: "postgresql"
  server: "my-database-server.com/myDatabaseName"
  user: "postrgres"
  password: "secret_password"
  port: 5432

# Store connection details as a json string in keyring
# Store with keyring::key_set_with_value("KEYNAME", jsonlite::toJSON(myConnectionDetails))
connectionDetailsSecureKey: ~

# If you used a table prefix to store the results (e.g. cd_)
tablePrefix: ""

# Results schema being used
resultsDatabaseSchema: "cohort_diagnostics"

# Vocabulary schemas. Should include the reuslts schema and optional custom vocabularies
vocabularyDatabaseSchemas: ["cohort_diagnostics"]

# Custom table name for cohorts and cdm databases (probably not needed, ignores table prefix)
cohortTableName: "cohort"
databaseTableName: "database"

# If you wish to enable annotation - not currently reccomended in multi-user environments
enableAnnotation: FALSE
enableAuthorization: FALSE

Publishing

To publish with a config you can use:

launchDiagnosticsExplorer(
  shinyConfigPath = "path/to/config.yml",
  makePublishable = TRUE,
  publishDir = file.path(getwd(), "MyStudyDiagnosticsExplorer"),
  overwritePublishDir = TRUE
)

And press the publish button in the shiny app that loads. Note - that in this situation the use of secure keys with keyring is not possible.