Integrate Grafana and Timescale Cloud

Log in to Grafana

In your browser, log in to either:

• Self-hosted Grafana: at http://localhost:3000/. The default credentials are admin, admin.
• Grafana Cloud: use the URL and credentials you set when you created your account.

Add your service as a data source

1. Open Connections > Data sources, then click Add new data source.
2. Select PostgreSQL from the list.
3. Configure the connection:
   • Host URL, Username, Password, and Database: configure using your connection details.
   • Database name: provide the name for your dataset.
   • TLS/SSL Mode: select require.
   • PostgreSQL options: enable TimescaleDB.
   • Leave the default setting for all other fields.

Click Save & test

Grafana checks that your details are set correctly.

On the Dashboards page, click New and select New dashboard

Click Add visualization

Select the data source

Select your service from the list of pre-configured data sources or configure a new one.

Configure your panel

Select the visualization type. The type defines specific fields to configure in addition to standard ones, such as the panel name.

Run your queries

You can edit the queries directly or use the built-in query editor. If you are visualizing time-series data, select Time series in the Format drop-down.

Click Save dashboard

You now have a dashboard with one panel. Add more panels to a dashboard by clicking Add at the top right and selecting Visualization from the drop-down.

Call $__timefilter() to link the user interface construct in a Grafana panel with the query

For example, to set the pickup_datetime column as the filtering range for your visualizations:

SELECT
  --1--
  time_bucket('1 day', pickup_datetime) AS "time",
  --2--
  COUNT(*)
FROM rides
WHERE $__timeFilter(pickup_datetime)

Copy

Group your visualizations and order the results by time buckets

In this case, the GROUP BY and ORDER BY statements reference time.

For example:

SELECT
  --1--
  time_bucket('1 day', pickup_datetime) AS time,
  --2--
  COUNT(*)
FROM rides
WHERE $__timeFilter(pickup_datetime)
GROUP BY time
ORDER BY time

Copy

When you visualize this query in Grafana, you see this:

Expand image

You can adjust the time_bucket function and compare the graphs:

SELECT
  --1--
  time_bucket('5m', pickup_datetime) AS time,
  --2--
  COUNT(*)
FROM rides
WHERE $__timeFilter(pickup_datetime)
GROUP BY time
ORDER BY time

Copy

When you visualize this query, it looks like this:

Expand image

Add a geospatial visualization

1. In your Grafana dashboard, click Add > Visualization.

2. Select Geomap in the visualization type drop-down at the top right.

Configure the data format

1. In the Queries tab below, select your data source.

2. In the Format drop-down, select Table.

3. In the mode switcher, toggle Code and enter the query, then click Run.

   For example:

   SELECT time_bucket('5m', rides.pickup_datetime) AS time,
          rides.trip_distance AS value,
          rides.pickup_latitude AS latitude,
          rides.pickup_longitude AS longitude
   FROM rides
   WHERE rides.trip_distance > 5
   GROUP BY time,
            rides.trip_distance,
            rides.pickup_latitude,
            rides.pickup_longitude
   ORDER BY time
   LIMIT 500;

   Copy

Customize the Geomap settings

With default settings, the visualization uses green circles of the fixed size. Configure at least the following for a more representative view:

• Map layers > Styles > Size > value.

  This changes the size of the circle depending on the value, with bigger circles representing bigger values.

• Map layers > Styles > Color > value.

• Thresholds > Add threshold.

  Add thresholds for 7 and 10, to mark rides over 7 and 10 miles in different colors, respectively.

You now have a visualization that looks like this:

Expand image