We need to have a database that has metadata about the sensors. This database will hold the tables that we discussed in the Introduction to the application section.

We are storing the data in a relational database MySQL, but you can use any other relational database equally well. Since we are using MySQL, we will be using the MySQL JDBC driver to connect to the database. Please ensure that you have the following things set up on your system:

1. MySQL database community version 5.5, 5.6, or 5.7. You can use an existing database if you already have it on your system.
2. Install the downloaded MySQL database and log in with the root user. Execute the script available  https://github.com/pranav-shukla/learningelasticstack/tree/v7.0/chapter-10/files/create_sensor_metadata.sql.
3. Log in to the newly created sensor_metadata database and verify that the three tables, sensor_type, locations, and sensors, exist in the database.

You can verify that the database was created and populated successfully by executing the following query:

select 
    st.sensor_type as sensorType,
    l.customer as customer,
    l.department as department,
    l.building_name as buildingName,
    l.room as room,
    l.floor as floor,
    l.location_on_floor as locationOnFloor,
    l.latitude,
    l.longitude
from
    sensors s
        inner join
    sensor_type st ON s.sensor_type_id = st.sensor_type_id
        inner join
    location l ON s.location_id = l.location_id
where
    s.sensor_id = 1; 

The result of the previous query will look like this:

Our sensor_metadata database is ready to look up the necessary sensor metadata. In the next section, we will build the Logstash data pipeline.