Introduction to Metabase ~ Environment Construction ~

Introduction

In recent years, I think that data visualization and democratization using BI tools have become extremely important. I have used BI tools such as Redash, but I haven't touched on Metabase, which has been a hot topic for a while, so I'll try to touch it once. For DB, I'm using MySQL8.

About the environment

Build with Docker. We have prepared the following docker-compose.yml.

docker-compose.yml

version: '3'
services:
  app:
    image: metabase/metabase:v0.37.0.2
    container_name: metabase
    ports:
      - 3000:3000
    volumes:
      - ./metabase/data:/mnt/data

  sample_db:
    build: ./db
    container_name: sample_db
    ports:
      - 33006:3306
    volumes:
      - mysql-sample-app-data:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: sample
      MYSQL_APP_USER: sample
      MYSQL_APP_USER_PASSWORD: sample
      MYSQL_DATABASE: metabase_sample
    restart: unless-stopped

volumes:
  mysql-sample-app-data:
    driver: local

Regarding the directory hierarchy, I uploaded the project to GitHub, so Please check here. https://github.com/inagacky/metabase_sample

Actually launch

Start it with the following command.

[inagacky@macbook] ~/workspace/metabase_sample
% docker-compose up -d

After that, access http: // localhost: 3000 and if the following screen appears, it is successful.

Initial setting

Follow the guide to Language, User Settings, and Data Source Settings. It's very polite, so you don't have to worry.

When the settings are complete, the screen below will appear. Initially there is Sample Dataset, but this time we will verify it with our own DB.

DB settings

Create a table and pour data into the DB that is running on Docker. Since we are in the real estate business, we make a simple table with a structure like that. This time, we will create customer information, property information, and contract information. (There are some fields that should be mastered, but it was troublesome, so I made it as it is.)

CREATE TABLE IF NOT EXISTS `customers` (      --Customer information
  `id` INT AUTO_INCREMENT,                    -- PK
  `name` VARCHAR(20) ,                        --Full name
  `age` INT,                                  --age
  PRIMARY KEY (`id`),
  KEY `idx_age`(`age`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
;

CREATE TABLE IF NOT EXISTS `property` (    --Property information
  `id` INT AUTO_INCREMENT,                 -- PK
  `prefName` VARCHAR(20) ,                 --Name of prefectures
  `price` BIGINT,                          --price
  `category` VARCHAR(20),                  --category(Detached house/land/Condominiums, etc.)
  PRIMARY KEY (`id`),
  KEY `idx_prefName`(`prefName`),
  KEY `idx_price`(`price`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
;

CREATE TABLE IF NOT EXISTS `contracts` (  --Contract information
  `customer_id` INT NOT NULL,             --Customer ID
  `property_id` INT NOT NULL,             --Property ID
  CONSTRAINT fk_customer_id
    FOREIGN KEY (customer_id) 
    REFERENCES customers (id)
    ON DELETE RESTRICT,
  CONSTRAINT fk_property_id
    FOREIGN KEY (property_id) 
    REFERENCES property (id)
    ON DELETE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
;

For the data, flow the following.

--Customer information
INSERT INTO `metabase_sample`.`customers` (`name`, `age`) VALUES ('Ichiro Yamada', '30');
INSERT INTO `metabase_sample`.`customers` (`name`, `age`) VALUES ('Jiro Yamada', '31');
INSERT INTO `metabase_sample`.`customers` (`name`, `age`) VALUES ('Ichiro Tanaka', '32');
INSERT INTO `metabase_sample`.`customers` (`name`, `age`) VALUES ('Jiro Tanaka', '45');
INSERT INTO `metabase_sample`.`customers` (`name`, `age`) VALUES ('Saburo Tanaka', '50');
INSERT INTO `metabase_sample`.`customers` (`name`, `age`) VALUES ('Ichiro Suzuki', '47');
INSERT INTO `metabase_sample`.`customers` (`name`, `age`) VALUES ('Jiro Suzuki', '20');

--Property information
INSERT INTO `metabase_sample`.`property` (`prefName`, `price`, `category`) VALUES ('Aichi prefecture', '32000000', 'Detached house');
INSERT INTO `metabase_sample`.`property` (`prefName`, `price`, `category`) VALUES ('Aichi prefecture', '40000000', 'Detached house');
INSERT INTO `metabase_sample`.`property` (`prefName`, `price`, `category`) VALUES ('Tokyo', '90000000', 'Detached house');
INSERT INTO `metabase_sample`.`property` (`prefName`, `price`, `category`) VALUES ('Tokyo', '120000000', 'apartment');
INSERT INTO `metabase_sample`.`property` (`prefName`, `price`, `category`) VALUES ('Aichi prefecture', '20000000', 'land');
INSERT INTO `metabase_sample`.`property` (`prefName`, `price`, `category`) VALUES ('Tokyo', '90000000', 'apartment');
INSERT INTO `metabase_sample`.`property` (`prefName`, `price`, `category`) VALUES ('Aichi prefecture', '24000000', 'apartment');

--Contract information
INSERT INTO `metabase_sample`.`contracts` (`customer_id`, `property_id`) VALUES ('1', '1');
INSERT INTO `metabase_sample`.`contracts` (`customer_id`, `property_id`) VALUES ('2', '2');
INSERT INTO `metabase_sample`.`contracts` (`customer_id`, `property_id`) VALUES ('3', '3');
INSERT INTO `metabase_sample`.`contracts` (`customer_id`, `property_id`) VALUES ('4', '4');
INSERT INTO `metabase_sample`.`contracts` (`customer_id`, `property_id`) VALUES ('5', '5');
INSERT INTO `metabase_sample`.`contracts` (`customer_id`, `property_id`) VALUES ('6', '6');
INSERT INTO `metabase_sample`.`contracts` (`customer_id`, `property_id`) VALUES ('7', '7');

Play with metabase

The introduction has become long, but I'm going to play with metabase a little.

Table join

Table joins can also be done on the GUI.

You can browse the table as shown below.

Graph

There are various types of graphs as well. (It didn't look good because there wasn't much data ...)

Dashboard

You can also create a dashboard as shown below.

Summary

I touched metabase for the first time, but I intuitively understood various things. Since the article became longer than I expected due to environment construction etc., regarding each function of metabase, I will touch on graph creation with various indicators in another article. .. (Please be aware that this is an environment construction edition ...)

That's all, thank you.