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 be jdbc:sqlite:PATH, where PATH is the location of your SQLite3 database, for example jdbc: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".

...from here we could have gone on to add the Ontop repository too as a local repository, but the current version of Ontop for GraphDB does not support Federated repositories that include Ontop repositories well, unfortunately. We have asked the Ontop developers for guidance, and will update the exercise if the offer a solution.

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/>