Specifying metric column results in error when alerting

[yvesmauron]

Bug report

We want to create dashboards and alerts in Grafana using the BigQuery plugin. More specifically, we want to use raw sql to check certain values (value) of specific tables periodically (time). If the conditions are met, we want to fire an alert indicating the name of the metric that has exceeded some threshold.

This works fine, as long as we do not add a metric column. If we add a column as metric, we get the following error:

tsdb.HandleRequest() response error &{unexpected type for field 'metric': STRING unexpected type for field 'metric': STRING A <nil> [] [] 0xc0014a9e60}

We have similar dashboards using e.g. the PostgreSQL connector; they use string/varchar columns as well for the metric, and they work as expected.

Please let me know if we missed something.

Expected Behavior

We want to use the metric column as we can, e.g. with the PostgreSQL connector.

Actual Behavior

Demo BigQuery Table project.dataset.GrafanaTest:

Raw SQL and working visual:

However, if we want to alert like we do in PostgreSQL, we get the following error:

When we remove the metric in the raw sql, the testing of the alert does not show any error anymore.

Steps to Reproduce the Problem

1. Add Plugin
2. Write a query with metric, time and value
3. Try to set an alert that will show the errors by metric and test it.

Specifications

• Version: 2.0.2
• Platform: GCP Kubernetes
• Grafana Version: Grafana v7.4.5

[alkmim]

I am facing the same issue. Is there any suggestion on how to overcome this?

Thanks,

[ofir5300]

Please update to v2.0.3 (Available on Grafana cloud) and let me know if it persist

[alkmim]

Hi @ofir5300

I still see the same error:

logger=context t=2022-03-02T23:06:52.82+0000 lvl=eror msg="Failed to evaluate queries and expressions: failed to execute conditions: failed to execute query A: unexpected type for field 'metric': STRING" error="Failed to evaluate queries and expressions: failed to execute conditions: failed to execute query A: unexpected type for field 'metric': STRING"

I am using the v2.0.3:

bash-5.1$ cat plugins/doitintl-bigquery-datasource/plugin.json 
{
  "id": "doitintl-bigquery-datasource",
  "type": "datasource",
  "name": "Google BigQuery",
  "metrics": true,
  "tables": true,
  "annotations": true,
  "alerting": true,
  "backend": true,
  "executable": "doitintl-bigquery-datasource",
  "dependencies": {
    "grafanaDependency": "7.x || 8.x"
  },
  "queryOptions": {
    "maxDataPoints": true
  },

  "info": {
    "description": "Google BigQuery datasource for Grafana",
    "author": {
      "name": "DoiT International",
      "url": "https://www.doit-intl.com/"
    },
    "keywords": ["bigquery"],
    "logos": {
      "small": "img/logo.png",
      "large": "img/logo.png"
    },
    "links": [
      { "name": "GitHub", "url": "https://github.com/doitintl/bigquery-grafana" },
      { "name": "MIT License", "url": "https://github.com/doitintl/bigquery-grafana/blob/master/LICENSE.md" }
    ],
    "updated": "2021-06-29",
    "version": "2.0.3"
  },
  "routes": [
    {
      "path": "bigquery",
      "url": "https://www.googleapis.com/bigquery",
      "jwtTokenAuth": {
        "scopes": ["https://www.googleapis.com/auth/bigquery", "https://www.googleapis.com/auth/drive"],
        "params": {
          "token_uri": "{{.JsonData.tokenUri}}",
          "client_email": "{{.JsonData.clientEmail}}",
          "private_key": "{{.SecureJsonData.privateKey}}"
        }
      }
    }
  ]
}

This is the query I am using (it works fine in the metrics explorer. The only place where it fails is in the alarms)

SELECT 
timestamp as time, 
metric_name as metric, 
value, 
FROM `monitor_alarms`
WHERE value > 1

[zrm000]

I am getting the same error when following original posters steps.

Versions: Grafana v8.3.2 DoiT International Bigquery 2.0.3

[zrm000]

The metric value is a unique store number that Im casting as a string. I want each value to have it's own metric name ( or store number ) So each store number has a percentage assigned to it

[alkmim]

Grafana has now an official plugin which works nicely! You can even get many columns and use it as label!

https://grafana.com/grafana/plugins/grafana-bigquery-datasource/ (It might actually be based on this one)

Feel free to close this issue.

[ofir5300]

We are retiring the DoiT International BigQuery DataSource plugin for Grafana. We recommend that you migrate to the official Google BigQuery data source maintained by Grafana. In August 2022, we will cease all development and archive the GitHub project.

We are sorry for any inconvenience.