Grafana, SQL and IN() list for multi-value variable

By Franck Pachot

.
I’ve recently looked at Grafana. Pros: it is really nice, I mean aesthetically. If you want to build a dashboard for your boss, or put it on a large screen in the open space, that’s beautiful. Cons: not mature. I’m working for years with technology that works. When I have a problem, I find the solution (blog posts, forums, mailing lists, support notes)… But with modern software, the first problems I encounter have a dead end in unresolved git issues. And I’m actually spending more time in finding workarounds than solving problems. Yes, Grafana is nice but is very far from what I can do with Excel in 5 minutes. However, when I find a workaround, I publish it to help others and hopefully find someone who has a better solution, so please leave a comment if what I do there is completely silly.

The problem to solve: IN() list in SQL from a multi-value variable

For this example I’ve created a PostgreSQL database in Google Cloud SQL, loaded sample data from Gerald Venzl and a free grafana cloud service. My goal is to get something like that where I can choose one, many, all or no country at all:

I can query the list of countries as:

select country_id __value , name __text from countries order by 2

I alias my columns with “__value” and “__text” to show the country name to the user but get the country code from what she selected.

So I’ve created the Grafana variable with this query, and selected “Multi-value” and “Include All option” to let the user chose any combination:

Now, let’s try to use this variable, that I called “mylist” for the demo, in a SQL statement:

select * from cities where country_id in ($mylist)

I’ll show the query generated for the different user combinations

select * from cities where country_id in ('AND','ARE','AFG','ATG','ALB','ARM','AGO','ARG','AUT','AUS','AZE','BIH','BRB','BGD','BEL','BFA','BGR','BHR','BDI','BEN','BRN','BOL','BRA','BHS','BTN','BWA','BLR','BLZ','CAN','COD','CAF','COG','CHE','CIV','CHL','CMR','CHN','COL','CRI','CUB','CPV','CYP','CZE','DEU','DJI','DNK','DMA','DOM','DZA','ECU','EST','EGY','ERI','ESP','ETH','FIN','FJI','FSM','FRA','GAB','GBR','GRD','GEO','GHA','GMB','GIN','GNQ','GRC','GTM','GNB','GUY','HND','HRV','HTI','HUN','IDN','IRL','ISR','IND','IRQ','IRN','ISL','ITA','JAM','JOR','JPN','KEN','KGZ','KHM','KIR','COM','KNA','PRK','KOR','KWT','KAZ','LAO','LBN','LCA','LIE','LKA','LBR','LSO','LTU','LUX','LVA','LBY','MAR','MCO','MDA','MNE','MDG','MHL','MKD','MLI','MMR','MNG','MRT','MLT','MUS','MDV','MWI','MEX','MYS','MOZ','NAM','NER','NGA','NIC','NLD','NOR','NPL','NRU','NZL','OMN','PAN','PER','PNG','PHL','PAK','POL','PRT','PLW','PRY','QAT','ROU','SRB','RUS','RWA','SAU','SLB','SYC','SDN','SWE','SGP','SVN','SVK','SLE','SMR','SEN','SOM','SUR','SSD','STP','SLV','SYR','SWZ','TCD','TGO','THA','TJK','TLS','TKM','TUN','TON','TUR','TTO','TUV','TWN','TZA','UKR','UGA','USA','URY','UZB','VAT','VCT','VEN','VNM','VUT','WSM','XKX','YEM','ZAF','ZMB','ZWE')

This is what I have when the user selects “All” and that’s correct.

select * from cities where country_id in ('BEL','NLD','LUX')

This is what I get when the user selects some countries,and this again is correct.

select * from cities where country_id in ()

This is what I get when the user selects nothing. And I see no option in Grafana variables to disable this possibility. This is an invalid SQL syntax and gets, in PostgreSQL: pq: syntax error at or near “)”, MySQL would show: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘)’ at line 1, Oracle would show: ORA-00936: missing expression, SQL Server would show: Incorrect syntax near ‘)’. Grafana colorful charts are now less appealing when we get an non-user-friendly message like this. I need a workaround.

Not so advanced formats

I checked the “Advanced variable format options” with the hope that “advanced” options should have a format for the most common data query language which is SQL. But not. You have CSV, OpenTSDB, Doublequote, Graphite, JSON, Elasticsearch, Percentencode, Pipe, Raw, Regex, Singlequote, Sqlstring, Text, URL formats, but nothing about SQL! Yes, SQL can filter on regexp but I want index access, partition pruning which are possible with equality or IN() lists only.

Nested variables and SQL transformation

Here is my workaround. I’ve created a second variable, that will be hidden, to build the IN() list from the list of value. And because I have a SQL engine, I’ll use it to do the transformation:

select case when length($$${mylist:singlequote}$$)>0 then $$ in ($mylist:singlequote) $$ else ' is null ' end

Here I expand the previous variable with the “singlequote” format, which is the default for SQL queries. But I encapsulate it in a CASE statement. When the list is not empty, my variable builds the IN(…) list but when it is empty I choose to replace it with a IS NULL to let the user select unknown values. Of course you can decide the behaviour with a predicate that is always true or false.

Then here is my dashboard panel query:

select * from cities where country_id ${mylist_sql:raw}

which expands to the same as when I used ${mylist} when the user chooses one, many, or all values.

But when the user selects nothing:

select * from cities where country_id is null

No error and the behavior I’ve chosen. Here no rows with no work because the optimizer knows that the primary key cannot be null.