This package provides functions to query a CPRD Aurum database on a local or remote MySQL server. It uses the dbplyr package (part of the tidyverse) to translate dplyr code into SQL. Analyses and outputs are run/stored on the remote server.
You can install the Exeter_Diabetes_aurum_package from
GitHub with:
# install.packages("devtools")
devtools::install_github("Exeter-Diabetes/CPRD-analysis-package")Data and lookup MySQL tables are named as per data-raw/data-tables.yaml and data-raw/lookup-tables.yaml.
As per vignettes/getting-started.Rmd, users must make a configuration text file containing username, password and server details to connect to the MySQL server. The example below will use CPRD Aurum data in a MySQL database called ‘cprd_data’ and store outputs in a database called ‘cprd_analysis_dev’.
default:
user: <mysql username>
password: <sql password>
server: "localhost"
port: 3306
sessionConfig:
SESSION default_storage_engine: "MyISAM"
test-remote:
dataDatabase: cprd_data
analysisDatabase: cprd_analysis_dev
server: 127.0.0.1
port: 3307
Use ‘test-remote’ to connect with MySQL on a remote server to which an SSH connection has been established.
Users can then connect by running e.g.
cprd = CPRDData$new(cprdEnv = "test-remote",cprdConf = "~/.aurum.yaml")where ~/.aurum.yaml is the configuration file detailed above.
Codelists (e.g. of medcodeids or prodcodeids) for use with CPRD Aurum can be added and deleted from MySQL as outlined in vignettes/loading-and-using-codesets.Rmd. Codelists developed by the Exeter Diabetes team can be found at CPRD-Codelists.
Analysis functions e.g. computing and caching results are stored in CPRDAnalysis.R and explained in vignettes/setting-up-an-analysis.Rmd. These can only be executed when connected to a MySQL server.
Note that this package contains different branches which were used to load the raw CPRD data into MySQL for various different downloads. For info about the different downloads and the names of the databases they're stored in (which need to be in the ~/.aurum.yaml), see here.