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. スクリーンショット 2020-10-29 9.47.05.png

Initial setting

Follow the guide to Language, User Settings, and Data Source Settings. It's very polite, so you don't have to worry. スクリーンショット 2020-10-29 9.49.31.png

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. スクリーンショット 2020-10-29 9.52.42.png

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. スクリーンショット 2020-10-29 11.12.30.png

You can browse the table as shown below. スクリーンショット 2020-10-29 11.13.15.png

Graph

There are various types of graphs as well. (It didn't look good because there wasn't much data ...) スクリーンショット 2020-10-29 11.18.37.png

Dashboard

You can also create a dashboard as shown below. スクリーンショット 2020-10-29 11.24.36.png

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.

Recommended Posts

Introduction to Metabase ~ Environment Construction ~
Introduction to Robot Battle with Robocode (Environment Construction)
Road to Java Engineer Part1 Introduction & Environment Construction
Introduction to kotlin for iOS developers ①-Environment construction
Introduction to Slay the Spire Mod Development (2) Development Environment Construction
Introduction to Ruby 2
[Java] Environment construction
Introduction to SWING
Java environment construction
Jekyll super introduction starting from 0 # 1 --Environment construction
Introduction to web3j
Introduction to Micronaut 1 ~ Introduction ~
[Java] Introduction to Java
Introduction to migration
Introduction to java
[Spring] Environment construction
Docker environment construction
Introduction to Doma
How to link Rails6 Vue (from environment construction)
Penronse environment construction [Windows]
[Environment construction] Eclipse installation
Introduction to JAR files
Introduction to Ratpack (8)-Session
[Flutter] Ubuntu 20.04 environment construction
Rails Docker environment construction
Introduction to RSpec 1. Test, RSpec
Introduction to bit operation
Introduction to Ratpack (6) --Promise
Introduction to Ratpack (9) --Thymeleaf
Introduction to PlayFramework 2.7 ① Overview
Introduction to Android Layout
Introduction to design patterns (introduction)
Introduction to Practical Programming
Introduction to javadoc command
Circle CI environment construction
Introduction to jar command
Introduction to Ratpack (2)-Architecture
java development environment construction
Introduction to lambda expression
Introduction to java command
Introduction to RSpec 2. RSpec setup
Introduction to Keycloak development
Introduction to javac command
Deploy to Ruby on Rails Elastic beanstalk (Environment construction)
Introduction to Design Patterns (Builder)
Rails6 development environment construction [Mac]
From 0 to Ruby on Rails environment construction [macOS] (From Homebrew installation to Rails installation)
Rails engineer environment construction ruby2.7.1
EC-CUBE4 environment construction (local edition)
I tried node-jt400 (Environment construction)
Rails environment construction Rails5.2.1 ruby2.5.1 Catalina
Introduction to RSpec 5. Controller specs
Introduction to RSpec 6. System specifications
Introduction to Android application development
Introduction to RSpec 3. Model specs
Introduction to Ratpack (5) --Json & Registry
MySQL 5.7 (Docker) environment construction memo
Introduction to Ratpack (7) --Guice & Spring
(Dot installation) Introduction to Java8_Impression
Introduction to Design Patterns (Composite)
Introduction to Micronaut 2 ~ Unit test ~