Dynamic schema reference dashboard for Timescale and Grafana

TimescaleDB and PostgreSQL
1

I use Grafana quite a bit for gathering data and doing analytical queries against our TimescaleDB internal databases. One thing I find difficult is navigating the different schemas and tables available. To address this problem I created a Schema Reference Dashboard which dynamically lists all the schemas and all the tables within those schemas.

Here’s how you can do it. Create a new dashboard, I named my dashboard Schema Reference

The dashboard needs 2 variables to work. The schema variable will be displayed at the top of the dashboard and allows users to choose which schemas to list out in the page while the table variable will be used internally.

schema
The first variable. This gathers all the different schemas available in your database which you can choose to list.

  • multi-value :white_check_mark:
  • include All option :white_check_mark: 
SELECT
    table_schema
FROM
    information_schema.tables
WHERE 1 = 1
  AND is_insertable_into = 'YES'
  AND table_schema NOT LIKE '_timescaledb_%'
  AND table_schema NOT IN ('pg_catalog', 'information_schema', '_timescaledb_internal')
ORDER BY 1 DESC;

table
The second and dependent variable. Table is dependent on schema :point_up: and gathers all the tables available within the schemas.

  • hide (variable) we don’t need to choose these
  • Include All option :white_check_mark: 
SELECT
    table_schema || '.' || table_name
FROM
    information_schema.tables
WHERE 1 = 1
  AND table_schema IN (${schema:singlequote})
ORDER BY table_schema DESC, table_name ASC;

Now that we have our dashboard variables setup we want to show the table schema by displaying some limited amount of data from each table. Add :one: new panel to the dashboard.

Panel
This panel will display the 3 rows of data from each table. Because we have the repeat option turned on this table will display for each table listed in our table variable giving you a page full of example data.

  • type = Table
  • Repeat options
    • Repeat by = table
    • Repeat direction = vertical
  • Show header
SELECT
  *
FROM 
  ${table:raw}
ORDER BY 1 DESC
LIMIT 3;

The complex (and unfinished) part here was attempting to get the sort order to dynamically choose a timestamp. I wanted the most recent data returned but timestamp columns could have many different names. I tried doing a lookup into the information_schema.columns table for timestamp columns and while this query does return the information I wanted no matter the return value it didn’t change the sort ordering.

Here’s the query I wanted to work but doesn’t yet. Any help is appreciated.

--- DON'T KNOW WHY THIS DOESN'T ORDER BY COLUMN NAME RETURNED FROM SUBQUERY
SELECT
  *
FROM 
  ${table:raw}
ORDER BY (
  SELECT column_name
  FROM information_schema.columns 
  WHERE 1 = 1
    AND table_schema=split_part('${table:raw}', '.', 1)
    AND table_name=split_part('${table:raw}', '.', 2)
    AND data_type LIKE 'timestamp%'
  LIMIT 1
) DESC
LIMIT 3
1 Like