Creating an inexpensive, serverless, BI suite on AWS

Paul Ridgway -

Create an RDS cluster:

We will use the Serverless feature:

Configure scaling and create a VPC:

It will take a short while to create:

A few details:

Endpoint
metabase.cluster-c2y8czud1r4o.eu-west-1.rds.amazonaws.com
Port
3306
VPC security groups
rds-launch-wizard (sg-0f43f3d0dd63a89bc)
( active )

Be careful not to expose the database ot the public/open internet.

Create security groups for SSH access and Portal DB access:

Create a VM for port tunnelling:

A small cheap Ubuntu instance will do.

Assign a public IP:

Defaults for storage, tags optional.

Select existing, metabase-ssh security group:

Make sure to use an ssh key pair.

Tunnel to the MySQL deployment created earlier. Note, if running MySQL locally then you must stop it or use a different local port (3307 in the example below):

-> % ssh ubuntu@my-ec2-hostname -i /path/to/keypair.pem -L 3307:metabase.cluster-c2y8czud1r4o.eu-west-1.rds.amazonaws.com:3306

Now, using the MySQL client (or a GUI) we can connect, note the port 3307 is specified, as well as the username we created earlier in AWS, the -p flag asks for the password and passing the host of 127.0.0.1 stops mysql trying to use the local socket:

-> % mysql -h 127.0.0.1 -P 3307 -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.08 sec)

mysql>

Create a database for metabase, along with credentials to access it from any host (%) as we will not be controlling the access IP but we can trust it will be internal to our VPC:

mysql> CREATE DATABASE metabase;
Query OK, 1 row affected (0.11 sec)

mysql> CREATE USER metabase;
Query OK, 0 rows affected (0.11 sec)

mysql> GRANT ALL on metabase.* to `metabase`@`%` IDENTIFIED BY 'somepassword';
Query OK, 0 rows affected (0.08 sec)

Verify it works:

-> % mysql -h 127.0.0.1 -P 3307 -u metabase -p            
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use metabase;
Database changed
mysql>

Deploy metabase on Elastic Beanstalk:

https://s3.amazonaws.com/downloads.metabase.com/v0.31.2/metabase-aws-eb.zip

Latest can be found by going to http://downloads.metabase.com/v0.31.2/launch-aws-eb.html and clicking "Launch Metabase on AWS".

This works in itself for the us-east-1 region, to launch elsewhere click "Upload your code", then "Upload", and copy the origin URL: https://downloads.metabase.com.s3.amazonaws.com/v0.31.2/metabase-aws-eb.zip

To avoid an SSL error this does need rewriting as above, or remove https.

The link is also availabe under the "Deploying New Versions of Metabase" section of this guide.

I will be launching in eu-west-1, start with a new Elastic Beanstalk application:

Metabase / docker / upload zip, or use s3 url if in us-east-1:

Then click Configure more options.

Select the High Availability preset.

Then modify Software, optionally configure logging as desired, then set up the MySQL database for metabase to use, the following Environment Properties need setting:

MB_DB_TYPE=mysql
MB_DB_DBNAME=metabase
MB_DB_PORT=3306
MB_DB_USER=metabase
MB_DB_PASS=somepassword
MB_DB_HOST=metabase.cluster-c2y8czud1r4o.eu-west-1.rds.amazonaws.com

Save, and now scroll down and modify the Network.
Select the VPC created as part of the RDS creation step. Tick all the subnets for the load balancer and instances:

Save, and modify Instances:

The instance size can be whatever is desired but for casual use a t-series should be fine. Other options can be configured here, but it is not required.

Select the Metabase Portal Security Group created earlier, and Save:

Finally, modify the Load Balancer, select the Application Load Balancer and leave everything as is for now:

Other sections, such as Capacity, Rolling updates, Notifications, Monitoring, Managed Updates and Tags can be as desired. There is no need to create a Database here.

Once ready click Create app, this will take a good few minutes:

Once the instance starts configure the initial user:

Create your user:

Add data later, choose analytics:

Metabase Dashboard:

Add some sample data:

https://stackoverflow.com/questions/2441062/where-can-i-download-sample-mysql-database

https://code.google.com/archive/p/northwindextended/downloads