As with any other MySQL database, you can connect and query BIAD through R. This is particularly useful for building a clean pipeline for your subsequent analysis, as you can write a single R script that pulls the latest data, and processes it within R. This ensures your analysis can easily be redone using the latest state of BIAD as more data is constantly added.
Indeed, this BIADwiki is supported by R code in the public github repository BIADwiki which contains various useful example R scripts.
To easily connect R to BIAD you will first need to add your credentials to your computer's environmental variables. The easiest way is to use a simple text file that is usually hidden (it has no actual file name) and only has the file extension .Renviron
The .Renviron must contain the following credentials which can be obtained from the database administrator. Note, many of the values are within quote marks. You might already have a .Renviron file, in which case just add the following variables to whatever is already in that file. Order is irrelevant.
BIAD_DB_USER='xxxxxx'
BIAD_DB_PASS='xxxxxxxxxx'
BIAD_DB_HOST='biad.xxxxx'
BIAD_DB_PORT=1234Now you have created your .Renviron file with your private credentials, there are various places you could put it, to ensure R can read its contents.
You could put it in the same folder as your R scripts, but this creates repetition if you have different folders for different R scripts. We suggest storing the .Renviron in your R_USER folder. You can check which folder this is by running path.expand('~/') in an R console. Alternatively you can find it by running Sys.getenv('R_USER') in an R console.
These credentials will now become accessible to R from any directory.
Whenever you run an R script that queries the database, you must first connect to the server using the function init.conn() in the BIADconnect package. Therefore this package should be installed (once). It isn't available on the CRAN yet, so you need devtools to install it from the github repository. Therefore the following might need to be done once from within R:
install.packages('devtools')
devtools::install_github('BIADcore/BIADconnect')After that, any R script querying the database will use functions in the package and the credentials you stored in your .Renviron. Don't forget to close the connection when finished. So your script will be something like the following:
require(BIADconnect)
conn <- init.conn()
# do something trivial
query <- query.database("SELECT * FROM `Sites`")
plot(table(query$Country),las=2)
disconnect()