Enable plotting multiple timeseries in a chart using clickhouse query

[ankitnayan]

The current implementation restricts labels to 1 column with name result. This should support more labels and add docs about how to do this

[srikanthccv]

Metabase: https://www.metabase.com/learn/visualization/line-charts

Metabase can’t read minds (yet), so it doesn’t know which columns from the Orders table to use for the x and y axes. To create a line chart, you’ll need to pick a metric for Metabase to plot over time. For example, you could show order totals over time by setting the x axis to created_at and the y axis to total. Metabase will automatically plot the line chart:

ClickHouse data source grafana:

For the query

SELECT
    type,
    client_version_major,
    avg(query_duration_ms) as avg_duration,
    toStartOfInterval(event_time, INTERVAL 1 MINUTE) AS ts
FROM system.query_log
WHERE $__timeFilter(event_time)
GROUP BY ts, type, client_version_major
HAVING avg_duration > 0 AND max_duration > 0
ORDER by ts, type, client_version_major

It considers every number as a time series result. As a result, the client_version_major 2 is the line chart instead of label.

[ankitnayan]

I see, so that's a bug there. Okay to use result_1, result_2 ? But the UI shouldn't should result_1 and result_2 as labels. Labels should be more meaningful.

[srikanthccv]

Or how about asking users to cast the number type labels to string in the final result? Either way, they will have to do some work. We don't have to worry about column names if we ask users to make sure all labels are string types in the result.

[ankitnayan]

that is even better IMO as we can treat labels as string

[srikanthccv]

The new expected format could break some existing dashboards and alerts since we are changing how the results are interpreted. I will collect queries from the existing users and find a plan for shipping this new change.