OpenLink Data Junction Box -- Pay-As-You-Go Edition for Amazon AWS Cloud

[hwilliams]

Content Index

• Introduction
• Deployment via AWS Marketplace Offer Page
• Administration
  • Administration via SSH
  • Administering Virtuoso Instance via HTTP
  • Administering OpenLink Multi-Tier Data Access Driver Installation
  • ODBC & JDBC Connectivity Administration
• ODBC Connectivity Examples
  • ODBC Connectivity using iODBC TEST
  • ODBC Connectivity using PyODBC
  • ODBC Connectivity using Node.js
  • ODBC Connectivity Example using GO Language
  • ODBC & Python Example using a Jupyter Notebook
• JDBC Connectivity
  • JDBC Connectivity Example
• REST-ful Interaction Examples using SPARQL
  • SPARQList Service
• GraphQL Middleware Usage
  • Grasp
  • Hypergraph
• Virtuoso SQLAlchemy Connector
• OpenLink Personal Assistant
• Performance Tuning
• Troubleshooting

Introduction

This document details the process for deploying the OpenLink Virtuoso + Universal Data Access Drivers Bundle via a Pay-As-you-GO (PAGO) Edition Marketplace Offer on the Amazon AWS Marketplace.

This AMI provides a powerful Data Junction Box for building Data Meshes that leverage the combined power of our Virtuoso and Universal Data Access Drivers for ODBC, JDBC, ADO.NET.

Prerequisites

• An Amazon Web Services account

Deployment via AWS Marketplace Offer Page

1. Goto the AWS Marketplace OpenLink Data Junction Box offer page:
   
   
   
   
   
   
   
2. Click on the Continue to Subscribe button to start the subscription and deployment of the BYOL offer.
   
   
   
   
   
   
   
3. Click on the Accept Term button to commence the deployment process in the AWS Marketplace.
   
   
   
   
   
   
   
4. Click on the Continue to Configuration button to subscribe to the offer
   
   
   
   
   
   
   
   
   
   
   
   
   
5. Select the EC2 Instance Type, VPC Settings, Subnet Settings, Security Group Settings and Key Pair Settings to be used for the deployment and click on the Continue to Launch button to launch the software. Note the Security Group used must allow access to the ports 8890 and 1111 for HTTP and SQL access to the Virtuoso server respectively; ports 8000 and 5000-5010 for the Universal Data Access Multi-Tier drivers; port 8888 for Jupyter; port 4000 for Grasp; port 3000 for SPARQList; port 8080 for Hypergraph and port 22 for SSH access which is allowed by default.
   
   
   
   
   
   
   
6. The successfull deployment page is displayed and clicking on the EC2 Console enabled the new instance deployed to be seen in the EC2 Management Console.
   
   
   
   
   
   
   
7. Once the instance is in the Running state deployment is complete and available for use

Administration

Administration via SSH

1. Make a ssh connection to the VM using the public key (pem-file) and username (ubuntu by default) chosen when creating the deployment, and the Public IP address from the previous section as follows:

    ssh -i {pem-file} ubuntu@{Public IP address}

2. Once connected it is strongly recommended to update the VM to get the latest operating system and Virtuoso updates with the command:

    sudo apt-get upgrade

3. Check the Virtuoso server is automatically started post deployment with the command:

    sudo service virtuoso status

4. The following commands can be used to Administer the Virtuoso server:

• Start the Virtuoso Server:

    sudo service virtuoso start

• Stop the Virtuoso Server:

   sudo service virtuoso stop

• Restart the Virtuoso Server:

    sudo service virtuoso restart

• Check status of Virtuoso Server:

    sudo service virtuoso status

5. Determine the random password set for the dba user with the command:

    sudo cat /opt/virtuoso/database/.initial-password

6. A SQL connection can then be made Virtuoso with the isql command line tool with the command on port 1111:

    isql 1111

7. Typical output for running these steps are:

$ ssh -i certificates/virtuoso.pem ubuntu@54.221.25.206
The authenticity of host '54.221.25.206 (54.221.25.206)' can't be established.
ECDSA key fingerprint is SHA256:QGsOFcQoa4x5DBavtdHWDQUUQtBdHJ/OkizKep8UOcM.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '54.221.25.206' (ECDSA) to the list of known hosts.
Welcome to Ubuntu 18.04.5 LTS (GNU/Linux 5.4.0-1025-aws x86_64)

 * Documentation:  https://help.ubuntu.com
 * Management:     https://landscape.canonical.com
 * Support:        https://ubuntu.com/advantage

  System information as of Fri Jan 29 12:41:03 UTC 2021

  System load:  0.0                Processes:           104
  Usage of /:   2.0% of 116.27GB   Users logged in:     0
  Memory usage: 4%                 IP address for eth0: 10.0.0.214
  Swap usage:   0%


 * Canonical Livepatch is available for installation.
   - Reduce system reboots and improve kernel security. Activate at:
     https://ubuntu.com/livepatch

9 packages can be updated.
0 updates are security updates.


Last login: Tue Sep 22 19:26:19 2020 from 108.26.205.225
ubuntu@ip-10-0-0-214:~$ cd /opt/virtuoso/database
ubuntu@ip-10-0-0-214:/opt/virtuoso/database$ sudo bash
root@ip-10-0-0-214:/opt/virtuoso/database# cat .initial-password 
i-0343ad51fe5e4f196
root@ip-10-0-0-214:/opt/virtuoso/database# service virtuoso status
● virtuoso.service - OpenLink Virtuoso Database
   Loaded: loaded (/lib/systemd/system/virtuoso.service; enabled; vendor preset: enabled)
   Active: active (running) since Fri 2021-01-29 12:04:31 UTC; 38min ago
  Process: 878 ExecStart=/opt/virtuoso/bin/virtuoso-start.sh $VIRTUOSO_DB_NAMES (code=exited, status=0/SUC
 Main PID: 1170 (virtuoso)
    Tasks: 15 (limit: 4915)
   CGroup: /system.slice/virtuoso.service
           └─1170 ./virtuoso

Jan 29 12:04:25 ip-10-0-0-214 systemd[1]: Starting OpenLink Virtuoso Database...
Jan 29 12:04:26 ip-10-0-0-214 virtuoso-start.sh[878]: Starting Virtuoso instance in [database]
Jan 29 12:04:26 ip-10-0-0-214 virtuoso-start.sh[878]:   - Starting the database
Jan 29 12:04:31 ip-10-0-0-214 systemd[1]: Started OpenLink Virtuoso Database.
root@ip-10-0-0-214:/opt/virtuoso/database# /opt/virtuoso/bin/isql 1111
OpenLink Virtuoso Interactive SQL (Virtuoso)
Version 08.03.3319 as of Sep  1 2020
Type HELP; for help and EXIT; to exit.

Enter password for dba :
Connected to OpenLink Virtuoso
Driver: 08.03.3319 OpenLink Virtuoso ODBC Driver
SQL> status('');
REPORT
VARCHAR
_______________________________________________________________________________

OpenLink Virtuoso VDB Server
Version 08.03.3319-pthreads for Linux as of Sep  1 2020 
Started on: 2021-01-29 12:45 GMT+0
CPU: 0.05% RSS: 148MB PF: 0
 
Database Status:
  File size 67108864, 8192 pages, 5733 free.
  20000 buffers, 1115 used, 85 dirty 0 wired down, repl age 0 0 w. io 0 w/crsr.
  Disk Usage: 1074 reads avg 0 msec, 0% r 0% w last  23 s, 138 writes flush          0 MB/s,
    34 read ahead, batch = 17.  Autocompact 0 in 0 out, 0% saved.
Gate:  166 2nd in reads, 0 gate write waits, 0 in while read 0 busy scrap. 
Log = virtuoso.trx, 8325 bytes
VDB: 0 exec 0 fetch 0 transact 0 error
2309 pages have been changed since last backup (in checkpoint state)
Current backup timestamp: 0x0000-0x00-0x00
Last backup date: unknown
Clients: 1 connects, max 1 concurrent
RPC: 6 calls, 1 pending, 1 max until now, 0 queued, 0 burst reads (0%), 0 second 0M large, 10M max
Checkpoint Remap 38 pages, 0 mapped back. 0 s atomic time.
    DB master 8192 total 5733 free 38 remap 1 mapped back
   temp  256 total 251 free
 
Lock Status: 0 deadlocks of which 0 2r1w, 0 waits,
   Currently 1 threads running 0 threads waiting 0 threads in vdb.

24 Rows. -- 2 msec.
SQL> 

Administering Virtuoso Instance via HTTP

1. The Virtuoso http server can be accessed via http://{Public IP Address}:8890:
   
   
   
   
   
   
   
2. Click on the Conductor link in the left frame to access the Virtuoso Conductor Admin UI.
   
   
   
   
   
   
   
3. Use the Virtuoso dba username and password determined in Step 5 of the previous section to login to the Conductor
   
   
   
   
   
   
   

Administering OpenLink Multi-Tier Data Access Driver Installation

The OpenLink Multi-Tier Universal Data Access installation is automatically started on AMI deployment or startup.

1. The OpenLink Request Broker service status can be checked with the command:

sudo service oplrqb status

2. The following commands can be used to Administer the OpenLink Request Broker server:

• Start the OpenLink Request Broker Server:

    sudo service oplrqb start

• Stop the OpenLink Request Broker Server:

   sudo service oplrqb stop

• Restart the OpenLink Request Broker Server:

    sudo service oplrqb restart

• Check status of OpenLink Request Brokero Server:

    sudo service oplrqb status

3. The OpenLink Multi-Tier installation can be administered using the HTTP based OpenLink Admin Assistant running on port 8000 ie http://{Public IP Address}:8000, with default username/password of admin/admin

ODBC & JDBC Connectivity Administration

ODBC and JDBC Connectivity Drivers are provided for the following databases:

• Virtuoso
• Microsoft SQL Server
• Oracle
• MySQL
• PostgreSQL
• JDBC Bridge
• ODBC Bridge

With the following default ODBC DSNs available in /etc/odbc.ini:

[ODBC Data Sources]
Local Virtuoso           = OpenLink Virtuoso
Local Virtuoso (Unicode) = OpenLink Virtuoso
sqlmu                    = OpenLink Generic ODBC Driver (Unicode)
mys5mu                   = OpenLink Generic ODBC Driver (Unicode)
pgr7mu                   = OpenLink Generic ODBC Driver (Unicode)
jdbcmu                   = OpenLink Generic ODBC Driver (Unicode)
odbcmu                   = OpenLink Generic ODBC Driver (Unicode)
ora12mu                  = OpenLink Generic ODBC Driver (Unicode)

[Local Virtuoso]
Driver  = OpenLink Virtuoso
Address = localhost:1111

[Local Virtuoso (Unicode)]
Driver  = OpenLink Virtuoso
Address = localhost:1111

[sqlmu]
Driver          = /opt/uda/lib/oplodbcu.so
ServerType      = SQLServer
Username        = {username}
Password        = {password}
Database        = {database}
Options         = -H {hostname} -P {portno}
FetchBufferSize = 99
ReadOnly        = No
Host            = localhost:5000
Description     = SQL Server  Connection
UseSSL          = No
NoLoginBox      = No

[mys5mu]
Driver          = /opt/uda/lib/oplodbcu.so
ServerType      = MySQL 5.x
Username        = {username}
Password        = {password}
Database        = {database}
Options         = -H {hostname} -P {portno}
FetchBufferSize = 99
ReadOnly        = no
DeferLongFetch  = no
Host            = localhost:5000
Description     = MySQL 5.x Connection

[pgrmu]
Driver          = /opt/uda/lib/oplodbcu.so
ServerType      = PostgreSQL
Username        = {username} 
Password        = {password} 
Database        = {database} 
Options         = -H {hostname} -P {portno}
FetchBufferSize = 99
ReadOnly        = no
DeferLongFetch  = no
Host            = localhost:5000
Description     = PostgreSQL Connection

[odbcmu]
Driver          = /opt/uda/lib/oplodbcu.so
ServerType      = Odbc
Username        = {username} 
Password        = {password} 
Database        = {ODBC DSN Name} 
Options         = 
FetchBufferSize = 99
ReadOnly        = no
DeferLongFetch  = no
Host            = localhost:5000
Description     = ODBC Bridge Connection

[jdbcmu]
Driver          = /opt/uda/lib/oplodbcu.so
ServerType      = Jdbc 1.8
Username        = {username} 
Password        = {password} 
Database        = {JDBC Driver Class Name} 
Options         = {JDBC Driver Connect String} 
FetchBufferSize = 99
ReadOnly        = No
Host            = localhost:5000
Description     = JDBC Brige Connection
UseSSL          = No
NoLoginBox      = No

[ora12mu]
Driver          = /opt/uda/lib/oplodbcu.so
ServerType      = Oracle 12.x
Username        = {username}
Password        = {password}
Database        = {database}
Options         = {Instant Client Connect String} 
FetchBufferSize = 99
ReadOnly        = no
DeferLongFetch  = no
Host            = localhost:5000
Description     = Oracle 12.x Connection

ODBC Connectivity Examples

ODBC Connectivity using iODBC TEST

The Live Virtuoso Demo Database can be used to test both Virtuoso and UDA ODBC connections as follows. Sample DSNs can be created for connecting to this database by either editing the /etc/odbc.ini file directly or remotely using the OpenLink Admin Assistant ( http://{Public IP Address}:8000). For this demonstration we will be editing the /etc/odbc.ini file directly and testing with the available sample programs from command line.

1. Run the following script to setup a working ODBC test environment:

. /opt/uda/openlink.sh

2. Create a new Virtuoso DSN called Live Virtuoso Demo in /etc/odbc.ini as follows:

[ODBC Data Sources]
Live Virtuoso Demo      = OpenLink Virtuoso
.
.
,
[Live Virtuoso Demo]
Driver  = /opt/virtuoso/lib/virtodbc_r.so
Address = demo.openlinksw.com:21118

3. The Virtuoso Live Virtuoso Demo DSN can be used to make a UDA Multi-Tier ODBC Bridge connection using the default odbcmu DSN by adding it as the Database params value in /etc/odbc.ini as follows:

[odbcmu]
Driver          = /opt/uda/lib/oplodbcu.so
ServerType      = Odbc
Username        = 
Password        = 
Database        = Live Virtuoso Demo
Options         =
FetchBufferSize = 99
ReadOnly        = no
DeferLongFetch  = no
Host            = localhost:5000
Description     = ODBC Bridge Connection

4. Make and ODBC connection to the Virtuoso Demo database with username/password of demo/demo with the Live Virtuoso Demo DSN and iodbctest program, then query the Demo..orders table as follows:

ubuntu@ip-10-0-0-61:/opt/virtuoso/$ iodbctest "DSN=Live Virtuoso Demo;UID=demo;PWD=demo"
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.1419.0723
Driver: 08.03.3322 OpenLink Virtuoso ODBC Driver (virtodbc.so)

SQL>select top 2 * from Demo..orders

OrderID    |CustomerID|EmployeeID |OrderDate          |RequiredDate|ShippedDate        |ShipVia    |Freight           |ShipName                                |ShipAddress                                                 |ShipCity       |ShipRegion     |ShipPostalCode|ShipCountry    |ShipCountryCode|ROWGUID                                                                                                                                                                                                                                                        
-----------+----------+-----------+-------------------+------------+-------------------+-----------+------------------+----------------------------------------+------------------------------------------------------------+---------------+---------------+--------------+---------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10248      |VINET     |5          |1994-08-04 00:00:00|1994-09-01 0|1994-08-16 00:00:00|3          |32.38             |Vins et alcools Chevalier               |59 rue de l'Abbaye                                          |Reims          |***************|51100         |France         |fr             |52131A6C-3B9C-11EA-B11F-DBF410C84CF6                                                                                                                                                                                                                           
10249      |TOMSP     |6          |1994-08-05 00:00:00|1994-09-16 0|1994-08-10 00:00:00|1          |11.61             |Toms Spezialit?ten                      |Luisenstr. 48                                               |M?nster        |***************|44087         |Germany        |gm             |52131A94-3B9C-11EA-B11F-DBF410C84CF6                                                                                                                                                                                                                           

 result set 1 returned 2 rows.

SQL>

5. Similar connection and query can then be made using the UDA Multi-Tier odbcmu DSN configured previously:

ubuntu@ip-10-0-0-61:/opt/virtuoso/$ iodbctest "DSN=odbcmu;UID=demo;PWD=demo"
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.1419.0723
Driver: 08.00.1610 OpenLink Generic ODBC Driver (Unicode) (oplodbcu.so)

SQL>select top 2 * from Demo..orders

OrderID    |CustomerID|EmployeeID |OrderDate          |RequiredDate|ShippedDate        |ShipVia    |Freight           |ShipName                                |ShipAddress                                                 |ShipCity       |ShipRegion     |ShipPostalCode|ShipCountry    |ShipCountryCode|ROWGUID                                                                                                                                                                                                                                                        
-----------+----------+-----------+-------------------+------------+-------------------+-----------+------------------+----------------------------------------+------------------------------------------------------------+---------------+---------------+--------------+---------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10248      |VINET     |5          |1994-08-04 00:00:00|1994-09-01  |1994-08-16 00:00:00|3          |32.380000000000003|Vins et alcools Chevalier               |59 rue de l'Abbaye                                          |Reims          |***************|51100         |France         |fr             |52131A6C-3B9C-11EA-B11F-DBF410C84CF6                                                                                                                                                                                                                           
10249      |TOMSP     |6          |1994-08-05 00:00:00|1994-09-16  |1994-08-10 00:00:00|1          |11.609999999999999|Toms Spezialit?ten                      |Luisenstr. 48                                               |M?nster        |***************|44087         |Germany        |gm             |52131A94-3B9C-11EA-B11F-DBF410C84CF6                                                                                                                                                                                                                           

 result set 1 returned 2 rows.

SQL>

ODBC Connectivity using PyODBC

Courtesy of the sample program pyodbc-virt.py, situated in /opt/virtuoso/pyodbc, you can query the Virtuoso Demo database using the Live Virtuoso Demo DSN:

ubuntu@ip-10-0-0-61:/opt/virtuoso/pyodbc$ python3 pyodbc-virt.py 
    CustomerID                                     ContactName
0   ALFKI                        Maria Anders
1   ANATR                        Ana Trujillo
2   ANTON                    Antonio Moreno
3   AROUT                        Thomas Hardy
4   BERGS            Christina Berglund
..         ...                                             ...
86  WARTH                Pirkko Koskitalo
87  WELLI                      Paula Parente
88  WHITC                    Karl Jablonski
89  WILMK                  Matti Karttunen
90  WOLZA  Zbyszek Piestrzeniewicz

[91 rows x 2 columns]
ubuntu@ip-10-0-0-61:/opt/virtuoso/pyodbc$

ODBC Connectivity using Node.js

Similarly, using the node-odbc sample program situated in node-virt.js provided in /opt/virtuoso/node-odbc, you can also query the Virtuoso Demo database using the Live Virtuoso Demo DSN:

ubuntu@ip-10-0-0-61:/opt/virtuoso/node-odbc$ node node-virt.js 
[
  { CustomerID: 'ALFKI', ContactName: 'Maria Anders' },
  { CustomerID: 'ANATR', ContactName: 'Ana Trujillo' },
  { CustomerID: 'ANTON', ContactName: 'Antonio Moreno' },
  { CustomerID: 'AROUT', ContactName: 'Thomas Hardy' },
  { CustomerID: 'BERGS', ContactName: 'Christina Berglund' },
  statement: 'SELECT TOP 5 CustomerID, ContactName FROM Demo..Customers',
  parameters: undefined,
  return: undefined,
  count: -1,
  columns: [
    {
      name: 'CustomerID',
      dataType: 12,
      columnSize: 5,
      decimalDigits: 0,
      nullable: false
    },
    {
      name: 'ContactName',
      dataType: 12,
      columnSize: 30,
      decimalDigits: 0,
      nullable: true
    }
  ]
]
ubuntu@ip-10-0-0-61:/opt/virtuoso/node-odbc$ 

ODBC Connectivity Example using GO Language

1. Set the following script to setup the required environment variables for go-odbc:

. /opt/uda/openlink.sh

2. Goto /opt/virtuoso/go-odbc and run the sample.go example program:

go run sample.go

3. The sample program returns the follow data:

ubuntu@ip-10-0-0-61:/opt/virtuoso/go-odbc$ go run sample.go
warning: GOPATH set to GOROOT (/usr/local/go) has no effect
Number of fields 16
	Field: 1 Name: OrderID
	Field: 2 Name: CustomerID
	Field: 3 Name: EmployeeID
	Field: 4 Name: OrderDate
	Field: 5 Name: RequiredDate
	Field: 6 Name: ShippedDate
	Field: 7 Name: ShipVia
	Field: 8 Name: Freight
	Field: 9 Name: ShipName
	Field: 10 Name: ShipAddress
	Field: 11 Name: ShipCity
	Field: 12 Name: ShipRegion
	Field: 13 Name: ShipPostalCode
	Field: 14 Name: ShipCountry
	Field: 15 Name: ShipCountryCode
	Field: 16 Name: ROWGUID

ubuntu@ip-10-0-0-61:/opt/virtuoso/go-odbc$

ODBC & Python Example using a Jupyter Notebook

Start the Jupyter Notebook service in a screen session as follows:

1. Ensure the following entry is in the file $HOME/.jupyter/jupyter_notebook_config.py of the user account running from, to start the service running on all network interfaces such that it is accessible publicly :

$ cat $HOME/.jupyter/jupyter_notebook_config.py 
c.NotebookApp.ip = '0.0.0.0'
$

2. Start a screen session for interacting with the Jupyter Notebook server:

screen -S jupyter

3. Start the Jupyter Notebook Server service by executing the command:

sudo jupyter-notebook --allow-root

4. Connect the the Jupyter Notebook server with browser URL http://{Public IP Address}:8888:
   
   
5. Enter the token the Jupyter Notebook service was started with in step 2 as the password to access the server:
   
   
6. Click on the pyodb-demo-1.ipynb link to run the PyODBC example query:

7. Click on the sparql-demo-1.ipynb link to tun the SPARQL example query:

8. To leave the service running in the screen session, detach from the screen session with the Ctrl+A Ctrl+D command or use Crtl +D to terminate the screen session and running service.

JDBC Connectivity

JDBC Connectivity Example

1. In the opt/virtuoso/jdbc directory is a sample.java program with a Virtuoso JDBC connect string jdbc:virtuoso://demo.openlinksw.com:21118/Database=Demo/UID=demo/PWD=demo used for connecting to the Virtuoso Demo database and query data from the Customers table:

ubuntu@ip-10-0-0-61:/opt/virtuoso/jdbc$ java sample
EXECUTE: select  top 5 * from Customers
CustomerID CompanyName ContactName ContactTitle Address City Region PostalCode Country CountryCode Phone Fax 
--------------
ALFKI Alfreds Futterkiste Maria Anders Sales Representative Obere Str. 57 Berlin NULL 12209 Germany gm 030-0074321 030-0076545 
ANATR Ana Trujillo Emparedados y helados Ana Trujillo Owner Avda. de la Constitución 2222 México D.F. NULL 05021 Mexico mx (5) 555-4729 (5) 555-3745 
ANTON Antonio Moreno Taquería Antonio Moreno Owner Mataderos  2312 México D.F. NULL 05023 Mexico mx (5) 555-3932 NULL 
AROUT Around the Horn Thomas Hardy Sales Representative 120 Hanover Sq. London NULL WA1 1DP United Kingdom uk (171) 555-7788 (171) 555-6750 
BERGS Berglunds snabbköp Christina Berglund Order Administrator Berguvsvägen  8 Luleå NULL S-958 22 Sweden sw 0921-12 34 65 0921-12 34 67 

ubuntu@ip-10-0-0-61:/opt/virtuoso/jdbc$

2. In the /opt/uda/samples/JDBC/jdk1.8 directory there is a UDA JDBC program sample.java that need to be edited and the JDBC connect string jdbc:openlink://localhost:5000/SVT=Odbc/Options=/Database=Live Virtuoso Demo/UID=demo/PWD=demo added and the program recompiled and executed as follows:

ubuntu@ip-10-0-0-61:/opt/uda/samples/JDBC/jdk1.8$ sudo javac sample.java
ubuntu@ip-10-0-0-61:/opt/uda/samples/JDBC/jdk1.8$ java sample
EXECUTE: select  top 5 * from Demo..Customers
CustomerID CompanyName ContactName ContactTitle Address City Region PostalCode Country CountryCode Phone Fax 
--------------
ALFKI Alfreds Futterkiste Maria Anders Sales Representative Obere Str. 57 Berlin NULL 12209 Germany gm 030-0074321 030-0076545 
ANATR Ana Trujillo Emparedados y helados Ana Trujillo Owner Avda. de la Constitucion 2222 Mexico D.F. NULL 05021 Mexico mx (5) 555-4729 (5) 555-3745 
ANTON Antonio Moreno Taquera Antonio Moreno Owner Mataderos  2312 Mexico D.F. NULL 05023 Mexico mx (5) 555-3932 NULL 
AROUT Around the Horn Thomas Hardy Sales Representative 120 Hanover Sq. London NULL WA1 1DP United Kingdom uk (171) 555-7788 (171) 555-6750 
BERGS Berglunds snabbkop Christina Berglund Order Administrator Berguvsvgen  8 Lule NULL S-958 22 Sweden sw 0921-12 34 65 0921-12 34 67 

ubuntu@ip-10-0-0-61:/opt/uda/samples/JDBC/jdk1.8$

REST Interaction Pattern Usage Examples

SPARQList Service

1. Start screen session:

screen -S sparqlist

2. Goto /opt/virtuoso/sparqlist
3. Goto root shell with sudo bash command
4. Run PORT=3000 ADMIN_PASSWORD=changeme npm start to start the SPARQList Query Service:

root@ip-10-0-0-61:/opt/virtuoso/sparqlist# PORT=3000 ADMIN_PASSWORD=changeme npm start

> sparqlist@0.0.0 start
> node --experimental-modules index.mjs

listening on 3000, repository is at ./repository

5. Connect the the SPARQList Query Service with browser URL http://{Public IP Address}:3000:
   
   
6. Select one of the SPARQList examples and click execute to view results:
   
   
7. To leave the service running in the screen session, detach from the screen session with the Ctrl+A Ctrl+D command or use Crtl +D to terminate the screen session and running service.

GraphQL Middleware Usage

Grasp

1. Start screen session:

screen -S grasp

2. Goto /opt/virtuoso/grasp
3. Goto root shell with sudo bash command
4. Run RESOURCES_DIR=./examples npm run watch to start the Grasp server:

root@ip-10-0-0-61:/opt/virtuoso/grasp# RESOURCES_DIR=./examples npm run watch

> watch
> nodemon -e ts,graphql

[nodemon] 2.0.6
[nodemon] to restart at any time, enter `rs`
[nodemon] watching path(s): *.*
[nodemon] watching extensions: ts,graphql
[nodemon] starting `ts-node main.ts`
🚀 Server ready at http://localhost:4000/

5. Connect the the Grasp server with browser URL http://{Public IP Address}:4000:

6. Execute the following query:

query {
  dbpediaBook(iri: "http://dbpedia.org/resource/Corona_(novel)") {
    iri
    bookTitle
    authorUri
    authorName
  }
}

7. Which returns the following results:

8. To leave the service running in the screen session, detach from the screen session with the Ctrl+A Ctrl+D command or use Crtl +D to terminate the screen session and running service.

Hypergraph

1. Start screen session:

screen -S hypergraph

2. Goto /opt/virtuoso/hypergraphql
3. Goto root shell with sudo bash command
4. If this is the first time Hypergraph is being started, run the following commands to install Java 11 required by Hypergraph, otherwise skip to step 5:

curl -s "https://get.sdkman.io" | bash
source "$HOME/.sdkman/bin/sdkman-init.sh";
sdk install java 11.0.2-open

5. Run java -jar ./hypergraphql-2.0.0-exe.jar --config ./config.dbpedia.json to start the:

root@ip-10-0-0-61:/opt/virtuoso/hypergraphql# java -jar ./hypergraphql-2.0.0-exe.jar --config ./config.dbpedia.json
log4j: reset attribute= "false".
log4j: Threshold ="null".
log4j: Retreiving an instance of org.apache.log4j.Logger.
log4j: Setting [org.hypergraphql] additivity to [false].
log4j: Level value for org.hypergraphql is  [INFO].
log4j: org.hypergraphql level set to INFO
log4j: Class name: [org.apache.log4j.ConsoleAppender]
log4j: Parsing layout of class: "org.apache.log4j.PatternLayout"
log4j: Setting property [conversionPattern] to [%d{yyyy-MM-dd HH:mm:ss} %-5p %c{1}:%L - %m%n].
log4j: Adding appender named [console] to category [org.hypergraphql].
log4j: Level value for root is  [WARN].
log4j: root level set to WARN
log4j: Adding appender named [console] to category [root].

  _   _                        ____                 _      ___  _     
 | | | |_   _ _ __   ___ _ __ / ___|_ __ __ _ _ __ | |__  / _ \| |    
 | |_| | | | | '_ \ / _ \ '__| |  _| '__/ _` | '_ \| '_ \| | | | |    
 |  _  | |_| | |_) |  __/ |  | |_| | | | (_| | |_) | | | | |_| | |___ 
 |_| |_|\__, | .__/ \___|_|   \____|_|  \__,_| .__/|_| |_|\__\_\_____|
        |___/|_|                             |_|                      
----------------------------------------------------------------------

2021-08-08 18:13:44 INFO  Application:84 - Starting controller...
HGQL service name: dbpedia-sparql-hgql-demo
GraphQL server started at: http://localhost:8080/graphql
GraphiQL UI available at: http://localhost:8080/graphiql

6. Connect to the Hypergraph server with browser URL http://{Public IP Address}:8080/graphiql:
   
   
7. Run the following sample DBpedia GraphQL query:

query {
  Person_GET(limit: 5, offset: 6) {
    _id
    _type
    name
    birthDate
  }
}

8. Producing results:
   
   
9. To leave the service running in the screen session, detach from the screen session with the Ctrl+A Ctrl+D command or use Crtl +D to terminate the screen session and running service.

Virtuoso SQLAlchemy Connector

What?

A new SQLAlchemy Connector for Virtuoso the enables use of Virtuoso SQL and SPASQL connectivity.

Why?

SQLAlchemy is the main data access layer (in object-relational form) for Python based solutions such as those from the emerging realms of Data Science, Machine Learning, and AI.
This enables the use of SQLAlchemy with LLamaIndex and LangChain for Retrieval Augmented Generation (RAG) using Virtuoso.

How?

The SQLAlchemy Connector for Virtuoso is preinstall on the OpenLink Data Junction Box and ready for use, with the following sample applications available to demonstrate use.

To get the latest updates for the git project, goto the /opt/virtuoso/Virtuoso-SQLAlchemy directory and run git pull

The sample applications are in the /opt/virtuoso/Virtuoso-SQLAlchemy/examples directory and can be run as follows:

1. Using LangChain to List Spike Lee Movies from the DBpedia Knowledge Graph via its SPARQL Query Services endpoint. Note first the langchain_to_dbpedia_movies.py file needs to be edited and a valid OpenAI Key entered for use, after which the application can be run with the command:

$ python3 langchain_to_dbpedia_movies.py 


> Entering new GraphSparqlQAChain chain...
Identified intent:
SELECT
Generated SPARQL:
PREFIX dbr: <http://dbpedia.org/resource/>
PREFIX dbo: <http://dbpedia.org/ontology/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>

SELECT ?movie
WHERE {
    ?movie rdf:type dbo:Film .
    ?movie dbo:director dbr:Spike_Lee .
}
Full Context:
[]

> Finished chain.
Here are some movies directed by Spike Lee:

1. "Do the Right Thing"
2. "Malcolm X"
3. "Inside Man"
4. "BlacKkKlansman"
5. "25th Hour"

These are just a few examples of the films directed by Spike Lee.
$

2. This SPASQL example leverages Virtuoso’s combined support of both SQL and SPARQL which enables SQL queries against any Knowledge Graph accessible via SPARQL. This example uses the popular SPARQL endpoint for the DBpedia Knowledge Graph. The ODBC DSN VOS required to connect against our demo database is already configured:

$ python3 sqlalchemy_spasql_test.py 
                                             movie
0   http://dbpedia.org/resource/Rodney_King_(film)
1          http://dbpedia.org/resource/School_Daze
2       http://dbpedia.org/resource/BlacKkKlansman
3             http://dbpedia.org/resource/Crooklyn
4      http://dbpedia.org/resource/Clockers_(film)
5         http://dbpedia.org/resource/Freak_(film)
6       http://dbpedia.org/resource/Get_on_the_Bus
7               http://dbpedia.org/resource/Girl_6
8  http://dbpedia.org/resource/Miracle_at_St._Anna
9     http://dbpedia.org/resource/Mo'_Better_Blues
$

3. This example presumes the Northwind Database has been installed on the target Virtuoso database via demo_dav.vad package. The ODBC DSN VOS required to connect against our demo database is already configured:

$ python3 sqlalchemy_to_virtuoso_basic.py 
  CustomerID                         CompanyName  ...           Phone             Fax
0      ALFKI                 Alfreds Futterkiste  ...     030-0074321     030-0076545
1      ANATR  Ana Trujillo Emparedados y helados  ...    (5) 555-4729    (5) 555-3745
2      ANTON             Antonio Moreno Taquería  ...    (5) 555-3932            None
3      AROUT                     Around the Horn  ...  (171) 555-7788  (171) 555-6750
4      BERGS                  Berglunds snabbköp  ...   0921-12 34 65   0921-12 34 67
5      BLAUS             Blauer See Delikatessen  ...      0621-08460      0621-08924
6      BLONP                Blondel père et fils  ...     88.60.15.31     88.60.15.32
7      BOLID           Bólido Comidas preparadas  ...  (91) 555 22 82  (91) 555 91 99
8      BONAP                            Bon app'  ...     91.24.45.40     91.24.45.41
9      BOTTM               Bottom-Dollar Markets  ...  (604) 555-4729  (604) 555-3745

[10 rows x 12 columns]
$

OpenLink Personal Assistant

The OpenLink Personal Assistant is a practical application of Knowledge Graph-driven Retrieval Augmented Generation (RAG) showcasing the power of knowledge discovery and exploration enabled by a modern conversational user interface.

The Personal Assistant requires Virtuoso be configured on a domain specific HTTPS Listener interface as detailed in the Personal Assistant VAD Installation, Configuration, and Usage Guide.

Performance Tuning

There are a range of AWS VM instance types with different system memory and CPU combinations. Collectively, the factors above affect the performance of your Virtuoso instance. Thus, use AWS VM Instance Type s with more memory and CPU cores for best performance.

Note: This VM is configured to use minimal system memory. For the instance type chosen, the NumberOfBuffer and MaxDirtyBuffers parameters in the /opt/virtuoso/database/virtuoso.ini configuration file should be increased to match the available memory, as detailed in the Virtuoso Performance Tuning Guide, for example –

VM Instance Type	System RAM	Number Of Buffers	Max Dirty Buffers
m5.large	8 GB	680000	500000
m5.xlarge	16 GB	1360000	1000000
m5.2xlarge	32 GB	2720000	2000000
m5.4xlarge	64 GB	5450000	4000000

– and the Virtuoso server restarted as detailed above.

Extrapolate the NumberOfBuffer and MaxDirtyBuffers parameters accordingly for different sized VMs.

Troubleshooting

If the Virtuoso server fails to start:

• Run the command sudo service virtuoso status to see if the Virtuoso server is running
• Check the /opt/virtuoso/database/virtuoso.log file to see why the server might have failed to start
• Ensure there file /opt/virtuoso/database/virtuoso.lck does not exist before starting the server
• Attempt to start the Virtuoso server with the command sudo service virtuoso start
• Run the command sudo service virtuoso status again to see if the Virtuoso server is running
• If it is now running attempt a connect via the SQL or HTTP interfaces as detailed above

Additional Information Sources

• OpenLink Data Junction Box Offer for the Amazon Web Services (AWS) Cloud Marketplace
• About the OpenLink Data Junction Box – Pay-As-You-Go Edition for Microsoft Azure Cloud
• About the OpenLink Data Junction Box – Pay-As-You-Go Edition for Google Cloud Platform
• OpenLink Newsletter
• OpenLink Software Weblog
• Virtuoso Weblog
• Universal Data Access Drivers Weblog
• Virtuoso Home Page
• Universal Data Access Drivers Home Page
• OpenLink Software Home Page
• OpenLink Software Community Forum
• Twitter Page
• LinkedIn Page