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) andASK
(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 logginglog_enable (1,1)
— enables transaction logging with manual commits and rollbackslog_enable (2,1)
— disables logging but enables autocommitlog_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.
Related Content
- Using a Semantic Web of Linked Data to enhance ODBC, JDBC, ADO.NET, and OLE DB data sources
- Microsoft PowerBI as Launch Point for Exploitation of a Semantic Web of Linked Data
- Tableau as Launch Point for Exploitation of a Semantic Web of Linked Data
- Yellowfin as Launch Point for Exploitation of a Semantic Web of Linked Data
- Microsoft Excel as Launch Point for Exploitation of a Semantic Web of Linked Data
- CSV Documents and Tableau Public as Launch Point for Exploitation of a Semantic Web of Linked Data
- Using SPARQL-Sourced CSV Documents and Airtable as Launch Point for Exploitation of a Semantic Web of Linked Data
- ACID and Transactions
- Relational Database & Graph Database Terminology Concerns