Grafana连接时序数据库（MD5模式）

1. 数据库密码认证方式必须为MD5

2. 安装grafana

   docker run -d -p 3000: 3000 --name grafana grafana/grafana

3. 创建测试表并插入数据

   CREATE TABLE youtube(
      id INTEGER PRIMARY KEY,
      trending_date DATE,
      title TEXT,
      channel_title TEXT,
      publish_date TIMESTAMPTZ,
      views INTEGER,
      likes INTEGER,
      dislikes INTEGER
   );
   
   insert into youtube 
      select generate_series(1,300),
      current_date , 
      md5((current_date + trunc(random()*100)::integer +' 00:22:22'::time + (trunc(random()*3600*24)||' second')::interval)::text),
      md5((current_date + trunc(random()*100)::integer +' 00:22:22'::time + (trunc(random()*3600*24)||' second')::interval)::text),
      current_date + trunc(random()*100)::integer +' 00:22:22'::time + (trunc(random()*3600*24)||' second')::interval, 
      random()*1000,
      random()*2000,
      random()*400
      ;

4. 配置Grafana

   • 增加数据源

     

     

   • 增加dashboard

     

   • 保证生成的SQL可以运行

     SELECT
       time_bucket('60.000s',publish_date) AS "time",
       avg(dislikes) AS "dislikes"
     FROM youtube
     WHERE
       publish_date BETWEEN '2022-08-02T23:23:44.331Z' AND '2022-08-03T05:23:44.331Z'
     GROUP BY 1
     ORDER BY 1

   • openGauss因为没有对应函数无法执行SQL