The purpose of this demonstration is to allow hands-on experience on Knowledge Graph databases by executing SPARQL queries side-by-side with their SQL counterparts. It is a learning by example experience, as per illustration below.
SQL
Azure Data Studio
SPARQL
Stardog Studio
Northwind is a well-known properly normalized online e-commerce database which is largely used for training purposes across many database platforms. Refer to w3schools.com for more info.
Try it yourself: The SPARQL queries from this article are available in the RDF Studio Query Library when connected to the Northwind database, which comes pre-installed with a 90-query executable library. If you're not already connected to Northwind, connect via Connect tab first, then navigate to Query -> Library.
The queries used in this demonstration can be downloaded from github here: SQL and SPARQL. They are properly identified on both files and return the same results on the relational and RDF Graph databases.
Our plan is to keep adding queries to this list to cover the most functionality possible between these two query languages. Note that the result sets of the initial queries are not sorted, as ORDER BY is only introduced later in this tutorial.
ORDER BY
Note that the results of both SQL and SPARQL queries will always match, despite of being cropped in the screenshots.
Inner Join
Note that OPTIONAL will be explained later in the " Working with Nulls" section. As an exercise, try and add the “ContactName” and “Address” columns missing in the SPARQL query.
OPTIONAL
# Query: Customers who never placed an order (Using MINUS or FILTER NOT EXISTS) SELECT ?customer ?companyName ?postalCode ?city ?country WHERE { GRAPH ?graph { { ?customer a :Customer ; # All customers :customerID ?customerID ; :companyName ?companyName ; :city ?city ; :country ?country . } FILTER NOT EXISTS # MINUS { ?customer a :Customer . # Customers who placed orders ?order a :Order . ?order :hasCustomer ?customer . } } }
NOT EXISTS and MINUS represent two ways of thinking about negation and they will be explored in more details in a future article.
NOT EXISTS
MINUS
The BIND form allows a value to be assigned to a variable.
BIND
Introduction to Property Paths in SPARQL
The same can be accomplished with two lines of code in SPARQL, using Property Paths.
The property path used in the query above, going from Product to Customer, can be easily identified in the Graph diagram below.
The following are the 6 steps taken to develop the Property Path query.
You can execute each of the following steps directly in RDF Studio. All scripts are available in the query library.
Step 1: Graph pattern traversing order, orderDetail, product and customer bound variables (nodes).
# List order items that contain product-61 SELECT * WHERE { ?orderDetail :hasProduct ?product ; :belongsToOrder ?order . ?order :hasCustomer ?customer . FILTER (?product = :product-61) }
Note the naming convention where bound variable names are declared in CamelCase, whereas the Class names, as seen in the diagram, are declared in PascalCase.
Step 2: Invert the hasProduct path expression to match the following direction: product → orderDetail → order → customer
# The same result is returned SELECT * WHERE { ?product ^:hasProduct ?orderDetail . # Invert direction ?orderDetail :belongsToOrder ?order . ?order :hasCustomer ?customer . FILTER (?product = :product-61) }
Step 3: Use sequence path to omit the binding of the?orderDetail variable.
SELECT * WHERE { ?product ^:hasProduct/:belongsToOrder ?order . ?order :hasCustomer ?customer . FILTER (?product = :product-61) }
Step 4: Use sequence path to omit the binding of the?order variable.
# All customers that bought product-61 # Distinct eliminates duplicates in case the same customer bought a product more than once SELECT DISTINCT * WHERE { ?product ^:hasProduct/:belongsToOrder/:hasCustomer ?customer . FILTER (?product = :product-61) } ORDER BY ?product
Step 5: Bind the filter value to the subject variable directly.
SELECT DISTINCT * WHERE { :product-61 ^:hasProduct/:belongsToOrder/:hasCustomer ?customer . } ORDER BY ?product
Step 6: And finally, omit the binding of the?customer variable and invert the full path back to product.
SELECT (COUNT (1) AS ?Count) WHERE { :product-2 ^:hasProduct/:belongsToOrder/:hasCustomer/ ^(^:hasProduct/:belongsToOrder/:hasCustomer) :product-61 }
For more details on Property Path, please refer to this.
As an exercise, try and add the "Type" column missing in the SPARQL query.
The following SQL queries (using EXISTS and JOIN) produce the same results as the one above.
EXISTS
JOIN
Another example of subquery, this time using aggregate functions.
Inserting a new customer
Checking the new customer
Updating the new customer
Checking the updated data.
Setting up the databases is straightforward, which are made available in the form of Docker Linux images or local install. The client query tools are also available on multiple platforms and installation steps are easy to follow. You should be up and running in a few minutes.
The first step is to download and Install Docker Desktop.
For the SQL Server database, you will pull a Linux image with the sample databases already configured and loaded with data.
Executing the following command from terminal/command line will pull the image and start the container for you.
docker run -it -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Monday*1" -p 1433:1433 --name sql1 -d mbarbieri77/sqlserver2019ubuntu18:latest
The image above contains the following MS Sample Databases. We are going to use the Northwind database in our demonstrations.
Download and Install Azure Data Studio (desktop SQL query tool).
To connect to the Northwind database in the running container, follow the next steps.
Create a new connection.
Fill up the connection details as per screenshot below.
The password for SA user is: Monday1*
After establishing a connection, you can browse the structure of the database in the tree view on the left-hand side.
Execute a test query.
You can stop and start the container when required using the following commands, however, you don’t need to do it now, as the container is already running.
docker stop sql1docker start sql1
Note that changes to the databases running inside the container won’t be lost between restarts.
For Stardog, you will pull a Linux image with the latest version of Stardog Installed and then load the Northwind data contained in a N-Triple file.
Download and Install Docker Desktop, if not already done previously.
Get a Stardog license from their website and save it to a folder on your local machine. This folder should only contain the license file, usually named stardog-license-key.bin
stardog-license-key.bin
From terminal/command line execute the following command to pull the latest Stardog image and start the container.
Note that you need to update the location highlighted below with the full path of the folder where you saved your license.
docker run -it -p 5820:5820 -p 5806:5806 -e STARDOG_EXT=/opt/stardog/ext -e STARDOG_HOME=/var/opt/stardog -v /FULL-PATH-OF-FOLDER-WHERE-YOU-SAVED-YOUR-LICENSE/:/var/opt/stardog --name stardog1 stardog/stardog:latest
Note: port mappings represent -p :.
Install Stardog Studio (browser SPARQL query tool) on the local machine. Select the option “Access Studio Now”, and fill up the form to get access to it.
Open Studio and connect to the database in the running container, by following the next steps.
To connect to the database, fill up the connection details as per screenshot below.
Password: admin
Create a new repository called Northwind.
Select the appropriate icon on the left hand side bar, as per screenshot below.
Fill up the name of the repository and leave all the remaining configurations with their default values and click on “Create”.
Download the Northwind N-Triple file (RDF Dump) from GitHub repository, which contains the data to be loaded into the sample database.
Select “Download”, as per screenshot below and unzip the file on a local folder.
Github
Click on “Load Data” and select the N-Triple file unzipped in the previous step. Set the file format to “Turtle” and click on “Load”.
Replace the http://api.stardog.com/ namespace with http://www.mysparql.com/resource/northwind/ and add the foaf namespace http://xmlns.com/foaf/0.1/, as illustrated below.
To stop and start the Stardog container when required, use the commands below.
You do not need to execute them now, as the container is currently running.
docker stop stardog1docker start stardog1
For GraphDB, we chose a local installation, to give you more options on how to set up your RDF Graph Database.
Download GraphDB Free here.
GraphDB
Install the GraphDB desktop application and then run the App, which will open the following page on the browser:
http://localhost:7200
Note that on a Mac, you may need to allow the App to run under Security & Privacy, as per screenshot below.
MacOS
Select “Create new repository”.
Select GraphDB Free option.
Fill up the create repository form as highlighted below.
Select the Northwind Repository.
Download the Northwind N-Triple file from GitHub repository, which contains the data to be loaded into the sample database.
Follow the steps below to import the dumpdataNTRIPLE7.nt data file.
GraphDB GraphDB GraphDB GraphDB
Execute a simple test query.
Add the foaf http://xmlns.com/foaf/0.1/ and empty namespace http://www.mysparql.com/resource/northwind/ one by one as per illustration below.
GraphDB GraphDB
Note that you may need to supply prefixes when executing queries, as per illustration below.
Individual queries can be executed in Azure Data Studio by highlighting the query and clicking on the green “Run” button, and in Stardog Studio by right-clicking and choosing “Run selected” (Ctrl+Shift+E on Windows or Cmd+Shift+E on MacOS). In GraphDB you must paste and execute queries in the web console one by one.
The Northwind N-Triple file (RDF Dump) can be downloaded from GitHub repository here.
Getting started with the SPARQL (the basics)
