This gist will teach you how to hook up your database to the United4Surveillance SignalDetectionTool Shiny app.
- R is installed on your system
- The SignalDetectionTool is installed as a R package
- You have access to a database
- You have working knowledge of R
- Load the SDT package:
library(SignalDetectionTool) - Define custom functions that retrieve data from your DB
- Monkey patch DB related package functions
Recently the SignalDetectionTool added the ability to start the app with a pre-defined configuration. There is currently full support for loading data from a file and in the future this will be extended to fully support data retrieval from relational databases. In the meantime you can make use of the tool's database mock functions and apply monkey patching to overwrite existing package functions. The functions of interest are contained in the package's file R/database.R:
get_database_connection()query_database()transform_required_format()load_data_db()
As stated above, these functions are merely mocks of real implementations and currently only load data from an example SQLite database file. To actually connect the Shiny app to your own database right now, you need to write custom functions and monkey patch the package's equivalents.
This gist's file db_functions.R shows an example of how to implement custom functions based on the assumption that the DB is on a MS SQL Server with this schema.
Here we use the data config YAML of the SignalDetectionTool and added new scalars server, database, driver and port to the db mapping. You would need to fill these with your actual values.
To retrieve the values from the config file we use the package's function get_data_config_value() and we supply a string with the parameter name, separating each nested level with a colon (i.e. "datasource:db:driver").
Alternatively, you could also get these from system environment variables like this:
get_env_config <- function() {
list(
host = Sys.getenv("DB_HOST", unset = "localhost"),
port = as.integer(Sys.getenv("DB_PORT", unset = "5432")),
driver = Sys.getenv("DB_DRIVER", unset = "postgresql"),
database = Sys.getenv("DB_NAME", unset = "test_db")
)
}
In the example we use the keyring package to get the username and password from the OS credentials manager. If your login details are not stored there yet, you first have to save them there using keyring::key_set(). There are of course other alternatives and you might want to read about best practices of managing credentials.
Depending on your database schema you need to adjust the SQL query (in query_database()). Certainly you will have different table structures, attribute names and you might have different data availabilities.
If you are not familiar with SQL you could also try dbplyr. This allows you to write standard dplyr style code and when you want to collect the data from the DB, dbplyr will write the necessary query and send it to the DB.
Depending on the format of the data you retrieve from your DB you will need to do some transformations. This can be done by adjusting transform_required_format() inside db_functions.R to your needs.
The SDT is rather strict at the moment. Your data needs to adhere to the required format.
If you want to perform the transformations on the SQL server side you can also create a that does nothing i.e. transform_required_format <- function(data){data)}.
After you have defined your custom functions you need to call the monkey_patch function from monkey_patching.R function like this:
monkey_patch(your_function, “SignalDetectionTool", "function_name_to_replace")
It doesn't matter how you name your custom functions as long as you substitute them for the tool's functions listed above.
Now start the Shiny app using
run_app(path_to_yaml='your/path/config.yml').
If you have any troubles you can read more about the data configuration file.
Somehow can't directly comment in the code will thus do it here. Some minor text based improvements first:
transform_required_format
load_data_db
I would suggest adding () behind the function name, i.e. get_database_connection().
schema link: does not work for me "page not found"
best practices of managing credentials) one closing bracket which is too much
required format) same here and a dot behind or the If afterwards with small i
create a that does nothing , I guess we are missing function here
for the very last two sentences a dot is missing at the end.
It already looks great thanks a lot! I will now try it out myself and then might add more content related comments.