Snowflake Datasource Plugin for Grafana Enterprise

With the Snowflake Enterprise plugin, you can visualize your Snowflake data alongside all of your other data sources in Grafana as well as log and metric data in context. This plugin includes a powerful type--ahead query editor, supports complex annotations, set alerting thresholds, control access and permissions and more.

This plugin is available to customers with a Grafana Enterprise subscription.

Table of Contents

• Overview
  • What is Snowflake?
  • Making the most of Snowflake and Grafana
  • Features
• Get started with the Snowflake plugin
  1. Set up the Snowflake Data Source
  2. Write queries for your Snowflake data
  3. Create and use Template Variables
  4. Set up an alert
  5. Create an annotation
• Additional Functionality
  • Using Display Name
  • Data Source Permissions
  • Understanding your Snowflake billing and usage data
• Development
  • Installation
  • Build

Overview

What is Snowflake?

Snowflake offers a cloud--based data storage and analytics service, generally termed “data warehouse--as--a--service” that offers a solution for data warehousing, data lakes, data engineering, data science, data application development, and data sharing. Over the last few years, Snowflake has gained massive popularity because of its ability to affordably store and analyze data using cloud--based hardware and software; recently culminating in the largest software IPO ever. Today, many companies use Snowflake as their primary database to store application and business data like transaction counts, active user sessions, and even time series and metric data.

Making the most of Snowflake and Grafana

Visualize Snowflake data without moving it: Grafana’s unique architecture queries data directly where it lives rather than moving it and paying for redundant storage and ingestion.

Compose panels from varied sources: With pre--built and custom dashboards, bring data together from many different data sources into a single pane of glass.

Transform and compute at the user level: Users can transform data and run various computations on data they see, requiring less data preparation.

Combine, compute, and visualize within panels: Create mixed--datasource panels that display related data from Snowflake and other sources.

Features

Query editor: The query editor is a Smart SQL autocompletion editor that allows you to visualize time series or table data, handles SQL syntax errors, and autocompletes basic SQL keywords.

Data source permissions: Control who can view or query Snowflake data in Grafana

Annotations: Overlay Snowflake events on any Grafana graph, to correlate events with other graph data

Alerting: Set alerts based metrics stores in Snowflake

Variables for queries: Create template variables in Grafana based on Snowflake data, and include variables in Snowflake queries to make interactive dashboards.

Multi--metric queries: Write a single query that returns multiple metrics, each in its own column

Get started with the Snowflake plugin

Here are 5 quick steps to get started with the Snowflake plugin in Grafana:

1. Set up the Snowflake Data Source

Install the Data Source

You need a Grafana Enterprise subscription before you can install this enterprise plugin. Follow the instructions on the Installation tab to install the Snowflake plugin in an on--premise of Grafana Coud instance.

Configure the Datasource

Configuration > Data Sources > Add data source > Snowflake

Add your authentication details, and the data source is ready to query!

Available configuration fields are as follows:

2. Write queries for your Snowflake data

Create a panel in a dashboard and select a Snowflake Data Source to start using the query editor.

-- Date / time can appear anywhere in the query as long as it is included. -- A numerical column must be included. This can be an aggregation or an int/float column. -- Optionally, you can include string columns to create separate data series, if your timeseries data is formatted for different metrics.

Layout of a Snowflake query

SELECT
  <time_column>,
  <any_numerical_column>
  <other_column_1>,
  <other_column_2>,
  <...>
FROM
  <any_table>
WHERE
  $__timeFilter(<time_column>) -- predefined WHERE clause for time range
  AND $<custom_variable> = 1 -- custom variables start with dollar sign

SQL Query Format For Timeseries Group By Interval

SELECT
  $__timeGroup(<time_column>, '1h'), -- group time by interval of 1h
  <any_numerical_column>
FROM
  <any_table>
WHERE
  $__timeFilter(<time_column>) -- predefined WHERE clause for time range
  AND $<custom_variable> = 1 -- custom variables start with dollar sign
GROUP BY 
  <time_column>

Example query using Group By Interval:

SELECT
  avg(execution_time) AS average_execution_time,
  $__timeGroup(start_time, $__interval),
  query_type
FROM 
  account_usage.query_history
WHERE 
  $__timeFilter(start_time) 
  AND query_type regexp '${queryType:regex}'
group by 
  query_type,start_time
order by 
  start_time,query_type ASC;

SQL Query Format For Tables

SELECT
  <time_column>, -- optional if result format option is table
  <any_column_1>
  <any_column_2>
  <any_column_3>
FROM
  <any_table>
WHERE
  $__timeFilter(time_column) -- macro for time range, optional if format as option is table
  AND $<custom_variable> = 1 -- custom variables start with dollar sign

3. Create and use Template Variables

Using template variables

You can include template variables in queries like so:

 SELECT
   <column>
 FROM 
   <table>
 WHERE 
  column >= '$variable'

Multi--value variables can also be used in the query like this:

SELECT
  <column>
FROM 
  <table>
WHERE 
  <column> regexp '${variable:regex}'

Here’s how the variable looks in Grafana:

Using the Snowflake datasource to create variables

In the dashboard settings, click "Variables", and "New".

Using the "Query" variable type, select the Snowflake datasource as the "Datasource".

Be sure to only select 1 column in your variable query.

Example:

SELECT DISTINCT 
  query_type 
FROM 
  account_usage.query_history;

will give you these variables:

All DESCRIBE USE UNKNOWN GRANT SELECT CREATE DROP SHOW

4. Set up an alert

Easily set alerts on specific Snowflake metrics or alert on queries you’ve created

Click on the alert tab button within the query editor, and click Create Alert.

Check out this example to see how we’ve set up an alert threshold based on the query we just wrote:

5. Create an annotation

Requires Grafana 7.2+

Annotations allow you to overlay events on a graph.

To create an annotation, in the dashboard settings click "Annotations", and "New", and select Snowflake as the datasource.

Because annotations are events, they require at least 1 time column and 1 column to describe the event.

Example query to annotate all failed logins to Snowflake:

SELECT
  EVENT_TIMESTAMP AS time,
  EVENT_TYPE,
  CLIENT_IP
FROM 
  ACCOUNT_USAGE.LOGIN_HISTORY
WHERE 
  $__timeFilter(time) 
  AND IS_SUCCESS!='YES'
ORDER BY time ASC;

And

• time: TIME
• title: EVENT_TYPE
• text: CLIENT_IP

This will overlay annotations of all failed logins to snowflake on your dashboard panels.

Additional Functionality

Using Display Name

This plugin uses the Display Name field in the Field tab of the Options panel (available in Grafana v7.0+) to shorten or alter a legend key based on its name, labels, or values. Other datasources use custom alias functionality to modify legend keys, but the Display Name function is a more consistent way to do so. Documentation for the Display Name field option is in the Grafana docs.

Data Source Permissions

Limit access to Snowflake by clicking on the Permissions tab in the datasource configuration page to enabledata source permissions. On the permission page, Admins can enable permissions and restrict query permissions to specific Users and Teams.

Understand your Snowflake billing and usage data

Within the Snowflake data source, you can import a billing and usage dashboard that shows you useful billing and usage information.

Add the dashboard in the Snowflake Data Source configuration page:

This dashboard uses the ACCOUNT_USAGE database, and requires the querier to have the ACCOUNTADMIN role. To do this securely, create a new Grafana data source that has a user with the ACCOUNTADMIN role. Then select that data source in the variables above.