Visualizing time series data with Grafana and CrateDB 📈
Tags: tutorial, grafana, database, opensource
Published: 2021-03-09 03:13:19

[Grafana](https://grafana.com) is an open-source tool that helps you build real-time dashboards and graphs. It is the perfect complement to [CrateDB](https://crate.io/products/cratedb/), which is purpose-built for monitoring large volumes of [machine data](https://crate.io/machine-data/) in real-time. 

In this tutorial, I will show you how to:

* Install CrateDB on-prem / Sign up for CrateDB Cloud
* Load a sample dataset
* Install Grafana
* Connect Grafana to CrateDB or CrateDB Cloud
* Build your first Grafana dashboard
* If you already have CrateDB or CrateDB Cloud installed with data loaded into it, jump to the "Connect Grafana to CrateDB" section to get straight to the point.

Let’s get started!

## Installing CrateDB / Signing up for CrateDB Cloud

If you want to use **CrateDB on-prem**, an easy way to do so is with Docker. If you don't have Docker, you can download it [here](https://www.docker.com/products/docker-desktop) — just follow the instructions that will pop-up. For alternative install options, check out [our docs](https://www.docker.com/products/docker-desktop).

Once you have Docker installed, create a directory where your data will be stored. Navigate there with your terminal, and run the following command:

```
docker run --rm --name=cratedb --publish=4200:4200 --publish=5432:5432 --volume="$(pwd)/var/lib/crate:/data" crate
```

Then, open `http://localhost:4200/` in your browser. You will see the CrateDB Admin UI:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5vfj7pqdoqey129n1msp.png)

If you prefer to use **CrateDB Cloud**, you can [sign up for free trial](https://go.cratedb.com/lp-free-trial.html?utm_source=startpage&utm_medium=website&utm_campaign=free-trial). For instructions on how to deploy a free-trial cluster, [click here](https://crate.io/docs/cloud/tutorials/en/latest/free-trial.html). You'll be done in a few minutes, no credit card required.

## Loading a sample dataset

In this tutorial, I’ll use a sample dataset that includes data published by the [NYC Taxi & Limousine Commission](https://www1.nyc.gov/site/tlc/about/tlc-trip-record-data.page)—**a reduced-size version of the NYC dataset we've used in other posts**.  This is a great dataset to learn Grafana, as it includes a wide variety of information while loading fast into CrateDB.

The process of importing the dataset is equivalent for both CrateDB on-prem and CrateDB Cloud. If you're using the latter, you can access the CrateDB Admin UI by clicking on "Open cluster administration" in your cluster "Overview" page:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kkk7zt5g3a3pldfeiswr.png)

You will be asked the username and password associated with your cluster (the 24-character password). If you don't remember those credentials, you can check out your username (and change your password) by clicking on "Settings".

Once you introduce your credentials, the CrateDB Admin UI will pop-up:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o0snxj77rr5hv7514t9t.png)

The first step to importing the data is [creating a table](https://crate.io/docs/crate/reference/en/4.4/general/ddl/create-table.html). In the CrateDB Console, type the following code, and click on "Execute query":

```sql
CREATE TABLE "nyc_taxi_sample" (
  "congestion_surcharge" REAL,
  "dolocationid" INTEGER,
  "extra" REAL,
  "fare_amount" REAL,
  "improvement_surcharge" REAL,
  "mta_tax" REAL,
  "passenger_count" INTEGER,
  "payment_type" INTEGER,
  "pickup_datetime" TIMESTAMP WITH TIME ZONE,
  "pulocationid" INTEGER,
  "ratecodeid" INTEGER,
  "store_and_fwd_flag" TEXT,
  "tip_amount" REAL,
  "tolls_amount" REAL,
  "total_amount" REAL,
  "trip_distance" REAL,
  "vendorid" INTEGER)
WITH ("column_policy" = 'dynamic', "number_of_replicas" = '0', "refresh_interval" = 10000);
```

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gbxowwtgb5s11c97kajo.png)

Now, import the dataset. Execute this query:

```sql
COPY "nyc_taxi_sample" FROM 'https://s3.amazonaws.com/crate.sampledata/nyc.yellowcab/yc.2019.07.gz'
WITH (compression = 'gzip');
```

The data is now loading into CrateDB. Click on "Tables"; you will see the table you just created ("nyc_taxi_sample") getting filled. In my case, it took less than 10 minutes to get the 6 million records.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cpf7ym1c41xdizzkmwnb.png)

Before moving on to the next section, let's explore our table. Run this simple query:

```sql
SELECT * FROM nyc_taxi_sample
LIMIT 10;
```

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o7nfknuvf2w4i7hrsmfs.png)

Scroll to the right to get familiar with the columns in the table, which display information about the NYC taxi rides. Some examples:

* `pickup_datetime`: this column shows the pickup date and time for each taxi ride recorded as a timestamp
* `trip_distance`: the ride's distance in miles
* `passenger_count`: the number of passengers present in each ride
* `total_amount`: the price of the ride, in USD

## Installing Grafana

To install Grafana, follow [the instructions on their website](https://grafana.com/grafana/download?cta=downloads&plcmt=top-nav&platform=mac). There are multiple options; in my case, I used the standalone MacOS installation. 

```
curl -O https://dl.grafana.com/oss/release/grafana-7.4.3.darwin-amd64.tar.gz 
tar -zxvf grafana-7.4.3.darwin-amd64.tar.gz
```

If you’re following this method too, once you've copied Grafana to your preferred directory, install and start its local web server with the following command:

```
./bin/grafana-server web
```

Now, access Grafana by opening `http://localhost:3000/` in your browser:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tnk22it1h382eo4fn16z.png)

In the previous screen, enter "admin" in both username and password. The screen below will pop up next, where you can set up a new password.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0bqd6s772vu6n13ldvat.png)

You're now in!

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u0vvcjcuhnmthp81q7aq.png)

## Connecting Grafana to CrateDB or CrateDB Cloud

If you just installed Grafana, click on "Add your first data source".

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cvshrab4o1gjxb9z4ktg.png)

If you don't see the previous screen, you can also go to "Configuration -> Data Sources" in the left menu.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/aayclcrzwchzg75c5wci.png)

Here, click on "Add data source":

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oz77phwfdye4bjpoq82b.png)

Once you get to a page like this, look for PostgreSQL. Select it.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/907p7d7vsk3n8cbmql1y.png)

You will get to a configuration screen.

If you are using **CrateDB on-prem**, enter the following values:

* **Name:** CrateDB
* **Host:** localhost:5432
* **Database: doc**
* **User:** crate
* **Password:** leave it empty
* **SSL mode:** disable

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7elwts5c7igmtzsye1ri.png)

Once you've filled it up, click on "Save and test". If everything goes well, you'll see “Database Connection OK”:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9yaz4mkl5atstbjvfon2.png)

If you are using **CrateDB Cloud**, the configuration will be the following:

* **Name:** CrateDB Cloud
* **Host:** to get this information, go to the CrateDB Cloud Console. In the cluster "Overview" page, click on "Learn how to connect to the cluster".

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hfcdvhg5e5g7a97o6cyd.png)

A screen like this will display:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hqtxhrr74rt3w4cdfriu.png)

To create a Grafana connection, use the `psql` client. In this   case:

`my-cluster.aks1.eastus2.azure.cratedb.net`

* **Database:** doc
* **User and password:** these are the credentials to access your CrateDB Cloud cluster (the 24-character password). Remember that they will show under "Settings" on the cluster "Overview" page
* **SSL mode:** require

Once you have everything ready, your configuration page will look like this:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e4ihoo2rxcz63c432jxu.png)

Click on "Save and test". If everything goes well, you'll see a "Database connection OK" message pop up.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/naq5b7rjqs8j3ol2zan0.png)

## Building a dashboard in Grafana

Now we're ready for the final step: let's build a dashboard in Grafana.

If you're new to Grafana, you'll see the screen below. Click on "Dashboards" in the menu on the left. Then, click on "Create your first dashboard":

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5rn7si31upqv17tsk2f7.png)

If you don't see the previous option, you can also create a dashboard under "Create -> Dashboard" on the left menu.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mqp0ov6z9j6tako1vwoi.png)

Either way, you will get to a screen like the one below. 

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vl5ou6x1wkltetgh83sg.png)

In Grafana, dashboards are composed of individual blocks called [panels](https://grafana.com/docs/grafana/latest/panels/), to which you can assign different visualization types and individual queries. Click on "Add new panel". 

In the next screen, we will define the query for your panel, the type of visualization (like graphs, stats, tables, or bar charts), and the time range. I won't get into all the options in the panel editor here, but I recommend you explore the menu on the right to start getting familiar with it. Also, check out the [Grafana documentation](https://grafana.com/docs/grafana/latest/panels/panel-editor/).

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/016myx54mpifmoo6rs8n.png)

We will start by defining our query. Click on "Edit SQL":

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fzvy4twc8k1c6428e0yw.png)

A console will open up where you can write your SQL code.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zh8nwvjxilg7h0zkcibx.png)

To build this panel, let's plot **the number of rides per day in the first week of July 2019**.

Paste this query in the SQL editor:  

```sql
SELECT date_trunc('day', pickup_datetime) AS time,
COUNT(*) AS rides
FROM nyc_taxi_sample
WHERE pickup_datetime BETWEEN '2019-07-01T00:00:00' AND '2019-07-07T23:59:59'
GROUP BY 1
ORDER BY 1;
```

*Note: something important to know about the "Time series" format mode in Grafana is that your query needs to return a column called "time". Grafana will identify this as your time metric, so make sure the column has the proper datatype (any datatype representing an epoch time). In this query, we're labeling pickup_datetime as "time" for this reason.*

This is how it looks like:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/glnpqbzj6ugmwwbh1gge.png)

Now, set up the following configuration:

* On the top of the panel, select the appropriate time range for your panel—in this case, from July 1st to July 7th, 2019:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2wbq1j8rup7w03dwgxkk.png)

* Under "Settings" on the right, define the name of your panel.
* Under "Visualization", select "Graph".
* Under "Display", select "Bars".

Click on "Apply" on the right side of the screen. Once everything is ready, your graph will show up:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qebumtpiwzxopt6e50bo.png)

The number of rides per day fluctuates between 100k and 200k, with July 2nd being the busiest day of the week.

Click on the arrow on the top left of the screen to go back to your dashboard. It now contains its first panel:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8usehzmnb59jay4s7djb.png)

Before wrapping up, let's add a couple more panels to it. For example, let's include the following information:

* How many taxi rides took place during the week?
* What was the average distance per ride per day?

To add more panels to your dashboard, click on "Add panel" at the top of the screen.

Now that you get the basics of panel building, I will go through these a bit quicker, describing only the key steps:

* **Panel 2: How many rides took place during the first week of July?**

For building this panel, paste this query in the SQL editor:

```sql
SELECT COUNT(*) AS total_rides
FROM nyc_taxi_sample
WHERE pickup_datetime BETWEEN '2019-07-01T00:00:00' AND '2019-07-07T23:59:59';
```

And under "Visualization", select "Stat" as your panel type. It will look like this:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5f8fy2droy4qwm7gwtab.png)

* **Panel 3: What was the average distance per ride per day?**

For building this panel, paste this query in the SQL editor:

```sql
SELECT
date_trunc('day', pickup_datetime) AS time,
COUNT(*) as rides,
SUM(trip_distance) as total_distance,
SUM(trip_distance) / COUNT(*) AS average_distance_per_ride
FROM nyc_taxi_sample
WHERE pickup_datetime BETWEEN '2019-07-01T00:00:00' AND '2019-07-07T23:59:59'
GROUP BY time
ORDER BY 1;
```

With a little bit of formatting, and after selecting "average_distance_per_ride" at the bottom of the graph (it will show the three columns returned by the query above), this is how the panel looks like:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e9jafmg6h8wed3lemrbe.png)

Our average distance per trip ranges around 3 miles on average.

Our dashboard now looks a little bit more complete! If you want to keep building it, check out [this blogpost](https://crate.io/a/time-series-cratedb-cloud-sql-examples/) for inspiration, where we use SQL queries to explore the NYC taxi data. You can also customize the look of your dashboard by dragging and dropping the panels, making them larger... Go to the [Grafana documentation](https://grafana.com/docs/grafana/latest/panels/) for more.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5qy00kj8odwg9r9iex1v.png)

Don't forget to click on the "Save dashboard" button before you go.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d2tamn5y54lp9dhm7qln.png)

_______________________________________________________________

*There's nothing better than trying things by yourself! [Download CrateDB](https://crate.io/download/) or [sign up for a CrateDB Cloud free trial](https://go.cratedb.com/lp-free-trial.html?utm_source=cloudproductpage&utm_medium=website&utm_campaign=free-trial). Experiment... And tell us what you think* 😁

*Apart from [Dev.to](https://dev.to/crate), you can reach to the [Crate.io](https://crate.io) team in:*

* *[Github](https://github.com/crate) (We'd love a* ⭐️ 🙏*)*
* *[Twitter](https://twitter.com/crateio)*
* *[Our community page](https://community.crate.io)*

*See you around* 🐐