Grafana Connects To Time-series Database (MD5 Mode)

1. Database password authentication method must be MD5.

2. Install Grafana.

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

3. Create a test table and insert data.

   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. Configure Grafana.

   • Add data source.

     

     

   • Add dashboard.

     

   • Ensure that the generated SQL can be run.

     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 cannot execute SQL because there is no corresponding function.