Relational Database Extractor

The purpose of this extractor is to gather data from a relational database and map it into an ontology format.
It is a tool that is required in the Civil Engineering use case, especially the sensors scenario. All sensor measurements and additional meta-information are stored in a relational Oracle database. This data represents the sensors and their relations and dependencies which shall be captured and preserved by the sensors ontology. The necessary step is to populate the data from the storage to the ontology by mapping the data and relations to the concepts and properties in the ontology, i.e. extract the data and populate it into the sensors domain-specific ontology in due consideration of the presented concepts.

The extraction is done using D2RQ, a relational database to sparql mapper that was mainly developed by the FU Berlin.

The extractor currently provides a config file for a mapping to the SensorDSO.

We tested the tool under Windows 7 and Windows Server and applied it to an Oracle 11g database instance. Both worked well and produced the expected outcome.

Prerequisites

1. D2RQ
2. a Relational database with a jdbc driver
   
3. JavaVM

General Usage Instructions

Download newest version of D2RQ from website: http://d2rq.org/

Download the specific jdbc driver for your database client, e.g. ojdbc6.jar for Oracle 11g (http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html).

Unzip the package and go to the extracted folder. Move the downloaded JDBC driver into the "lib/db-drivers" directory.

Go back to the main folder. Run "generate-mapping.bat [options] jdbcURL", with the jdbcURL for the database connection, and options for the output file (-o), JDBC driver (-d), username (-u) and password (-p).
Example:

Windows:

"generate-mapping.bat -o timbus.ttl -d oracle.jdbc.driver.OracleDriver -u user -p pass jdbc:oracle:thin:@timbus.teco.edu:49153/xe "

Linux/Mac:

"sh generate-mapping -o timbus.ttl -d oracle.jdbc.driver.OracleDriver -u pass -p pass jdbc:oracle:thin:@timbus.teco.edu:49153/xe "

You may choose to include/exclude some tables. This can be specified by using:
" --skip- [schema.]table[.column]"
e.g.: "--tables EXTRESISTENCIA,EXTRESISTENCIARES" to just include these two tables.

Now, a first version of a mapping file (timbus.ttl) was created. You now open this Turtle file and adjust is to your needs (e.g. a specific ontology), by specifying the prefix and the required mappings.

An example file archive can be found in the "example files" folder. You can unzip it and you will find all d2rq files and a mapping file created as explained above.
The mapping file that is currently used for a SensorDSO mapping in the context of T8.3 is in the "mappings" folder.

Start Service

To run the service, first open your command line/terminal. Change the folder and go to the d2rq main folder. Run the d2rq-server.bat or the shell script in the following way:

Windows:

"d2r-server.bat timbus.ttl"

Linux/Mac:

"sh d2r-server timbus.ttl"

Expected Output

The output will be a SensorDSO graph that can be queried.

The output that is generated by this tool is an RDF graph that represents a SensorDSO, i.e. the extracted data accordingly to the concepts and properties of the ontology as specified in the mapping file. Once the mapping file is written for a database and ontology, the data can be accessed and populated almost automatically. This is a huge advantage as it saves the time it takes to manually populate the data. Moreover, it has a higher guarantee for consistency and completeness and a lower risk for typing errors.

There are several ways to acquire the results.

One option is to use the service d2r query:

"d2r-query mapping-file.ttl query"

Another option is to use the GUI. Go to the adress specified in the mapping file (e.g. "localhost:2020"). There, you can browse through the graph and also run SPARQL queries.

To run the queries, you can go to "localhost:2020/snorql" and run queries online and extract information.

Alternatively, you can use s-query by Jena Fuseki and query the SPARQL endpoint "localhost:2020/sparql" and get the results directly (console output, possibly: output into file). The command looks like this:

"s-query --service==http://localhost:2020/sparql query"

In both cases, "query" is your SPARQL query. An example is this:

PREFIX sensor: <http://timbus.teco.edu/ontologies/DSOs/sensors.owl#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
Construct {?d sensor:hasName "Pedrógăo" . ?s sensor:hasLocation ?d . ?s ?p ?o . } WHERE { ?d sensor:hasName "Pedrógăo" . ?s sensor:hasLocation ?d . ?s rdf:type sensor:Sensor . ?s ?p ?o . }

Be aware of the following things:
Online SPARQL query in the GUI is via SNORQL (therefore: http://localhost:2020/SNORQL) but the command line version uses the SPARQL endpoint (therefore: http://localhost:2020/SPARQL).

The above mentioned SPARQL example query will generate an RDF subgraph containing all sensors from the Pedrógăo dam (see d2rq-sensors-query-example.rdf in the "example files" folder).

Such an extracted graph can be linked to other domain-independent or domain-specific ontologies. With such a linkage, individuals can be mapped to instances from other ontologies, e.g. as in the sensors scenario’s “main.owl“ which connects the sensors in the RDF subgraphs to extensometers in the scenario’s domain-independent ontology (see main.owl in the "example files" folder). In a similar way, individuals could be mapped to concepts and instances from the Context Model or other DIOs.