Data Merging Problem

• Diverse domain experts provide critical information.
• Current practice involves either
  • downloading and massaging e.g. CSV files (cheap).
  • ETL (Extract Translate Load) and warehouses (big).
• Current practice involves downloading and massaging e.g. CSV files.
• Infrastructure should enable query distribution.

SPARQL State of the Art

• SPARQL offers a uniform protocol (no drivers needed).
• SPARQL query includes query distribution (SERVICE).
• Data publication norms leverage Web to popularize identifiers and schemas.
• SPARQL graph patterns suffice when data lines up.
• SPARQL CONSTRUCTs suffice when graph patterns differ.
• SPARQL 1.1 SELECT (f(?foo) AS ?bar) addresses some identifier divergence.

Exercise: run SPARQL binary

• Ask for the version:
  sparql --version

  sparql version 1.0 .
  Revision 1352 modified 2010-12-06 00:04:25 -0500 (Mon, 06 Dec 2010) by ericprud.
  https://swobjects.svn.sourceforge.net/svnroot/swobjects/branches/sparql11

• Get some triples (self-description):
  sparql -D

  {
    <> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://usefulinc.com/ns/doap#Project> .
    <> <http://usefulinc.com/ns/doap#homepage> <http://swobj.org/sparql/v1> .
    <> <http://usefulinc.com/ns/doap#shortdesc> "a semantic web query toolbox"  .
  }

Exercise: SPARQL as Web Server

• Start up a web server:
  sparql -D --serve http://localhost:8888/SPARQL
  and go to http://localhost:8888/ to play with it.:

  SELECT * WHERE { ?s ?p ?o }

• Start up with some graphs:
  sparql -G graph1 --serve http://localhost:8888/SPARQL
• Try more SPARQL queries:
  

  SELECT * WHERE { ?s ?p ?o }

  SELECT * WHERE { GRAPH <graph1> { ?s ?p ?o } }

CONSTRUCT Usage Patterns

... typically used to materialize transformations:

   PREFIX :mydb <http://cityhospital.example/dbs>
CONSTRUCT { ?o a               study:SubjectObservation .
            ?o study:subject   ?p .
            ?o study:clinician ?d .
            ?d :foaf:name ?dName }

    WHERE { ?o mydb:patient ?p .
            ?o mydb:doctor  ?d .
            ?d mydb:name    ?dName }

Exercise: Simple CONSTRUCT

• Reveal author's character:
  sparql -D -e "CONSTRUCT { ?s a <hobby> } WHERE { ?s a <http://usefulinc.com/ns/doap#Project> }"

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

• Multi-triple rule:
  sparql -D -e "PREFIX doap: <http://usefulinc.com/ns/doap#> CONSTRUCT { ?page <hasText> ?text } WHERE { ?s doap:homepage ?page ; doap:shortdesc ?text }"
  or

  PREFIX doap: <http://usefulinc.com/ns/doap#>
  CONSTRUCT { ?page <hasText> ?text }
      WHERE { ?s doap:homepage ?page .
              ?s doap:shortdesc ?text }

  <http://swobj.org/sparql/v1> <hasText> "a semantic web query toolbox"  .

Virtual Views

• In SQL, many views are virtual (and some are updatable).
• An SQL view definition looks like a rule:
  

  CREATE VIEW foo (
    SELECT genes.id AS gene, labels.text AS label
      FROM genes
      JOIN lables ON genes.label = lables.id 
  )

• SWObjects does that with SPARQL CONSTRUCTs.

  CONSTRUCT {
      ?gene uniprot:id ?id ; skos:prefLabel ?gene_symbol
    } WHERE {
      _:gene Ugene:acc ?id ; Ugene:val ?gene_symbol
    }

Query Transformation

CONSTRUCT
{
  ?g uniprot:id ?id . 
  ?g skos:prefLabel ?gene_symbol .
}
WHERE
{
  ?g <uniProt/gene#acc> ?id .
  ?g <uniProt/gene#val> ?gene_symbol .
}

Exercise: Query Transformation

• Transform query: sparql -m goProt0.map -np goProt0.rq
• Execute query: sparql -m goProt0.map -d goProt.ttl goProt0.rq

SELECT ?symbol  WHERE {
  _:prot uniprot:id 'P04637' .
  _:prot skos:prefLabel ?symbol .
}

SELECT  ?symbol
 WHERE {
   _:p <uniProt/gene#acc> "P04637"  .
   _:p <uniProt/gene#val> ?symbol .
 }

CONSTRUCT
{
  ?g uniprot:id ?id . 
  ?g skos:prefLabel ?gene_symbol .
}
WHERE
{
  ?g <uniProt/gene#acc> ?id .
  ?g <uniProt/gene#val> ?gene_symbol .
}

<gene#_1> <uniProt/gene#acc> 'P04637' .
<gene#_1> <uniProt/gene#val> 'P53' .
<gene#_1> <uniProt/gene#val> 'TP53' .

?symbol
  "P53"
 "TP53"

Exercise: Query Execution

• Execute query: sparql -m goProt0.map -d goProt.ttl goProt0.rq

SELECT ?symbol  WHERE {
  _:prot uniprot:id 'P04637' .
  _:prot skos:prefLabel ?symbol .
}

SELECT  ?symbol
 WHERE {
   _:p <uniProt/gene#acc> "P04637"  .
   _:p <uniProt/gene#val> ?symbol .
 }

CONSTRUCT
{
  ?g uniprot:id ?id . 
  ?g skos:prefLabel ?gene_symbol .
}
WHERE
{
  ?g <uniProt/gene#acc> ?id .
  ?g <uniProt/gene#val> ?gene_symbol .
}

<gene#_1> <uniProt/gene#acc> 'P04637' .
<gene#_1> <uniProt/gene#val> 'P53' .
<gene#_1> <uniProt/gene#val> 'TP53' .

?symbol
  "P53"
 "TP53"

Mergable Data

What makes data sharable?

• Same concepts
• in the same model
• using the same schema
• connecting the same identifiers

How do we make this happen?

• Pick a model (RDF, XML, SQL),
• share a schema (standards/consensus),
• share identifiers.

Producing Computable Shared Names

• CONSTRUCT {
      ?gene uniprot:id ?id ; skos:prefLabel ?gene_symbol
    } WHERE {
      SELECT (fn:concat(<http://purl.org/shared-names/protein/>, ?id) AS ?gene)
             (fn:lower-case(?u_gene_symbol) AS ?gene_symbol)
      {
        _:gene Ugene:acc ?id ; Ugene:val ?u_gene_symbol
      }
    }

• <http://purl.org/shared-names/protein/P04637>

If it's not computable, you need a lookup.

Exercise: Term Transformation

• Transform data: sparql -d goProt.ttl goProt1.map
• Execute query: sparql -m goProt0.map -d goProt.ttl goProt0.rq

<gene#_1> <uniProt/gene#acc> 'P04637' .
<gene#_1> <uniProt/gene#val> 'P53' .
<gene#_1> <uniProt/gene#val> 'TP53' .

<http…/P04637> skos:prefLabel "p53" .
<http…/P04637> skos:prefLabel "tp53" .

CONSTRUCT
{
  ?gene uniprot:id ?id . 
  ?gene skos:prefLabel ?sym .
}
WHERE
{
  SELECT (IRI(fn:concat("http…/", ?id)) AS ?gene)
         (fn:lower-case(?u_sym) AS ?sym) {
    _:x <uniProt/gene#acc> ?id .
    _:x <uniProt/gene#val> ?u_sym .
  }
}

<gene#_1> <uniProt/gene#acc> 'P04637' .
<gene#_1> <uniProt/gene#val> 'P53' .
<gene#_1> <uniProt/gene#val> 'TP53' .

?symbol
  "P53"
 "TP53"

Exercise: Query Term Transformation

• Transform query: sparql -d goProt.ttl goProt1.map
• Execute query: sparql -m goProt1-cheat.map -d goProt.ttl goProt1.rq

SELECT ?symbol  WHERE {
  <http…/P04637>
      skos:prefLabel ?symbol .
}

CONSTRUCT
{
  ?gene uniprot:id ?id . 
  ?gene skos:prefLabel ?sym .
}
WHERE
{
  SELECT (fn:concat(<http…/>, ?id) AS ?gene)
         (fn:lower-case(?u_sym) AS ?sym) {
    _:x <uniProt/gene#acc> ?id .
    _:x <uniProt/gene#val> ?u_sym .
  }
}

SELECT ?symbol WHERE {
  SELECT (<http…/P04637> AS ?gene)
         (fn:lower-case(?_r1_0_u_sym) AS ?symbol) 
  WHERE {
    _:_r1_0_x <uniProt/gene#acc> "P04637"  .
    _:_r1_0_x <uniProt/gene#val> ?_r1_0_u_sym .
  }
}

<gene#_1> <uniProt/gene#acc> 'P04637' .
<gene#_1> <uniProt/gene#val> 'P53' .
<gene#_1> <uniProt/gene#val> 'TP53' .

?symbol
  "P53"
 "TP53"

Query Federation

• Inject SERVICE constraints around rule body:

  CONSTRUCT {
      ?gene uniprot:id ?id ; skos:prefLabel ?gene_symbol
    } WHERE { SERVICE <http://localhost:8001/uniProt> {
      ?gene uniprot:id ?id ; skos:prefLabel ?gene_symbol
    }
  }

• Enables unchoreographed queries:

  SELECT ?id ?gene_symbol WHERE {
      ?gene uniprot:id ?id ; skos:prefLabel ?gene_symbol
    }

  to be directed to appropriate endpoints.
• No new tooling required.

Exercise: Choreograph a Query

sparql -m goProt2.map -np goProt2.rq

SELECT ?symbol ?label 
WHERE
{
  {
    SELECT (<http://www.uniprot.org/uniprot/P04637> AS ?gene)
           (fn:lower-case(?_uniProt_0_u_gene_symbol) AS ?symbol) 
    WHERE
    SERVICE <http://localhost:8001/uniProt>
      {
        _:_uniProt_0_gene <http://ucsc.example/uniProt/gene#acc> "P04637"  .
        _:_uniProt_0_gene <http://ucsc.example/uniProt/gene#val> ?_uniProt_0_u_gene_symbol .
      }
      }
  SERVICE <http://localhost:8003/go>
    {
      ?_go_0_gp <http://ucsc.example/go/gene_product#Symbol> ?symbol .
      ?_go_0_association <http://ucsc.example/go/association#gene_product_id> ?_go_0_gp .
      ?_go_0_association <http://ucsc.example/go/association#term_id> ?_go_0_t .
      ?_go_0_t <http://ucsc.example/go/term#name> ?label .
    }
}

Exercise: Debug a Choreography

sparql -m goProt2.map -np goProt2-bug.rq

failed to match triples prefixed by "!" in
SELECT ?symbol ?label 
WHERE
{
     <http://www.uniprot.org/uniprot/P04637> skos:prefLabel ?symbol .
!    ?product <http://yetanothergenevocabulary.org999/#symbol> ?symbol .
!    ?id <http://yetanothergenevocabulary.org999/#product> ?product .
     ?id <http://www.geneontology.org/dtd/go.dtdterm> ?goterm .
     ?goterm <http://www.w3.org/2000/01/rdf-schema#label> ?label .
}

sparql --debug 1 -m goProt2.map -np goProt2-bug.rq

Intra-model Mapping

• Many islands (silos) of vital data in SQL databases.
• SPARQL language ideally suited to connecting islands.
• Simple matter of mapping SPARQL to SQL.

Direct Mapping

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

<People/ID=7#_> <People#fname> "Bob" .
<People/ID=7#_> <People#addr> <Addresses/ID=18#_> .
<People/ID=8#_> <People#ID> 8 .

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

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

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

Direct Graph

• Exposes relational graph.
• Reflects SQL schema.
• Doesn't use popular RDF schema;
  <People#fname> vs. foaf:name.

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

<People/ID=7#_> <People#fname> "Bob" .
<People/ID=7#_> <People#addr> <Addresses/ID=18#_> .
<People/ID=8#_> <People#ID> 8 .

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

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

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

Interface Graph

• SPARQL CONSTRUCTs can create a nice interface graph.
• As before, this graph can be virtual.

One Hammer, So Many Nails

• Query pipelines can terminate in SPARQL or SQL endpoints.
• Different parties can present native content or custom views.
• Set up a uniprot sparql2sql endpoint:
  sparql --stem http://ucsc.example/uniProt/ -S mysql://genome@genome-mysql.cse.ucsc.edu/uniProt --serve http://localhost:8001/uniProt
• Set up a go sparql2sql endpoint:
  sparql --stem http://ucsc.example/go/ -S mysql://genome@genome-mysql.cse.ucsc.edu/go --serve http://localhost:8003/go
• Issue a unifying query:
  sparql -m goProt2.map -8d goProt.ttl goProt2.rq

SPARQL 2 SPARQL 2 SQL

• A SPARQL query over the virtual view:

  SELECT ?symbol  WHERE {
    _:prot uniprot:id 'P04637' .
    _:prot skos:prefLabel ?symbol .
    ...
  }

• turns into a SPARQL query over the antecedent data:

  SELECT (<http://www.uniprot.org/uniprot/P04637> AS ?gene)
         (fn:lower-case(?_uniProt_0_u_gene_symbol) AS ?symbol) 
   WHERE SERVICE <http://localhost:8001/uniProt> {
    _:_uniProt_0_gene gene:acc "P04637"  .
    _:_uniProt_0_gene gene:val ?_uniProt_0_u_gene_symbol }
  }

• SELECT DISTINCT _uniProt_0_gene.val AS _uniProt_0_u_gene_symbol
         FROM gene AS _uniProt_0_gene
   WHERE _uniProt_0_gene.acc="P04637"

Federation Dependencies

• User query:

    ...
    ?product gene:symbol    ?symbol .
    ?id      gene:product   ?product .
    ?id      go:term        ?goterm .
    ?goterm  rdfs:label     ?label .

• Direct query:

        SERVICE <http://localhost:8003/go>
          {
            ?_go_0_gp <http://ucsc.example/go/gene_product#Symbol> ?symbol .
            ?_go_0_association <http://ucsc.example/go/association#gene_product_id> ?_go_0_gp .
            ?_go_0_association <http://ucsc.example/go/association#term_id> ?_go_0_t .
            ?_go_0_t <http://ucsc.example/go/term#name> ?label .
          }

• SELECT DISTINCT _go_0_gp.ID AS _go_0_gp, _go_0_gp.Symbol AS symbol, _go_0_association.ID AS _go_0_association, _go_0_association.term_id AS _go_0_t, _go_0_t.name AS label
         FROM gene_product AS _go_0_gp
              INNER JOIN association AS _go_0_association ON _go_0_association.gene_product_id=_go_0_gp.ID
              INNER JOIN term AS _go_0_t ON _go_0_t.ID=_go_0_association.term_id
   WHERE (_go_0_gp.Symbol = "tp53" OR _go_0_gp.Symbol = "p53")

Standards Issues

• Query transformation useful at many points.
• Commodity language: SPARQL
• SPARQL is a nice syntax for rules
  but is not meant for rules.

Rules Languages

• RIF BLD
• SWRL
• SPARQL CONSTRUCT
• RuleML

The semantics align well, but implementations focus on different use case optimizations.

The Hard^h^hFun Stuff

• General Query Transformation
  • Graph pattern match against rule heads.
  • Query may entail multiple rule head instantiations.
    A foaf:know B. B foaf:knows C. requires 2 X ?x foaf:knows ?y
  • Query body instantiated from vars bound in head.
  • Unbound vars made unique.
• Complex query patterns handled recursively.
• Function inversion still in progress.

Function Inversion

Resolve as much as possible;
huge impact on performance.

  <http://www.uniprot.org/uniprot/P04637>
           skos:prefLabel ?symbol .

= >

SELECT (fn:lower-case(?_uniProt_0_u_gene_symbol) AS ?symbol) 
 WHERE {
   _:_uniProt_0_gene <http://ucsc.example/uniProt/gene#acc> "P04637"  .
   _:_uniProt_0_gene <http://ucsc.example/uniProt/gene#val> ?_uniProt_0_u_gene_symbol .
}

Function Inversion

Resolve as deeply as possible.

• CONCAT("a", ?b, "c", ?d) = "a123bxyz": → regexp("a(.*?)c(.*?)")
• ?a + ?b = 3
  • ?a bound → ?b bound to 3-?a
  • ?a bound → FILTER (?a + ?b = 3)
• fn:upper-case(?s) = "ASDF"
  • optimistic → ?s bound "asdf"
  • pesimistic → FILTER (fn:upper-case(?s) = "ASDF")

SWObjects Implementation Status

• Open source (Apache license, sourceforge host)
• SPARQL 1.0 query implementation
• SPARQL 1.1 such as we know it.
• Views for SPARQL 1.1 - path expressions.
• Bindings for python, perl, php, java, lua
• Some research on optimization.

Acknowledgements

• Nigam Shah
• M. Scott Marshall
• Helena Deus
• HCLS IG gang

Questions?

• Surely there are none.