Gauss DB(DWS)对接系列-数据可视化工具Grafana 7.4.3版本对接

        GaussDB Kernel提供自研的TSDB时序引擎,提供扩展的时序场景语法,以及分区管理、时序计算、时序生态函数等服务功能,此博文详细描述如何使用Grafana与DWS时序数仓对接,展示时序数据。

1.DWS(时序数仓)构造测试数据

Data Studio登录DWS(时序数仓)集群,创建用户、数据库及测试表

--创建用户

create user sxsc password 'Huawei@123';

--创建数据库

create database sxscdb owner sxsc encoding='utf-8' TEMPLATE template0;

--赋予用户sxsc 模式public权限

GRANT ALL PRIVILEGES ON SCHEMA public TO sxsc;

执行结果如下:

创建测试表,构造时序数据

Data Studio上使用新建的sxsc用户登录sxscdb数据库创建测试表,并构造时序数据

创建测试表:

drop table if exists public.jiaocai;

create  table if not exists jiaocai(

type        text TSTag

,devId      text  TSTag

,PhV_phsA    double precision  TSField

,PhV_phsB    double precision  TSField

,PhV_phsC    double precision  TSField

,PhV_neut    double precision  TSField

,A_phsA      double precision  TSField

,A_phsN      double precision  TSField

,A_phsC      double precision  TSField

,A_phsB      double precision  TSField

,PhVAr_phsA  double precision  TSField

,PhVAr_phsB  double precision  TSField

,PhVAr_phsC  double precision  TSField

,TotVAr      double precision  TSField

,PhPF_phsA   double precision  TSField

,PhPF_phsB   double precision  TSField

,PhPF_phsC   double precision  TSField

,TotPF       double precision  TSField

,TotVA       double precision  TSField

,PhW_phsA    double precision  TSField

,PhW_phsB    double precision  TSField

,PhW_phsC    double precision  TSField

,TotW        double precision  TSField

,PhVA_phsA   double precision  TSField

,PhVA_phsB   double precision  TSField

,PhVA_phsC   double precision  TSField

,time        timestamp without time zone  TSTime

)with (TTL='7 days', PERIOD = '1 day', orientation=TIMESERIES);

COMMENT ON COLUMN public.jiaocai.PhVAr_phsA IS 'A相无功功率';

COMMENT ON COLUMN public.jiaocai.PhVAr_phsB IS 'B相无功功率';

COMMENT ON COLUMN public.jiaocai.PhVAr_phsC IS 'C相无功功率';

COMMENT ON COLUMN public.jiaocai.TotVAr     IS '总无功功率';

COMMENT ON COLUMN public.jiaocai.PhPF_phsA  IS 'A相功率因数';

COMMENT ON COLUMN public.jiaocai.PhPF_phsB  IS 'B相功率因数';

COMMENT ON COLUMN public.jiaocai.PhPF_phsC  IS 'C相功率因数';

COMMENT ON COLUMN public.jiaocai.TotPF      IS '总功率因数';

COMMENT ON COLUMN public.jiaocai.TotVA      IS '总视在功率';

COMMENT ON COLUMN public.jiaocai.PhW_phsA   IS 'A相有功功率';

COMMENT ON COLUMN public.jiaocai.PhW_phsB   IS 'B相有功功率';

COMMENT ON COLUMN public.jiaocai.PhW_phsC   IS 'C相有功功率';

COMMENT ON COLUMN public.jiaocai.TotW       IS '总有功功率';

COMMENT ON COLUMN public.jiaocai.PhVA_phsA  IS 'A相视在功率';

COMMENT ON COLUMN public.jiaocai.PhVA_phsB  IS 'B相视在功率';

COMMENT ON COLUMN public.jiaocai.PhVA_phsC  IS 'C相视在功率';

语法介绍:

TSTag:维度属性字段

TSField:指标属性字段

TSTime:时间属性字段

TTL:数据生命周期,此表数据生命周期为7

PERIOD:自动创建分区间隔,此表分区按照1天间隔创建

Orientation:表属性,TIMESERIES说明是时间序列表

创建存储过程insert_data(),模拟时序数据入库

CREATE OR REPLACE PROCEDURE insert_data(v_number bigint)

AS

DECLARE

  v_insert_jiaocai_string VARCHAR2(4000);

  var_count               bigint;

BEGIN

  var_count               := 0;

  v_insert_jiaocai_string :=

             'insert into public.jiaocai(

                                          type

                                          ,devId

                                          ,PhV_phsA

                                          ,PhV_phsB

                                          ,PhV_phsC

                                          ,PhV_neut

                                          ,A_phsA

                                          ,A_phsN

                                          ,A_phsC

                                          ,A_phsB

                                          ,PhVAr_phsA

                                          ,PhVAr_phsB

                                          ,PhVAr_phsC

                                          ,TotVAr

                                          ,PhPF_phsA

                                          ,PhPF_phsB

                                          ,PhPF_phsC

                                          ,TotPF

                                          ,TotVA

                                          ,PhW_phsA

                                          ,PhW_phsB

                                          ,PhW_phsC

                                          ,TotW

                                          ,PhVA_phsA

                                          ,PhVA_phsB

                                          ,PhVA_phsC

                                          ,time

                                          )

                                          values(

                                          ''analog''

                                          ,''025BHN6RK9522172_JC''

                                          ,random()

                                          ,random()*300

                                          ,random()*300

                                          ,random()*300

                                          ,random()*300

                                          ,random()*90

                                          ,random()*300

                                          ,random()*300

                                          ,random()*60

                                          ,random()*50

                                          ,random()*40

                                          ,random()*30

                                          ,random()

                                          ,random()

                                          ,random()

                                          ,random()

                                          ,random()*20

                                          ,random()*30

                                          ,random()*40

                                          ,random()*50

                                          ,random()*60

                                          ,random()*70

                                          ,random()*80

                                          ,random()*90

                                          ,clock_timestamp() - interval ''7.815 hour''

                                          );';

  while var_count < v_number loop

    EXECUTE IMMEDIATE v_insert_jiaocai_string;

    var_count := var_count+1;

    pg_sleep(5);

    commit;

  end loop;

END;

/

 

执行存储过程,执行数据插入1000次,每5s插入一次

call insert_data(1000);


2.Grafana对接DWS时序数仓

部署Grafana(windows)

Grafana下载地址:https://grafana.com/grafana/download?platform=windows

下载完成后,在windows路径下解压文件grafana-7.4.3.windows-amd64.rar,如下图:

进入bin目录

双击grafana-server启动服务

Grafana对接DWS

Grafana登录地址:

http://localhost:3000 

默认用户名及密码:admin/admin,第一次登陆会提示修改密码

Grafana中配置DWS数据源,点击Data Sources

点击 Add data source

选择PostgreSQL数据源

配置DWS集群信息

Save&Test保存

Database Connection OK 说明连通性测试成功


配置时序图标展示数据

点击+Add new panel

点击配置数据源,选择$DWS

配置区配置要展示的时序数据,如下图:

点击,可切换为SQL模式

配置完成后点击apply应用此配置

页面可以选择时序数据展示的时间区间及刷新频率


GaussDB(DWS)博文后缀.png

(完)