This example explains how to install and setup a Metabase instance on EC2 in a fully automated way. So what is Metabase? Metabase is an open-source business intelligence (BI) tool that allows users to ask questions and learn from data. It offers a simple interface to visualize and share data across teams without requiring a deep knowledge of SQL, though it does allow for SQL queries for more advanced users. Metabase can connect to a wide range of databases, from traditional RDBMS systems like PostgreSQL, MySQL, and SQL Server to some NoSQL databases. So this is ideal to get some high level insights into your databases but it also mandates a high level of security and careful permission and access management. In this example we will address the following challenges by installing, securely accessing and setting up Metabase together with qbee on EC2 creating the following benefits:
- easy software installation without SSH access
- secure access: instance is not publicly available and we can control/audit who access it (through our RBAC + Audit Log)
- scalability: one can manage several instances of Metabase (or any docker service) for many customers/locations with minimal operational overhead
Prepare qbee account
Bootstrap EC2 instance
- Create a new instance using Ubuntu 22.04 LTS (HVM) AMI
- Select instance type which has enough CPU and memory to meet your needs. We will start with t2.medium which seems to be a good starting point.
- Placed the instance inside a VPC subnet which has internet access and can communicate with your database instance.
- Set your SSH keys – you can manage your SSH keys with qbee, but we need to set one to even start the instance (as it’s a required field).
- In the advanced section, set the User data to the qbee install script:
Remember to replace the <bootstrap-key> with the bootstrap key obtained in the preparation section.
- Launch the instance.
Configure the system
Once the instance appears on your devices list, it will present itself with the hostname of the EC2 instance. We can change that to a more user-friendly name using the device attributes section:
Now, that we’ve identified our new device, we can go to the Configure section to setup the system and run the metabase docker image:
- Select your instance in the node dropdown and then select Software Management section.
- There we need to configure installation of docker.io package which will run the docker service.
- Once we fill out both fields, we need to save the change using Save settings button.
- After that, we need to navigate to the Docker section, where we configure our metabase container:
- In this section, we need to set:
– Name: “metabase” – this identifies our docker container
– Image: “metabase/metabase” – this defines the docker image used to start the container
– Docker arguments: “-p 3000:3000 -v /metabase.db:/metabase.db”
-p 3000:3000exposes metabase running on port TCP 3000 on the host port 3000
-v /metabase.db:/metabase.dbstores the metabase configuration database on the host
- Once configured, we can hit “Save settings”
- To make the changes permanent and apply them to the device, we need to Commit changes:
- After the next agent run (which by default is scheduled every 5 minutes), the system will install the docker debian package and start the docker container with metabase service.
- To access the service securely, we can use qbee-connect:
We need to select custom port Type, enter 3000 into the port field and select TCP as Protocol. Once we click on the Connect button, we will be presented with “Mapped port: 123” which will identify localhost port on which we can access the metabase service.
From here, you can setup your Metabase using the official guide
For the real production use, one should look into using a shared database (in case of AWS it would be RDS Postgresql or MySQL), which can be configured through qbee using environmental file containing the right variables set – . Alternatively, one could try using AWS EFS to make metabase H2 database survive EC2 instance deletion.