getting R2RML to work

[PaulZH]

Having a MySQL database

a) materialised

using Stardog Mapping Language -> Success

./stardog-admin virtual import KBO /Users/paul/Desktop/KBO/kbo_conversion/rml/activity.properties /Users/paul/Desktop/KBO/kbo_conversion/rml/activity.ttl

Successfully imported 3701 triples into KBO

Activity ttl

<http://data.kbodata.be/organisation/{"EntityNumber"}#id> a org:FormalOrganization ;

    rov:orgActivity <http://id.fedstats.be/nace{"NaceVersion"}/{"NaceCode"}#id> ;

    sm:map [

      sm:table "activity" ;

    ] .

using R2RML -> no success

./stardog-admin virtual import KBO /Users/paul/Desktop/KBO/kbo_conversion/rml/activity.properties /Users/paul/Desktop/KBO/kbo_conversion/rml/activity.rml --format r2rml

No value present

Activity rml

<#ActivityMapping>

  a rr:TriplesMap;

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

  rr:subjectMap [

    rr:template "http://data.kbodata.be/organisation/{\"EntityNumber\"}#id";

    rr:class org:FormalOrganization 

  ];

 rr:predicateObjectMap [

    rr:predicate rov:orgActivity;

    rr:objectMap [rr:template "http://id.fedstats.be/nace{\"NaceVersion\"}/{\"NaceCode\"}#id";]

   ];

.

b) virtual graph

./stardog-admin virtual add /Users/paul/Desktop/KBO/kbo_conversion/rml/activity.properties /Users/paul/Desktop/KBO/kbo_conversion/rml/activity.ttl

There is a virtual graph for activity

But when I want to query this virtual graph

SELECT * {

   GRAPH <virtual://activity> {

      ?s ?p ?o.

   }

}

I get

Error!

com.complexible.stardog.plan.eval.operator.OperatorException: Error executing SQL query:  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"activity" QVIEW1 WHERE  QVIEW1.`NaceCode` IS NOT NULL AND QVIEW1.`Classificatio' at line 1 SQL query:  SELECT     2 AS `sQuestType`, NULL AS `sLang`, CAST(CAST(QVIEW1.`EntityNumber` AS CHAR(8000) CHARACTER SET utf8) AS CHAR(8000) CHARACTER SET utf8) AS `s`,     1 AS `pQuestType`, NULL AS `pLang`, 'virtual://activityactivity#Classification' AS `p`,     7 AS `oQuestType`, NULL AS `oLang`, CAST(QVIEW1.`Classification` AS CHAR(8000) CHARACTER SET utf8) AS `o`  FROM  "activity" QVIEW1 WHERE  QVIEW1.`NaceCode` IS NOT NULL AND QVIEW1.`Classification` IS NOT NULL AND QVIEW1.`EntityNumber` IS NOT NULL AND QVIEW1.`ActivityGroup` IS NOT NULL AND QVIEW1.`NaceVersion` IS NOT NULL UNION ALL SELECT     2 AS `sQuestType`, NULL AS `sLang`, CAST(CAST(QVIEW1.`EntityNumber` AS CHAR(8000) CHARACTER SET utf8) AS CHAR(8000) CHARACTER SET utf8) AS `s`,     1 AS `pQuestType`, NULL AS `pLang`, 'virtual://activityactivity#ActivityGroup' AS `p`,     7 AS `oQuestType`, NULL AS `oLang`, CAST(QVIEW1.`ActivityGroup` AS CHAR(8000) CHARACTER SET utf8) AS `o`  FROM  "activity" QVIEW1 WHERE  QVIEW1.`NaceCode` IS NOT NULL AND QVIEW1.`Classification` IS NOT NULL AND QVIEW1.`EntityNumber` IS NOT NULL AND QVIEW1.`ActivityGroup` IS NOT NULL AND QVIEW1.`NaceVersion` IS NOT NULL UNION ALL SELECT     2 AS `sQuestType`, NULL AS `sLang`, CAST(CAST(QVIEW1.`EntityNumber` AS CHAR(8000) CHARACTER SET utf8) AS CHAR(8000) CHARACTER SET utf8) AS `s`,     1 AS `pQuestType`, NULL AS `pLang`, 'virtual://activityactivity#NaceCode' AS `p`,     7 AS `oQuestType`, NULL AS `oLang`, CAST(QVIEW1.`NaceCode` AS CHAR(8000) CHARACTER SET utf8) AS `o`  FROM  "activity" QVIEW1 WHERE  QVIEW1.`NaceCode` IS NOT NULL AND QVIEW1.`Classification` IS NOT NULL AND QVIEW1.`EntityNumber` IS NOT NULL AND QVIEW1.`ActivityGroup` IS NOT NULL AND QVIEW1.`NaceVersion` IS NOT NULL UNION ALL SELECT     2 AS `sQuestType`, NULL AS `sLang`, CAST(CAST(QVIEW1.`EntityNumber` AS CHAR(8000) CHARACTER SET utf8) AS CHAR(8000) CHARACTER SET utf8) AS `s`,     1 AS `pQuestType`, NULL AS `pLang`, 'http://www.w3.org/1999/02/22-rdf-syntax-ns#type' AS `p`,     1 AS `oQuestType`, NULL AS `oLang`, 'virtual://activityactivity' AS `o`  FROM  "activity" QVIEW1 WHERE  QVIEW1.`NaceCode` IS NOT NULL AND QVIEW1.`Classification` IS NOT NULL AND QVIEW1.`EntityNumber` IS NOT NULL AND QVIEW1.`ActivityGroup` IS NOT NULL AND QVIEW1.`NaceVersion` IS NOT NULL UNION ALL SELECT     2 AS `sQuestType`, NULL AS `sLang`, CAST(CAST(QVIEW1.`EntityNumber` AS CHAR(8000) CHARACTER SET utf8) AS CHAR(8000) CHARACTER SET utf8) AS `s`,     1 AS `pQuestType`, NULL AS `pLang`, 'virtual://activityactivity#NaceVersion' AS `p`,     7 AS `oQuestType`, NULL AS `oLang`, CAST(QVIEW1.`NaceVersion` AS CHAR(8000) CHARACTER SET utf8) AS `o`  FROM  "activity" QVIEW1 WHERE  QVIEW1.`NaceCode` IS NOT NULL AND QVIEW1.`Classification` IS NOT NULL AND QVIEW1.`EntityNumber` IS NOT NULL AND QVIEW1.`ActivityGroup` IS NOT NULL AND QVIEW1.`NaceVersion` IS NOT NULL UNION ALL SELECT     2 AS `sQuestType`, NULL AS `sLang`, CAST(CAST(QVIEW1.`EntityNumber` AS CHAR(8000) CHARACTER SET utf8) AS CHAR(8000) CHARACTER SET utf8) AS `s`,     1 AS `pQuestType`, NULL AS `pLang`, 'virtual://activityactivity#EntityNumber' AS `p`,     7 AS `oQuestType`, NULL AS `oLang`, CAST(QVIEW1.`EntityNumber` AS CHAR(8000) CHARACTER SET utf8) AS `o`  FROM  "activity" QVIEW1 WHERE  QVIEW1.`NaceCode` IS NOT NULL AND QVIEW1.`Classification` IS NOT NULL AND QVIEW1.`EntityNumber` IS NOT NULL AND QVIEW1.`ActivityGroup` IS NOT NULL AND QVIEW1.`NaceVersion` IS NOT NULL

[Zachary Whitley]

My best guess would be that there is an issue with the ANSI_QUOTES mode in MySQL [1] and it's not liking the double quotes on the table name "activity". Try running

mysql> select @@SQL_MODE

to check the setting. If it's empty it's not set. To set ANSI_QUOTES run

mysql> SET @@session.sql.mode=ANSI_QUOTES;

Let me know if that fixes things up.

[1] http://dev.mysql.com/doc/refman/5.7/en/identifiers.html

--
-- --
You received this message because you are subscribed to the C&P "Stardog" group.
To post to this group, send email to sta...@clarkparsia.com
To unsubscribe from this group, send email to
stardog+u...@clarkparsia.com
For more options, visit this group at
http://groups.google.com/a/clarkparsia.com/group/stardog?hl=en
---
You received this message because you are subscribed to the Google Groups "Stardog" group.
To unsubscribe from this group and stop receiving emails from it, send an email to stardog+u...@clarkparsia.com.

[Zachary Whitley]

Or an easier alternative would be to drop the quotes from the table name. I don't think they're required. I don't know if that will fix the problem or why the behaviour is different between R2RML and SMS. I'll let one of the Stardog people weigh in on that but hopefully that will get you going if it's a blocking issue for you or at least give you something you can try.

[Evren Sirin]

Looking at the snippet you provided, I don't know why SMS and R2RML
would behave differently either. It might be the case that You can
export your SMS mappings in R2RML format using `stardog-admin virtual
mappings -f r2rml vg` and voce versa. That might give you an idea what
is different between the two.

The other problem must be the ANSI_QUOTES issue. Another possible
solution is to add "?sessionVariables=sql_mode='ansi'" at the end of
your jdbc URL.

Best,
Evren

[PaulZH]

Zachary,

Thanks, I'll investigate further.

[PaulZH]

Hi Evren,

Thanks for the input. I let you know what I find out.

Paul

[PaulZH]

Hi Evren,

I did the following

./stardog-admin virtual mappings -f r2rml activity

With result

@prefix vcard: <http://www.w3.org/2006/vcard/ns#> .

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

@prefix schema: <http://schema.org/> .

@prefix foaf: <http://xmlns.com/foaf/0.1/> .

@prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#> .

@prefix kbo: <http://data.kbodata.be/def#> .

@prefix skos: <http://www.w3.org/2004/02/skos/core#> .

@prefix stardog: <tag:stardog:api:> .

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

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

@prefix owl: <http://www.w3.org/2002/07/owl#> .

@prefix oslo: <http://purl.org/oslo/ns/localgov#> .

@prefix rov: <http://www.w3.org/ns/regorg#> .

@prefix : <http://api.stardog.com/> .

@prefix dcterms: <http://purl.org/dc/terms/> .

@prefix rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> .

@prefix sm: <tag:stardog:api:mapping:> .

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

@prefix ql: <http://semweb.mmlab.be/ns/ql#> .

@prefix rml: <http://semweb.mmlab.be/ns/rml#> .

_:genid-9590922abc2b47649b70bac58a58b8d5-node1aenfskj8x1 a rr:BaseTableOrView ;

rr:tableName "activity" .

_:genid-9590922abc2b47649b70bac58a58b8d5-node1aenfskj8x2 a rr:SubjectMap , rr:TermMap ;

rr:class org:FormalOrganization ;

rr:template "http://data.kbodata.be/organisation/{EntityNumber}#id" ;

rr:termType rr:IRI .

_:genid-9590922abc2b47649b70bac58a58b8d5-node1aenfskj8x3 a rr:TriplesMap ;

rr:logicalTable _:genid-9590922abc2b47649b70bac58a58b8d5-node1aenfskj8x1 ;

rr:predicateObjectMap _:genid-9590922abc2b47649b70bac58a58b8d5-node1aenfskj8x9 ;

rr:subjectMap _:genid-9590922abc2b47649b70bac58a58b8d5-node1aenfskj8x2 .

_:genid-9590922abc2b47649b70bac58a58b8d5-node1aenfskj8x8 a rr:ObjectMap , rr:TermMap ;

rr:template "http://id.fedstats.be/nace{NaceVersion}/{NaceCode}#id" ;

rr:termType rr:IRI .

_:genid-9590922abc2b47649b70bac58a58b8d5-node1aenfskj8x9 a rr:PredicateObjectMap ;

rr:objectMap _:genid-9590922abc2b47649b70bac58a58b8d5-node1aenfskj8x8 ;

rr:predicate rov:orgActivity .

Using this generated rml file (activity2.rml) in

./stardog-admin virtual import KBO /Users/paul/Desktop/KBO/kbo_conversion/rml/activity.properties /Users/paul/Desktop/KBO/kbo_conversion/rml/activity2.rml --format r2rml

gives

No value present

Similar to the handcrafted rml.

Something else to investigate?

Paul

On Friday, March 25, 2016 at 6:46:38 PM UTC+1, Evren Sirin wrote:

[PaulZH]

Zachari,

Indeed solved with using

SET GLOBAL SQL_MODE=ANSI_QUOTES;

on MySQL.

Thanks.

On Friday, March 25, 2016 at 5:47:43 PM UTC+1, Zachary Whitley wrote:

[Evren Sirin]

The problem turns out to be the file extension. You can have r2rml
mappings in turtle, rdf/xml, or any other RDF format. The CLI command
uses the file extension to guess the correct format and fails with a
useless error message if the file extension is not recognized. We will
improve the error message in the command. If you rename your file
extension to .ttl it should work for you.

Best,
Evren

[PaulZH]

Indeed. Many thanks.