metabase 数据可视化

Metabase是一种开源的数据分析和可视化工具，用户可以轻松创建各种类型的数据可视化图表和报表，帮助用户更深入地了解数据。

[TOC]

安装部署

• 第1步，安装 java 20.0.2

curl -sL http://iso.sqlfans.cn/jdk/install_openjdk_2002.sh | bash
source /etc/bashrc
java -version

• 第2步，安装 mysql 8.0.32

#.安装基础依赖包
yum install -y lrzsz wget telnet net-tools ntpdate

#.安装 mysql 8.0.32
cd /opt
wget -c http://iso.sqlfans.cn/mysql/percona-server-8.0.32-24.tar.gz
wget -c http://iso.sqlfans.cn/mysql/config/8.0/my_innodb.cnf
wget -c http://iso.sqlfans.cn/mysql/install_percona_8032.sh
bash install_percona_8032.sh /opt /data 3306

• 第3步，创建mysql数据库及账号

mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3306.sock -e"CREATE DATABASE metabase DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3306.sock -e"CREATE USER 'user_metabase'@'%' IDENTIFIED with mysql_native_password BY 'Admin_147';"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3306.sock -e"GRANT ALL PRIVILEGES ON metabase.* TO 'user_metabase'@'%';"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3306.sock -e"FLUSH PRIVILEGES;"

• 第4步，下载 metabase v0.49

mkdir -p /data/metabase_3000
curl -L http://iso.sqlfans.cn/linux/metabase.v0.49.jar -o /data/metabase_3000/metabase.jar

• 第5步，创建 metabase 启动脚本，注意修改mysql的ip地址，示例为 127.0.0.1

cat > /data/metabase_3000/start.sh <<EOF
#!/bin/sh

export DMB_JETTY_PORT=3000
export MB_DB_TYPE=mysql
export MB_DB_DBNAME=metabase
export MB_DB_PORT=3306
export MB_DB_USER=user_metabase
export MB_DB_PASS=Admin_147
export MB_DB_HOST=127.0.0.1

nohup java -jar /data/metabase_3000/metabase.jar > /data/metabase_3000/metabase.log 2>&1 & 
EOF

• 第6步，启动 metabase

chmod +x /data/metabase_3000/start.sh
sh /data/metabase_3000/start.sh

• 第7步，确认metabase端口已监听，且mysql元数据表已创建

sleep 5
mysql -uuser_metabase -pAdmin_147 -h127.0.0.1 -e"show tables from metabase;"
netstat -lnpt | grep 3000

• 第8步，登录 metabase 控制台，开始配置

地址：http://127.0.0.1:3000
邮箱地址：123456@qq.com
团队名称：xx科技
密码：Admin_147

使用手册

遇到的问题

• 问题1：如何全部铲掉

ps -ef | grep java | grep metabase | awk '{print $2}' | xargs kill -9 2> /dev/null
rm -rf /data/metabase_3000
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3306.sock -e"drop database metabase;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3306.sock -e"drop user user_metabase;"

• 问题2：启动 metabase.v0.49.jar 报错，解决：将 mysql 由 5.7.42 改为 8.0.32

2024-04-10 05:08:02,048 INFO plugins.dependencies :: Metabase cannot initialize plugin Metabase Oracle Driver due to required dependencies. 
  Metabase requires the Oracle JDBC driver in order to connect to Oracle databases, but we can't ship it as part of Metabase due to licensing restrictions.
2024-04-10 05:08:02,050 INFO plugins.dependencies :: Metabase Oracle Driver dependency {:class oracle.jdbc.OracleDriver} satisfied? false
2024-04-10 05:08:02,050 INFO plugins.dependencies :: Plugins with unsatisfied deps: ["Metabase Oracle Driver"]

使用技巧

• 技巧1：metabase添加2个过滤框(注意2个and被括的位置)

WHERE 1=1 [[and his.host =  ]] [[and fr.project_name =  ]]

• 技巧2：as语法不兼容

不支持：select feiyong,feiyong / 10 as xxx from fusion_resource where xxx > 40
改造为：select a1.xxx as '费用' from (select feiyong,feiyong / 10 as xxx from fusion_resource)a1 where xxx > 40