logstash input jdbc plugin Apache Drill

Question

I'm trying to configure input for logstash 5 with Apache Drill JDBC (https://drill.apache.org/docs/using-the-jdbc-driver/)

Below is my input jdbc configuration for logstash.

input {
  jdbc {
    jdbc_driver_library => "jdbc_jars/drill-jdbc-all-1.10.0.jar"
    jdbc_driver_class => "org.apache.drill.jdbc.Driver"
    jdbc_connection_string => "jdbc:drill:zk=local"
    jdbc_user=> "dfs"
    schedule => "* * * * *"
    statement => "select * from `sample.json`;"
  }
}

I essentially get logstash WARN of "Failed test_connection". Hence, although logstash is launching, the DB connection is failing.

Any suggestions?

ilooner ilooner · Accepted Answer · 2018-01-26T00:29:35

I see a few problems with your configuration.

You need to provide a valid IP address and port for a zookeeper node that Drill is using. The line you provided to logstash jdbc_connection_string => "jdbc:drill:zk=local" is telling logstash that zookeeper is running on the same node as logstash. What you need to provide instead is jdbc_connection_string => "jdbc:drill:zk=zk_hostname_or_ip:zk_port". Talk to the guy who setup your drill cluster to figure out the hostname or ip and port of your zookeeper node.
dfs is not a drill user, it is the name of one of Drill's storage plugins. If you want to run your query on a file stored on hdfs change
```
statement => "select * from `sample.json`;" 
```
to
```
statement => "select * from dfs.`/path/to/sample.json`;"
```

If you do not have authentication configured for Drill your config should look like this.

input {
  jdbc {
    jdbc_driver_library => "jdbc_jars/drill-jdbc-all-1.10.0.jar"
    jdbc_driver_class => "org.apache.drill.jdbc.Driver"
    jdbc_connection_string => "jdbc:drill:zk=zk_hostname_or_ip:zk_port"
    schedule => "* * * * *"
    statement => "select * from `dfs./path/to/sample.json`;"
  }
}

If you have authentication configured for Drill and you know your Drill username and password your config should look like this.

input {
  jdbc {
    jdbc_driver_library => "jdbc_jars/drill-jdbc-all-1.10.0.jar"
    jdbc_driver_class => "org.apache.drill.jdbc.Driver"
    jdbc_connection_string => "jdbc:drill:zk=zk_hostname_or_ip:zk_port"
    schedule => "* * * * *"
    statement => "select * from `dfs./path/to/sample.json`;"
    jdbc_user => "myusername"
    jdbc_password => "mypassword"
  }
}

logstash input jdbc plugin Apache Drill

1 Answers