We essentially specify the whereabouts of the database that we want to connect to, the username/password, the JDBC driver .jar file, and the class. We already created the database in the Setting up the metadata database section. 

Download the latest MySQL JDBC Driver, also known as Connector/J, from https://dev.mysql.com/downloads/connector/j/. At the time of writing this book, the latest version is 5.1.45, which works with MySQL 5.5, 5.6, and 5.7. Download the .tar/.zip file containing the driver and extract it into your system. The path of this extracted .jar file should be updated in the jdbc_driver_library parameter.

To summarize, you should review and update the following parameters in the Logstash configuration to point to your database and driver .jar file:

• jdbc_connection_string
• jdbc_password
• jdbc_driver_library

The statement parameter has the same SQL query that we saw earlier. It looks up the metadata for the given sensor_id. A successful query will fetch all additional fields for that sensor_id. The result of the lookup query is stored in a new field, lookupResult, as specified by the target parameter.

The resulting document, up to this point, should look like this:

{
  "sensor_id": 1,
  "time": 1512113760000,
  "reading": 16.24,
  "lookupResult": [
    {
      "buildingName": "222 Broadway",
      "sensorType": "Temperature",
      "latitude": 40.710936,
      "locationOnFloor": "Desk 102",
      "department": "Engineering",
      "floor": "Floor 1",
      "room": "101",
      "customer": "Linkedin",
      "longitude": -74.0085
    }
  ],
  "@timestamp": "2019-05-26T05:23:22.618Z",
  "@version": "1",
  "host": "0:0:0:0:0:0:0:1",
  "headers": {
    "remote_user": "sensor_data",
    "http_accept": "*/*",
    ...
  }
}

As you can see, the jdbc_streaming filter plugin added some fields apart from the lookupResult field. These fields were added by Logstash and the headers field was added by the HTTP input plugin.

In the next section, we will use the mutate filter plugin to modify this JSON to the desired end result that we want in Elasticsearch.