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.

(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.

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 (and updates?)

Task: Write SPARQL queries that answer the following questions:

• XXX

Task: Write the following SPARQL updates:

• XXX

If you have more time

Running federated SPARQL queries

Task: Create two separate SQLite3 databases, one for each of the two tables InvestigatedPeople and Investigations.

Task: Create two separate OBDA and/or R2RML mappings for the new databases.

Task: Create two separate GraphDB Ontop repositories InvestigatedPeople and Investigations using the new mappings.

Task: Write federated SPARQL queries that combine data from the two new repositories to answer the same questions as before.

Tip: The URI/IRI for a GraphDB repository is available from its listing under Settings -> Repository .