Using Virtuoso’s ODBC and JDBC Drivers to bring LODCloud Knowledge Graph exploitation to existing SQL-based Apps & Services

Tools & Utilities
, , , ,
1

OPL-twitter-lod-graph-access-2 (1)
OPL-twitter-lod-graph-access-2 (1)800×418 111 KB

OpenLink Software’s company vision since its inception in 1992 has been:

“Empowerment of Individuals and Enterprises via Open Access to Data.”

Today, more than ever, this is imperative. In our world’s fight against COVID-19, we should should all be unleashing the Force of the LOD Cloud Knowledge Graph.

Our CEO, Kingsley Idehen, has published a variety of articles that demonstrate practical and agile uses of RDF, Linked Data, and SPARQL, aimed at minimizing the overhead associated with Data Connectivity and Data Wrangling, and at bringing the full Force of the massive LOD Cloud Knowledge Graph to bear on this global pandemic.

In addition to the Data Wrangling issues covered in those posts, it is important to understand that all the data, information, and knowledge in the LOD Cloud Knowledge Graph is also available to any ODBC-, JDBC-, ADO.NET-, or OLE DB-compliant application or service using SQL. Yes! It means exactly that — i.e., this federation of knowledge is immediately usable by existing tools for producing Dashboards, Business Intelligence Reports, Data Science Notebooks (like Jupyter, among others), and anything else that uses SQL to operate on data.

What is our LOD Cloud Connectivity License?

The LOD Cloud Connectivity License is a single license (in the form of an X.509 Certificate) which enables you to use any deployed Virtuoso ODBC, JDBC, OLE DB, or ADO.NET client driver or provider to query over the massive LOD Cloud Knowledge Graph, via our URIBurner service.

Fundamentally, this enables the magic demonstrated by the Web to be applied to Data Access & Connectivity, in a manner that supports your existing SQL-based apps and services, without disruption.

LOD_Driver
LOD_Driver1544×1194 114 KB

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

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

Putting the LOD Connectivity License to use

First, Acquire a LOD Cloud Connectivity License

  1. Select the desired Virtuoso Data Access Driver for World Wide Web RDBMS License from our current Offers page.

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) 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 (b) a Private Key which will be used for Digital Signatures 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 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, ca_list_shop_2016.pem, for the LOD Connectivity License, found here.

  5. As above, double-click on the ca_list_shop_2016.pem file to also import it 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 (a/k/a SPASQL) connections to Virtuoso.

Third, Query the LOD Cloud with your preferred tool; examples follow

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

  1. The command below uses the basic iSQL launch string, including the host and port of the target Virtuoso instance (linkeddata.uriburner.com:1113), and instructing iSQL to use the PKCS#12 file (-X uriburner_lod_latest.p12) and the Certificate Authority Trust Chain file (-T ca_list_shop_2016.pem) downloaded earlier. When using these files for authentication, there is no need to provide a username or password to iSQL nor Virtuoso.

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

  2. Upon successful connection, you will have the usual SQL> prompt, where you can execute any valid SQL or SPASQL query.

    screenshot_of_command-line_interaction.png

b. via ODBC, using the iODBC Demo ‘C’ application

  1. DSN Setup — DSN tab — Identifying Virtuoso Server’s Hostname & TLS (SSL) port

    screenshot_of_DSN_setup.png

  2. DSN Setup — Connection tab (1) — Setting Up TLS (SSL) Connectivity — Associating PKCS#12 Credentials File with DSN

    screenshot_of_TLS_setup(1)_within_DSN.png

  3. DSN Setup — Connection tab (2) — Associating Certificate Authority (CA) Trust Chain File with DSN

    screenshot_of_TLS_setup(2)_within_DSN.png

  4. DSN Setup — Connection tab (3) — Setting Up TLS (SSL) Connectivity — Providing Password for PKCS#12 File Access en route to testing TLS connectivity

    screenshot_of_TLS_setup(3)_within_DSN.png

  5. DSN Setup — Options tab

    screenshot_of_TLS_setup(4)_within_DSN.png

Now we prove that Database Connectivity over TLS using LOD Connectivity License is functional.

  1. DSN Selection

    screenshot_of_TLS_setup(5)_within_DSN.png

  2. Authentication

    screenshot_DSN_selection.png

  3. SPARQL Query

    screenshot_of_authentication.png

  4. Query Solution (Results)

    screenshot_of_SPARQL_Query.png

c. via JDBC, using the JDBCDemo Java application

  1. You can start the Virtuoso JDBC Demo app by executing the following command from its installation folder; for instance, on macOS —

    /Library/Application Support/openlink/samples/JDBC/jdk1.8/JDBCDemo

    java -cp .:./virtjdbc4_2ssl.jar JDBCDemo

  2. Virtuoso JDBC URLs using TLS/SSL follow the pattern below:

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

    Here is a 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

  3. From the File menu, select “Set Connection URL…”, and you’ll get the dialog shown below. Put “virtuoso.jdbc4.Driver” into the Driver Name box, and put the URL you built from the pattern shown above into the Connection Url box.

    screenshot_of_connection_setup_dialog.png

  4. Into the Query text box, input any SQL query — or, with the simple addition of the keyword SPARQL at the start, any SPARQL query, such as this:

    screenshot_of_query_input_dialog.png

  5. See the results that return, with live URLs that can start you on follow-your-nose exploration and discovery.

    screenshot_of_results.png

Why 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 a Knowledge Graph query and exploration tool, without requiring any changes to the application nor to your basic usage patterns — courtesy of Linked Data, i.e., 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 portable, desktop, or application server.

Related