Google BigQuery

Note

In workspaces that support version 9 or newer, this data source might require you to install the appropriate plugin. For more information, see Grafana plugins.

Provides support for Google BigQuery as a backend database.

Note

Amazon Managed Grafana includes two different data sources for BigQuery. The current data source is from Grafana Labs. An older, no longer actively supported, data source is from DoiT International. New data sources should use the Grafana Labs provided data source. To learn about migrating your existing queries, see Importing queries created with DoiT International BigQuery DataSource plug in.

Features:

• Query setup

• Raw SQL editor

• Query builder

• Macros support

• Additional functions

• Table view

• Annotations

• BQ queries in variables

• Sharded tables

• Partitioned tables

• Granular slot allocation (Running queries in a project with flat-rate pricing)

Adding the data source

1. Open the Grafana console in the Amazon Managed Grafana workspace and make sure you are logged in.

2. In the side menu under Configuration (the gear icon), choose Data Sources.

3. Choose Add data source.

   Note

   If you don't see the Data Sources link in your side menu, it means that your current user does not have the Admin role.

4. Select Google BigQuery by Grafana Labs from the list of data sources.

5. Enter the following information:

   • For Name, enter the data source name.

   • Default means that it will be pre-selected for new panels.

   • For Service Account Key, enter the Service Account Key File for a GCP Project. Instructions for how to create this are later in this document.

You can set query priority INTERACTIVE or BATCH per data source.

Authentication

To authenticate the BigQuery plugin, upload a Google JWT file. You need to create a Google Cloud Platform (GCP) Service Account for the Project you want to show data for. An Amazon Managed Grafana data source integrates with one GCP Project. If you want to visualize data from multiple GCP Projects, then you can give the service account permissions in each project or create one data source per GCP Project.

Enable APIs

Go to BigQuery API and Enable the API.

Create a GCP Service Account for a Project

To create a GCP service account for a project

1. Navigate to the API & Service Credentials Page .

2. Choose Create credentials and choose Service account key.

3. On the Create service account key page, choose key type JSON. Then in the Service Account dropdown, choose the New service account option.

4. Enter a name for the service account in the Service account name field and then choose the BigQuery Data Viewer and BigQuery Job User roles from the Role dropdown.

5. Choose Create. A JSON key file is created and downloaded to your computer. Store this file in a secure place, as it allows access to your BigQuery data.

6. Upload it to Amazon Managed Grafana on the data source configuration page. You can either upload the file or paste in its contents.

7. The file contents are encrypted and saved in the Grafana database. Don't forget to save after uploading the file.

Using the query builder

The query builder provides a simple yet a user-friendly interface to help you quickly compose a query. The builder enables you to define the basic parts of your query, The common ones are:

• The table that you want to query from

• The time field and metric field

• WHERE clause: either use one fo the pre-defined macros, to speed your writing time, or set up your own expression. The existing supported macros are the following:

  • Macro $__timeFiler with last 7 days example

    WHERE `createDate` BETWEEN TIMESTAMP_MILLIS (1592147699012) AND TIMESTAMP_MILLIS (1592752499012) AND _PARTITIONTIME >= '2020-06-14 18:14:59' AND _PARTITIONTIME < '2020-06-21 18:14:59'

  • Macro $__timeFrom with last 7 days example

    WHERE `createDate` > TIMESTAMP_MILLIS (1592223758609)  AND _PARTITIONTIME >= '2020-06-15 15:22:38' AND _PARTITIONTIME < '2020-06-22 15:22:38'

  • Macro $__timeTo with last 7 days

    WHERE `createDate` < TIMESTAMP_MILLIS (1592828659681)  AND _PARTITIONTIME >= '2020-06-15 15:24:19' AND _PARTITIONTIME < '2020-06-22 15:24:19'

• GROUPBY option: you can use a pre-defined macro or use one of the fields from your query a. time ($_interval,none)

• ORDER BY option

Note

Note: If your processing location is not the Default US one, set your location from the processing Location drop-down at the top right bottom of the query builder

Troubleshooting

To troubleshoot a query, use the Query Inspector at the top of the query builder. This helps you see the clean query and troubleshoot SQL errors.

Importing queries created with DoiT International BigQuery DataSource plug in

If you have existing queries that use the DoiT International BigQuery plug in, you can import those queries by changing the data source to Grafana BigQuery. Your queries will be imported as raw SQL queries.