Several of my recent blog posts have been focussed on using {purrr} and {glue} to programatically query a database.

This is something I’d been wanting to achieve for some time, but my efforts got put on hold when COVID-19 came along.

The general approach is to use a config file to denote the relevant tables and columns, along with the server and database details.

These then get fed into flexible SQL queries, using purrr to loop over each table then merging the results.

Over the last few weeks at work I have been refining this and building a shiny dashboard to visualise the data.

Currently we have a mix of ggplot2, reactable & reactablefmtr, plus ggiraph for the visuals, with data.table doing a lot of the wrangling.

I run the data processing manually at present as a background job within RStudio, but it’s likely I will get this set up as a scheduled job soon.

I demonstrated the app today during a network meeting of SCODAS (Scottish Community of Data Analysts – there’s a bonus ‘S’ on the end so let’s pretend it stands for ‘shiny’).

During the meeting I was asked if the code was available, so I’ve made a generic version available on github, which other analysts can tweak as appropriate.

The main thing to amend is the config.yml file – everything else flows from that.

This can be adapted by writing more advanced SQL queries, and doing more than just simple counts.

For our needs, this app works well, it was always intended to be a quick hit to get a feel for the contents of the various views and tables within the warehouse.

As proof of its flexibility, the version of the app I showed today at just after 1pm, was completely different to how the app looked around midday.

We’ve had a new view added to the warehouse, and we made some changes to some of the existing column choices, so I quickly copied and amended the config file, pointed the processing script to it, and within a couple of minutes, our shiny app was updated.

I know this is going to save us a lot of time in the future, and could possibly be of benefit beyond our team.

If you’re in the NHS and you use this, please let me know about it – would be good to know if it helps others.