How Virtuoso extends SQL with SPARQL — and vice versa

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

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

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)

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:

Image for post
Image for post

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:

Image for post
Image for post

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

Image for post
Image for post

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

Image for post
Image for post

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

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

Image for post
Image for post

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

Image for post
Image for post
Query Results

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.

Related Content

Links

OpenLink Virtuoso Weblog

News & Articles related to OpenLink Virtuoso & Related…

Daniel Heward-Mills

Written by

Technical Specialist @ OpenLink Software: https://www.linkedin.com/in/daniel-heward-mills-a0940465/

OpenLink Virtuoso Weblog

News & Articles related to OpenLink Virtuoso & Related Technologies

Daniel Heward-Mills

Written by

Technical Specialist @ OpenLink Software: https://www.linkedin.com/in/daniel-heward-mills-a0940465/

OpenLink Virtuoso Weblog

News & Articles related to OpenLink Virtuoso & Related Technologies

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store