document the grafana queries

[SSmale]

hey, this tool looks really cool, so it would be good to get some more info on the setup from the video.

1. run grafana container
   docker run -d -p 3000:3000 --name=grafana grafana/grafana-enterprise
2. create network
   docker network create ga
3. add both the clickhouse and grafana containers to the network
   docker network connect ga trench-clickhouse-1
docker network connect ga grafana
4. add the clickhouse plugin
   host: trench-clickhouse-1
   port: 8123
   protocol: http
   username: user
   password: password
5. add some data for the $pageview event

curl --location 'http://localhost:4000/events' \
--header 'Authorization: Bearer public-d613be4e-di03-4b02-9058-70aa4j04ff28' \
--header 'Content-Type: application/json' \
--data '{
  "events": [
    {
      "userId": "550e8400-e29b-41d4-a716-4466554400",
      "type": "track",
      "event": "$pageview",
      "properties": {
        "totalAccounts": 4,
        "country": "Denmark"
      }
    }]
}'

6. query is

SELECT 
toStartOfHour(timestamp) as time,
count(DISTINCT user_id) as unique_pageviews
FROM "default"."events" 
where event = '$pageview'
GROUP BY time
order by time

or the dashboard can be imported as

{
  "__inputs": [
    {
      "name": "DS_GRAFANA-CLICKHOUSE-DATASOURCE",
      "label": "grafana-clickhouse-datasource",
      "description": "",
      "type": "datasource",
      "pluginId": "grafana-clickhouse-datasource",
      "pluginName": "ClickHouse"
    }
  ],
  "__elements": {},
  "__requires": [
    {
      "type": "grafana",
      "id": "grafana",
      "name": "Grafana",
      "version": "11.4.0"
    },
    {
      "type": "datasource",
      "id": "grafana-clickhouse-datasource",
      "name": "ClickHouse",
      "version": "4.5.1"
    },
    {
      "type": "panel",
      "id": "timeseries",
      "name": "Time series",
      "version": ""
    }
  ],
  "annotations": {
    "list": [
      {
        "builtIn": 1,
        "datasource": {
          "type": "grafana",
          "uid": "-- Grafana --"
        },
        "enable": true,
        "hide": true,
        "iconColor": "rgba(0, 211, 255, 1)",
        "name": "Annotations & Alerts",
        "type": "dashboard"
      }
    ]
  },
  "editable": true,
  "fiscalYearStartMonth": 0,
  "graphTooltip": 0,
  "id": null,
  "links": [],
  "panels": [
    {
      "datasource": {
        "type": "grafana-clickhouse-datasource",
        "uid": "${DS_GRAFANA-CLICKHOUSE-DATASOURCE}"
      },
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "palette-classic"
          },
          "custom": {
            "axisBorderShow": false,
            "axisCenteredZero": false,
            "axisColorMode": "text",
            "axisLabel": "",
            "axisPlacement": "auto",
            "barAlignment": 0,
            "barWidthFactor": 0.6,
            "drawStyle": "line",
            "fillOpacity": 0,
            "gradientMode": "none",
            "hideFrom": {
              "legend": false,
              "tooltip": false,
              "viz": false
            },
            "insertNulls": false,
            "lineInterpolation": "linear",
            "lineWidth": 1,
            "pointSize": 5,
            "scaleDistribution": {
              "type": "linear"
            },
            "showPoints": "auto",
            "spanNulls": false,
            "stacking": {
              "group": "A",
              "mode": "none"
            },
            "thresholdsStyle": {
              "mode": "off"
            }
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green",
                "value": null
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          }
        },
        "overrides": []
      },
      "gridPos": {
        "h": 8,
        "w": 12,
        "x": 0,
        "y": 0
      },
      "id": 1,
      "options": {
        "legend": {
          "calcs": [],
          "displayMode": "list",
          "placement": "bottom",
          "showLegend": true
        },
        "tooltip": {
          "mode": "single",
          "sort": "none"
        }
      },
      "pluginVersion": "11.4.0",
      "targets": [
        {
          "builderOptions": {
            "aggregates": [],
            "columns": [
              {
                "alias": "user_id",
                "custom": false,
                "name": "user_id",
                "type": "String"
              }
            ],
            "database": "default",
            "filters": [],
            "groupBy": [
              "user_id"
            ],
            "limit": 1000,
            "meta": {},
            "mode": "aggregate",
            "orderBy": [],
            "queryType": "table",
            "table": "events"
          },
          "datasource": {
            "type": "grafana-clickhouse-datasource",
            "uid": "${DS_GRAFANA-CLICKHOUSE-DATASOURCE}"
          },
          "editorType": "sql",
          "format": 1,
          "meta": {
            "builderOptions": {
              "aggregates": [],
              "columns": [
                {
                  "alias": "user_id",
                  "custom": false,
                  "name": "user_id",
                  "type": "String"
                }
              ],
              "database": "default",
              "filters": [],
              "groupBy": [
                "user_id"
              ],
              "limit": 1000,
              "meta": {},
              "mode": "aggregate",
              "orderBy": [],
              "queryType": "table",
              "table": "events"
            }
          },
          "pluginVersion": "4.5.1",
          "queryType": "table",
          "rawSql": "SELECT \ntoStartOfHour(timestamp) as time,\ncount(DISTINCT user_id) as unique_pageviews\nFROM \"default\".\"events\" \nwhere event = '$pageview'\nGROUP BY time\norder by time",
          "refId": "A"
        }
      ],
      "title": "Panel Title",
      "type": "timeseries"
    }
  ],
  "schemaVersion": 40,
  "tags": [],
  "templating": {
    "list": []
  },
  "time": {
    "from": "now-6h",
    "to": "now"
  },
  "timepicker": {},
  "timezone": "browser",
  "title": "New dashboard",
  "uid": "ee6c3vm4x4dtsb",
  "version": 2,
  "weekStart": ""
}

testing trench.postman_collection.json

[christianmat]

This is excellent documentation! Would you mind writing it up on a page in the official docs via a PR? If not I should have some time later this week to do so.

[SSmale]

Where in the docs is it best to place it?

[christianmat]

@SSmale let's create a new section called "Guides" -- you can create a subfolder with it and add it to the menu via mint.json. Thanks!

[tuminzee]

@SSmale @christianmat let me know if I could be of any help

[christianmat]

@SSmale let us know if you intend to write the docs for this, otherwise @tuminzee can pick it up.

[SSmale]

@christianmat happy either way, with the holidays it's a busy time.

[tuminzee]

@SSmale I can give it a shot. I will prepare a draft of my idea soon. I will need your guidance on this one

[tuminzee]

I’ve successfully set up Grafana with a simple dashboard for ClickHouse, and it seems to be working fine. As per the linked issue, should I prepare a guide as part of the README, or would a video demo be more appropriate? And where should this be added?

I couldn’t find @SSmale on Slack, so should I ask these clarifications directly here on GitHub, or would it be better to discuss elsewhere? Would continuing the conversation here risk confusing other contributors, or could it provide helpful context about the project?

I’m flexible with how we communicate—just let me know what works best.

attaching ss of grafana dashboard

[SSmale]

There is already a video in the readme, that is where I got the steps from. The work for this issue is:

• Create a new section in the docs called 'Guides'
• Create a page for Grafana dashboard
• Copy the original post text
• Format and make it flow as a guide
• Add screenshots
• Add links to the postman collection

[tuminzee]

@SSmale this is of great help. thanks.
I'll get to it.