R2RML mapping with values from another table

Question

I want to use R2RML and Virtuoso to map some tables from a MySql source. I have a table with "Things" and I want them to have predicates that are not URIs but coming from the name column from a table "Values" connected via the thingID. I can map the table "Values" via the ID of a "Thing" to the thingId of "Values" - but with this mapping I only get the URI of the respective "Values" entry. What I want to have is the string stored in the column "Value".

The expected resulting triple should be for example:

<http://localhost:8890/ex/things/1> rdf:type <http://localhost:8890/ex/types/vmware> ;
ex:hasValue "Name from the table Values" .

Example of Things table: 
ID  typeId 
1   3

Example of the Types table: 
ID   Name 
3    vmware

Example of the Values table:

ID   thingId   Value 
1    1         VMware Virtual Platform

That is my mapping so far:

<#TriplesMapThings> a rr:TriplesMap; rr:logicalTable [ rr:tableSchema "exdb" ; rr:tableOwner "ex" ; rr:tableName "Things" ]; 
rr:subjectMap [ rr:termType rr:IRI  ; rr:template "http://localhost:8890/ex/things/{id}"; rr:class ex:Things; rr:graph <http://localhost:8890/ex_test#> ];
rr:predicateObjectMap [ rr:predicateMap [ rr:constant ex:id ] ; rr:objectMap [ rr:column "id" ]; ] ;
rr:predicateObjectMap [ rr:predicateMap [ rr:constant ex:typeid ] ; rr:objectMap [ rr:column "typeId" ]; ] ;

rr:predicateObjectMap [ rr:predicateMap [ rr:constant rdf:type ] ; rr:objectMap [ rr:parentTriplesMap <#TriplesMapTypes>; rr:joinCondition [rr:child "typeId"; rr:parent "id";]; ];] ;

rr:predicateObjectMap [ rr:predicateMap [ rr:constant ex:hasValue ] ; rr:objectMap [ rr:parentTriplesMap <#TriplesMapValues>; rr:joinCondition [rr:child "id"; rr:parent "thingId";]; ];] .





<#TriplesMapTypes> a rr:TriplesMap; rr:logicalTable [ rr:tableSchema "exdb" ; rr:tableOwner "ex" ; rr:tableName "Types" ]; 
rr:subjectMap [ rr:termType rr:IRI  ; rr:template "http://localhost:8890/ex/types/{nameUri}"; rr:class owl:Class; rr:graph <http://localhost:8890/ex_test#> ];
rr:predicateObjectMap [ rr:predicateMap [ rr:constant ex:id ] ; rr:objectMap [ rr:column "id" ]; ] ;
rr:predicateObjectMap [ rr:predicateMap [ rr:constant ex:name ] ; rr:objectMap [ rr:column "name" ]; ] .





<#TriplesMapValues> a rr:TriplesMap; rr:logicalTable [ rr:tableSchema "exdb" ; rr:tableOwner "ex" ; rr:tableName "Values" ]; 
rr:subjectMap [ rr:termType rr:IRI  ; rr:template "http://localhost:8890/ex/values/{id}"; rr:class ex:Values; rr:graph <http://localhost:8890/ex_test#> ];

rr:predicateObjectMap [ rr:predicateMap [ rr:constant rdf:type ] ; rr:objectMap [ rr:parentTriplesMap <#TriplesMapAttributes>; rr:joinCondition [rr:child "attributeId"; rr:parent "id";]; ];]  ;

rr:predicateObjectMap [ rr:predicateMap [ rr:constant ex:hasThing ] ; rr:objectMap [ rr:parentTriplesMap <#TriplesMapThings>; rr:joinCondition [rr:child "thingId"; rr:parent "id";]; ];]  ;

rr:predicateObjectMap [ rr:predicateMap [ rr:constant ex:id ] ; rr:objectMap [ rr:column "id" ]; ] ;
rr:predicateObjectMap [ rr:predicateMap [ rr:constant ex:thingid ] ; rr:objectMap [ rr:column "thingId" ]; ] ;
rr:predicateObjectMap [ rr:predicateMap [ rr:constant ex:value ] ; rr:objectMap [ rr:column "value" ]; ] .

score 1 · Accepted Answer · edited Jan 23 '17 at 22:56

The simplest way is to use SQL queries:

<#TriplesMapThings> a rr:TriplesMap;
rr:logicalTable [rr:sqlQuery “SELECT Things.ID, Types.Name FROM Things, Types WHERE Things.typeId = Types.ID” ];
rr:subjectMap [ rr:template “http://localhost:8890/ex/things/{ID}”];
rr:predicateObjectMap [
    rr:predicate rdf:type;
    rr:objectMap [ rr:template “http://localhost:8890/ex/types/{Name}”]
].
<#TriplesMapValues> a rr:TriplesMap;
rr:logicalTable [rr:tableName “Values” ];
rr:subjectMap [ rr:template “http://localhost:8890/ex/things/{thingId}”];
rr:predicateObjectMap [
    rr:predicate ex:hasValue;
    rr:objectMap [ rr:column “Value”]
].

However, looks like this won't work with Virtuoso. Per their documentation, rr:sqlQuery is not supported.

If Types.Name is a unique key, I believe you could do the following:

<#TriplesMapThings> a rr:TriplesMap;
rr:logicalTable [rr:tableName “Things” ];
rr:subjectMap [ rr:template “http://localhost:8890/ex/things/{ID}”];
rr:predicateObjectMap [
    rr:predicate rdf:type;
    rr:objectMap [ 
        rr:parentTriplesMap <#TriplesMapTypes>;
        rr:joinCondition [ rr:child “typeId”; rr:parent “ID”];
    ]
].

<#TriplesMapTypes> a rr:TriplesMap;
rr:logicalTable [rr:tableName “Types” ];
rr:subjectMap [ rr:template “http://localhost:8890/ex/types/{Name}”].

<#TriplesMapValues> a rr:TriplesMap;
rr:logicalTable [rr:tableName “Values” ];
rr:subjectMap [ rr:template “http://localhost:8890/ex/things/{thingId}”];
rr:predicateObjectMap [
    rr:predicate ex:hasValue;
    rr:objectMap [ rr:column “Value”]
].

Note that you are defining the subject URIs for the table Types using the Name attribute. So even though you are joining between Things.typeId and Types.ID, the R2RML processor should use the subject definition of the <#TriplesMapTypes> TriplesMap.

If Types.Name is not a unique key, then you have to do it using SQL queries.

Note that R2RML was designed to use SQL queries in case you need complex mappings (like the one you are trying to do).

R2RML mapping with values from another table

1 Answers1