feature request: add opt-in support for special handling the metric label in time-series queries

[eraac]

summary by @gabor : there is a special behavior in a special situation in postgres. the bigquery datasource does not do this special handling currently. we do not want to change the default behavior in bigquery, but it may be useful to offer this as an opti-in behavior. there are many open questions, like how exactly should it behave (100% mirror of the postgres behavior?), how exactly would you enable it (both in query-editor and in raw-query-editor) etc.
see discussion in grafana/sqlds#109 too.

---

An image is probably better to explain the problem, but I've switch from the old plugin to this plugin and with the same query I've different result. I don't mind update my queries to reproduce the previous behavior but I'm not sure this is possible

New plugin

Old plugin

Not the presence of "cost" for the name with the new plugin

My query

SELECT 
  $__timeGroup(usage_start_time, $interval),
  service.description AS metric, 
  sum(cost / currency_conversion_rate) AS cost
FROM 
  `$from_dataset`,
  UNNEST(project.labels)
WHERE 
  $__timeFilter(usage_start_time)
  AND key = "environment" AND value IN ($environment)
GROUP BY 1, 2 
ORDER BY 1, 2

For the new plugin, the behavior can be reproduce via this simple query

SELECT
  CURRENT_TIMESTAMP(),
  "sku" AS metric,
  1 AS cost

I've try to remove the AS cost, but the prefix is now f1_

[shih-chris]

Hello! I don't work directly on the plugin, but we do use Grafana & BigQuery pretty extensively for analytics internally at Grafana Labs.

My initial guess is that this might be related to the Grafana time series panel, rather than the data source. Do you mind sharing which version of Grafana you are on?

fwiw - I tried your query

SELECT
  CURRENT_TIMESTAMP(),
  "sku" AS metric,
  1 AS cost

but couldn't replicate the behavior on v10.2.0 (the version we're on for our business intelligence instance, internally), which kinda led me to think it might be a nuance with the panel.

[eraac]

I use 10.1.1 (10.1.2 doesn't seems to include change in the timeseries panel)

Unfortunately 10.2 isn't available yet and can't test it, but I've try with other visualization (pie chart), and got the same issue

[shih-chris]

As a really hacky work around until v10.2, you might be able to use the Organize Fields transform to rename the cost column to a single "space" character?

[dxan29a]

I have a similar issue here. Looks like in the new big-query plugin, keyword 'metric' is not working. I need to use "Pivot query" to do this. The problem is I can't do this if I want to use a column value as new row in the results table. Previously one is more "dynamic". And I think it's not Grafana's problem because 'metric' keyword works fine with other data source for example postgresql.

[eraac]

Grafana 10.2.1
Plugin: 1.4.1

the issue is still here with basic queries

I expect to have "sku" as legend, not "cost sku"

[gabor]

status update:

hi, the reason this works in Postgres for example, is because we do an exception there for the case where:

• time series mode
• exactly 3 columns of data
• etc.

( https://github.com/grafana/grafana/blob/cdd3e1c77601a3b87d07678cc1e1aec27e9fbc04/pkg/tsdb/sqleng/sql_engine.go#L340-L354 )

currently this special handling is not done in bigquery. as far as i know, it's node done in redshift-plugin either. we basically have to decide whether we should add this or not, but adding is problematic, because that's a breaking change now, people may be relying on the current behavior.

for now i recommend using the organize fields by name transform or the labels to fields transform to adjust the legend.

[gabor]

i ran some test queries on both postgres and bigquery, and wrote down the legend-name, to illustrate the difference. all queries were run in format=time_seris (in format_table they produce the same result, as far as i know):

---

SELECT CURRENT_TIMESTAMP as time, 'sku' AS something, 1 AS cost

bigquery: cost sku
postgres: cost sku

---

SELECT CURRENT_TIMESTAMP as time, 'sku' AS metric, 1 AS cost

bigquery: cost sku
postgres: sku

---

SELECT CURRENT_TIMESTAMP as time, 'sku' AS metric, 'xyz' as thing, 1 AS cost

bigquery: cost {metric="sku", thing="xyz"}
postgres: cost {metric="sku", thing="xyz"}

---

as you can see, the change happens when there's one "label" field, and it's named metric. if there are more "label" fields, the special-handling stops.

[gabor]

technical note: these notes are mostly about the legend displayed. internally, the change is in how the grafana-dataframe is built. to be exact, the value column. what will be it's name, and what labels will be added to it.

[gabor]

i asked about this in the sqlds repo, it's the library this plugin relies on for this part of the code: grafana/sqlds#109

[gabor]

i discussed this, and the conclusion is that we don't think making this the default-behavior is the way to go, it would make it very confusing to new users. i adjusted this github issue to be a feature-request instead, about adding some opt-in way to enable this behavior.

currently the recommended workaround is to use transforms, as described in the comments above.