Generating Linked Data Views of SQL Relational Data with OpenLink Virtuoso

Published in

OpenLink Virtuoso Weblog

5 min readMay 4, 2018

On the Importance of Views

Companies worldwide are integrating and migrating toward data solutions outside of traditional SQL-compliant RDBMS (relational database systems). These new solutions are often labeled or branded as “NoSQL,” which is best understood to mean “Not only SQL.” Unfortunately, some solutions that have adopted the “NoSQL” brand ignore their roots and drop the entire concept of relational data, instead reading the label as meaning “Zero SQL.”

Conceptually, Views have generally been considered an integral element of an SQL RDBMS, as they provides users with a non-physical view of Columns from one or many Tables, which may come from one or many Schemas (sometimes incorrectly called Databases).

In practice, this serves as the first layer of data security for an SQL RDBMS as each View only presents the Columns contained within its own parameters.

As a multi-model RDBMS — supporting both SQL and SPARQL for operating on the data it manages — Virtuoso enables the power and utility of SQL views to be replicated and enhanced in a manner that leverages a Semantic Web of Linked Data, courtesy of its ability to generate RDF Views (Virtual RDF Graphs) over SQL Data (Relational Tables). These RDF Views also incorporate core principles associated with Linked Data publication and machine-readable relations semantics via terms defined in a vocabulary or ontology.

Virtuoso’s Multi-Model Architecture for handling interactions with both Tabular Relations (Tables) and RDF Relations (Graphs)

How To Generate Linked Data Views

Virtuoso provides a one-click option, as part of its RDF Views Wizard for generating a basic Linked Data view over Tabular Relations (Tables) scoped to local or remote data sources. As is the case in a conventional SQL RDBMS usage scenario, these views provide a change-sensitive abstraction for operating on data via the SPARQL Query Language.

This feature enables both beginner and advanced users alike to experience enhancements that a Semantic Web of Linked Data brings to Data Flow and Interaction with a minimal learning curve.

Prerequisites:

Admin privileges on an instance of OpenLink Virtuoso, v7.0 or later
Optionally, a working link between Virtuoso Enterprise Edition (this feature is not available in VOS, the Open Source Edition) and a remote table attached through an ODBC DSN (Instructions)

Step 1. Select Table(s) for Generation of Linked Data View(s)

In the Conductor, drill down to Linked Data → Views. Locate the SQL table(s) and/or view(s) for which you wish to generate a Linked Data View. For demo purposes, we’ll leave the Base URI as-generated, but you may change this to a preferred location.

Step 2. Generate and Publish Linked Data View(s)

Tick the boxes for the desired table(s) and/or view(s), and Click on Generate & Publish to automatically generate mappings that translate entity relation expressions from SQL tables to RDF statements.

Click to view the transient ontology generated for the the course table using R2ML
Click to view the transient entity generated for a school course via R2ML

Step 3. View Results

Results of the transient Linked Data view generation can be seen through iSQL, your SPARQL endpoint, or any other ODBC-, JDBC-, ADO.NET-, OLE DB-, or SPARQL-compliant program connecting to your Virtuoso. The transient views named graph is used to query the data source directly, from which results the SQL relational data will be translated into RDF statements using the rules created in the step 2, above.

This can be confirmed with a basic query against the named graph. For this example, we can run the following against our public SPARQL endpoint:

SELECT *
FROM <http://demo.openlinksw.com/School#>
WHERE { ?s ?p ?o }

If you are reproducing this demonstration on your own instance, the SPARQL query can also be run as SPASQL (SPARQL-in-SQL), through iSQL or any SQL-based data interface (ODBC, JDBC, ADO.NET, OLE DB), by prepending the SPARQL keyword, and appending a terminating semicolon (;):

SPARQL
SELECT *
FROM <http://demo.openlinksw.com/School#>
WHERE { ?s ?p ?o } ;

Upon executing the query, you will see the resulting data from your data sources, translated and accessed through a named graph:

Following a successful test query, you can now add conditions to your WHERE clause using the newly generated RDF properties against your data. For example —

PREFIX school: 
   <http://demo.openlinksw.com/schemas/School/>SELECT  ?s AS ?courseURI
              ?courseID 
              ?title
              ?credits
FROM <http://demo.openlinksw.com/School#>
WHERE
 {
   ?s a                school:Course ;
      school:title     ?title        ;
      school:courseid  ?courseID     ;
      school:credits   ?credits      .
      FILTER (contains (?title,"economics") )
 }

The query results show the URI, the ID, the title, and the credits for each school:Course. The HTML table can be copied and pasted into applications such as Microsoft Excel, or imported directly into a Google or Excel Spreadsheet.

Conclusion

Views remain an important conceptual DBMS aspect that should be included when considering a NoSQL environment. Virtuoso’s standing as a hybrid RDBMS allows users to extend the functionality of their SQL RDBMS by supplementing them with the benefits provided by a Semantic Web of Linked Data, rather than “ripping and replacing” legacy infrastructure and systems.