Using Grafana with Sneller

Prerequisites

Before setting up Grafana to work with Sneller, make sure you already set up Sneller and have ingested some data. This tutorial works with both the open-source and cloud version of Sneller.

Installing Grafana

In this example we will be using Docker to run Grafana:

docker volume create grafana-storage
docker run --rm -p 3000:3000 --name=grafana \
  --volume grafana-storage:/var/lib/grafana \
  grafana/grafana-oss

This will run Docker on your local machine on port 3000 and store all persistent data in the grafana-storage volume. When the Grafana container is removed, then the data will still be in the Docker volume.

Navigate to http://localhost:3000 and enter your administrator credentials (defaults to admin user and admin password). You are required to change the default password after your initial login.

Simply press CTRL-C to stop the Grafana container. If you want to get rid of the Grafana volume, then run docker volume rm grafana-storage. This will also delete ALL the dashboards that you have created.

Installing the Sneller plug-in

The Sneller plug-in is available via the official Grafana library, so click on the three bars on the left, select Connections and select Add new connection. Search for the Sneller plug-in and click the Install button.

TIP: It’s also possible to install the data-source automatically during startup by specifying -e "GF_INSTALL_PLUGINS=sneller-sneller-datasource" in the docker run command.

Setting up a connection

Setting up a connection to Sneller is straightforward. Click on the three bars on the left, select Connections and select Data sources. Type Sneller and the Sneller data-source plug-in should appear.[^no-datasource]

Click on the Sneller icon and specify the name for the connection (defaults to Sneller) and wether or not you want this to become the default connection.

Then you need to specify the region of the Sneller engine:

• Self hosted Sneller: Select the Custom EndPoint region and specify the endpoint of your Sneller instance. If you can curl <endpoint> and get back the Sneller daemon date, then you can be sure that you have the proper endpoint.
• Playground: If you don’t have a Sneller account and don’t host it yourself, then you can use our playground that hosts the GitHub archive data for 2021.

To ensure access, you need to specify the Sneller token when (unless you are using the playground).

If you host Sneller yourself, then you should have created a token during installation.

Click Save & test and when everything is set up correctly, then you should be able to use your Sneller account.

Creating your first dashboard

Click on the three bars on the left, select Dashboards and click the New button to create a new dashboard. Add a visualization and select the Sneller data source.

In this example we assume that you are using the playground connection. Of course, it works with any connection, but you will see other tables and the queries in this example won’t work.

Before you can run any queries, you have to select the proper database first. We will select the demo database. The playground only contains data for 2021, the time range should be set from 2021-01-01 until 2022-01-01 (of course you may choose a smaller range).

Now start typing the query to replace the default query:

SELECT repo_name, COUNT(*) nr_of_commits
FROM gha gha, gha.payload.commits commits
WHERE gha.created_at >= `${__from:date}` AND gha.created_at < `${__to:date}`
  AND gha.type = 'PushEvent' AND gha.repo.name ILIKE 'grafana/%'
GROUP BY gha.repo.name repo_name
ORDER BY nr_of_commits DESC
LIMIT 10

This query will list the 10 Grafana repositories that had the most commits in the specified period. This result set doesn’t have a date field in its output, so make sure you switch to the table view. Apply the changes to persist it to the dashboard.

Note that the ${__from:date} and ${__to:date} are used to forward the date range into the Sneller SQL query. Grafana also support the use of dashboard variables to allow more flexible queries.

Now, let’s add a query that returns time-series data:

SELECT time, SUM(opened) opened, SUM(closed) closed
FROM (
   SELECT created_at,
          repo.name AS repo,
          CASE WHEN payload.action IN ('opened', 'reopened') THEN 1 ELSE 0 END opened,
          CASE WHEN payload.action = 'closed' THEN 1 ELSE 0 END closed
   FROM gha
   WHERE created_at >= `${__from:date}` AND created_at < `${__to:date}`
   AND type = 'IssuesEvent' AND repo.name = 'grafana/grafana'
) gha
GROUP BY DATE_BIN('$__interval_ms milliseconds', created_at, `${__from:date}`) AS time

This query will return the number of opened and closed issue for each date. Grafana detects the date field in the output and will automatically select a Time series graph. The default line graph isn’t appropriate. Dates without any opened/closed issues should be 0, so switch to a bar graph in the Time series graph style.

Make sure you save your dashboard to prevent losing your changes.

Debugging queries

Especially when using the Grafana variables, it’s sometimes difficult to know which query is actually executed to the database. You can use the Query inspector when writing your query to determine the query. When hitting the Refresh button in the inspector will run the query again and you can view the results in the Data tab.