How to link Virtuoso to Elasticsearch?

Basically what I want to know is that is there a way for me to index the results of the sparql queries from the virtuoso triples store

I have read its possible to connect relational databases such as Mysql and postgres using the jdbc-input-plugin and index the query result.

the logstash conf file looks something like this for the relational database

Is it possible to do the same for Virtuoso?or there any other methods?

Hi @Shreyas_Devadiga,

What goes for MySQL (a JDBC-compliant RDBMS) also goes for Virtuoso (which is also a JDBC- and ODBC-compliant RDBMS).

Simply establish your JDBC connection to Virtuoso and you should be set, using your MySQL config file as the template.

Note, Virtuoso supports both SQL and SPARQL queries via JDBC or ODBC connections.


1 Like

Hi @Shreyas_Devadiga,

The Logstash method shown in your screenshot can be done with Virtuoso’s JDBC Driver.
There are some extra requirements when connecting to Virtuoso via JDBC using this method:

  1. jdbc_driver_class value Must have Java:: At the beginning
  2. When returning SPARQL queries to Logstash using JDBC, SPARQL-within-SQL must be used to cast Virtuoso ExtendedClass and any other native datatypes as plain strings (SPASQL query example is provided in the “statement” parameter)
input {
  jdbc {
    jdbc_driver_library => "/absolute_path_to_folder_containing_virtuoso_jdbc_driver/virtjdbc4.jar"
    jdbc_driver_class => "Java::virtuoso.jdbc4.Driver"
    jdbc_connection_string => "jdbc:virtuoso://localhost:1111"
    jdbc_user => "{username}"
    jdbc_password => "{password}"
    statement => "SELECT CAST(x.s AS varchar) AS test FROM (SPARQL SELECT DISTINCT ?s WHERE {?s ?p ?o} LIMIT 10) as x"

1 Like

Hey @danielhm ,

I tried following your instructions and this is my conf file

But I am getting this error where it’s asking me → Are you sure you’ve included the correct jdbc driver in :jdbc_driver_library? Exception: LogStash::PluginLoadingError

I am using – Virtuoso OpenSource 7.2
– Java 8
– Windows 10
– Logstash 7.11.1

Hi @Shreyas_Devadiga,

I tried using the same details in your screenshot successfully.

Are you running Logstash as Admin, or as another user that has required permissions to access the directory containing the Virtuoso JDBC Driver “.jar” file? The “.jar” file can also be copied/moved to another directory if needed.

1 Like

Hey @danielhm,
Thank you so much for the help, it works like a charm now.

If I may ask how could I modify the statement so that I could display even the predicates and objects

1 Like

Hi @Shreyas_Devadiga,

Great! Expanding the results is done by modifying the number of variables available. This is done by expanding the selections of both the SQL and SPARQL select clauses in the SPARQL-within-SQL Query.

One example being:

statement => "SELECT CAST(x.s AS varchar) AS s, CAST(x.p AS varchar) AS p, CAST(x.o AS varchar) AS o FROM (SPARQL SELECT * WHERE {?s ?p ?o} LIMIT 10) AS x"

Sample result:

1 Like

Hey @danielhm,

Got it! Once again thank you so much for the help.

1 Like