- 搭建docker可视化
- docker-compose
- Dockerfile
- mysql
- superset
- elasticsearch kibana
- 迁移数据
- 花生壳映射
- 其他
docker-compose由于集群在内网，无法展示效果，且出于安全等因素考虑，不搭建内网穿透。所以，将可视化的模块从庞大的数仓集群独立出来，在个人电脑上使用docker搭建一个小服务，对外开放。只保留关键的ads数据库、后端、前段可视化展示。包括如下服务：es，mysql，superset。为了方便管理这些零散的服务，使用docker-compose将其制作成镜像。
服务搭建完成后，进行数据导入。
mysql的数据用navicate导出为sql，再导入docker中的新数据库。
elasticsearch借助logstash导入到新的es数据库。
version: "2"
services:
mysql:
image: vs-mysql
build:
context: ./docker/mysql
dockerfile: ./Dockerfile
environment:
- MYSQL_ROOT_PASSWORD=root
- MYSQL_USER=vsserver
- MYSQL_PASSWORD=vsserver
- MYSQL_DATABASE=gmall_report
volumes:
- "./data/mysqldb:/var/lib/mysql"
ports:
- 3307:3306
networks:
vs_server_net:
ipv4_address: 172.42.1.10
superset:
image: vs-superset
build:
context: ./docker/superset
dockerfile: ./Dockerfile
ports:
- 8787:8787
networks:
vs_server_net:
ipv4_address: 172.42.1.11
elasticsearch:
image: vs-elasticsearch
build:
context: ./docker/elasticsearch
dockerfile: ./Dockerfile
deploy:
resources:
limits:
memory: 4096M
ulimits:
memlock:
soft: -1
hard: -1
volumes:
- "./data/elasticsearch:/usr/share/elasticsearch/data"
# - "./data/kibana:/usr/local/kibana-7.11.1-linux-x86_64/"
ports:
- 9201:9200
- 5602:5601
networks:
vs_server_net:
ipv4_address: 172.42.1.12
networks:
vs_server_net:
ipam:
driver: default
config:
- subnet: 172.42.1.0/24
Dockerfile
mysql
platform.sql中是自己的建表语句
FROM mariadb:latest USER root COPY ./platform.sql /docker-entrypoint-initdb.d RUN chown -R mysql:mysql /docker-entrypoint-initdb.d/
supersetplatform.sql创建ads表，sql内容较长，放到结尾。
如下的dockerfile主要是为了安装python的依赖，将python和pip编译并安装好。然后使用pip安装superset及gunicorn其依赖。其中gunicorn是superset的后端http服务
FROM centos:7 ENV FLASK_APP=superset RUN useradd -m platform && yum install -y gcc gcc-c++ libffi-devel MySQL-python mysql-devel python-devel python-devel python-pip python-wheel python-setuptools openssl-devel cyrus-sasl-devel openldap-devel bzip2-devel expat-devel gdbm-devel readline-devel sqlite-devel automake autoconf libtool make libffi-devel wget RUN cd /usr/local/ #编译安装python && wget https://www.python.org/ftp/python/3.7.0/Python-3.7.0.tgz &&tar -zxvf Python-3.7.0.tgz && cd /usr/local/Python-3.7.0/ && ./configure --prefix=/usr/local/python3 && make && make install &&mv /usr/bin/python /usr/bin/python_bak &&ln -s /usr/local/python3/bin/python3 /usr/bin/python && cd /usr/local && wget https://bootstrap.pypa.io/get-pip.py && python get-pip.py &&ln -s /usr/local/python3/bin/pip3 /usr/bin/pip Run pip install --upgrade setuptools pip -i https://pypi.douban.com/simple/ Run pip install apache-superset==0.36.0 -i https://pypi.douban.com/simple/ markupsafe==2.0.1 -i https://pypi.douban.com/simple/ WTForms==2.3.3 sqlalchemy==1.3.24 pymysql COPY --chown=platform:platform ./entrypoint.sh /home/platform/ USER root CMD ["sh","/home/platform/entrypoint.sh"] #CMD ["python3","-m","http.server","8888"]
#第一次启动还要进行如下操作 #superset需要创建数据库，指定登陆web界面的用户和密码，及后续的gunicorn这一部分需要如下手动完成。 先使用ln -s将superset、gunicorn的可执行文件链接到/usr/bin，然后： superset db upgrade export FLASK_APP=superset superset fab create-admin superset init #配置前端页面,其中ip为容器ip pip install gunicorn -i https://pypi.douban.com/simple/ gunicorn --workers 5 --timeout 120 --bind ip:8787 "superset.app:create_app()" #关于为什么可以直接在终端使用superset命令，因为它将site-packages/superset/bin/superset软连接到了/usr/bin或添加了环境变量。有时候其可执行文件不是放在对应安装包的bin目录下，而是在python环境的bin目录，gunicorn就是这样，其可执行文件放在conda/env/my_python/bin/gunicornelasticsearch kibana
将elasticsearch拷贝进去，修改配置文件，启动
导入数据
FROM centos:7 ADD ./jdk-11.0.10_linux-x64_bin.tar.gz /usr/local/java/ ENV JAVA_HOME /usr/local/java/jdk-11.0.10 ENV PATH $JAVA_HOME/bin:$PATH RUN useradd -m elasticsearch ADD ./kibana-7.11.1-linux-x86_64.tar.gz /usr/local COPY --chown=elasticsearch:elasticsearch ./elasticsearch /usr/share/elasticsearch COPY --chown=elasticsearch:elasticsearch ./kibana.yml /usr/local/kibana-7.11.1-linux-x86_64/config COPY --chown=elasticsearch:elasticsearch ./entrypoint.sh /usr/local USER elasticsearch ENTRYPOINT ["/usr/local/entrypoint.sh"]
entrypoint.sh启动脚本
#!/bin/bash /usr/share/elasticsearch/bin/elasticsearch -d /usr/local/kibana-7.11.1-linux-x86_64/bin/kibana迁移数据
mysql中的数据分为两块，
一块是gmall_report，是数据仓库的的统计结果ads。
另一块是data_supervisor,是数据质量控制的统计结果。这两块借助navicate的导出导入功能迁移数据
elasticsearch主要是spakrstreaming的统计结果，可以使用logstash将数据从集群采集到docker中的elasticsearch中。
#mysql 使用navicate选中数据库，右键转储为sql文件。 或使用终端，mysqldump --opt -h-uusername -ppassword --skip-lock-tables databasename>database.sql #然后到docker的myql中执行该sql source database.sql
#elasticsearch使用其配套的logstash导入,host1,host2分别是源ip和目的ip
vim demo.conf
#添加如下内容
input {
elasticsearch {
hosts => ["host1:9200"]
index => "gmall0523_order_info_20220410"
size => 1000
scroll => "5m"
}
}
filter {}
output {
elasticsearch{
hosts => ["host2:9200"]
index => "gmall0523_order_info_20220410"
}
}
#创建好文件后，执行logstash
bin/logstash -f config/demo.conf
花生壳映射
其他到官网下载软件并安装，免费用户可以使用两个映射，并且只能使用http，每个月有1G免费流量。
点击添加映射，输入服务所在的ip和端口，然后开启映射，就可以复制它的映射地址，通过公网访问了。
如果你是在window电脑上创建虚拟机，然后在虚拟机内部使用的docker，需要将docker的端口映射到外部的虚拟机端口，然后将花生壳映射配置为虚拟机的端口。
platform.sql
use gmall_report; DROP TABLE IF EXISTS ads_visit_stats; CREATE TABLE `ads_visit_stats` ( `dt` DATE NOT NULL COMMENT '统计日期', `is_new` VARCHAr(255) NOT NULL COMMENT '新老标识,1:新,0:老', `recent_days` INT NOT NULL COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天', `channel` VARCHAr(255) NOT NULL COMMENT '渠道', `uv_count` BIGINT(20) DEFAULT NULL COMMENT '日活(访问人数)', `duration_sec` BIGINT(20) DEFAULT NULL COMMENT '页面停留总时长', `avg_duration_sec` BIGINT(20) DEFAULT NULL COMMENT '一次会话，页面停留平均时长', `page_count` BIGINT(20) DEFAULT NULL COMMENT '页面总浏览数', `avg_page_count` BIGINT(20) DEFAULT NULL COMMENT '一次会话，页面平均浏览数', `sv_count` BIGINT(20) DEFAULT NULL COMMENT '会话次数', `bounce_count` BIGINT(20) DEFAULT NULL COMMENT '跳出数', `bounce_rate` DECIMAL(16,2) DEFAULT NULL COMMENT '跳出率', PRIMARY KEY (`dt`,`recent_days`,`is_new`,`channel`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS ads_page_path; CREATE TABLE `ads_page_path` ( `dt` DATE NOT NULL COMMENT '统计日期', `recent_days` BIGINT(20) NOT NULL COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天', `source` VARCHAr(255) DEFAULT NULL COMMENT '跳转起始页面', `target` VARCHAr(255) DEFAULT NULL COMMENT '跳转终到页面', `path_count` BIGINT(255) DEFAULT NULL COMMENT '跳转次数', UNIQUE KEY (`dt`,`recent_days`,`source`,`target`) USING BTREE ) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; DROP TABLE IF EXISTS ads_user_total; CREATE TABLE `ads_user_total` ( `dt` DATE NOT NULL COMMENT '统计日期', `recent_days` BIGINT(20) NOT NULL COMMENT '最近天数,0:累积值,1:最近1天,7:最近7天,30:最近30天', `new_user_count` BIGINT(20) DEFAULT NULL COMMENT '新注册用户数', `new_order_user_count` BIGINT(20) DEFAULT NULL COMMENT '新增下单用户数', `order_final_amount` DECIMAL(16,2) DEFAULT NULL COMMENT '下单总金额', `order_user_count` BIGINT(20) DEFAULT NULL COMMENT '下单用户数', `no_order_user_count` BIGINT(20) DEFAULT NULL COMMENT '未下单用户数(具体指活跃用户中未下单用户)', PRIMARY KEY (`dt`,`recent_days`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS ads_user_change; CREATE TABLE `ads_user_change` ( `dt` DATE NOT NULL COMMENT '统计日期', `user_churn_count` BIGINT(20) DEFAULT NULL COMMENT '流失用户数', `user_back_count` BIGINT(20) DEFAULT NULL COMMENT '回流用户数', PRIMARY KEY (`dt`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS ads_user_action; CREATE TABLE `ads_user_action` ( `dt` DATE NOT NULL COMMENT '统计日期', `recent_days` BIGINT(20) NOT NULL COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天', `home_count` BIGINT(20) DEFAULT NULL COMMENT '浏览首页人数', `good_detail_count` BIGINT(20) DEFAULT NULL COMMENT '浏览商品详情页人数', `cart_count` BIGINT(20) DEFAULT NULL COMMENT '加入购物车人数', `order_count` BIGINT(20) DEFAULT NULL COMMENT '下单人数', `payment_count` BIGINT(20) DEFAULT NULL COMMENT '支付人数', PRIMARY KEY (`dt`,`recent_days`) USING BTREE ) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; DROP TABLE IF EXISTS ads_user_retention; CREATE TABLE `ads_user_retention` ( `dt` DATE DEFAULT NULL COMMENT '统计日期', `create_date` VARCHAr(255) NOT NULL COMMENT '用户新增日期', `retention_day` BIGINT(20) NOT NULL COMMENT '截至当前日期留存天数', `retention_count` BIGINT(20) DEFAULT NULL COMMENT '留存用户数量', `new_user_count` BIGINT(20) DEFAULT NULL COMMENT '新增用户数量', `retention_rate` DECIMAL(16,2) DEFAULT NULL COMMENT '留存率', PRIMARY KEY (`create_date`,`retention_day`) USING BTREE ) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; DROP TABLE IF EXISTS ads_order_total; CREATE TABLE `ads_order_total` ( `dt` DATE NOT NULL COMMENT '统计日期', `recent_days` BIGINT(20) NOT NULL COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天', `order_count` BIGINT(255) DEFAULT NULL COMMENT '订单数', `order_amount` DECIMAL(16,2) DEFAULT NULL COMMENT '订单金额', `order_user_count` BIGINT(255) DEFAULT NULL COMMENT '下单人数', PRIMARY KEY (`dt`,`recent_days`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; DROP TABLE IF EXISTS ads_order_by_province; CREATE TABLE `ads_order_by_province` ( `dt` DATE NOT NULL, `recent_days` BIGINT(20) NOT NULL COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天', `province_id` VARCHAr(255) NOT NULL COMMENT '统计日期', `province_name` VARCHAr(255) DEFAULT NULL COMMENT '省份名称', `area_code` VARCHAr(255) DEFAULT NULL COMMENT '地区编码', `iso_code` VARCHAr(255) DEFAULT NULL COMMENT '国际标准地区编码', `iso_code_3166_2` VARCHAr(255) DEFAULT NULL COMMENT '国际标准地区编码', `order_count` BIGINT(20) DEFAULT NULL COMMENT '订单数', `order_amount` DECIMAL(16,2) DEFAULT NULL COMMENT '订单金额', PRIMARY KEY (`dt`, `recent_days` ,`province_id`) USING BTREE ) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; DROP TABLE IF EXISTS ads_repeat_purchase; CREATE TABLE `ads_repeat_purchase` ( `dt` DATE NOT NULL COMMENT '统计日期', `recent_days` BIGINT(20) NOT NULL COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天', `tm_id` VARCHAr(255) NOT NULL COMMENT '品牌ID', `tm_name` VARCHAr(255) DEFAULT NULL COMMENT '品牌名称', `order_repeat_rate` DECIMAL(16,2) DEFAULT NULL COMMENT '复购率', PRIMARY KEY (`dt` ,`recent_days`,`tm_id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; DROP TABLE IF EXISTS ads_order_spu_stats; CREATE TABLE `ads_order_spu_stats` ( `dt` DATE NOT NULL COMMENT '统计日期', `recent_days` BIGINT(20) NOT NULL COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天', `spu_id` VARCHAr(255) NOT NULL COMMENT '商品ID', `spu_name` VARCHAr(255) DEFAULT NULL COMMENT '商品名称', `tm_id` VARCHAr(255) NOT NULL COMMENT '品牌ID', `tm_name` VARCHAr(255) DEFAULT NULL COMMENT '品牌名称', `category3_id` VARCHAr(255) NOT NULL COMMENT '三级品类ID', `category3_name` VARCHAr(255) DEFAULT NULL COMMENT '三级品类名称', `category2_id` VARCHAr(255) NOT NULL COMMENT '二级品类ID', `category2_name` VARCHAr(255) DEFAULT NULL COMMENT '二级品类名称', `category1_id` VARCHAr(255) NOT NULL COMMENT '一级品类ID', `category1_name` VARCHAr(255) NOT NULL COMMENT '一级品类名称', `order_count` BIGINT(20) DEFAULT NULL COMMENT '订单数', `order_amount` DECIMAL(16,2) DEFAULT NULL COMMENT '订单金额', PRIMARY KEY (`dt`,`recent_days`,`spu_id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS ads_activity_stats; CREATE TABLE `ads_activity_stats` ( `dt` DATE NOT NULL COMMENT '统计日期', `activity_id` VARCHAr(255) NOT NULL COMMENT '活动ID', `activity_name` VARCHAr(255) DEFAULT NULL COMMENT '活动名称', `start_date` DATE DEFAULT NULL COMMENT '开始日期', `order_count` BIGINT(11) DEFAULT NULL COMMENT '参与活动订单数', `order_original_amount` DECIMAL(16,2) DEFAULT NULL COMMENT '参与活动订单原始金额', `order_final_amount` DECIMAL(16,2) DEFAULT NULL COMMENT '参与活动订单最终金额', `reduce_amount` DECIMAL(16,2) DEFAULT NULL COMMENT '优惠金额', `reduce_rate` DECIMAL(16,2) DEFAULT NULL COMMENT '补贴率', PRIMARY KEY (`dt`,`activity_id` ) ) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; DROP TABLE IF EXISTS ads_coupon_stats; CREATE TABLE `ads_coupon_stats` ( `dt` DATE NOT NULL COMMENT '统计日期', `coupon_id` VARCHAr(255) NOT NULL COMMENT '优惠券ID', `coupon_name` VARCHAr(255) DEFAULT NULL COMMENT '优惠券名称', `start_date` DATE DEFAULT NULL COMMENT '开始日期', `rule_name` VARCHAr(200) DEFAULT NULL COMMENT '优惠规则', `get_count` BIGINT(20) DEFAULT NULL COMMENT '领取次数', `order_count` BIGINT(20) DEFAULT NULL COMMENT '使用(下单)次数', `expire_count` BIGINT(20) DEFAULT NULL COMMENT '过期次数', `order_original_amount` DECIMAL(16,2) DEFAULT NULL COMMENT '使用优惠券订单原始金额', `order_final_amount` DECIMAL(16,2) DEFAULT NULL COMMENT '使用优惠券订单最终金额', `reduce_amount` DECIMAL(16,2) DEFAULT NULL COMMENT '优惠金额', `reduce_rate` DECIMAL(16,2) DEFAULT NULL COMMENT '补贴率', PRIMARY KEY (`dt`,`coupon_id` ) ) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
