Connecting Virtuoso to Elasticsearch (Logstash + JDBC)

[danielhm]

Connecting to Virtuoso from Elasticsearch (Logstash)

Prerequisites

• Elasticsearch
• Logstash
• Knowledge of your Virtuoso SQL listening port (default: 1111)

Installations

1. Download the OpenLink Virtuoso JDBC Driver, and move it to a location of your choice.

Note: The user executing Logstash must have appropriate permissions to access the JDBC .JAR file location.

Setup

2. Create a JDBC Input Plugin and provide the following parameter values:

input {
  jdbc {
    jdbc_driver_library => "/path_to_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}"

    #SPARQL-Within-SQL  Example
    statement => "SELECT CAST(x.s as varchar) AS test FROM (SPARQL SELECT ?s WHERE {?s ?p ?o} LIMIT 10) as x"
    
    #SQL Example
    #statement => "SELECT * FROM wine.kg.data"

  }
}

Important note for SPARQL Query Results:

SPARQL-within-SQL (SPASQL) must be used to return SPARQL query results due to the required use of a SQL alias and CAST() to convert native datatypes into strings compatible with Logstash.

Output

3. Run Logstash using your preferred method. Once successfully connected, an output plugin can be created for further use of returned query results.