Luca's Notes

Introduction to Grafana & SQL (clickhouse)

4 min read

A few notes

  • SQL is not pronounced sequel
  • The SQL queries contain almost all the logic, Grafana itself just takes the result of those queries and makes a fairly slow plot from them, with a nice GUI and save system
  • Without admin permissions, the only way you can fuck something up beyond repair is by deleting a dashboard completely… there’s version tracking built in
  • Simple graph logic is usually easy, and some complicated things are easy, but lots of things are not worth doing in such a badly designed language as SQL… use python on your computer :)

How to get access and use existing visualizations on Grafana

  1. visit graf.fsdaq.rombutan.com
  2. Use “Sign in with google” and your ucsc google account. This will automatically grant you view access to all dashboards
  3. Click on “dashboards” in the top left to see all dashboards which other people have made
  4. Chose a dashboard to view by clicking on it

You can change the file being viewed in a particular dashboard by clicking the drop-down in the top of the pane. In this same pane, you can change the beginning and end of the time range being visualized (start ms) (end ms). Note that the “legit” date+time selector on the right side does nothing for us.

You can also click/ctrl+click on legend entries of line plots to show/hide those lines, in the particular manner.

Editing dashboards

  1. Ask Luca to provision you edit access DON’T DELETE DASHBOARDS. Anything else you do is recoverable.
  2. Hover over a visualization you want to change and press e.
  • Most of your screen will be populated with the visualization (which updates on variable changes, ui setting changes, and when you click “refresh” or “run query”)
  • Under the visualization is the query. The query is the text commands which are sent to the database and executed with very fast access to cached data by highly optimized code.
  • On the right side of your screen will be the visualization options. There are lots, most are self explanatory.
    • For time series data, use the “trend” plot. The “time series” plot requires that there be a native time column, which I haven’t put the energy into figuring out. Doing so would fix the native time range selector though, and drag to zoom.
  1. Make sure to save your work. Consider if you should save to the same dashboard (and describe your changes in the “commit message”) or select “save as a copy”

I highly recommend duplicating dashboards with the “save as a copy” feature rather than creating new ones from scratch.

SQL

The documentation for how Clickhouse (the database) parses SQL is here. SQL is not really standardized, so be sure to tell chatgpt you’re using clickhouse. It’s perfectly capable of writting complex queries if you do so, and sometimes feed it the right bits of documentation.

Here’s a simple SQL query for the “meta” visualization in most dashboards:

SELECT "Time_ms", 
"SME_THROTL_TorqueDemand" * 0.00003333 AS "SME_THROTL_TorqueDemand", "VDM_X_AXIS_ACCELERATION" * -1 AS "Forward Acceleration", "VDM_Y_AXIS_ACCELERATION" AS "Sideways Acceleration", 
"SME_TRQSPD_Speed" * 0.0004 AS "SME_TRQSPD_Speed", 
"VDM_GPS_SPEED" AS "GPS Speed"
 
FROM file('$srcfile', parquet) WHERE 
"Time_ms" >= CAST('$time_ms_from' AS INT) 
AND "Time_ms" <= CAST('$time_ms_to' AS INT) 
ORDER BY "Time_ms"

This is all you need to plot any normal columns (which are listed in the DBC file) as a time series line graph in a trend plot. Here’s how it goes:

  1. SELECT keyword, followed by the column names you want, comma seperated, and with quotes to be safe. The AS keyword let’s you rename things, you don’t need to use it, even if you do multiplications or whatever on columns, or even between columns
  2. FROM keyword, followed by the file() table function, which takes arguments filename and file_type, the '$srcfile' is a dashboard variable, which is populated by Grafana from the drop down in the dashboard header, before the query is send off
  3. WHERE keyword, which indicates that you’re going to give it some filters, which are just boolean expressions separated by commas
  4. ORDER BY guarantees the trend plot won’t get angry about things being in the wrong order.

Tips

  • Don’t use the query builder. SQL is not very difficult, especially for what the query builder can actually do. This isn’t a programming language, it’s just some basic ah syntax
  • Don’t use the influxdb data source. It was my first attempt, and it’s going to be removed soon. Clickhouse is much better.
  • Ask for help. I’ll gladly support anyone figuring things out.

Coming soon

  • Python query API documentation (really, read only API keys…)
  • More dashboards
  • Integration with telemetry site for live telemetry and maybe not needing to load the entire parquet (decompressed) into memory

Graph View