Creating an inexpensive, serverless, BI suite on AWS

Creating an inexpensive, serverless, BI suite on AWS

Create an RDS cluster:

Screenshot-from-2019-01-20-16-37-19

We will use the Serverless feature:

Screenshot-from-2019-01-20-16-37-47

Configure scaling and create a VPC:

Screenshot-from-2019-01-20-16-38-45

It will take a short while to create:

Screenshot-from-2019-01-20-16-39-21

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:

c

Screenshot-from-2019-01-21-07-50-01

Screenshot-from-2019-01-21-07-51-04

Create a VM for port tunnelling:

A small cheap Ubuntu instance will do.

Screenshot-from-2019-01-27-20-22-54

Assign a public IP:

Screenshot-from-2019-01-27-20-23-46

Defaults for storage, tags optional.

Select existing, metabase-ssh security group:

Screenshot-from-2019-01-27-20-24-44

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 [email protected] -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

Screenshot-from-2019-01-28-08-19-13

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:

Screenshot-from-2019-01-28-08-31-20

Then click Configure more options.

Select the High Availability preset.

Screenshot-from-2019-01-28-08-31-53

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

Screenshot-from-2019-01-28-08-32-37

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:

Screenshot-from-2019-01-28-08-33-33

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:

Screenshot-from-2019-01-28-08-35-29

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

Screenshot-from-2019-01-28-08-36-42

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:

Screenshot-from-2019-01-28-08-39-15

Once the instance starts configure the initial user:

Screenshot-from-2019-01-28-09-31-36

Screenshot-from-2019-01-28-09-32-40

Create your user:

Screenshot-from-2019-01-28-09-31-59

Add data later, choose analytics:

Screenshot-from-2019-01-28-09-32-14

Metabase Dashboard:

Screenshot-from-2019-01-28-09-32-31

Add some sample data:

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

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