基于 GBase 8a MPP Cluster 的数据可视化技术案例

基于 GBase 8a MPP Cluster 的数据可视化技术案例

• 一、概述：
• 二、名词解释
• 三、架构图
• 四、业务数据的准备
• 五、Python 环境的准备
• 六、Python 逻辑层代码（getCertInfo.py）
• 七、视图层
• 八、工程代码架构

@作者：郝军

一、概述：

      本文讲解以 GBase 8a MPP Cluster （以下简称 8a集群）做为大数据平台、python Flask 做为WEB框架、eCharts 组件做为富客户端展示。以实例代码做为主线，阐述基于8a集群的数据可视化技术。
      本文最适合读者：参加过 GBase 8a MPP Cluster GDCA 认证培训的学员，SQL 编码熟练、初级WEB开发经验、Python 编码基础。

二、名词解释

（一）、 GBase 8a MPP Cluster
      天津南大通用数据技术股份有限公司（以下简称南大通用）自主研发的大规模分布式并行处理数据集集群。8a集群是国内最早的数据库集群产品，至今将近10年，可谓十年磨一剑，始终保持国产数据库市场占有份额前七的位置。其联邦架构和 Shared Nothing + MPP 核心技术始终保持国内领先。

（二）、OLAP 函数：
封装在 8a集群数据库引擎内部的，用简单语法处理复杂数据分析的 组SQL 函数集。虽然比 Oracle 9i OLAP 函数落后 3 年，但最新版本的成熟度已经非常高了。

（三）、Flask：

• Python 领域轻量型、可定制、可扩展的强大的 WEB 微框架。
• 遵循 WSGI (Web Server Gateway Interface) 标准。
• 提供灵活的模板引擎，与特定数据源组合以呈现动态网页。
• 其 MVC （模型/视图/控制器）模式非常接近 J2EE 的 MVC。
  未来我会发表《python3_Chapter19_Flask》课件，系统讲解 Flask Web 编码知识。

（四）、路由函数：
Flask 框架下，在视图函数定义时候，使用装饰器指定的特殊函数。装饰器指定的路径即该函数访问的 URL 相对路径，或者说路由地址。

三、架构图

用户通过浏览器发出请求，Flask模板引擎（jinjia2）将路由地址和参数传送给Python代码端的控制器，调用对应的路由函数。路由函数接收参数，通过GBase Python Connector建立与GBase 8a MPP Cluster的数据链接，执行SQL或者存储过程，GBase 8a MPP Cluster将结果集回馈给控制器，控制器再将数据集以JSON格式传递给模板引擎，经过模板引擎的解释最终将动态数据展示在网页上。

四、业务数据的准备

1、主题数据：南大通用认证证书数量。
2、表结构主要字段：

字段名称	数据类型	注解
certificate_id	varchar(32)	证书数据唯一标识
certificate_num	varchar(30)	证书编号
certificate_createDate	varchar(50)	证书数据产生时间
certificate_is_delete	varchar(1)	行数据是否被删除

3、借助 OLAP 函数实现环比增长的计算：
(1) 核心 SQL 文：

SELECT concat(Y,M)'YM', certQty, 
concat( round(certQty*100 / LAG(certQty,1) over(partition by Y ORDER BY M)-100 ,2), '%')'环比'
from (
SELECT year(certificate_createDate)'Y', right(concat('0',month(certificate_createDate)),2)'M',
	count(certificate_num)'certQty'
FROM certificate_info
WHERE certificate_is_delete=0
GROUP BY year(certificate_createDate),month(certificate_createDate)
)T
order by Y,M;

(2) SQL 架构：
      子表 T 的作用是以证书生成年月做为聚合条件统计证书数量。从certificate_createDate抽取出年和月，月份格式两位0补齐。然后在子表 T 的基础上，使用 LAG 函数得到上期证书数量。环比增长率 = (本期 / 上期 - 1 ) * 100%

(3) OLAP 函数：
      LAG是偏移类OLAP函数，返回窗口中当前行之前给定偏移量的输入表达式的值作为新的投影列。
LAG(certQty,1) over(partition by Y ORDER BY M) 的意思是以年（Y）做为窗口（大分组），每组以月份升序排序，当前行数据带出上期的数据，做为环比计算最重要的因子。
未来我会发表《05-GBase 8a MPP Cluster OLAP 函数》系统讲解8a集群支持的OLAP函数。

(4) 将 SQL 文封装为存储过程：
可在 GBase Data Studio 中执行以下SQL，创建存储过程 getCertQty。

CREATE PROCEDURE "getCertQty"(IN iYear smallint)
BEGIN
SELECT concat(Y,M)'YM', certQty, 
#LAG(certQty,1) over(partition by Y ORDER BY M)
concat( round(certQty*100 / LAG(certQty,1) over(partition by Y ORDER BY M)-100 ,2), '%')'环比'
from (
SELECT year(certificate_createDate)'Y', right(concat('0',month(certificate_createDate)),2)'M',
#concat( year(certificate_issuingDate), right(concat('0',month(certificate_issuingDate)),2) ) as 'YM',
	count(certificate_num)'certQty'
FROM certificate_info
WHERE certificate_is_delete=0 and year(certificate_createDate)=iYear
GROUP BY year(certificate_createDate),month(certificate_createDate)
)T
order by Y,M;
END

输入参数 iYear 可以过滤出当前年份的数据。存储过程具体语法，未来我会发表《04-GBase 8a MPP Cluster 存储过程》的文章系统讲述。

五、Python 环境的准备

1、下载必要的安装包：
◆ 网盘地址：https://pan.baidu.com/s/1PMBzZ-bJN33Bce5YFbOuXQ
◆ 提取码：bpg6

网盘目录：GBase数据可视化技术

◆ 文件列表：
- python-3.6.8-amd64.exe
- gbase-connector-python-3.0.1_forpy3.6.tar.gz
- GBaseDataStudio_9.5.2.0_build7_Windows_x86_64.zip

2、数据库环境
使用云服务器（IP：118.195.185.192；数据服务端口：21039）安装好的8a v95集群，或者自己安装练习版。数据库账户：student 密码：student2022

3、开发环境：
本文在 windows 7 旗舰版 64bit 操作系统下，使用 pycharm-professional 作为 IDE。请自行下载和安装。

4、GBase Python Connector
(1) 安装 python3.6：
执行 python-3.6.8-amd64.exe，安装 Python 运行环境。
目前 GBase Python Connector 支持的 Python 环境最高版本是 3.6.8，所以本文代码都是在此环境下调试和运行的。
(2) 解压 gbase-connector-python-3.0.1_forpy3.6.tar.gz 文件，在 .\dist\gbase-connector-python-3.0.1\ 目录下的 console 下执行 python setup.py install
(3) 验证是否装好 ：在 DOS 下执行 pip list 可以看到 列表中有 gbase-connnector-python
5、安装 Flask：

pip install Flask

六、Python 逻辑层代码（getCertInfo.py）

1、引入模块

import flask
from flask import Flask, render_template, jsonify
from GBaseConnector import connect,GBaseError

2、路由函数

# 引入 Flask 应用
app = Flask(__name__)

# 浏览器中输入 http://localhost/certification 即可跳转此路由。此为主路由函数。
@app.route("/certification", methods=["GET"])
def form():
    return render_template("query.html")

# 为 query.html 中 eCharts 组件提供业务数据的路由函数。
@app.route("/certStat", methods=["GET"])
def certStat():
    if request.method == "GET":
        res = getCertQtyFromGBase(2021,None,'8a')
    n = len(res)
    ym,cQty,rate,d = [],[],[],[]
    for r in res:
        if not r[0] is None:
            ym.append(r[0]) # 年月
            cQty.append(r[1]) # 证书数量
            rate.append(r[2]) # 环比增长率
    #yMax = max(cQty)
    return jsonify(yearmonth=ym,
                   certRate=rate,
                   certQty=cQty)

3、getCertQtyFromGBase 函数定义：

def getCertQtyFromGBase(y,m,productName):
    # 8a 集群连接配置信息（如果集群服务对外开放多个节点，则多个IP以逗号分隔）
    config = {'host': '118.195.185.192', 'port': 21039, 'database': 'courseware', 'user': 'gbase', 'passwd': 'gbase20110531'}

    conn = None # 连接对象变量初始化
    rv = None # 返回值初始化
    try:
        conn = connect() # 创建 connector 对象
        conn.connect(**config) # 汇入集群连接字段配置数据
        cur = conn.cursor() # 生成光标
        res = cur.callproc('getCertQty', [y, m, productName]) # 调用存储过程
        for r in res:
            rv = r.fetchall() # 获得结果集
    except GBaseError.DatabaseError as e:
        print(e)
    finally:
    	if not conn is None:
        	conn.close()
    return rv

4、启动网站：

if __name__ == '__main__':
    app.run(host="0.0.0.0", port=80)

“0.0.0.0” 表示所有客户端都能访问，port 是 HTTP 协议的端口。

七、视图层

1、网页模板（.\templates\query.html）

<html>
<head>
  <title>基于8a集群的数据可视化演示</title>
  <script src="{{ url_for('static', filename='jquery-3.2.1.min.js') }}"></script>
  <script src="{{ url_for('static', filename='echarts4.js') }}"></script>
  <script type="text/javascript">
    function btnClick() {
        name = txtName.value;
        certCode = txtCode.value;
        phone = txtPhone.value;
        if(name=="" && certCode=="" && phone==""){
            alert("Three conditions can't be all empty!")
        }
        else{
            frmSubmit.submit();
        }
    }
  </script>
</head>
<body>
    <!--为ECharts准备一个具备大小（宽高）的Dom-->
    <div id="main" style="width:800px;height:500px;border:1px solid #ccc;padding:10px;"></div>

    <script type="text/javascript">
    // 初始化 echarts 组件
    var myChart = echarts.init(document.getElementById('main'));
    // 设置暂时没有业务数据的柱线组合图框架：基本样式、标题、图例等
    myChart.setOption({
        title: {
            text: '南大通用认证证书概览'
        },
        tooltip: {},
        legend: {
            data:['环比增长','证书数量']
        },
        xAxis: {
            data: [],
            axisLabel: {
                rotate: 60,
                inside: false,
                textStyle: {
                    color: '#000000'
                }
            },
            axisTick: {
                show: false
            },
            axisLine: {
                show: false
            }
        },
        yAxis: {
            axisTick: {
                show: false
            }
        },
        series: [{
            name: '环比增长',
            type: 'line',
            smooth: false,
            itemStyle: {
                normal: {
                    lineStyle: {
                        width: 1,
                        type: 'dotted'  //'dotted'虚线 'solid'实线
                    }
                }
            },
            z:100,
            data: []
        },{
            name: '证书数量',
            type: 'bar',
            z:99, // 保证柱图实体在 shaddow 上面，解决 tooltiip 被遮盖问题
            itemStyle: {
                color: new echarts.graphic.LinearGradient(
                    0, 0, 0, 1,
                    [
                        {offset: 0, color: '#83bff6'},
                        {offset: 0.5, color: '#188df0'},
                        {offset: 1, color: '#188df0'}
                    ]
                )
            },
            data: []
        }
        ]
    });

    myChart.showLoading(); // 显示加载动画
    // 异步加载数据，调用路由地址为 /certStat 的路由函数
    $.get('/certStat').done(function (data) {
        myChart.hideLoading(); // 隐藏加载动画
        // 填入业务数据
        myChart.setOption({
            xAxis: {
                data: data.yearmonth
            },
            series: [{
                name: '环比增长', // 根据名字对应到相应的系列
                data: data.certRate.map(parseFloat) // 转化为数字（注意map）
            },/*
            { // For shadow
            type: 'bar',
            itemStyle: {
                color: 'rgba(0,0,0,0.05)'
            },
            barGap: '-100%',
            barCategoryGap: '40%',
            data: data.dataShadow,
            animation: false
        },*/{
                name: '证书数量',
                data: data.certQty.map(parseFloat)
            }]
        });
    });
    </script>
</body>
</html>

2、eCharts 组件异步加载数据：
(1) 用户在浏览器中输入 http://localhost/certification，/certification 是路由地址，调用 getCertInfo.py 中 form() 函数

@app.route("/certification", methods=["GET"])
def form():
    return render_template("query.html")

render_template(“query.html”) 调用 jinjia2 模板引擎，网址会跳转到 query.html
(2) query.html 的

$.get('/certStat').done(function (data) {...}

完成。这句话是 jQuery 语法，/certStat 是路由地址，调用 getCertInfo.py 中的 certStat() 函数

@app.route("/certStat", methods=["GET"])
def certStat():

certStat() 函数再通过 getCertQtyFromGBase(…) 调用存储过程 getCertQty 从 8a 集群中获取数据。结果集以 JSON 格式反馈给query.html 的匿名函数 function (data) {…}，“.done” 函数的参数即 call back 函数。匿名函数中的 data 就是接收 getCertInfo.py 中的 certStat() 回馈的 JSON 数据。以下代码

return jsonify(yearmonth=ym,
                   certRate=rate,
                   certQty=cQty)

明确了JSON数据中的关键字：yearmonth、certRate、certQty、dataShadow。
我们再看 query.html 客户端代码 call back 匿名函数中接收数据的语句就是通过这三个关键字为 eCharts 组件填充数据的：
data: data.yearmonth
data.certRate.map(parseFloat)
data: data.certQty.map(parseFloat)

• 最终展示效果
  

八、工程代码架构

• .\static\echarts4.js
• .\static\jquery-3.2.1.min.js
• .\templates\query.html
• getCertInfo.py

以上是基于 8a 集群的数据可视化项目代码讲解。有问题请加郝老师微信：HAO1982131006