Virtuoso ODBC, JDBC, and ADO.NET Drivers for the LOD Cloud Knowledge Graph

Virtuoso Universal Server Tips, Tricks, and HowTos
, , , ,
1

Using Virtuoso ODBC and JDBC Drivers (Connectors) to interact with the LOD Cloud Knowledge Graph using SQL and/or SPARQL

Situation Analysis

SQL-based applications dominate the landscape of enterprise solutions and services aimed at increasing agility through insights extracted from transactional systems of record . Unfortunately, SQL as a structured query language is challenged on three critical fronts:

  • Semantic Expressivity — use of fine-grained relationship type (relations) semantics in query definitions
  • Data Structures — relationship types are represented in a wide variety of ways (tables, sentence/statement graphs, hierarchical trees, etc.)
  • Data Federation — data is found in a variety of forms and locations

The last challenge arises even in situations where you have the same SQL-compliant RDBMS in use at disparate locations, within the same enterprises. For instance, imagine the exponential difficulty associated with the need to integrate data from multiple SQL-compliant (but not fully interoperable) RDBMS instances, as well as from external data sources like DBpedia, Wikidata, and the many other rich data sources found across the Linked Open Data (LOD) Cloud Knowledge Graph, and even further around the rest of the World Wide Web).

The Largest Public Knowledge Graph on Earth — illustrated using the Linked Open Data Cloud Pictorial

This post covers the use of our URIBurner Service — a Virtuoso instance that functions as a “Data Junction Box in the Cloud” —and Virtuoso’s ODBC and JDBC drivers as a cost-effective solution for enhancing SQL (one standard) with SPARQL (another open standard), en route to addressing the challenges outlined above.

Note:

The usage examples covered here focus on the use of SQL queries that are enhanced via inclusion of SPARQL Queries (for semantic expressivity and federation) in the FROM clause. The net effect is the ability to produce “webby” query solutions with any ODBC or JDBC application.

A simple example

Here is a SQL query that’s been enhanced by way of a SPARQL Query functioning like a conventional SQL View in the FROM clause. In addition, notice how SPARQL-Federation has been used to incorporate data from DBpedia — dynamically queried from the main DBpedia endpoint, not from the local Virtuoso DB — as part of the data sources used in the SQL query evaluation.

Remember, DBpedia is a highly connected Knowledge Graph within the massive LOD Cloud.

SELECT DISTINCT DBpedia.name AS "Actor Label",
                DBpedia.actor AS "DBpdia ID",
                DBpedia.school AS "School ID",
                DBpedia.schoolLabel AS "School Label"
FROM
(
    SPARQL
    PREFIX dct: <http://purl.org/dc/terms/>

    SELECT *
    WHERE {
            SERVICE <http://dbpedia.org/sparql>
            {
                ?actor
                dct:subject <http://dbpedia.org/resource/Category:Tony_Award_winners> ;
                <http://dbpedia.org/ontology/almaMater> ?school ;
                foaf:name ?name .
                ?school
                rdfs:label ?schoolLabel .
                FILTER (LANG(?name) = “en”)
                FILTER (LANG(?schoolLabel)= “en”)
            }
          }
    ORDER BY ASC(?school)
    LIMIT 100
) AS DBpedia

Example Results

Query Solutions herein will be presented by applications that demonstrates the inclusion of hyperlinks as Super Keys in the webby query solution. Net effect, you can click on any hyperlink that piques your interest, as part of a natural drill-down process.

First, an ODBC-compliant application, iODBC Demo Ansi.app , our enhanced version of the long-used C++Demo —

SQL Query Results from the DBpedia Knowledge Graph via an ODBC Connection

Same query result presented via a JDBC-compliant application; here, our JDBCDemo —

SQL Query Results from the DBpedia Knowledge Graph via a JDBC Connection

What is our LOD Connectivity License Offer about?

A LOD Cloud Connectivity License enables query access massive LOD Cloud Knowledge Graph via our URIBurner service — a powerful conduit into the Linked Open Data Cloud — through any deployed Virtuoso ODBC, JDBC, OLE DB, or ADO.NET client driver or provider.

How to obtain Virtuoso Data Access Drivers for a variety of platforms

Simply click on the links that follow, to obtain an installer for the platform that hosts your data-consuming application or service.

Putting the LOD Connectivity License to use

First, Acquire LOD Cloud Connectivity License

  1. Visit LOD Cloud Connectivity License Offer page

  1. Purchase License

Second, Install the LOD Cloud Connectivity License

  1. Download the PKCS#12 file generated by our online shop system. This file is a secure credential store (also known as a key store ) that holds a Token (in the form of an X.509 certificate) that represents your license to use the services provided by our URIBurner Service (a Data Junction Box for the Web’s Linked Open Data Cloud) and a Private Key which will be used for Digital Signature over secure TLS sessions.
  2. Rename the PKCS#12 file using a shorter file name. Our filenames are useful for distinguishing between different purchases, but can be unwieldy for active use. You may want to change, for instance, VirtuosoDataAccessDriverfortheWorldWideWebRDBMS — 30DayLicenseforaSingleNetworkClient_2017–05–000021–001–001.p12 to uriburner_lod_latest.p12.
  3. Double-click on the renamed file to trigger automatic import and backup of the PKCS#12 file to the host OS Key Store. (This works in most macOS and Windows environments, and some Linux. Users of other OS may need to take some additional steps.)
  4. Download the Certificate Authority Trust Chain file for the LOD Connectivity License.
  5. As above, double-click on this file to also import and back it up to your local key store.

You are now ready to put your LOD Connectivity License to use — to query data across the Web using SPARQL–inside–SQL connections to Virtuoso.

Third, Query the LOD Cloud

via iSQL (Virtuoso native client), using the Command Line

./isql-new linkeddata.uriburner.com:1113 dba 1243 -X uriburner_lod_latest.p12 -T ca_list_shop_2016.pem

via ODBC, using the iODBC Demo ‘C’ application

DSN Setup

Setting Up TLS (SSL) Connectivity — Identifying Virtuoso Server’s Hostname & TLS port

Setting Up TLS (SSL) Connectivity — Associating PKCS#12 Credentials File with DSN

Setting Up TLS (SSL) Connectivity — Associating Certificate Authority (CA) Trust Chain File with DSN

Setting Up TLS (SSL) Connectivity — Providing Password for PKCS#12 File Access en route to testing TLS connectivity

Proof that Database Connectivity over TLS using LOD Connectivity License is in place

DSN Selection

Authentication

SPARQL Query

Query Solution (Results)

via JDBC, using the JDBCDemo Java application

You can start the Virtuoso JDBC Demo app by executing the following command from its installation folder ( e.g., /Library/Application\ Support/openlink/samples/JDBC/jdk1.8/JDBCDemo on macOS):

java -cp .:./virtjdbc4_2.jar JDBCDemo

Pattern for Virtuoso JDBC URLs

jdbc:virtuoso://<host>:<port>/charset=UTF-8/SSL/kpass=<pkcs-file-password>/kpath=<pkcs-file-name>/ts=<ca-trust-chain-file-name>

Sample Virtuoso JDBC URL

jdbc:virtuoso://linkeddata.uriburner.com:1113/charset=UTF-8/SSL/kpass=1234/kpath=uriburner_lod_latest.p12/ts=ca_list_shop_2016.pem

What is this important?

As you can see, any basic standards-compliant application, whether using ODBC, JDBC, ADO.NET, or OLE DB data access, has instantly morphed into Knowledge Graph query and exploration tool, without requiring any changes to the application nor to your basic usage patterns — courtesy of Linked Data (hyperlinks functioning as Super Keys).

Your existing RDBMS applications and services can now take full advantage of the power unleashed by Knowledge Graphs such as the massive LOD Cloud and/or your own internal equivalents without “ripping and replacing” anything. All it takes is installation of the relevant Virtuoso client (e.g., ODBC, JDBC, ADO.NET, or OLE DB) on your notebook, desktop, or application server.

Related