SQL query parameterization: Run multiple SQL queries in a dynamic way in a panel

I am using Grafana with the Amazon Athena data source.

My backend is an AWS S3 based Parquet data lake structured as follows:

[DeviceID]/[MessageName]/YYYY/MM/DD/filename.parquet

Each table is thus equivalent to a message from a specific device and contains sub signals of that message group within the columns of the Parquet file. An example query in Grafana may look as below:

SELECT
    $__timeGroup(t, $__interval) as time,
    AVG(mysignal) as AVG_mysignal
FROM
    tbl_${device:csv}_mymessage
WHERE
    date_created BETWEEN $__rawTimeFrom('yyyy/MM/dd') AND $__rawTimeTo('yyyy/MM/dd')
    AND
    $__timeFilter(t)
GROUP BY
    $__timeGroup(t, $__interval)

The structure works well for performing visualizations from a single device and e.g. creating separate panels per message.

However, for some use cases I want to show information across multiple devices, e.g. all devices or say 100 different devices. For example, I might want to display the average signal value of Speed from the table MessageSpeed grouped to a daily basis across all devices. However, this is not trivial to do in a single SQL query ala the one above, as I e.g. do not want to perform a table join across all the device tables (it would be extremely time consuming).

I am considering whether it may be practical to use some form of parameterization logic to submit multiple parallel SQL queries within Grafana in order to perform the relevant query from each device table, then visualize the results from this in a single panel.

However, I struggle to find a method to do this within Grafana (as well as other Athena-supporting tools). Basically, I would want to be able to provide a list of DeviceID values to a function that then runs the above query once per device and returns the results, but in a dynamic way, with the number of executions depending on the original device list provided.

Any suggestions are welcome.

you can try variables for this.

create a query variable to build your list of deviceid’s.

then in your query, write your sql to fetch where deviceid’s in your $variable. using you variable like this : ```
${deviceid:csv} fetches it in a comma separated format, useful for use in sql statements

It looks like maybe part of the reply is missing?

What I am looking to achieve is very similar to Grafana’s ability to ‘repeat panels’ based on e.g. multiple entries in a deviceID dropdown list. But I would like it to ‘repeat queries’ within a panel instead, so that I can e.g. plot AvgSpeed from 5 separate device tables using 5 x the same independent query, but without having to hardcode a big UNION query in the panel.