Mapping RDBMS to RDF with D2RQ (yet another geeky title)

One of the coolest thing have seen here at Biohackathon 2010 is D2RQ (thank you Jan !):
D2RQ is a declarative language to describe mappings between relational database schemata and OWL/RDFS ontologies. The D2RQ Platform uses these mapping to enables applications to access a RDF-view on a non-RDF database.
In this post, I'll describe how I've installed a D2RQ server.
First, Download D2RQ:

wget http://downloads.sourceforge.net/project/d2rq-map/D2R%20Server/v0.7%20%28alpha%29/d2r-server-0.7.tar.gz
tar xfz d2r-server-0.7.tar.gz

Check if the java mysql driver is presnet in the lib folder (yes, it is)

ls lib/mysql-connector-java-5.1.7-bin.jar

Create one table in mysql describing some SNPs:

mysql> create table snp(id int unsigned primary key, name varchar(20) not null,avHet float);
Query OK, 0 rows affected (0.04 sec)

insert into snp(id,name,avHet) values (3210717,"rs3210717",0.2408);
insert into snp(id,name,avHet) values (1045871,"rs1045871",0.4278);
insert into snp(id,name,avHet) values (1045862,"rs1045862",0.2688);
insert into snp(id,name,avHet) values (17149433,"rs17149433",0.1958);
insert into snp(id,name,avHet) values (17149429,"rs17149429",0.1128);
insert into snp(id,name,avHet) values (16925319,"rs16925319",0.2822);
insert into snp(id,name,avHet) values (17353727,"rs17353727",0.495);
insert into snp(id,name,avHet) values (17157186,"rs17157186",0.4118);
insert into snp(id,name,avHet) values (3210688,"rs3210688",0.1638);
insert into snp(id,name,avHet) values (17157183,"rs17157183",0.4422);

Now call generate-mapping to generate the mapping between MYSQL and RDF:

./generate-mapping -u root -d com.mysql.jdbc.Driver -o mapping.n3 -b "my:bio:database" "jdbc:mysql://localhost/test"

Here is the file mapping.n3 that was generated

@prefix map: <file:/home/pierre/tmp/D2RQ/d2r-server-0.7/mapping.n3#> .
@prefix db: <> .
@prefix vocab: <my:bio:databasevocab/resource/> .
@prefix rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> .
@prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#> .
@prefix xsd: <http://www.w3.org/2001/XMLSchema#> .
@prefix d2rq: <http://www.wiwiss.fu-berlin.de/suhl/bizer/D2RQ/0.1#> .
@prefix jdbc: <http://d2rq.org/terms/jdbc/> .

map:database a d2rq:Database;
d2rq:jdbcDriver "com.mysql.jdbc.Driver";
d2rq:jdbcDSN "jdbc:mysql://localhost/test";
d2rq:username "root";
jdbc:autoReconnect "true";
jdbc:zeroDateTimeBehavior "convertToNull";
.

# Table snp
map:snp a d2rq:ClassMap;
d2rq:dataStorage map:database;
d2rq:uriPattern "snp/@@snp.id@@";
d2rq:class vocab:snp;
d2rq:classDefinitionLabel "snp";
.
map:snp__label a d2rq:PropertyBridge;
d2rq:belongsToClassMap map:snp;
d2rq:property rdfs:label;
d2rq:pattern "snp #@@snp.id@@";
.
map:snp_id a d2rq:PropertyBridge;
d2rq:belongsToClassMap map:snp;
d2rq:property vocab:snp_id;
d2rq:propertyDefinitionLabel "snp id";
d2rq:column "snp.id";
d2rq:datatype xsd:int;
.
map:snp_name a d2rq:PropertyBridge;
d2rq:belongsToClassMap map:snp;
d2rq:property vocab:snp_name;
d2rq:propertyDefinitionLabel "snp name";
d2rq:column "snp.name";
.
map:snp_avHet a d2rq:PropertyBridge;
d2rq:belongsToClassMap map:snp;
d2rq:property vocab:snp_avHet;
d2rq:propertyDefinitionLabel "snp avHet";
d2rq:column "snp.avHet";
d2rq:datatype xsd:float;
.

...now, start the d2r-server:

./d2r-server -p 8080 mapping.n3
03:15:03 INFO log :: Logging to org.slf4j.impl.Log4jLoggerAdapter(org.mortbay.log) via org.mortbay.log.Slf4jLog
03:15:03 INFO log :: jetty-6.1.10
03:15:03 INFO log :: NO JSP Support for , did not find org.apache.jasper.servlet.JspServlet
03:15:03 INFO D2RServer :: using config file: file:/home/pierre/tmp/D2RQ/d2r-server-0.7/ucsc.n3
(...)

Open your web browser at http://localhost:8080/snorql/ and TADA!!!!!!!! Here is a functional SPARQL engine mapping your database.

FTW !

That's it !
Pierre