document the grafana queries

SSmale commented 3 months ago

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

run grafana container docker run -d -p 3000:3000 --name=grafana grafana/grafana-enterprise
create network docker network create ga
add both the clickhouse and grafana containers to the network docker network connect ga trench-clickhouse-1 docker network connect ga grafana
add the clickhouse plugin host: trench-clickhouse-1 port: 8123 protocol: http username: user password: password

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"
  }
}]
}'

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 commented 3 months ago

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 commented 3 months ago

Where in the docs is it best to place it?

christianmat commented 3 months ago

@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 commented 3 months ago

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

christianmat commented 3 months ago

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

SSmale commented 3 months ago

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

tuminzee commented 3 months ago

@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 commented 3 months ago

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 commented 3 months ago

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 commented 3 months ago

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

FrigadeHQ / trench

document the grafana queries #33