Database Visualization using Metabase Part 1 - Install Metabase on Ubuntu 16.04

By Liptan Biswas, Alibaba Cloud Tech Share Author. Tech Share is Alibaba Cloud’s incentive program to encourage the sharing of technical knowledge and best practices within the cloud community.

Metabase is an open source application for visualizing your database and get insights from it. Metabase provides an intuitive and easy to use web-based interface for running queries on a database without writing a SQL statement. It works with almost all popular databases such as MySQL/MariaDB, Postgres, Mongo, SQL Server, Druid, H2, SQLite, and Oracle, etc. Data can be obtained in raw tables or beautiful graphs or charts. Metabase refers the queries to a database as questions. It lets you save the questions once asked, saved queries can be repeatedly executed to get the answers quickly. It also lets you create dashboards in which you can add multiple questions to get all the important insights in one place. Dashboards are auto-refreshed every time and can also be shared among your team members.

This tutorial is divided into two parts. In the first part of the tutorial, we will install Metabase on Ubuntu 16.04 server. We will use PostgreSQL to host the Metabase database. We will also set up Nginx as a reverse proxy and secure the Metabase instance using Let’s Encrypt SSL.

In the second part of the tutorial, we will learn the basics of how to use the platform. We will also configure emails to setup pluses.

Requirements

• Alibaba Cloud ECS instance with Ubuntu 16.04 64-bit installed.
  
• Firewall or Security group rules configured to allow the port “80”, “443”.
  
• A domain name that needs to be pointed towards your ECS instance.

You can follow the “Quick Start Guide” to create the instance and steps to connect to your instance. This tutorial assumes that you have created your Alibaba instance and “192.168.0.101” is the public IP address assigned to your Ubuntu instance. You have also configured “metabase.example.com” to point to your Ubuntu instance. Once you have connected to your instance via SSH, run the following command to update the repository cache and the base system.

apt update && apt -y upgrade && apt -y autoremove

Install Java

Since Metabase is written in Java, we are required to install Java runtime to run Metabase. Metabase supports both Oracle Java 8 and OpenJDK 8. In this tutorial, we will install OpenJDK Java runtime version 8.

apt -y install openjdk-8-jre

Check whether Java is installed successfully by running java -version.

root@aliyun:~# java -version
openjdk version "1.8.0_162"
OpenJDK Runtime Environment (build 1.8.0_162-8u162-b12-0ubuntu0.16.04.2-b12)
OpenJDK 64-Bit Server VM (build 25.162-b12, mixed mode)

Find the directory where Java is installed into your system.

readlink -f /usr/bin/java | sed "s:/jre/bin/java::"

You should see the following output.

root@aliyun:~# readlink -f /usr/bin/java | sed "s:/jre/bin/java::"
/usr/lib/jvm/java-8-openjdk-amd64

Now, set the JAVA_HOME environment variable according to the path to your Java installation.

echo "export JAVA_HOME=/usr/lib/jvm/java-8-openjdk-amd64" | tee -a /etc/profile
source /etc/profile

Check if JAVA_HOME variable is set by running echo $JAVA_HOME. You should see.

root@aliyun:~# echo $JAVA_HOME
/usr/lib/jvm/java-8-openjdk-amd64

Setup ApsaraDB for PostgreSQL

By default, Metabase is configured to use H2 database. H2 database is a flat file based database and it does not require any special software to run. However, using an H2 database in the multi-user production environment is not recommended as it deteriorates the performance of the application. In this tutorial, we will use the PostgreSQL server from ApsaraDB for RDS to host the Metabase database. Go to “https://rdsnew.console.aliyun.com” and create a new RDS instance for PostgreSQL. Choose your subscription type and select region. Creating the RDS instance in the same region where your ECS instance is located gives you many leverages such as ECS and RDS instances can communicate using a private intranet address. We do not require to apply for an internet address and intranet data transfers are free of charge. For this tutorial, I have created the RDS instance in Mumbai region where my ECS instance is also located.

Choose DB Engine as PostgreSQL and select version 9.4. Select the zone and VPC where your ECS instance is located. Choose the type and capacity, for the start, you can choose 1GB instance with 5GB capacity. Instance type and capacity can be increased later on demand.

After your purchase of the instance, it will take a few minutes for the instance to be active. Once your instance is activated, click on “manage” link and switch to “Security” tab. Now add a new whitelist group, provide any descriptive name for the new group and enter the private or intranet IP address of the ECS instance. Since we have only whitelisted the ECS instance, the database server will be accessible only from our ECS instance.

Now, switch to “Accounts” tab and create a new master account for your PostgreSQL server with username “postgres”. The master account will be used to create new users and databases from PSQL shell.

Finally, go the connection options tab to find the intranet address and port assigned to your RDS instance. Make a note of the intranet address and the port as we will require it later in the tutorial.

Our PostgreSQL server instance is ready and now we can move on to the installation of Metabase.

Install Metabase

Metabase provides a Java executable file which is cross-platform and can be easily executed in the terminal. Find the link to the latest release of the application on Metabase download page and download Metabase using the following command.

wget http://downloads.metabase.com/v0.28.6/metabase.jar

Create a new unprivileged user for running Metabase Java executable. Since the Metabase executable will also start an inbuilt Jetty web server, it is recommended to use an unprivileged user to ensure the security of the system.

adduser --home /var/metabase --gecos="Metabase User" --disabled-login --disabled-password metabase

Move the downloaded executable file into “metabase” user’s home directory.

mv metabase.jar /var/metabase

Before starting the Metabase service, let’s verify if we can connect to the remote RDS instance from our ECS instance. We will also need to create the database and database user for Metabase.

Install the PostgreSQL client.

apt -y install postgresql-client

Connect to the remote PostgreSQL instance you created using the following command. Replace the intranet address with the actual address assigned to your instance. When prompted, provide the password of the “postgres” master user you have created earlier.

psql -h rm-6gjlyl343w252w65g.pgsql.ap-south-1.rds.aliyuncs.com -p 3433 -U postgres -d postgres

If the connection is successful, you will be logged into “psql” shell from where you can run queries on the database server. If you have followed the tutorial correctly, you shouldn’t have any problem connecting. You will get the following output.

root@aliyun:~# psql -h rm-6gjlyl343w252w65g.pgsql.ap-south-1.rds.aliyuncs.com -p 3433 -U postgres -d postgres
Password for user postgres: 
psql (9.5.12, server 9.4.10)
Type "help" for help.

postgres=> 

Now, create a new database user for Metabase. Replace StrongPassword with some very strong password.

CREATE USER metabase WITH PASSWORD 'StrongPassword';

Create the database.

CREATE DATABASE metabasedb;

Provide all the privileges to “metabase” user over the “metabasedb” database.

GRANT ALL PRIVILEGES ON DATABASE metabasedb to metabase;

Log out to the root user’s shell by entering q command. Since we are using PostgreSQL as the database server instead of the default H2 database with Metabase, we will need to configure Metabase to use PostgreSQL server. Metabase reads configuration parameters from environment variables. Since we are installing Metabase for production use we will set up a systemd service unit for running the application. Systemd services can read environment variables from a file. Create a new file to store Metabase environment variables.

nano /var/metabase/metabase.env

Populate the file with the following content. Make sure to change the values according to your setup and preferences. Descriptions of the parameters are provided as a comment.

# Password complexity for Metabase user, allowed values <weak|normal|strong>
MB_PASSWORD_COMPLEXITY=normal
# Password length for Metabase user
MB_PASSWORD_LENGTH=8

# Host and Port on which the inbuilt jetty server listens, 
# Leave it unchanged
MB_JETTY_HOST=localhost
MB_JETTY_PORT=3000

# Provide Intranet or Private IP address of PostgresSQL server
MB_DB_TYPE=postgres
MB_DB_HOST=rm-6gjlyl343w252w65g.pgsql.ap-south-1.rds.aliyuncs.com
MB_DB_PORT=3433

# Provide the database name
MB_DB_DBNAME=metabasedb
# Provide the username of database user
MB_DB_USER=metabase
# Provide the password of database user
MB_DB_PASS=StrongPassword

# Setting it true will include emojis in logs, to disable set it to false
MB_EMOJI_IN_LOGS=true

Save the file and exit from the editor. Provide ownership of the file to the “metabase” user.

chown metabase:metabase -R /var/metabase

Managing Metabase through systemd service simplifies the process of starting and stopping the application. It also ensures that the application is automatically started on system reboots and process failures. Create a new systemd unit file.

nano /etc/systemd/system/metabase.service

Populate the file with the following content.

[Unit]
Description=Metabase server
After=syslog.target
After=network.target

[Service]
EnvironmentFile=/var/metabase/metabase.env
User=metabase
Group=metabase
Type=simple
ExecStart=/usr/bin/java -jar /var/metabase/metabase.jar
Restart=always
StandardOutput=syslog
StandardError=syslog
SyslogIdentifier=metabase

[Install]
WantedBy=multi-user.target

You can start the application by running.

systemctl start metabase

To enable Metabase server to automatically start at boot time, run.

systemctl enable metabase

You can check the status of the service by running.

systemctl status metabase

On the first startup of the Metabase server, the application writes the database to the PostgreSQL server. It may take a few minutes before the application is initialized for the first time. You can monitor the status of the application by checking the syslog using the following command.

journalctl -f -e -u metabase

Your application is ready once you see the following line in the log.

Apr 23 21:30:34 aliyun metabase[30486]: 04-23 21:30:34 INFO metabase.core :: Metabase Initialization COMPLETE

Install Nginx

Although Metabase has a built-in Jetty web server to serve the application, in a production system it is not recommended to expose such web servers on the internet. The best way is set up a production-grade web server, such as Nginx or Apache in front and proxy the requests to the Jetty server. In this tutorial, we will install Nginx web server as a reverse proxy to the Metabase server.

Install Nginx web server.

apt -y install nginx

Start Nginx and enable the server to automatically start at boot time.

systemctl start nginx
systemctl enable nginx

It is also important to secure the web server with SSL/TLS encryptions because logins and other important data will be sent from the browser session to the web server and vice versa. If the data being exchanged is not encrypted, a person eavesdropping into the network may obtain the data. In this tutorial, we are going to use the free SSL certificates from the Let’s Encrypt CA. If you want to use a more production friendly and trusted SSL, you may purchase SSL certificates from Alibaba.

Let’s Encrypt provides an easy to use tool “Certbot” for requesting and generation of certificates. Add Certbot repository and install Certbot.

apt -y install software-properties-common
add-apt-repository --yes ppa:certbot/certbot
apt update
apt -y install certbot

For Certbot to verify the ownership of the domain, it is important that the domain is pointed towards your ECS instance. If not, certificates for the domain will not be generated. Request for the certificates using Certbot.

certbot certonly --webroot -w /var/www/html -d metabase.example.com

Once your certificates have been generated, you will see the following output.

Obtaining a new certificate
Performing the following challenges:
http-01 challenge for metabase.example.com
Using the webroot path /var/www/html for all unmatched domains.
Waiting for verification...
Cleaning up challenges

IMPORTANT NOTES:

 - Congratulations! Your certificate and chain have been saved at:
   /etc/letsencrypt/live/metabase.example.com/fullchain.pem
   Your key file has been saved at:
   /etc/letsencrypt/live/metabase.example.com/privkey.pem
   ...

Create a cron job for auto-renewing the certificates before they expire.

{ crontab -l; echo '36 2 * * * /usr/bin/certbot renew --post-hook "systemctl reload nginx"'; } | crontab -

Create a new Nginx server block configuration of the reverse proxy for Metabase.

nano /etc/nginx/sites-available/metabase

Enter the following configuration in the editor. Replace all occurrences of the example domain with the actual one.

server {
    listen 80;
    server_name metabase.example.com;
    return 301 https://$host$request_uri;
}

server {
    listen 443;
    server_name metabase.example.com;

    ssl_certificate           /etc/letsencrypt/live/metabase.example.com/fullchain.pem;
    ssl_certificate_key       /etc/letsencrypt/live/metabase.example.com/privkey.pem;

    ssl on;
    ssl_session_cache  builtin:1000  shared:SSL:10m;
    ssl_protocols  TLSv1 TLSv1.1 TLSv1.2;
    ssl_ciphers HIGH:!aNULL:!eNULL:!EXPORT:!CAMELLIA:!DES:!MD5:!PSK:!RC4;
    ssl_prefer_server_ciphers on;

    gzip  on;
    gzip_http_version 1.1;
    gzip_vary on;
    gzip_comp_level 6;
    gzip_proxied any;
    gzip_types text/plain text/html text/css application/json application/javascript application/x-javascript text/javascript text/xml application/xml application/rss+xml application/atom+xml application/rdf+xml;
    gzip_buffers 16 8k;
    gzip_disable “MSIE [1-6].(?!.*SV1)”;
    
    access_log  /var/log/nginx/metabase.access.log;
    
location / {
    proxy_pass            http://localhost:3000;        
    proxy_set_header    host $host;
    proxy_http_version  1.1;
    proxy_set_header upgrade $http_upgrade;         
    proxy_set_header connection "upgrade";     
    } 
}

Activate the configuration file by running.

ln -s /etc/nginx/sites-available/metabase /etc/nginx/sites-enabled/metabase

You can verify if the configuration file is error free by running nginx -t.

root@aliyun:~# nginx -t
nginx: the configuration file /etc/nginx/nginx.conf syntax is ok
nginx: configuration file /etc/nginx/nginx.conf test is successful

Restart the Nginx web server so that the change in configuration can be applied.

systemctl restart nginx

You can now access your Metabase instance through your favorite browser by browsing “https://metabase.example.com”. You should see the welcome screen from Metabase. Provide the basic information about the administrator account and your organization.

You will be prompted to add a database on the first run. Skip adding a new database, for now, we will do it later from the dashboard. Once you are logged into the Metabase, you will see a similar dashboard.

Conclusion

In this detailed tutorial, we have installed Metabase web application on Ubuntu 16.04 server. We have seen how to create a PostgreSQL RDS instance on ApsaraDB for RDS. We have configured Nginx as a reverse proxy and also secured it with Let’s Encrypt SSL. Your Metabase instance is now ready to be used by your organization.

In second part of this tutorial, we will learn the basics of how to use Metabase by running some sample queries on a sample database. We will also set up emails in Metabase and learn to create pulses. You can learn more about the uses of Metabase from its official user guide.