Direct Mapping, R2RML

Jakub Klímek

This work is licensed under a Creative Commons Attribution 4.0 International License.

Triplifying relational DB - requirements

2

<http://data.example.com/employee/7369> a ex:Employee ;

ex:name "SMITH"@en ;

ex:department <http://data.example.com/department/10> .

​

<http://data.example.com/department/10> a ex:Department ;

ex:name "APPSERVER"@en ;

ex:location "NEW YORK"@en ;

ex:staff 1 .

construction of custom IRIs for departments and employees - rows of tables

use of a custom target vocabulary

ex:department property relates an employee to their department, based on foreign key

ex:staff property has the total number of staff of a department - this value has to be computed

Relational database wrapper

3

RDB

Wrapper

SQL

Table�(CSV)

SPARQL

RDF

Mapping�(DM, R2RML, D2RQ)

Direct Mapping

4

Direct Mapping (DM)

A Direct Mapping of Relational Data to RDF

• W3C Recommendation
• 27 September 2012
• Implementations
• https://github.com/bsletten/db2triples

• Specifies one particular generic mapping
• Result is “raw RDF”
• no vocabularies used
• need for postprocessing
• Almost no configuration required
• just the source relational database

5

DM - Simple relational database example

CREATE TABLE "Addresses" (

"ID" INT, PRIMARY KEY("ID"),

"city" CHAR(10),

"state" CHAR(2)

)

​

CREATE TABLE "People" (

"ID" INT, PRIMARY KEY("ID"),

"fname" CHAR(10),

"addr" INT,

FOREIGN KEY("addr") REFERENCES "Addresses"("ID")

)

​

INSERT INTO "Addresses" ("ID", "city", "state") VALUES (18, 'Cambridge', 'MA')

INSERT INTO "People" ("ID", "fname", "addr") VALUES (7, 'Bob', 18)

INSERT INTO "People" ("ID", "fname", "addr") VALUES (8, 'Sue', NULL)

6

People

Addresses

DM - Direct mapping example

@base <http://foo.example/DB/> .

@prefix xsd: <http://www.w3.org/2001/XMLSchema#> .

​

<People/ID=7> rdf:type <People> .

<People/ID=7> <People#ID> 7 .

<People/ID=7> <People#fname> "Bob" .

<People/ID=7> <People#addr> 18 .

<People/ID=7> <People#ref-addr> <Addresses/ID=18> .

<People/ID=8> rdf:type <People> .

<People/ID=8> <People#ID> 8 .

<People/ID=8> <People#fname> "Sue" .

​

<Addresses/ID=18> rdf:type <Addresses> .

<Addresses/ID=18> <Addresses#ID> 18 .

<Addresses/ID=18> <Addresses#city> "Cambridge" .

<Addresses/ID=18> <Addresses#state> "MA" .

7

People

Addresses

DM - Foreign keys referencing candidate keys

@base <http://foo.example/DB/> .

@prefix xsd: <http://www.w3.org/2001/XMLSchema#> .

​

<People/ID=7> a <People> .

<People/ID=7> <People#ID> 7 .

<People/ID=7> <People#fname> "Bob" .

<People/ID=7> <People#addr> 18 .

<People/ID=7> <People#ref-addr> <Addresses/ID=18> .

<People/ID=7> <People#deptName> "accounting" .

<People/ID=7> <People#deptCity> "Cambridge" .

<People/ID=7> <People#ref-deptName;deptCity> <Department/ID=23> .

<People/ID=8> a <People> .

<People/ID=8> <People#ID> 8 .

<People/ID=8> <People#fname> "Sue" .

<Addresses/ID=18> a <Addresses> .

<Addresses/ID=18> <Addresses#ID> 18 .

<Addresses/ID=18> <Addresses#city> "Cambridge" .

<Addresses/ID=18> <Addresses#state> "MA" .

​

<Department/ID=23> a <Department> .

<Department/ID=23> <Department#ID> 23 .

<Department/ID=23> <Department#name> "accounting" .

<Department/ID=23> <Department#city> "Cambridge" .

<Department/ID=23> <Department#manager> 8 .

<Department/ID=23> <Department#ref-manager> <People/ID=8> .

8

People

Addresses

Department

DM - Multi-column primary keys

<People/ID=7> <People#ref-deptName;deptCity> <Department/name=accounting;city=Cambridge> .

<Department/name=accounting;city=Cambridge> a <Department> .

<Department/name=accounting;city=Cambridge> <Department#ID> 23 .

<Department/name=accounting;city=Cambridge> <Department#name> "accounting" .

<Department/name=accounting;city=Cambridge> <Department#city> "Cambridge" .

9

Department

DM - Empty (non-existent) primary keys

@base <http://foo.example/DB/>

@prefix xsd: <http://www.w3.org/2001/XMLSchema#> .

_:a a <Tweets> .

_:a <Tweets#tweeter> 7 .

_:a <Tweets#ref-tweeter> <People/ID=7> .

_:a <Tweets#when> "2010-08-30T01:33"^^xsd:dateTime .

_:a <Tweets#text> "I really like lolcats." .

10

CREATE TABLE "Tweets" (

"tweeter" INT,

"when" TIMESTAMP,

"text" CHAR(140),

FOREIGN KEY("tweeter") REFERENCES "People"("ID")

)

​

_:b a <Tweets> .

_:b <Tweets#tweeter> 7 .

_:b <Tweets#ref-tweeter> <People/ID=7> .

_:b <Tweets#when> "2010-08-30T09:01"^^xsd:dateTime .

_:b <Tweets#text> "I take it back." .

Tweets

DM - Referencing tables with empty primary keys

@base <http://foo.example/DB/>

@prefix xsd: <http://www.w3.org/2001/XMLSchema#> .

_:c a <Projects> .

_:c <Projects#lead> <People/ID=8> .

_:c <Projects#name> "pencil survey" .

_:c <Projects#deptName> "accounting" .

_:c <Projects#deptCity> "Cambridge" .

_:c <Projects#ref-deptName;deptCity> <Department/ID=23> .

​

<TaskAssignments/worker=7;project=pencil%20survey> a <TaskAssignments> .

<TaskAssignments/worker=7;project=pencil%20survey> <TaskAssignments#worker> 7 .

<TaskAssignments/worker=7;project=pencil%20survey> <TaskAssignments#ref-worker> <People/ID=7> .

<TaskAssignments/worker=7;project=pencil%20survey> <TaskAssignments#project> "pencil survey" .

<TaskAssignments/worker=7;project=pencil%20survey> <TaskAssignments#deptName> "accounting" .

<TaskAssignments/worker=7;project=pencil%20survey> <TaskAssignments#deptCity> "Cambridge" .

<TaskAssignments/worker=7;project=pencil%20survey> <TaskAssignments#ref-deptName;deptCity> <Department/ID=23> .

<TaskAssignments/worker=7;project=pencil%20survey> <TaskAssignments#ref-project;deptName;deptCity> _:c .

11

Projects

TaskAssignments

R2RML

12

R2RML

• R2RML: RDB to RDF Mapping Language
• W3C Recommendation
• 27 September 2012
• Implementations
• https://github.com/oeg-upm/morph-rdb
• https://github.com/bsletten/db2triples
• …
• The original (now non-standard) is D2RQ

13

R2RML - Vocabulary

14

Generated Output Dataset

Generated Triples

TriplesMap

LogicalTable

PredicateObjectMap

GraphMap

SubjectMap

PredicateMap

ObjectMap

Join

RefObjectMap

R2RML - Mapping a simple table

@prefix rr: <http://www.w3.org/ns/r2rml#>.

@prefix ex: <http://example.com/ns#>.

​

<#TriplesMap1> a rr:TriplesMap;

rr:logicalTable [ rr:tableName "EMP" ];

rr:subjectMap [

rr:template "http://data.example.com/employee/{EMPNO}";

rr:class ex:Employee;

];

rr:predicateObjectMap [

rr:predicate ex:name;

rr:objectMap [ rr:column "ENAME" ];

].

15

<http://data.example.com/employee/7369> a ex:Employee.

<http://data.example.com/employee/7369> ex:name "SMITH".

Generated Output Dataset

Generated Triples

TriplesMap

LogicalTable

PredicateObjectMap

GraphMap

SubjectMap

PredicateMap

ObjectMap

Join

RefObjectMap

R2RML - Computing a property

<#DeptTableView> rr:sqlQuery """

SELECT DEPTNO,

DNAME,

LOC,

(SELECT COUNT(*) FROM EMP WHERE EMP.DEPTNO=DEPT.DEPTNO) AS STAFF

FROM DEPT;

""".

​

​

16

R2RML - Computing a property

<#TriplesMap2>

rr:logicalTable <#DeptTableView>;

rr:subjectMap [

rr:template "http://data.example.com/department/{DEPTNO}";

rr:class ex:Department;

];

rr:predicateObjectMap [

rr:predicate ex:name;

rr:objectMap [ rr:column "DNAME" ];

];

rr:predicateObjectMap [

rr:predicate ex:location;

rr:objectMap [ rr:column "LOC" ];

];

17

rr:predicateObjectMap [

rr:predicate ex:staff;

rr:objectMap [ rr:column "STAFF" ];

].

​

<http://data.example.com/department/10> a ex:Department ;

ex:name "APPSERVER" ;

ex:location "NEW YORK" ;

ex:staff 1 .

​

R2RML - Linking two tables

<#TriplesMap1>

rr:predicateObjectMap [

rr:predicate ex:department;

rr:objectMap [

rr:parentTriplesMap <#TriplesMap2>;

rr:joinCondition [

rr:child "DEPTNO";

rr:parent "DEPTNO";

];

];

].

18

<http://data.example.com/employee/7369> ex:department <http://data.example.com/department/10>.

​

Generated Output Dataset

Generated Triples

TriplesMap

LogicalTable

PredicateObjectMap

GraphMap

SubjectMap

PredicateMap

ObjectMap

Join

RefObjectMap

R2RML - TermMap

<#TriplesMap1>

rr:logicalTable [ rr:sqlQuery """

SELECT EMP.*, (CASE JOB

WHEN 'CLERK' THEN 'general-office'

WHEN 'NIGHTGUARD' THEN 'security'

WHEN 'ENGINEER' THEN 'engineering'

END) ROLE FROM EMP

""" ];

rr:subjectMap [

rr:template "http://data.example.com/employee/{EMPNO}";

];

rr:predicateObjectMap [

rr:predicate ex:role;

rr:objectMap [ rr:template "http://data.example.com/roles/{ROLE}" ];

].

19

TermMap

TermMap

TermMap

Generated Output Dataset

Generated Triples

TriplesMap

LogicalTable

PredicateObjectMap

GraphMap

SubjectMap

PredicateMap

ObjectMap

Join

RefObjectMap

R2RML - Term maps

20

R2RML - Term type

• Subject map
• rr:IRI or rr:BlankNode
• Predicate map
• rr:IRI
• Object map
• rr:IRI or rr:BlankNode or rr:Literal
• Graph map
• rr:IRI

21

[] rr:objectMap [

rr:template "\\{\\{\\{ \\\\o/ {TITLE} \\\\o/ \\}\\}\\}";

rr:termType rr:Literal;

].

​

R2RML - Data types

22

[] rr:objectMap [ rr:column "EMPNO"; rr:datatype xsd:positiveInteger ].

R2RML - Graph map

[] rr:subjectMap [

rr:template "http://data.example.com/department/{DEPTNO}";

rr:graph ex:DepartmentGraph;

].

​

[] rr:subjectMap [

rr:template "http://data.example.com/department/{DEPTNO}";

rr:graphMap [ rr:constant ex:DepartmentGraph ];

].

​

[] rr:subjectMap [

rr:template "http://data.example.com/employee/{EMPNO}";

rr:graphMap [ rr:template "http://data.example.com/jobgraph/{JOB}" ];

].

23

Generated Output Dataset

Generated Triples

TriplesMap

LogicalTable

PredicateObjectMap

GraphMap

SubjectMap

PredicateMap

ObjectMap

Join

RefObjectMap

D2RQ

24

D2RQ

D2RQ

• Mapping language
• v0.8.1 – 22 June 2012

D2R Server

• Implementation
• Wrapper of RDB using a R2RQ mapping
• Supports W3C Direct Mapping (not R2RML)
• Provides browser and SPARQL endpoint

25

D2RQ - Database configuration

@prefix jdbc: <http://d2rq.org/terms/jdbc/> .

​

map:Database1 a d2rq:Database;

d2rq:jdbcDSN "jdbc:mysql://localhost/iswc";

d2rq:jdbcDriver "com.mysql.jdbc.Driver";

d2rq:username "user";

d2rq:password "password";

​

# ... other database configuration ...

jdbc:autoReconnect "true";

jdbc:zeroDateTimeBehavior "convertToNull";

.

26

D2RQ - d2rq:ClassMap

URI patterns

• urn:isbn:@@Books.isbn@@
• https://ex.org/persons/@@Persons.ID@@

d2rq:ClassMap

• Defines how instances of the class are identified
• d2rq:dataStorage
• d2rq:class
• d2rq:uriPattern
• d2rq:uriColumn
• d2rq:uriSqlExpression
• d2rq:bNodeIdColumns
• d2rq:constantValue
• d2rq:condition

27

D2RQ - d2rq:ClassMap Example

map:PaperClassMap a d2rq:ClassMap;

d2rq:uriPattern "http://www.conference.org/conf02004/paper#Paper@@Papers.PaperID@@";

d2rq:class :Paper;

d2rq:classDefinitionLabel "paper"@en;

d2rq:classDefinitionComment "A conference paper."@en;

d2rq:dataStorage map:Database1;

.

Produces:

<http://www.conference.org/conf02004/paper#Paper4> a :Paper.

​

:Paper a rdfs:Class ;

rdfs:label "paper"@en;

rdfs:comment "A conference paper."@en .

​

​

​

​

28

D2RQ - d2rq:PropertyBridge

Mapping of RDB column to RDF property

• d2rq:belongsToClassMap
• d2rq:property
• d2rq:dynamicProperty
• d2rq:column
• d2rq:pattern
• d2rq:sqlExpression

• d2rq:datatype
• d2rq:lang
• d2rq:constantValue
• d2rq:refersToClassMap
• d2rq:uriColumn
• d2rq:uriPattern
• d2rq:uriSqlExpression
• d2rq:join
• d2rq:condition

29

D2RQ - d2rq:PropertyBridge Example

map:authorName a d2rq:PropertyBridge;

d2rq:belongsToClassMap map:PaperClassMap;

d2rq:property :authorName;

d2rq:column "Persons.Name";

d2rq:join "Papers.PaperID <= Rel_Person_Paper.PaperID";

d2rq:join "Rel_Person_Paper.PersonID => Persons.PerID";

d2rq:datatype xsd:string;

d2rq:propertyDefinitionLabel "name"@en;

d2rq:propertyDefinitionComment "Name of an author."@en .

Produces:

<http://www.conference.org/conf02004/paper#Paper4> :authorName "John" .

​

:authorName a rdf:Property ;

rdfs:label "name"@en ;

rdfs:comment "Name of an author."@en .

​

30

D2RQ - More constructs

• DownloadMap
• Generates dumps: dump-rdf utility
• Generates basic mapping: generate-mapping utility

31

RDB

Wrapper

SQL

Table�(CSV)

SPARQL

RDF

Mapping�(DM, R2RML, D2RQ)