Pair CrateDB with Grafana 6.x, an Open Platform for Time Series Data Visualization

2019-05-21, by Naomi Slater

(In a previous post, I showed you how to get started with CrateDB and Grafana 5.x. Since then, the Grafana 6.x line has been released. So here’s an updated blog post.)

In case you didn’t know, Grafana is an open platform for time series data visualization and monitoring that works well for time series analytics.

And CrateDB is a distributed SQL database that makes it simple to store and analyze massive amounts of machine data in real-time.

It’s a match made in heaven.

In this post, I will show you how to get started on macOS, but these instructions should be trivially adaptable for Linux or Windows.

Install CrateDB

If you don’t already have CrateDB running locally, it’s relatively effortless to get set up.

Run this command:

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

This command just downloads CrateDB and runs it from the tarball. If you'd like to actually install CrateDB a bit more permanently, or you are using Windows, check out our collection of super easy one-step install guides.

If you're using the command above, it should pop open the CrateDB admin UI for you automatically once it has finished. Otherwise, head over to http://localhost:4200/ in your browser.

You should see something like this:

CrateDB Admin UI screenshot

Get some data

If you’re playing around with a fresh CrateDB install, it's likely that you don't have any data. So head on over to the Help screen by selecting the question mark icon on the left-hand navigation menu.

The help screen looks like this:

CrateDB Admin UI screenshot

Select IMPORT TWEETS FOR TESTING and follow the instructions to authenticate your Twitter account.

Don’t worry. This isn’t going to post anything on your behalf. It doesn’t even look at your tweets. All this does is import a bunch of recent public tweets on Twitter.

Once you're done, select the Tables icon from the left-hand navigation, and then select the tweets table. You should end up here:

http://localhost:4200/#/tables/doc/tweets

Which should look like this:

CrateDB Admin UI screenshot

Get Grafana running

Head on over to the Grafana download page. Select the operating system you're using and follow the instructions.

The current version of Grafana was 6.1.6 at the time of writing. You may have to adapt these instructions if you are running a newer version.

I'm using macOS with Homebrew installed, so I ran these commands:

$ brew update
$ brew install grafana

Then, to start Grafana up, I ran:

$ brew services start grafana

If you do the same, you should see something like this:

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

Now, you can access the Grafana interface by visiting http://localhost:3000/ in your browser.

You should see something like this:

Grafana screenshot

The default username admin and the default password is admin. Enter those on this screen. After doing so, you will be prompted to change your password.

Once logged in, you should see your Grafana home dashboard:

Grafana screenshot

Add a data source

From the Grafana home dashboard, select Add data source.

Select the PostgreSQL data source, and then:

  • Set the Name CrateDB
  • Set the Host to localhost:5432
  • Set the Database to doc
  • Set the User to crate
  • Set SSL Mode to disable

When you're done it should look like this:

Grafana screenshot

Scroll down and select Save & Test.

You should see this:

Grafana screenshot

Set up a new dashboard

Go back to your Grafana home dashboard.

You should be invited to set up a new dashboard:

Grafana screenshot

Select New dashboard.

The next screen should look like this:

Grafana screenshot

We have some imported tweets. So let’s work with that and graph the number of imported tweets over time.

Select Add Query, and you should see this:

Grafana screenshot

Use the query editor at the bottom of the screen, and do the following:

  1. On the FROM line, change select table to tweets. Then, change time to created_at.
  2. On the SELECT line, change the Column value to id. Thgen, select + and choose Aggregate FunctionsCount. Finally, change the Alias id to tweets.

At this point, the graph above the query editor should be displaying some data. Here's what my screen looked like:

Grafana screenshot

The query we’ve configured counts the number of tweets imported per time interval. Here, we what looks like a single data point, on the far right-hand side of the graph. This data point corresponds to the short burst of tweets we imported.

Also, notice that Grafana has set GROUP BY to time ($__interval, none). This is needed for the aggregate function we’re using.

Zoom in

That little data point isn’t exciting by itself. But fortunately, Grafana lets us configure the graphed time range and the aggregation time interval.

The first thing to do is set the Min time interval (bottom left) to 1s. This means that Grafana will graph the total number of imported tweets per second, instead of per minute, which is the default.

Now, move your pointer over the chart and select a time range that includes the data point of interest. The chart should zoom in:

Grafana screenshot

Select another area of the chart to zoom in some more:

Grafana screenshot

At this level of detail, we can see that throughout the data import, CrateDB was writing somewhere between 30 and 50 tweets per second. (Note: CrateDB can ingest millions of records per second. In this instance, the rate of ingestion is limited by the Twitter API and our ingestion method.)

From here, you can select the Vizualization tab from the left-hand icon menu and configure the visual appearance of the chart.

You can also set a title from the General tab.

Once you’re done, select the back arrow in the top left-hand corner of the screen to return to your new dashboard.

Here it is:

Grafana screenshot

Wrap Up

In this post, I showed you how to get CrateDB and Grafana up-and-running on your local machine. We imported some tweets and then visualized the import process by graphing the number of imported tweets over time.

Grafana is an excellent tool for data visualization and a perfect fit for the sorts of time series and machine data use-cases CrateDB was built for.

This is a simple demo of what CrateDB and Grafana can do together. If this was a production app, it is likely we would have a continuous stream of data to monitor—and that’s where Grafana’s live dashboards really excel.

From here, you might want to continue with either the Grafana documentation or the CrateDB documentation.

Newsletter

Stay up to date

Sign up here to keep informed about CrateDB product news,
events, how-to articles, and community update.