Repository to demo how to create a Virtual Knowledge Graph in a Stardog triplestore using data from a PostgreSQL database.

For this demo we use the MIMIC-IV dataset, more details and request access at https://physionet.org/content/mimiciv/2.2/

1. Go to https://cloud.stardog.com

2. Connect with your Google account (or any other option)

3. Create a New Connection for the Stardog server deployed at IDS

   1. Provide the username and password you were given by the IDS Stardog admin (Vincent probably)
   2. And the IDS Stardog server endpoint URL: https://stardog.137.120.31.102.nip.io

4. You can now connect to IDS server, create database, create model, etc

In the future you will just need to reconnect to https://cloud.stardog.com with your Google account, and access IDS Stardog server from there (it will save your connections credentials)

Stardog proposes 3 main interfaces to manage your knowledge graphs:

• Studio to query and navigate your KG
• Designer to define models
• Explorer to do full text searches

To federate multiple SQL databases

Go to the Data tab in Stardog Studio, and click the + button to add a data source.

Add PostgreSQL database sources for cohort 1 and 2:

1. Data Source Type: PostgreSQL

2. JDBC Connection URL (use postgres-mimic-iv-2 for cohort 2):

   jdbc:postgresql:https://postgres-mimic-iv:5432/mimic_iv
   

3. JDBC username is postgres, and the password is the one you defined (or passwordtochange if you kept the default)

4. Driver Class: keep org.postgresql.Driver

Add MariaDB database source for cohort 2:

Alternatively you could also use MariaDB instead of PostgreSQL for cohort 2:

1. Data Source Type: MariaDB

2. JDBC Connection URL:

   jdbc:mariadb:https://mariadb-mimic-iv:3306/mimic_iv
   

3. JDBC username is root, and the password is the one you defined (or passwordtochange if you kept the default),

4. Driver Class: ⚠️ change to org.mariadb.jdbc.Driver

ℹ️ Build scripts are available to load MIMIC-IV in various DBMS: https://github.com/MIT-LCP/mimic-code/tree/main/mimic-iv/buildmimic

Go to the Models tab in Stardog Studio.

Add classes with their properties from the OMOP Common Data Model, e.g. Patient, Death

Through this interface you can browse the model through a tree view, and edit the model ontology as turtle RDF, making it easier if you need to import an existing ontology.

Validation that the data complies with the model can be set using SHACL: https://docs.stardog.com/data-quality-constraints

In the Virtual Graphs tab in Stardog Studio

Mappings in Stardog is done using the Stardog Mapping Syntax (SMS).

Here we provide an example of mappings from a patients.csv file to a Person, and it's Death, if recorded.

Mapping from patients to the Person class, converting the gender from M/F to 0/1 to comply with the OMOP CDM:

# Map patients to persons
PREFIX omop-cdm: <tag:stardog:designer:omop-cdm:model:>
MAPPING
FROM SQL {
  SELECT *, (CASE "gender"
    WHEN 'M' THEN '0'
    WHEN 'F' THEN '1'
  END) AS gender_id FROM patients
}
TO {
  ?Person_iri a omop-cdm:Person ;
    omop-cdm:year_of_birth ?anchor_year_integer_field ;
    omop-cdm:gender_concept_id ?gender_integer_field ;
    omop-cdm:id ?subject_id_integer_field .
}
WHERE {
  BIND(TEMPLATE("tag:stardog:designer:omop-cdm:data:Person:{subject_id}") AS ?Person_iri)
  BIND(StrDt(?anchor_year, <http:https://www.w3.org/2001/XMLSchema#integer>) AS ?anchor_year_integer_field)
  BIND(StrDt(?gender_id, <http:https://www.w3.org/2001/XMLSchema#integer>) AS ?gender_integer_field)
  BIND(StrDt(?subject_id, <http:https://www.w3.org/2001/XMLSchema#integer>) AS ?subject_id_integer_field)
}

Mapping from patients to the Death class, we only create Death entities when a dod is present:

# Map patients to deaths
PREFIX omop-cdm: <tag:stardog:designer:omop-cdm:model:>
MAPPING
FROM SQL {
  SELECT * FROM patients WHERE dod IS NOT NULL
}
TO {
  ?Death_iri a omop-cdm:Death ;
    omop-cdm:death_date ?dod_date_field .

  ?Death_iri omop-cdm:person_id ?Person_iri .
}
WHERE {
  BIND(TEMPLATE("tag:stardog:designer:omop-cdm:data:Person:{subject_id}") AS ?Person_iri)
  BIND(TEMPLATE("tag:stardog:designer:omop-cdm:data:Death:{subject_id}") AS ?Death_iri)
  BIND(StrDt(?dod, <http:https://www.w3.org/2001/XMLSchema#date>) AS ?dod_date_field)
}

Go to the Workspace tab in Stardog Studio

Or directly query the SPARQL endpoint at https://stardog.137.120.31.102.nip.io/icare4cvd

Query all virtual graphs with SPARQL:

SELECT *
FROM stardog:context:virtual
WHERE {
    ?s ?p ?o .
} LIMIT 10000

You can also use stardog:context:all to query all materialized and virtual graphs.

Query a specific virtual graph using its name:

SELECT *
WHERE {
  GRAPH <virtual:https://virtual_graph_name> {
    ?s ?p ?o .
  }
} LIMIT 10000

Get all persons:

SELECT DISTINCT ?id ?gender ?year_of_birth ?death_date
FROM stardog:context:virtual
WHERE {
    ?s a omop-cdm:Person ;
        omop-cdm:id ?id ;
        omop-cdm:gender_concept_id ?gender ;
        omop-cdm:year_of_birth ?year_of_birth .
    OPTIONAL {
        ?death omop-cdm:person_id ?s ;
               omop-cdm:death_date ?death_date
    }

} LIMIT 1000000

Get persons with no death date:

SELECT DISTINCT ?id ?gender ?year_of_birth
FROM stardog:context:virtual
WHERE {
    ?s a omop-cdm:Person ;
        omop-cdm:id ?id ;
        omop-cdm:gender_concept_id ?gender ;
        omop-cdm:year_of_birth ?year_of_birth .
    FILTER NOT EXISTS {?death omop-cdm:person_id ?s}

} LIMIT 1000000

Get how many years the patients stayed in hospital before dying:

SELECT DISTINCT ?id ?gender ?year_of_birth ?death_date (?year_of_death - ?year_of_birth AS ?age_of_death)
FROM stardog:context:virtual
WHERE {
    ?s a omop-cdm:Person ;
        omop-cdm:id ?id ;
        omop-cdm:gender_concept_id ?gender ;
        omop-cdm:year_of_birth ?year_of_birth .
        ?death omop-cdm:person_id ?s ;
               omop-cdm:death_date ?death_date
   BIND(xsd:integer(STRBEFORE(str(?death_date), "-")) AS ?year_of_death)

} LIMIT 1000000

⚠️ omop-cdm:year_of_birth is not the year of birth, but the year of admission at the hospital (to be fixed)

Get persons born after a specific date:

SELECT DISTINCT *
FROM stardog:context:virtual
WHERE {
    ?s a omop-cdm:Person ;
        omop-cdm:year_of_birth ?birthYear .
    FILTER (?birthYear > 2130)
} LIMIT 10000

See the Stardog introduction to SPARQL if you need to.

Install dependencies:

python3 -m venv .venv
source .venv/bin/activate
pip install csvkit mysql-connector-python

Generate schema from CSV. Note it needs to be manually fixed as they don't add (128) after VARCHAR

csvsql --db mysql:https://user:password@localhost:3306/heart-failure-db --insert stroke-prediction-cohort1.csv

Fix the password, cf. https://docs.stardog.com/stardog-admin-cli-reference/user/user-passwd

docker-compose exec stardog stardog-admin user passwd --username admin admin

To run in the Stardog docker container:

docker-compose exec stardog stardog-admin virtual mappings -f r2rml virtualgraph

TODO

SELECT COLUMNS[0] AS id, COLUMNS[1] AS age FROM dfs.`/data/stroke-prediction-cohort1.csv` LIMIT 3

The Stardog documentation is quite consequent, please look into it when you want to do something: https://docs.stardog.com

• Docs to easily load CSV/JSON through the UI: https://docs.stardog.com/virtual-graphs/importing-json-csv-files

• Docs to access the SPARQL, HTTP, GRAPHQL APIs: https://stardog-union.github.io/http-docs/

Community forum: https://community.stardog.com

Example docker-compose for cluster: https://github.com/stardog-union/pystardog/blob/develop/docker-compose.cluster.yml

Requirements: docker 🐳, and you will need to get your Stardog license at https://www.stardog.com/license-request ⚠️

Deploys a local Stardog triplestore, a PostgreSQL database, and a MariaDB SQL database to create a Virtual Knowledge Graph (VKG).

Place the stardog-license-key.bin file in the root folder of this repository.

Download the JDBC drivers in the drivers/ folder by running this script:

./prepare.sh

Optionally create a .env file with the password for the SQL database, otherwise the default is passwordtochange:

echo "PASSWORD=yourpassword" > .env

Start Stardog and postgreSQL:

docker-compose up -d

ℹ️ The PostgreSQL database will be automatically initialized using the schema and data in virtual-kg/