search

doitintl / bigquery-grafana

Google BigQuery Datasource Plugin for Grafana. (NO LONGER MAINTAINED)
https://www.doit-intl.com/labs/
MIT License
242 stars 76 forks source link

Raw SQL mode fails to extract table name from FROM clause when using variables or when using FROM in a subquery. #314

Closed peikk0 closed 3 years ago

peikk0 commented 3 years ago

Bug Report

When using raw SQL queries, _extractFromClause() fails to extract the project, dataset and table name from the FROM clause when using variables or when using FROM in a subquery.

Expected Behavior

Variables are interpolated before extracting the FROM clause and there are no API errors when using raw SQL queries with variables and/or subqueries.

Actual Behavior

Given a variable $source_table set as my_project.my_dataset.my_table:

SELECT
  CURRENT_TIMESTAMP() AS time,
  SUM(cost) AS value
FROM
  `$source_table`
WHERE
  $__timeFilter(usage_start_time)
GROUP BY 1
ORDER BY 1 ASC

results in this error:

t=2021-02-26T02:15:51+0000 lvl=info msg=Requesting logger=data-proxy-log url=https://www.googleapis.com/bigquery/v2/projects/$source_table/datasets/undefined/tables/undefined
t=2021-02-26T02:15:52+0000 lvl=info msg="Request Completed" logger=context userId=1 orgId=1 uname=admin method=GET path=/api/datasources/proxy/2/bigquery/v2/projects/$source_table/datasets/undefined/tables/undefined status=400 remote_addr=172.17.0.1 time_ms=713 size=279 referer="http://localhost:3000/d/XXX/my-dashboard?editPanel=20&orgId=1"

Also if using FROM in a subquery earlier in the SELECT clause for instance:

SELECT
  CURRENT_TIMESTAMP() AS time,
  SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c), 0)) AS value
FROM
  `$source_table`
WHERE
  $__timeFilter(usage_start_time)
GROUP BY 1
ORDER BY 1 ASC

results in this error:

t=2021-02-26T02:18:32+0000 lvl=info msg=Requesting logger=data-proxy-log url=https://www.googleapis.com/bigquery/v2/projects/NNEST%28credits/datasets/undefined/tables/undefined
t=2021-02-26T02:18:33+0000 lvl=info msg="Request Completed" logger=context userId=1 orgId=1 uname=admin method=GET path=/api/datasources/proxy/2/bigquery/v2/projects/NNEST(credits/datasets/undefined/tables/undefined status=400 remote_addr=172.17.0.1 time_ms=503 size=282 referer="http://localhost:3000/d/XXX/my-dashboard?editPanel=54&orgId=1"

Steps to Reproduce the Problem

Variable interpolation case:

  1. Create a textbox variable source_table set as the full table name
  2. Add a BigQuery panel using the variable source_table in the FROM clause (see example above)
  3. Notice error in Grafana logs

Subquery case:

  1. Add a BigQuery panel using a subquery in the SELECT clause (see example above)
  2. Notice error in Grafana logs

Specifications

peikk0 commented 3 years ago

Also could we have an option for disabling "fixing" the raw queries? I prefer adding the partition filter explicitly myself and not having the query altered in any way (aside from variable/macro interpolation).