Visualize Time-series with Grafana and TimescaleDB

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

We’re visual creatures by nature, so it’s no surprise that visualizing data can help generate new, meaningful insights into it. Pairing the visualization power of Grafana with the time-series database functionality of TimescaleDB makes it easy to obtain a visual picture of data that changes with time. In this article, we’ll show you how to visualize time-series with Grafana and TimescaleDB with detailed instructions for each step of the process.

Prerequisite

Before you proceed with this tutorial, you’ll need to install and configure a few items: – PostgreSQL 11TimescaleDBGrafana

You’ll need to have basic knowledge of PostgreSQL in order to follow along with this tutorial.

What Is Time-Series Data?

Put simply, time-series data is a type of data that represents how a process, behavior or system changes over a period of time.

What Is Grafana?

Grafana is an open source visualization and analytics software that allows analysts to view complex time-series data in a more readable visual format.

How to Visualize Time-Series Data in Grafana

In this section, we’ll walk you through the steps needed to to visualize time-series data in Grafana and TimescaleDB.

Setting Up Sample Dataset

Our first task will be to create a sample dataset that we can use in our Grafana dashboard and our TimescaleDB hypertable.

We’ll log in to the Postgres shell and execute the following command to create the sample database named ‘youtubedb’:

1
CREATE DATABASE youtubedb;

We can then connect to the database using this command: \c youtubedb;

After creating the database, we’ll need to extend TimescaleDB against the table using the following command:

1
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

After we create that extension, we’ll create our table named ‘youtube’ using the statement shown below:

1
2
3
4
5
6
7
8
9
10
CREATE TABLE youtube(
    id INTEGER PRIMARY KEY,
    trending_date DATE,
    title TEXT,
    channel_title TEXT,
    publish_date TIMESTAMPTZ,
    views INTEGER,
    likes INTEGER,
    dislikes INTEGER
);

NOTE: Although the step is not outlined in this tutorial, let’s assume that we already populated the above table with 300 rows of sample data.

Notice the text in the green notification box.

Setting Up Grafana Data Source

Now that we’ve created our sample dataset, let’s turn to Grafana. First, we’ll open our Grafana web application using the following URL: http://localhost:3000/login.

We’ll provide the default user name and password, which is admin, and we click the Log In button to proceed.

The application will ask you to change the password. You should go ahead and change the password at this stage; however, for the purposes of this tutorial, we’re going to skip this process by clicking the Skip button to continue.

On the Grafana welcome page, we’ll click Add data source to access the list of available data sources.

Then we’ll click PostgreSQL to select it as our data source.

Next, we’ll need to provide some necessary details. Notice that we need to enable ‘TimescaleDB’ against the data source in the “PostgreSQL Details” section:

We then click the Save & Test button at the lower left corner of our window to save our changes and verify that we successfully set up our data source.

Visualizing Time-Series via Grafana Dashboard

In the previous section, we connected a Postgres data source to our Grafana application, Now, we’ll create a dashboard to visualize our data within the data source.

First, we’ll go back to the Grafana welcome page and click the New Dashboard icon.

Then, we click the Choose Visualization button to proceed.

At this point, we can choose which visualization we want to use, for this example, we’ll select the graph.

We’ll click the Queries icon and provide details as if we’re creating a SQL query. Here’s a list of what should be provided in each field:

  • FROM: Provide the table name here; in this case, it’s ‘youtube’.
  • Time column: Provide the column that contains the time-series data; in this case, it’s the ‘publish_date’ column.
  • SELECT: Provide the column from which we are going to gather data for display in the graph.
  • WHERE: Provide the query condition; in this case, it will be $_timeFilter.
  • GROUP BY: Provide a time interval such 1minute or 5minute.

Make sure that you set a correct time range for your visualization in order to pull out the correct records from the database.

Here’s another example that shows how we can visualize our time-series data:

By changing the column values and grouping of the time-series, we can look at our data in a different, more meaningful way.

In the image shown above, we can view the trending youtube videos, and we can see the likes they garnered on an hourly basis.

Conclusion

Visualizing time-series data can make it easier to understand the information, identify trends and patterns and gain meaningful insights. In this article, we showed you how to visualize time-series with Grafana and TimescaleDB. With our step-by-step instructions, you’ll be able to create visualizations of your own TimescaleDB data.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.