How Virtuoso extends SQL with SPARQL — and vice versa

On-demand Relational Data Virtualization across SQL Tables and RDF Graphs

Published in

OpenLink Virtuoso Weblog

7 min readJan 29, 2018

To fully and cost-effectively exploit the data flow and integration potentials that underlie the notion of a Semantic Web, we need to consider the possibility of extending SQL with SPARQL.

Along similar lines, SPARQL-compliant solutions can also benefit from leveraging functionality that is typical and expected of SQL Relational Database Managements Systems (RDBMS).

What does SPARQL offer me?

SPARQL is a declarative query language that operates on entity-relationship-types (relations) represented as RDF sentences/statements.

SPARQL features include —

enhanced data interaction expressivity due to the entity-relationship-type granularity of RDF which is accompanied by SELECT (returns query solutions in tabular form) and ASK (returns query solutions in yes/no form) query modalities
use of hyperlinks (specifically, HTTP URIs) to identify subject, predicate, and object across datasets from which a query solution is constructed
in Virtuoso’s SPARQL implementation, the ability to de-reference hyperlinks during query solution processing, extending to wherever data might reside on an HTTP-based network
semantically-informed entity-relationship-graph traversal — that is, the use of the nature (semantics) of entity-relationship-types (relations identified by sentence predicates) to inform actual data de-reference as opposed to the brute-force mechanics associated with SQL joins

What does SQL add to SPARQL?

SQL — a broadly understood query language that operates on entity-relationship-types (relations) represented as records in tables — may be used to access and enhance SPARQL functionality in Virtuoso.

SQL features include —

time-tested semantics for handling transaction Atomicity, Consistency, Isolation, and Durability (ACID)
established methods for handling optimistic and pessimistic concurrency in the context of transaction isolation and consistency
Stored Procedures (a/k/a Persistent Stored Modules) which enable server-hosted execution of procedures that are modular in nature, don’t incur client-server overhead, and are compiled for increased speed of execution

As a multi-model RDBMS, Virtuoso is a natural candidate to support SPASQL (a shortening of SPARQL-in-SQL). We’ve implemented this support through a hybrid declarative query language (“Virtuoso SPASQL”) which delivers a built-in blend of native SQL functionality with native SPARQL functionality. By extending one open standard (SQL) with another (SPARQL), we make the use of proprietary SQL or SPARQL extensions a matter of last resort.

How To Extend SQL using SPARQL

Simply starting a query with the keyword SPARQL, followed by a SPARQL query, in any of Virtuoso’s SQL interfaces (iSQL, ODBC, JDBC, OLE DB, ADO.NET, etc.), causes the the SPARQL solution set — drawn only from relations represented as RDF sentences/statements — to be returned in tabular form to an SQL client application.

Query pattern:

SPARQL {SPARQL Query Goes Here}

Usage example:

SPARQL SELECT * WHERE { ?s ?p ?o } LIMIT 10

Sometimes you may need to combine data from relations represented as SQL Tables with data from relations represented as RDF sentences/statements. Virtuoso SPASQL leverages the SQL FROM clause to achieve this goal.

Query pattern:

SELECT {select-list}
FROM (SPARQL {sparql-query}) AS {sql-alias}
WHERE {sql-query-join-conditions}

Usage example:

SELECT  {select-list}
FROM
  ( 
    {sparql-query-including-select-list-variables}
  ) AS <SQL-Table-Alias>, 
       <SQL-Table-Name>
WHERE 
  <SQL-Table-Name>.<column-name> 
     = <SQL-Table-Alias>.<sparql-select-list-variable> 
AND 
  <SQL-Table-Alias>.<sparql-select-list-variable> 
     LIKE 'D%'

This feature allows users to extend functionalities built into existing SQL-, ODBC-, JDBC-, OLE DB-, and ADO.NET-compliant applications, without needing to “reinvent the wheel” or contend with the learning curves that inevitably arise from the introduction of any new technology.

For instance, existing Data Visualization and Business Intelligence tools (e.g., Tableau, PowerBI, Yellowfin, and others) can take advantage of the built-in drill-down functionality that hyperlinks (HTTP URIs) bring to any Data Visualization endeavor — by manifesting a launch-point for exploration of a semantic web of linked data that requires only a single-click to serendipitously discover new insights.

Examples

Example: Accessing RDF Data via a SQL Client

Here’s a how-to sequence that walks you through a live example using our visual SPASQL Query Builder, which you can access now at https://tinyurl.com/y79xvcbh.

When challenged to authenticate, as depicted below, enter the value vdb for both username and password:

After authenticating, you will be redirected to a publicly accessible SQL Query-By-Example (QBE) application deployed on a live Virtuoso instance. A pre-populated query will execute, with results presented as depicted below:

Example: Blending SQL and SPARQL for richer data access

SELECT  "PersonInstance"."webid" , 
        "PersonInstance"."name"
FROM
( SPARQL
  SELECT DISTINCT *
  WHERE 
    { ?webid  a          foaf:Person ; 
              foaf:name  ?name       .
      FILTER ( isIRI(?webid) )
      FILTER ( ?name != '' )
      FILTER ( !CONTAINS(STR(?webid),'uriburner') )
    }
  LIMIT 50
) AS "PersonInstance"
WHERE "PersonInstance"."name" LIKE 'D%'

Live Link to Results: https://tinyurl.com/y9cwmhvs

Example: SQL extended by Federated SPARQL (SPARQL-FED)

SELECT  "PersonInstance"."webid" , 
        "PersonInstance"."name"
FROM
  ( SPARQL
    SELECT DISTINCT *
    WHERE 
    { SERVICE <http://linkeddata.uriburner.com/sparql>
      { SELECT DISTINCT ?webid ?name            
        WHERE 
        {
          ?webid  a          foaf:Person ; 
                  foaf:name  ?name       .
                  FILTER ( isIRI(?webid) )
                  FILTER ( ?name != '' )
                  FILTER ( !CONTAINS(STR(?webid),'uriburner' ))
        }
        LIMIT 50
      }
    }
  ) AS "PersonInstance"
WHERE "PersonInstance"."name LIKE 'D%'

Live Link to Results: https://tinyurl.com/yb9oc2vu

How to Extend SPARQL using SQL

SPARQL Read-Write Operations

As SPARQL usage advances, there will inevitably be a need for Write operations.

Virtuoso already provides a solution for the challenges inherent to multi-user Writes, through its ability to apply SQL’s ACID (Atomicity, Consistency, Isolation, and Durability) semantics to transactions.

Here’s a quick summary of Virtuoso SQL function calls that enable various transaction logging modalities:

log_enable () — no transaction logging
log_enable (1,1) — enables transaction logging with manual commits and rollbacks
log_enable (2,1) — disables logging but enables autocommit
log_enable (3,1) — enables logging and enables autocommit

A Virtuoso SPARQL endpoint also provides the DEFINE pragma for exploiting the modalities above:

Query Pattern:

DEFINE sql:log-enable {bitmask}

Usage Example:

DEFINE sql:log-enable 3

The example below illustrates how a SQL Statement creates a Cursor that invokes Exclusive Locking via FOR UPDATE in order to isolate INSERT transaction from the effects of other transactions via Row-Level Locking.

SPARQL 
  CLEAR GRAPH <iud-test> ;SELECT *
  FROM
    (
      SPARQL 
      DELETE
        { GRAPH <iud-test> 
          {?s ?p ?o} 
        }
      INSERT
        { GRAPH <iud-test> 
          { ?webid  a          foaf:Person ; 
                    foaf:name  ?name       .
          }
        }
      WHERE
        { GRAPH ?g 
          { ?webid  a          foaf:Person ; 
                    foaf:name  ?name       .
            FILTER (isIRI(?webid))
            FILTER (?name != ‘’)
            FILTER (!CONTAINS(str(?webid),’uriburner’))
          }
        }
    ) AS "PersonInstance"
FOR UPDATE ;

Verify Effect of SPARQL INSERT

SELECT DISTINCT * 
  FROM 
    ( SPARQL 
      SELECT * 
      FROM <iud-test>
      WHERE { ?s ?p ?o }
    ) AS "PeopleInstance";

Live Link to Results: https://tinyurl.com/yazs4tak

SQL Stored Procedures executed from SPARQL

Stored Procedures (a/k/a Persistent Stored Modules, or PSMs) provide the benefits of code stored, and compiled for execution, in the host RDBMS, with close proximity to the target data on which it operates. This approach to data manipulation also minimizes overhead associated with client-server connections.

Note — Virtuoso’s application layers (i.e., functionality outside the core) are live examples of the power (performance and scalability) that stored procedures can bring to bear.

Example: Stored Procedure `sys_stat` executed from SPARQL

SELECT
 ( bif:sys_stat('st_dbms_name')          AS ?name )
 ( bif:sys_stat('st_dbms_ver')           AS ?version )
 ( bif:sys_stat('st_build_date')         AS ?date )
 ( bif:sys_stat('git_head')              AS ?git_id )
 ( bif:sys_stat('st_build_thread_model') AS ?thread )
 ( bif:sys_stat('st_build_opsys_id')     AS ?opsys )##  uncomment the two lines below to check 
##  license details for Enterprise Edition# ( bif:sys_stat('st_lic_owner')          AS ?owner )
# ( bif:sys_stat('st_lic_serial_number')  AS ?serial )WHERE { }
LIMIT 1

Live Link to Query: https://tinyurl.com/y7ntkw8w
Live Link to Results: https://tinyurl.com/ybbsqhsu

What’s going on here?

As a multi-model RDBMS, Virtuoso provides its users with the ability to declaratively operate on data (entity-relationship-type collections or relations) that are organized as SQL Tables and/or RDF sentences/statements (depictable as Graph Pictorials).

Why is this Important?

Each sample query in this article demonstrates the use of Virtuoso SPASQL to bring SPARQL-generated values into a SQL environment.

These queries can be executed using any Virtuoso SQL interface, or using third-party applications through Virtuoso’s ODBC, JDBC, OLE DB, and ADO.NET drivers. Not having to replace every older tool to get new query language compliance saves time, and lowers adoption costs.

How Virtuoso extends SQL with SPARQL — and vice versa

On-demand Relational Data Virtualization across SQL Tables and RDF Graphs

What does SPARQL offer me?

What does SQL add to SPARQL?

How To Extend SQL using SPARQL

Examples

Example: Accessing RDF Data via a SQL Client

Example: Blending SQL and SPARQL for richer data access

Example: SQL extended by Federated SPARQL (SPARQL-FED)

How to Extend SPARQL using SQL

SPARQL Read-Write Operations

SQL Stored Procedures executed from SPARQL

Example: Stored Procedure `sys_stat` executed from SPARQL

What’s going on here?

Why is this Important?

Related Content

Links

Written by Daniel Heward-Mills

How Virtuoso extends SQL with SPARQL — and vice versa

On-demand Relational Data Virtualization across SQL Tables and RDF Graphs

What does SPARQL offer me?

What does SQL add to SPARQL?

How To Extend SQL using SPARQL

Examples

Example: Accessing RDF Data via a SQL Client

Example: Blending SQL and SPARQL for richer data access

Example: SQL extended by Federated SPARQL (SPARQL-FED)

How to Extend SPARQL using SQL

SPARQL Read-Write Operations

SQL Stored Procedures executed from SPARQL

Example: Stored Procedure sys_stat executed from SPARQL

What’s going on here?

Why is this Important?

Related Content

Links

Written by Daniel Heward-Mills

Example: Stored Procedure `sys_stat` executed from SPARQL