Lab: Ontop
Topics
- Creating SQLite3 databases
- Creating OBDA or R2RML mappings
- Setting up Ontop in GraphDB
- Querying SQLite3 databases through a virtual Ontop KG
Useful materials
- The SQLite project page.
- Accessing relational databases with data virtualization (GraphDB's Ontop documentation)
- The ontop-examples repository on GitHub contains many OBDA mapping examples
- Section 2 in SPARQL 1.1 Federated Query
Tasks
We recommend you run this exercise through the Ontop plugin to the free desktop version of OntoText's GraphDB tool. You can also use one of the open-source versions of Ontop.
Installing and running SQLite3
Task: Download and install a relational database system on your computer. We recommend the very light-weight and easy-to-install SQLite3.
Task: Create a folder on your local computer for this exercise. Go to the new folder and open a Console or Terminal window. Start SQLite3 with the name of a new database, for example 'MyTestDB.sqlite'.
Task: Based on the example files Fig6.3-ReportClaimantTable-original.sql in the Examples folder in mitt.uib.no, create the following two tables and fill them with test data:
Table InvestigatedPeople:
| InvestigationID | American | CPDate | CPDays | IndictmentDays | Investigation | Name | Outcome | Overturned | Pardoned |
| investigation_114 | true | None | None | None | bush-clinton-passport | None | None | false | false |
| investigation_100 | true | 1993-10-26 | 1335 | 789 | pierce | Deborah Gore Dean | conviction | false | false |
| investigation_113 | true | None | None | None | sealed-investigation-hwbush-2 | None | None | false | false |
| investigation_10 | true | 1975-01-01 | 592 | 286 | watergate | Robert C. Mardian | conviction | true | false |
| investigation_11 | true | None | None | -9 | watergate | Maurice H. Stans | indictment | false | false |
| investigation_115 | true | 1994-03-22 | -136 | -316 | whitewater | David L. Hale | guilty-plea | false | false |
Table Investigations:
| Investigation | InvestigationDays | InvestigationStart | InvestigationEnd | President |
| bush-clinton-passport | 1081 | 1992-12-14 | 1995-11-30 | Bill Clinton |
| pierce | 3162 | 1990-03-01 | 1998-10-27 | George H.W. Bush |
| sealed-investigation-hwbush-2 | 453 | 1991-04-19 | 1992-07-15 | George H.W. Bush |
| watergate | 1492 | 1973-05-19 | 1977-06-19 | Richard Nixon |
| whitewater | 2770 | 1994-08-05 | 2002-03-06 | Bill Clinton |
Tip: You can either enter SQL statements interactively one by one, or you can save them to a file and use SQLite3's .read command. Use the .exit command when you are done.
Tip: Although SQLite3 does not require it, Ontop will later expect that you have declared all PRIMARY KEYS as NOT NULL (for example: "InvestigationID text NOT NULL PRIMARY KEY,").
(If you want more data, the full CSV file is available on GitHub.)
OBDA and R2RML mappings
Task: Draw an example graph on paper that represents the investigation_100 and pierce rows of the two tables.
Task: Based on the example files Fig6.4-ReportClaimant-mapping.obda and/or Fig6.4-ReportClaimant-mapping.r2rml in the Examples folder, create a mapping from the two tables into an RDF graph.
GraphDB Ontop configuration
Task: You already have Ontop as part of your GraphDB installation, but you need an additional .jar-file - a JDBC driver - for connecting to your database of choice. For SQLite3, you can download the driver from Xenial on GitHub. Place the .jar-file in the lib/-subfolder of your GraphDB installation folder(*). Restart Ontop.
(*) On Linux, the path is <ONTOP_INSTALL_DIR>/lib/app/lib/sqlite-jdbc-3.49.0.0.jar .
Task:
- Create a new Ontop repository.
- The Driver class should be
org.sqlite.JDBC. The JDBC URL should bejdbc:sqlite:PATH, where PATH is the location of your SQLite3 database, for examplejdbc:sqlite:C:/Users/MyUser/OntopLab/MyTestDB.sqlite. - Load the OBDA or R2RML mapping file.
- Click Create and activate the new repository.
Running SPARQL queries
Task: Write SPARQL queries that answer the following questions:
- List all investigations in your graph.
- Count the number of investigations in your graph.
- List all investigated people in your graph.
- List all investigated people in your graph along with the investigations they were involved in.
- You can also re-do the following queries from before (you may need to load more data):
- List everyone who pleaded guilty, along with the name of the investigation.
- List everyone who were convicted, but who had their conviction overturned by which president.
- For each investigation, list the number of indictments made.
- For each investigation with multiple indictments, list the number of indictments made.
- For each investigation with multiple indictments, list the number of indictments made, sorted with the most indictments first.
If you have more time
Task: Create a new No inference (plain RDF) GraphDB repository and import triples from the file Presidents.ttl in the Examples folder in mitt.uib.no.
Activate the new repository. Its triples connect the names of recent US presidents to their Wikidata URIs. Run a SPARQL SELECT query to list them all.
Task: Create a new Federated (FedX Virtual SPARQL) repository.
- As a local repository, add the new Presidents repository.
- As a remote repository, add https://query.wikidata.org/sparql as a Generic SPARQL endpoint.
Activate the new repository.
Task: Re-run the SPARQL SELECT query that lists the names of recent US presidents along with their Wikidata URIs.
Task: Extend the SPARQL SELECT query to also list the schema:description of each US president.
Task: Use FILTER and the LANG function to only list president descriptions with language "en".
Tip: Here are some useful PREFIXES:
PREFIX foaf: <http://xmlns.com/foaf/0.1/> PREFIX owl: <http://www.w3.org/2002/07/owl#> PREFIX schema: <http://schema.org/>
