You can connect and directly query your data via SQL using R. Here we are using R within the Rstudio IDE, but the same steps should work within a standard R session or Revo R.
In order to connect to Direct Access using R, you will have to use an SSL connection. There are a number of different ways to do this depending on platform and packages used. For Windows and macOS an ODBC connection can be used whilst Linux requires no additional configuration outside of RStudio. The steps for connecting on each platform are detailed below:
Windows
1. Configure the PostgreSQL ODBC driver using the ODBC Data Source Administrator (ODBC Data Sources on Windows 8 onwards)
2. In RStudio, install the R ODBC library and define a data source using the following commands:
|
1 2 3 4 5 6 |
install.packages("odbc") library("odbc") con <- dbConnect(odbc::odbc(), .connection_string = "Driver={<driver>};Uid=<username>;Pwd=<password>;Server=data.deltadna.net;Port=5432;Database=<database>;SslMode=require;") |
<driver> should be replaced with the name of the driver you configured (PostgreSQL ANSI(x64) for example), <username> and <password> are your deltaDNA username and password and <database> is your account name and game name, separated by a full stop (for example, demo-account.demo-game) – this can be found on your game details page.
You can also connect using a pre-configured data source (DSN):
|
1 |
con <- dbConnect(odbc::odbc(), "DA SSL") |
3. From here you can query your data:
|
1 2 3 |
dtab = dbGetQuery(con, "select eventDate, count(*) from events_live where eventDate < current_date -7 group by eventDate") summary(dtab) |
It’s worth noting that the R PostgreSQL package can be used so long as SSL support is complied in on the client.
macOS
The steps for connecting on macOS are the same as the above Windows steps however ODBC connections cannot be managed without a third party application.
We would recommend using ODBC Manager although any manager application can be used. In ODBC manager, the connection is configured as below and the driver file path (e.g /usr/local/lib/psqlodbcw.so) is used in the connection string to replace <driver>:
If not already installed on your machine, PostgreSQL installers for macOS can be found here. PostgreSQL should be installed on most systems, although this will be dependant on OS version – if this is the case then the path ‘/usr/local/lib/psqlodbcw.so’ can be used for the <driver>.
Linux
Linux users can make use of the RPostreSQL as SSL support should be compiled in on the client:
|
1 2 |
install.packages('RPostgreSQL') install.packages('PostgreSQL') |
Once the packages are installed we can activate it and establish a connection to the deltaDNA database:
|
1 2 3 4 5 |
library('RPostgreSQL') pg = dbDriver('PostgreSQL') con = dbConnect(pg, user="<username>", password="<password>", host="data.deltadna.net", port=5432, dbname=<database> sslmode=require) |
<username> and <password> are your deltaDNA username and password and <database> is your account name and game name, separated by a full stop (for example, demo-account.demo-game) – this can be found on your game details page.
You can then write queries as R strings and pass them to the database, they will be returned as R data frames
|
1 2 |
dtab = dbGetQuery(con, "select eventDate, count(*) from events_live group by 1") summary(dtab) |
