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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: In this tutorial, we will install Metabase on an Alibaba Cloud ECS Ubuntu 16.04 server to achieve data visualization.

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

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. ![1](https://yqfile.alicdn.com/3271cf623a3177ad9e0a463bbf065cab9c29e2fe.png) 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. ![2](https://yqfile.alicdn.com/c2d768de2e5a072fea0231ed28f5478ad35fee96.png) 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. ![3](https://yqfile.alicdn.com/c9e035a022be94860fb849cbdd070d280ebe5a49.png) 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 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. ![4](https://yqfile.alicdn.com/a147d0b6b2580e9e2a0917c53b9456f1839e108a.png) 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. ![5](https://yqfile.alicdn.com/af36e08b8a0fe4f4cf785e60ee542fa4009d03ea.png)

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.

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
弹性计算 Cloud Native 5G
藏经阁2023年书籍推荐
好书一起看,技术干货学习就上藏经阁。藏经阁收录内容涵盖编程语言、云原生、数据库、大数据、AI等热门技术领域,让开发者们享受阅读优质内容。这次为您带来2023年4月至2024年1月的书籍推荐。
|
数据可视化 大数据 数据挖掘
Apache Superset 1.2.0教程 (一)—— 安装(Windows版)
Apache Superset 是一款由 Airbnb 开源的“现代化的企业级 BI(商业智能) Web 应用程序”,其通过创建和分享 dashboard,为数据分析提供了轻量级的数据查询和可视化方案。 近日推出了全新的 1.2.0版本,本教程也就从头开始讲解Apache Superset的使用。
2644 0
Apache Superset 1.2.0教程 (一)—— 安装(Windows版)
|
SQL 缓存 Java
flink cdc 同步问题之如何同步多张库表
Flink CDC(Change Data Capture)是一个基于Apache Flink的实时数据变更捕获库,用于实现数据库的实时同步和变更流的处理;在本汇总中,我们组织了关于Flink CDC产品在实践中用户经常提出的问题及其解答,目的是辅助用户更好地理解和应用这一技术,优化实时数据处理流程。
|
11月前
|
前端开发 Java API
【IDEA版】简单快速上手撸Struts框架
【IDEA版】简单快速上手撸Struts框架
465 1
|
7月前
|
存储 小程序 前端开发
微信小程序与Java后端实现微信授权登录功能
微信小程序极大地简化了登录注册流程。对于用户而言,仅仅需要点击授权按钮,便能够完成登录操作,无需经历繁琐的注册步骤以及输入账号密码等一系列复杂操作,这种便捷的登录方式极大地提升了用户的使用体验
2347 12
|
机器学习/深度学习 算法 TensorFlow
交通标志识别系统Python+卷积神经网络算法+深度学习人工智能+TensorFlow模型训练+计算机课设项目+Django网页界面
交通标志识别系统。本系统使用Python作为主要编程语言,在交通标志图像识别功能实现中,基于TensorFlow搭建卷积神经网络算法模型,通过对收集到的58种常见的交通标志图像作为数据集,进行迭代训练最后得到一个识别精度较高的模型文件,然后保存为本地的h5格式文件。再使用Django开发Web网页端操作界面,实现用户上传一张交通标志图片,识别其名称。
458 7
交通标志识别系统Python+卷积神经网络算法+深度学习人工智能+TensorFlow模型训练+计算机课设项目+Django网页界面
|
11月前
|
监控 安全 测试技术
如何确保API对接过程中的数据安全?
确保API对接过程中的数据安全至关重要。最佳实践包括:使用HTTPS协议、强化身份验证和授权、数据加密、输入验证、访问控制、限流限速、日志记录和监控、安全测试、数据脱敏、错误处理、API网关、Web应用程序防火墙(WAF)、审计和合规性。这些措施能有效提升API的安全性,保护数据免受恶意攻击和泄露风险。
|
存储 安全 JavaScript
服务器验证Cookie
【8月更文挑战第21天】
337 1
|
设计模式 Java 容器
Java一分钟之-Swing基础:JFrame, JPanel, JButton
Java Swing教程介绍了构建桌面应用的关键组件:JFrame(顶级容器,显示主窗口)、JPanel(组合其他组件的容器)和JButton(交互元素)。文中通过示例代码展示了这些组件的使用,并列出常见问题及解决方法,如确保设置JFrame的可见性和关闭操作,正确添加组件至JPanel,以及为JButton添加事件监听器。理解这些基础将有助于开发功能完善的GUI应用。
558 0