How to Get Started Using CrateDB and Grafana to Visualize Time-Series Data

Grafana, the open platform for time-series data visualization and monitoring that is quite useful for time-series analytics, makes a pretty compelling team when paired with CrateDB, the open-source distributed SQL database that simplifies the real-time storage and analysis of massive quantities of machine data.

Getting started using Grafana and CrateDB together is a relatively simple process, which this article will walk you through (these instructions pertain to macOS, but can be adapted for other platforms quite easily).

Installing CrateDB

Setting up CrateDB to run on a local machine is relatively simple. To begin, run this command:

$ bash -c "$(curl -L https://try.crate.io/)"

Doing so will download CrateDB and run it from the tarball. For information on installing CrateDB more permanently or doing so for Windows, follow these one-step installation guides.

The CrateDB admin UI should open automatically once the above command has finished running. If it doesn’t happen automatically, navigate your browser to http://localhost:4200/. You should have an admin UI on your screen similar to this:

A fresh CrateDB installation won’t include any data to begin with, so you’ll need to add some. Click on the question mark icon on the left-hand menu to reach the help screen, which looks like this:

On this page, click on IMPORT TWEETS FOR TESTING. The next page will provide directions for authenticating your Twitter account. Don’t be alarmed! Nothing will be posted to your account and your own tweets won’t even be processed; this will simply import some recent public tweets to use as sample data.

When this is done, click the Tables icon on the left-hand menu and select the tweets table. This will take you to http://localhost:4200/#/tables/doc/tweets, which looks like this:

With CrateDB installed and running, let’s now get Grafana in the same condition.

Installing Grafana

Get Grafana from its download page, select your operation system, and follow the instructions provided. For example, if you use macOS with Homebrew, you’ll run these commands:

$ brew update

$ brew install grafana

With Grafana installed, you’d then run it with this command:

$ brew services start grafana

And see this:

==> Tapping homebrew/services

Cloning into '/usr/local/Homebrew/Library/Taps/homebrew/homebrew-services'...

remote: Enumerating objects: 14, done.

remote: Counting objects: 100% (14/14), done.

remote: Compressing objects: 100% (10/10), done.

remote: Total 14 (delta 0), reused 9 (delta 0), pack-reused 0

Unpacking objects: 100% (14/14), done.

Tapped 1 command (43 files, 55.3KB).

==> Successfully started `grafana` (label: homebrew.mxcl.grafana)

Grafana’s interface will now be available at http://localhost:3000/, and will look like this:

Enter the default username and password, which are both “admin.” You’ll then be asked to enter a new password.

Once logged in, you’ll see the Grafana home dashboard:

How to Add a Data Source and Use CrateDB With Grafana

On Grafana’s home dashboard, click Add data source.

Next, choose the “PostgreSQL” data source, and follow these steps:

1. Set the name of the data source to “CrateDB.”

2. Click the Default checkbox.

3. In the Host field, enter “localhost:5432.”

4. In the Database field, enter “doc.”

5. In the User field, enter “crate.”

6. Set SSL Mode to disabled.

Click "Save & Test." You’ll then see this success message:

Setting up Your Dashboard

Back on your Grafana home dashboard, you should now be invited to create a new dashboard. Click on New dashboard:

The New dashboard screen looks like this (below). Let’s again use those sample tweets imported into the CrateDB database and graph our number of imported tweets over time. Dashboards in Grafana are made up of user-customized panels. To add a new panel that graphs the tweet import data, click Graph on the New Panel widget.

The next screen will look like this:

Don’t be worried that you’re not seeing any data points — this is the screen you see when a new panel is ready to configure. Click on the panel table (here helpfully named “Panel Title”) and you’ll see a drop-down menu. Choose Edit.

Your interface will now look like this:

Follow these steps using the query editor at the bottom of this screen:

1. On the FROM line, change “select table” to “tweets.”

2. Change the “time” setting to “created at.”

3. On the SELECT line, change Column to “id.”

4. Click the + next after Column, and choose Aggregate Functions, Count.

5. Change the Alias to “tweets.”

As you enter these configuration changes, Grafana will set GROUP BY to “time ($__interval, none),” which is correct.

We’ve now told Grafana to plot the number of total unique tweets per set time window (plotting a data point for each time window named “tweets” that corresponds to COUNT(id)). In a later step, we’ll look at adjusting the time window, which can be set to a certain number of seconds, hours, etc.

While the graph should update automatically, it may not show much yet because the default time period is set to six hours, and the number of imported tweets is low. To change the time period, click on “Last 6 hours” on the top right-hand menu and change it to “Last 15 minutes” (or whatever setting you like).

Your screen should now show visualized data, something like this:

This example panel is telling us that around 10 minutes ago, CrateDB imported about 1,000 tweets over about 30 seconds. It should be understood that CrateDB is capable of ingesting millions of records each second — in this case, the ingestion rate was simply limited by the Twitter API and our ingestion method.

Keeping your Grafana dashboard open, return to the CrateDB admin UI help screen, and select IMPORT TWEETS FOR TESTING another time. Next, refresh the Grafana dashboard using the refresh icon on the top menu. You can also simply change the time period, which refreshes the data as well.

Here is what our example shows after importing these new tweets:

Now click the General tab below the graph and rename the panel “Imported tweets” (or something similar). Then click the blue arrow icon at the top right to return to your dashboard.

Once resized, the example dashboard looks like this:

In the case of a production app, the graph would likely have a continuous stream of data to display. Finally, be sure to click the save icon to save your dashboard.

Hopefully, this simple demonstration of what CrateDB and Grafana are capable of together will spark your imagination as to the real-world applications of these technologies. You may wish to continue exploring the possibilities of these solutions by studying the available documentation for Grafana and/or CrateDB as needed.