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 Ontop does not support SQLite3 fully, unfortunately. Using H2 instead of SQLite3 is an option, we outline below.

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

If you really have a lot of time

It is possible to create a Federated (FedX Virtual SPARQL) repository that federates

• a local repository (like the Presidents repository),
• a remote repository (for example https://query.wikidata.org/sparql as a Generic SPARQL endpoint), and
• a virtual repository (like the Investigations and InvestigatedPeople repository) as long as it runs on top of H2 instead of SQLite3.

Task: Install a recent Java on your computer if you do not have already, for example [for example from https://adoptium.net/en-GB/download/ the open version from Adoptium].

java -version

must show version 11 or later, and Java cannot be blocked by your firewall.

Task: Download and install H2.

Updated Java, for example from https://adoptium.net/en-GB/download/

download h2 jdbc driver for Ontop

Database and Ontop Setup | Ontop

Start the console (the default values are fine). After you connect, you can enter SQL commands in the form on the top.

SQL commands work, BUT: all the TABLES and COLUMNS now have UPPER CASE NAMES:

obda file: Not only table name, but scheme table (not Investigations, but PUBLIC.INVESTIGATIONS).

H2 install dir: h2-2.3.232.jar

org.h2.Driver

source SELECT * FROM PUBLIC.InvestigatedPeople

jdbc:h2:C:/Users/sinoa/test

(test.mv.db)

Disconnect web GUI

User name and pwd