How to run Grafana with QuestDB

user_1267728 0
Viewed 246

I've imported a demo dataset in QuestDB and I can query it successfully from the console. I'm using Grafana to build a dashboard to test visualization.

My QuestDB installation is running on port 9000 and I can import it without any issues:

curl -F data=@weather.csv http://localhost:9000/imp

I'm running the following query which is failing:

SELECT timestamp as time,
       avg(visMiles) AS average_visibility
FROM 'weather.csv'
WHERE $__timeFilter(timestamp)
SAMPLE BY $__interval
LIMIT 1000

The error I get is

pq: unknown function name: between(TIMESTAMP,STRING,STRING)

I'm using a dataset provided in their examples.

questdb grafana
1 Answers

QuestDB relies on a designated timestamp specified during table creation. This would not cause an error if one was provided with the curl request as a URL param, given a column named 'timestamp':

curl -F data=@weather.csv http://localhost:9000/imp?timestamp=timestamp

Another option is during a SELECT operation, a timestamp() function can specify one dynamically. If you've imported using curl and not set a designated timestamp, there are two options:

  1. Modify your query to use timestamp() on the column you want to designate:

    SELECT timestamp as time,
       avg(visMiles) AS average_visibility
FROM (‘weather.csv’ timestamp(timestamp))
WHERE $__timeFilter(timestamp)
SAMPLE BY $__interval
LIMIT 1000

  2. Create a new table which is a copy of your original dataset but designate a timestamp during creation. ORDER BY is used because the demo dataset has unordered timestamp entries:

    create table temp_table as (select * from ‘weather.csv’ order by timestamp) timestamp(timestamp);

    And instead of querying your original dataset, use the temp_table:

    SELECT timestamp as time,
  avg(visMiles) AS average_visibility
FROM temp_table
WHERE $__timeFilter(timestamp)
SAMPLE BY $__interval
LIMIT 1000

If you need more info on the use of designated timestamps, the QuestDB concepts / timestamp docs page has further details.

Edit: There are some more resources to with this topic such as a guide for Grafana with QuestDB and GitHub repo with docker-compose.

user_88362650
Related