R track: setup

What changes in the R track

The pipeline and the data are the same as in the SQL exercises. You run each step with a gaiaCore function from RStudio, which calls the same gaiaDB SQL functions underneath, and you keep the SQL only where it is the clearest way to look at the data (the answer key and the lineage trace).

SQL track R track
pgAdmin, or psql in the gaia-db container RStudio in the ohdsi/gaia-core container
SELECT * FROM backbone.ingest_datasource(...) ingestDatasource(connection, ...)
INSERT INTO working.location ... loadLocationsFromOmop(connection)
SELECT working.spatial_join_from_catalog(...) spatialJoin(connection, ...)
Hand-written QA queries checkExposure(connection)
INSERT INTO omopgis.external_exposure ... copyExposureToOmop(connection)

Start the R environment

  1. Deploy the Gaia stack (with the pinned images, see Get Started) and restore the synthetic dataset as in Exercise 2, step 1 of the SQL track (the restore is the same in both tracks).

  2. Pull and start the R image on the same Docker network as the database (stop the stack’s own gaia-core service first if it is running, because both use port 8787; on Apple Silicon keep --platform linux/amd64):

    docker pull --platform linux/amd64 ohdsi/gaia-core@sha256:40ebb1e5a4e57b455bf6444f8389bd71b2e8483491b00f0ba5193f9969636eb4
docker run -d --name gaia-core --platform linux/amd64 --network gaiadocker_default -p 8787:8787 -e USER=ohdsi -e PASSWORD=<choose a password> -e GAIA_POSTGRES_PASSWORD=<postgres password> ohdsi/gaia-core@sha256:40ebb1e5a4e57b455bf6444f8389bd71b2e8483491b00f0ba5193f9969636eb4

    (gaiadocker_default is the network docker compose creates for a checkout in a folder called gaiaDocker; list yours with docker network ls. <postgres password> is the content of the file gaiaDocker/secrets/gaia/POSTGRES_PASSWORD; paste it in directly. Keep the command on one line: it is the same in PowerShell, Terminal and bash.)

  3. Open http://localhost:8787 and sign in with ohdsi and the password you chose.

Connect

library(gaiaCore)
library(DatabaseConnector)

connection <- connectGaia(createGaiaConnectionDetails(server = "gaia-db/gaiacore"))

querySql(connection, "SELECT count(*) AS persons FROM omopgis.person")   # 10,000

createGaiaConnectionDetails() reads the password from GAIA_POSTGRES_PASSWORD and the JDBC driver folder from DATABASECONNECTOR_JAR_FOLDER, both set by the commands above. Run disconnectGaia(connection) when you are done. The connection is a normal DatabaseConnector connection, so querySql() and executeSql() work on it whenever you want to look at a table directly.


Next: R Exercise 2