Metabase Influxdb

| 02-05-2021
[RAND-1-100] Comments



This post demonstrates the process of installation of Metabase Business Intelligence Tool on an AWS EC2 instance.

Findコマンドでfind 全ディレクトリを検索対象する方法を記述してます。ちなみに、locateはデータが肥大化するのが嫌なので、遅いけどfindを利用してます。. From the openhabian-config output it is obvious that the InfluxDB daemon fails to start. The most common reasons are port already in use (as @jswim788 already mentioned), and access rights. Since the setup is done with openHABian, the 2nd option is unlikely. The first option can easily be checked with.

Metabase is an open source Business Intelligence Tool used to query databases for individuals and companies with or without knowledge of SQL.

‘Metabase is a simple and powerful analytics tool which lets anyone learn and make decisions from their company’s data. No technical knowledge required!’

  • Learn more about the Metabase at: https://www.metabase.com/
  • Official Installtion documentation of Metabase at: https://www.metabase.com/docs/latest/operations-guide/installing-metabase.html

Installation of Metabase in an AWS EC2 instance

  1. Spin up an AWS EC2 instance with the following specifications
    • Ubuntu 18.04 Server LTS OS
    • 2 vCPUs, 4 GB RAM (t2.medium)
    • In Security policy,
      • Open SSH Port 22 to be accessed only from your local machine/ or from anywhere.
      • Open Custom TCP Port 3000 to be accessed from any IP (0.0.0.0/0)
  2. In the terminal, Update your Ubuntu repositories

  1. We will be installing the Metabase software by the Docker container approach. https://www.metabase.com/docs/latest/operations-guide/running-metabase-on-docker.html

  2. First, we need to install Docker. https://docs.docker.com/engine/install/ubuntu/ Run the following commands to install Docker

  1. Verify that docker had installed properly.

You should get the following output. If so, then docker is installed properly.

  1. Run the following one-liner docker command for installating Metabase
  1. Start Metabase in your browser by putting in the URL as
    • http://AWS-EC2-PublicIP:3000 if you are installing in an AWS EC2 instance
      • E.g. http://15.206.178.52:1880
    • NOTE: It takes some time for the software to run for the first time. Sometimes it can takes minutes! Please wait till you get the following landing page on your browser.
  1. Click on ‘Getting Started’, enter your details and a password. Submitting this will take you to the main Dashboard page of Metabase.
    You are now ready to explore Metabase!

NOTE: If you stop your EC2 instance and then restart it, the Metabase docker container does not start automatically.

a. To list the status of your docker containers, whether it has started or stopped, type:

b. To start the Metabase docker container after a EC2 instance restart, type:

Happy exploring this great open source BI tool!

Spring Boot Actuator is one of the most modified projects after the release of Spring Boot 2. It has been through major improvements, which aimed to simplify customization and include some new features like support for other web technologies, for example, the new reactive module – Spring WebFlux. Spring Boot Actuator also adds out-of-the-box support for exporting metrics to InfluxDB – an open-source time-series database designed to handle high volumes of timestamped data. It is really a great simplification in comparison to the version used with Spring Boot 1.5. You can see for yourself how much by reading one of my previous articles Custom metrics visualization with Grafana and InfluxDB. I described there how to export metrics generated by Spring Boot Actuator to InfluxDB using @ExportMetricsWriter bean. The sample Spring Boot application has been available for that article on GitHub repository sample-spring-graphite (https://github.com/piomin/sample-spring-graphite.git) in the branch master. For the current article, I have created the branch spring2 (https://github.com/piomin/sample-spring-graphite/tree/spring2), which show how to implement the same feature as before using version 2.0 of Spring Boot and Spring Boot Actuator.

Additionally, I’m going to show you how to use Spring Boot Actuator to export the same metrics to another popular monitoring system – Prometheus. There is one major difference between models of exporting metrics between InfluxDB and Prometheus. The first of them is a push based system, while the second is a pull based system. So, our sample application needs to actively send data to the InfluxDB monitoring system, while with Prometheus it only has to expose endpoints that will be fetched for data periodically. Let’s begin from InfluxDB.

Metabase Sqlite

1. Running InfluxDB

In the previous article I didn’t write much about this database and its configuration. The first step is typical for my examples – we will run a Docker container with InfluxDB. Here’s the simplest command that runs InfluxDB on your local machine and exposes HTTP API over 8086 port.

Once we started that container, you would probably want to login there and execute some commands. Nothing simpler, just run the following command and you would be able to do it. After login, you should see the version of InfluxDB running on the target Docker container.

The first step is to create a database. As you can probably guess, it can be achieved using the command create database. Then switch to the newly created database.

Does that semantic look familiar for you? InfluxDB provides a very similar query language to SQL. It is called InfluxQL, and allows you to define SELECT statements, GROUP BY or INTO clauses, and many more. However, before executing such queries, we should have data stored inside the database, am I right? Now, let’s proceed to the next steps in order to generate some test metrics.

2. Integrating Spring Boot Actuator with InfluxDB

If you include artifact micrometer-registry-influx to the project’s dependencies, an export to InfluxDB will be enabled automatically. Of course, we also need to include starter spring-boot-starter-actuator.

The only thing you have to do is to override the default address of InfluxDB because we are running InfluxDB a Docker container on VM. By default, Spring Boot Data tries to connect to a database named mydb. However, I have already created a database springboot, so I should also override this default value. In version 2 of Spring Boot all the configuration properties related to Spring Boot Actuator endpoints have been moved to management.* section.

Metabase

You may be surprised a little after starting Spring Boot application with actuator included on the classpath, that it exposes only two HTTP endpoints by default /actuator/info and /actuator/health. That’s why in the newest version of Spring Boot all actuators other than /health and /info are disabled by default, for security purposes. To enable all the actuator endpoints, you have to set property management.endpoints.web.exposure.include to '*'.
In the newest version of Spring Boot monitoring of HTTP metrics has been improved significantly. We can enable collecting all Spring MVC metrics by setting the property management.metrics.web.server.auto-time-requests to true. Alternatively, when it is set to false, you can enable metrics for the specific REST controller by annotating it with @Timed. You can also annotate a single method inside the controller, to generate metrics only for specific endpoints.
After application boot you may check out the full list of generated metrics by calling endpoint GET /actuator/metrics. By default, metrics for Spring MVC controller are generated under the name http.server.requests. This name can be customized by setting the management.metrics.web.server.requests-metric-name property. If you run the sample application available inside my GitHub repository it is by default available under port 2222. Now, you can check out the list of statistics generated for a single metric by calling the endpoint GET /actuator/metrics/{requiredMetricName}, as shown in the following picture.

3. Building Spring Boot application

The sample Spring Boot application used for generating metrics consists of a single controller that implements basic CRUD operations for manipulating Person entity, repository bean and entity class. The application connects to MySQL database using Spring Data JPA repository providing CRUD implementation. Here’s the controller class.

Before running the application we have set up a MySQL database. The most convenient way to achieve it is through MySQL Docker image. Here’s the command that runs a container with database grafana, defines user and password, and exposes MySQL 5 on port 33306.

Then we need to set some database configuration properties on the application side. All the required tables will be created on application’s boot thanks to setting property spring.jpa.properties.hibernate.hbm2ddl.auto to update.

4. Generating metrics with Spring Boot Actuator

After starting the application and the required Docker containers, the only thing that needs to be is done is to generate some test statistics. I created the JUnit test class that generates some test data and calls endpoints exposed by the application in a loop. Here’s the fragment of that test method.

Now, let’s move back to step 1. As you probably remember, I have shown you how to run the influx client in the InfluxDB Docker container. After some minutes of working, the test unit should call exposed endpoints many times. We can check out the values of metric http_server_requests stored on Influx. The following query returns a list of measurements collected during the last 3 minutes.

As you see, all the metrics generated by Spring Boot Actuator are tagged with the following information: method, uri, status and exception. Thanks to that tag we may easily group metrics per single endpoint including failures and success percentage. Let’s see how to configure and view it in Grafana.

5. Metrics visualization using Grafana

Once we have exported succesfully metrics to InfluxDB, it is time to visualize them using Grafana. First, let’s run Docker container with Grafana.

Grafana provides a user friendly interface for creating influx queries. We define a graph that visualizes requests processing time per each of calling endpoints and the total number of requests received by the application. If we filter the statistics stored in the table http_server_requests by method type and uri, we would collect all metrics generated per single endpoint.

Metabase Mobile

The similar definition should be created for the other endpoints. We will illustrate them all on a single graph.

Here’s the final result.

Here’s the graph that visualizes the total number of requests sent to the application.

6. Running Prometheus

The most suitable way to run Prometheus locally is obviously through a Docker container. The API is exposed under port 9090. We should also pass the initial configuration file and name of Docker network. Why? You will find all the answers in the next part of this step description.

In contrast to InfluxDB, Prometheus pulls metrics from an application. Therefore, we need to enable the Spring Boot Actuator endpoint that exposes metrics for Prometheus, which is disabled by default. To enable it, set property management.endpoint.prometheus.enabled to true, as shown on the configuration fragment below.

Then we should set the address of the Spring Boot Actuator endpoint exposed by the application in the Prometheus configuration file. A scrape_config section is responsible for specifying a set of targets and parameters describing how to connect with them. By default, Prometheus tries to collect data from the target endpoint once a minute.

Similarly for integration with InfluxDB we need to include the following artifact to the project’s dependencies.

In my case, Docker is running on VM, and is available under IP 192.168.99.100. If I would like Prometheus, which is launched as a Docker container, to be able to connect my application, I also should launch it as a Docker container. The most convenient way to link two independent containers is through the Docker network. If both containers are assigned to the same network, they would be able to connect to each other using the container’s name as a target address. Dockerfile is available in the root directory of the sample application’s source code. Second command visible below (docker build) is not required, because the required image piomin/person-service is available on my Docker Hub repository.

7. Integrate Prometheus with Grafana

Prometheus exposes a web console under address 192.168.99.100:9090, where you can specify query and display graphs with metrics. However, we can integrate it with Grafana to take an advantage of nicer visualization offered by this tool. First, you should create a Prometheus data source.

Then we should define queries for collecting metrics from Prometheus API. Spring Boot Actuator exposes three different metrics related to HTTP traffic: http_server_requests_seconds_count, http_server_requests_seconds_sum and http_server_requests_seconds_max. For example, we may calculate a per-second average rate of increase of the time series for http_server_requests_seconds_sum, that returns the total number of seconds spent on processing requests by using rate() function. The values can be filtered by method and uri using expression inside {}. The following picture illustrates configuration of rate() function per each endpoint.

Here’s the graph.

Summary

The improvement in metrics generation between version 1.5 and 2.0 of Spring Boot is significant. Exporting data to such popular monitoring systems like InfluxDB or Prometheus is now much easier than before with Spring Boot Actuator, and does not require any additional development. The metrics relating to HTTP traffic are more detailed and they may be easily associated with specific endpoints, thanks to tags indicating the uri, type and status of HTTP request. I think that modifications in Spring Boot Actuator in relation to the previous version of Spring Boot, could be one of the main motivations to migrate your applications to the newest version.