Annotations with Bigquery Plugin not rendering properly on panels

[mazad01]

Background info: -Grafana 9.2.5 OSS -Bigquery Plugin version: 1.2.2

Issue: We are creating a dashboard annotation to dynamically generate panel annotations and seeing a visual inconsistency with the annotation range. From the picture, you'll see the annotation carrot all the way on the left with the correct date (2/7/24) in the metadata. But it's not showing in the 2/7 area in the y axis. The graph time range is also filtered appropriately, but it just seems that the carrot is stuck on the left. Any idea?

[zoltanbedi]

Hey @mazad01,

Can you please provide a dashboard or a way for us to reproduce this?

[mazad01]

I can provide you with some steps for reproducibility:

Create some fake data in BQ:

• Set up a new project or use an existing a project. Doesn't really matter.
• Open the BQ console in GCP console and create a new dataset with the following info: dataset id = test_dataset region = us-east-1
• Don't set any other options and click Create Dataset
• Within the newly created test_dataset create 2 new tables with the following options:
• Create table from source file, with type CSV.
• Destination table should be a native table. Names should be test_table and fake_table
• For test_table csv file contents that you upload should be

  date,tag
  20240101,good
  20240102,bad

• For fake_data we should have

  date,data
  20231230,120
  20231231,100
  20240101,200
  20240102,150
  20240103,180

For grafana config

1. create a new datasource using BQ plugin using the GCP project that the fake dataset is in. Then create a new dashboard.

2. In the new dashboard, create a new timeseries panel. Datasource should be the newly created BQ data source. Query should be something like SELECT CAST(date as STRING) as date, data from <some gcp project>.test_dataset.fake_data a. Add a Convert field type transform with options Field='date', as=Time, Input format=YYYYMMDD

3. Open dashboard settings and go to Annotations

4. Click on New Query

5. Annotation query config: a. Set name to annotation b. Set datasource to BQ. c. Set query to SELECT CAST(date as STRING) as date, tag from <some gcp project>.test_dataset.test_table d. Ensure Enabled is checked. e. Run the query and ensure the event fields are set like so:

6. Now if you go back to the dashboard and set the time range to roughly 2023-12-01 > 2024-01-31 you should see this:

[gabor]

hi @mazad01 thank you for the test-data.. i tested this, and i could not even see the annotations anywhere. in my case what fixed the problem is making the annotation-timestamps "real" bigquery timestamps. i modified your query:

• from: SELECT CAST(date as STRING) as date, tag from
• to: SELECT PARSE_TIMESTAMP('%Y%m%d', CAST(date as string)) as date, tag from

and it suddenly started to work.

could you try the same?

(in the meantime i will look into why the string-based timestamps do not work correctly in annotations)

[gabor]

(created a separate github-issue for the annotation-string-dates-problem: https://github.com/grafana/grafana/issues/88135)

[gabor]

as we got no feedback here, we'll close the issue. if you think the issue is not fixed yet, just reply and we can reopen the issue. thanks!