MySQL in the Semantic Web
a SPARQL parser in mysqld

Eric Prud'hommeaux, RDF Data Access Working Group team contact

http://www.w3.org/2007/Talks/0424-spasql/
http://tinyurl.com/39sp65

Why did you add a parser to mysqld?

Allow Semantic Web users to query MySQL databases.
Allow MySQL users to federate queries across different data sources.
- other relational databases, vendor independent.
- document, XML, whatever data.

One Query Language

A pharmaceutical company does a query joining

a protein expression (MicroArray) test
a HUGE database of properties and genes
very proprietary database of chemical similarities
lots of public and private toxicity studies
homolgene databases, kinase databases, curated PubMed papers...

Save $1,000,000 / day with early termination.

What is SPASQL

Making queries like:

SELECT ?billCity ?billState
 WHERE { ?order <Orders.customer> ?cust .
         ?cust <Customers.billingAddress> ?addr .
         ?addr <Addresses.city> ?billCity .
         ?addr <Addresses.state> ?billState }

on data like:

Orders
id	customer	product	orderDate	shippingAddress
2186	518	7385	2006-05-01 01:08:30	NULL
2186	208	2922	2006-05-01 18:34:38	NULL
2187	518	7439	2006-05-01 01:05:30	NULL

Customers
givenName	familyName	billingAddress
Bob	Smith	18

(simple one-database example)

What is That Query?

Semantic Web (RDF) query language.
Refactors the what we see in SQL.

What's RDF?

Formal defn: directed, labeled graph language based on URIs.
Practical defn: short sentences of subject relationship object.

Why Put Data in RDF?

Basis in URIs means everything is globally identifiable...

including subjects:

<http://service1.example/A23D5F> more useful "A23D5F"
and distinct from <http://service1.example/CAD232>
relationships:

rdf:type
objects:

<http://en-dictionary.example/chat> more useful "chat"
and distinct from <http://fr-dictionary.example/chat>

Data Models

XML: query element/attribute structure.

<Person name="Sue"><knows><Person name="Bob">...</Person></name></Person>

<Person id="p7"><name>Sue</name>...</Person>
<Person id="p23"><name>Bob</name>...</Person>
<knows who="p7" whom="p23"/>
<knows who="p7" whom="..."/>

Relational: tuples of attributes

RDF: relationships between objects.

Patterns

XQuery: query element/attribute structure.

let $pairs := //knows
let $n1 := Person/@name[@id=$pairs/@who]
let $n2 := Person/@name[@id=$pairs/@whom]

SQL: query attributes of objects

FROM knows
     JOIN Persons as who ON knows.who=who.id
     JOIN Persons as whom ON knows.whom=whom.id

SPARQL: query relationships between objects.

WHERE { ?who foaf:knows ?whom .
        ?who foaf:name ?n1 .
        ?whom foaf:name ?n2 }

What is SPASQL?

SPARQL queries over conventional relational data.
Implicit relational mapping to RDF.
Not a triple store:
triples

subjects predicates object

order7 product product23

order7 shipDate 20050508

product23 product-name "Mystifier"
Not a table-per-predicate mapping:
shipDates

subjects object

order7 20050508

product-names

subjects object

product23 "Mystifier"

triples
subjects	predicates	object
order7	product	product23
order7	shipDate	20050508
product23	product-name	"Mystifier"

shipDates
subjects	object
order7	20050508

product-names
subjects	object
product23	"Mystifier"

SPASQL Example

What data is in RDBs today?
(What can you query right now with SPASQL?)

Most of the data used in computer automation.

Use case: OrderTracking.

Orders
id	customer	product	orderDate	shippingAddress
2186	518	7385	2006-05-01 01:08:30	NULL
2186	208	2922	2006-05-01 18:34:38	NULL
2187	518	7439	2006-05-01 01:05:30	NULL

Data Structure

Term: Graph

Many ways to represent graphs:

XML

<order prodct="#p23">...<Product id="p23" name="mystifier">

relational

RDF

Comparing SPARQL to SQL — references

Both SPARQL and SQL can query graph data.

SQL graph shape comes from reuse of table aliases:

... FROM Orders AS o
    WHERE o.orderDate < "20060501"
      AND o.shipDate = "20060508"

SPARQL graph shape comes from reuse of variables.

   PREFIX ot: <http://bigco.example/Orders/>
... WHERE { ?o ot:orderDate "20060501" .
            ?o ot:shipDate "20060508" }

Comparing SPARQL to SQL — links

Term: restrict

SQL restricts cross joins on attribute values.

  ...
  FROM Orders AS o
       JOIN Products AS p ON o.product=p.id

SPARQL has links in the data model that it queries.

  ...
  WHERE { ?o <Orders.products> ?p }

What's Out There?

Virtuoso
Oracle 10.2g
d2rq
DartQuery

Costs of SPASQL

Parsing — extra hash lookups and delayed processing
Execution — same compile tree modulo primary key lookup
Familiarity — a LOT of people know a little SQL.
SPASQL is relatively new.
Expressivity — many fewer standard functions at present.

UNION Example — SPARQL

SELECT ?part ?descr ?given ?family ?posn ?rma ?billCity ?billState ?shipCity ?shipState
 WHERE { { ?order <Orders.product> ?prod .
           ?order <Orders.customer> ?cust .
           OPTIONAL { ?order <Orders.shippingAddress> ?shipAddr .
                      ?shipAddr <Addresses.city> ?shipCity .
                      ?shipAddr <Addresses.state> ?shipState }
         } UNION {
           ?order <Returns.product> ?prod .
           ?order <Returns.customer> ?cust .
           ?order <Returns.rma> ?rma
         }
         ?prod <Products.partNo> ?part .
         ?prod <Products.description> ?descr .
         { ?cust <Customers.id> ?num .
           ?cust <Customers.givenName> ?given .
           ?cust <Customers.familyName> ?family .
           ?cust <Customers.billingAddress> ?addr
         } UNION {
           ?cust <Employees.id> ?num .
           ?cust <Employees.givenName> ?given .
           ?cust <Employees.familyName> ?family .
           ?cust <Employees.position> ?posn
         }
         ?cust <Employees.homeAddress> ?addr .
         ?addr <Addresses.city> ?billCity .
         ?addr <Addresses.state> ?billState }

UNION Example — corresponding SQL

SELECT Products_0.partNo AS part,
       Products_0.description AS descr,
       U1.given,
       U1.family,
       U1.posn,U0.rma,
       Addresses_1.city AS billCity,
       Addresses_1.state AS billState,
       U0.shipCity,
       U0.shipState
  FROM (
              SELECT Orders_0.customer AS cust,
                     Orders_0.product AS prod,
                     NULL AS rma,
                     IF(Addresses_0.city IS NULL OR Addresses_0.state IS NULL, NULL, Addresses_0.city) AS shipCity,
                     IF(Addresses_0.city IS NULL OR Addresses_0.state IS NULL, NULL, Addresses_0.state) AS shipState
                FROM Orders AS Orders_0
                     LEFT OUTER JOIN Addresses AS Addresses_0 ON Orders_0.shippingAddress=Addresses_0.id
               WHERE Orders_0.customer IS NOT NULL AND Orders_0.product IS NOT NULL
        UNION ALL
              SELECT Returns_0.customer AS cust,
                     Returns_0.product AS prod,
                     Returns_0.RMAnumber AS rma,
                     NULL AS shipCity,
                     NULL AS shipState
                FROM Returns AS Returns_0
               WHERE Returns_0.customer IS NOT NULL AND Returns_0.product IS NOT NULL AND Returns_0.RMAnumber IS NOT NULL
    ) AS U0
    INNER JOIN Products AS Products_0 ON U0.prod=Products_0.id
    INNER JOIN (
              SELECT Customers_0.id AS num,
                     Customers_0.givenName AS given,
                     Customers_0.familyName AS family,
                     NULL AS posn,
                     Customers_0.billingAddress AS addr
                FROM Customers AS Customers_0
               WHERE Customers_0.id IS NOT NULL AND Customers_0.givenName IS NOT NULL AND Customers_0.familyName IS NOT NULL AND Customers_0.billingAddress IS NOT NULL
        UNION ALL
              SELECT Employees_0.id AS num,
                     Employees_0.givenName AS given,
                     Employees_0.familyName AS family,
                     Employees_0.position AS posn,
                     Employees_0.homeAddress AS addr
                FROM Employees AS Employees_0
               WHERE Employees_0.id IS NOT NULL AND Employees_0.givenName IS NOT NULL AND Employees_0.familyName IS NOT NULL AND Employees_0.position IS NOT NULL AND Employees_0.homeAddress IS NOT NULL
    ) AS U1 ON U1.num=U0.cust
    INNER JOIN Addresses AS Addresses_1 ON U1.addr=Addresses_1.id
 WHERE Products_0.partNo IS NOT NULL AND Products_0.description IS NOT NULL AND Addresses_1.city IS NOT NULL AND Addresses_1.state IS NOT NULL;

Benefits

XQuery argument: one language to access all domains.
Variables make structure more visible (to me).

Dual access — arbirary migration.

$query1 = "SELECT id FROM Orders WHERE Orders.shipDate = '20060501'";
mysql_query($query1);
...
$query2 = "SPARQL: SELECT ?o WHERE { ?o <Orders.product> '20060501' }";
mysql_query($query2);

Possible minor performance windfalls.

no boundry around subselects (e.g. for UNIONS).

  FROM (...) AS U0
    INNER JOIN (
        SELECT ... FROM Customers AS Customers_0 WHERE ...
      UNION ALL
        SELECT ... FROM Employees AS Employees_0 WHERE ...
    ) AS U1 ON U1.num=U0.cust

Future Work

Finish UNION — takes some attention to details.
Heterogeneous (bybrid) databases — previous implementation experience.
Query federation with GRAPH.

Deployment Plan

Depends mostly on demand.
MySQL 5 x86 binary available from the project page.
Binaries for other viable debian platforms trivially built.
Perhaps MySQL SPASQL availability will inspire other DB vendors.

Data Mapping Details

Term: tuple

Term: predicate

Tuples correspond to RDF nodes.
Attribute names correspond to RDF predicates.
Foreign keys point to other nodes.
Other attribute values correspond to RDF literals (the boxes).
NULL attributess do NOT correspond to anything.

Data Mapping Details

Tuples correspond to RDF nodes.
Attribute names correspond to RDF predicates.
Foreign keys point to other nodes.
Other attribute values correspond to RDF literals (the boxes).
NULL attributess do NOT correspond to anything.

Data Mapping Details

Tuples correspond to RDF nodes.
Attribute names correspond to RDF predicates.
Foreign keys point to other nodes.
Other attribute values correspond to RDF literals (the boxes).
NULL attributess do NOT correspond to anything.

{ ?o <Orders.product> ?p }

Data Mapping Details

Tuples correspond to RDF nodes.
Attribute names correspond to RDF predicates.
Foreign keys point to other nodes.
Other attribute values correspond to RDF literals (the boxes).
NULL attributess do NOT correspond to anything.

{ ?o <Orders.product> ?p
  ?o <Orders.shipDate> ?ship }

Data Mapping — NULLs

Tuples correspond to RDF nodes.
Attribute names correspond to RDF predicates.
Foreign keys point to other nodes.
Other attribute values correspond to RDF literals (the boxes).
NULL attributess do NOT correspond to anything.

{ ?t <Orders.product> ?p
  OPTIONAL { ?t <Orders.shipDate> ?ship } }

Data Mapping — UNION

SPARQL UNION corresponds to an SQL subselected UNION

SPARQL

WHERE { { ?x <TableA.field> ?y }
        UNION
        { ?x <TableB.field> ?y } }

SQL

JOIN ( SELECT TableA.field AS foo FROM ... WHERE ...
       UNION
       SELECT TableB.field [AS foo] FROM ... WHERE ...  ) AS U0 ON U0.foo=...

Questions?

??? — ..!
_____? — .... .

$#&@!!!

MySQL in the Semantic Weba SPARQL parser in mysqld

Why did you add a parser to mysqld?

One Query Language

What is SPASQL

What is That Query?

What's RDF?

Why Put Data in RDF?

Data Models

Patterns

What is SPASQL?

SPASQL Example

Data Structure

XML

relational

RDF

Comparing SPARQL to SQL — references

Comparing SPARQL to SQL — links

What's Out There?

Costs of SPASQL

UNION Example — SPARQL

UNION Example — corresponding SQL

Benefits

Future Work

Deployment Plan

Data Mapping Details

Data Mapping Details

Data Mapping Details

Data Mapping Details

Data Mapping — NULLs

Data Mapping — UNION

SPARQL

SQL

Questions?

MySQL in the Semantic Web
a SPARQL parser in mysqld