How do I get a pie chart?

[JohnnyBee2]

How do I get a pie chart for a query type? select name, sum (field) as val group by name where the pie chart shows the groups and their sum.

[fr-ser]

I don't think this is related to the SQLite plugin. I would either recommend asking on stackoverflow or the pie-chart plugin's site.

[JohnnyBee2]

Hmmmm, pie charts for MySQL or MSSQL work fine. For pie charts to work (MySQL) must use the SQL Format as Time series type, but the SQLite plugin does not have this option. I think it only uses the Format as Table option.

For Time series: For the Format as Table option - no pie chart

[fr-ser]

The SQLite plugin does not use the old timeseries or table formats, but the new "DataFrame" format.

Maybe the pie chart plugin does not support it?

[JohnnyBee2]

I don't know what data format the pie chart uses. For me, pie charts work fine with data sources: mysql, mssql, elasticsearch. I do not use other data sources. I only have a problem with SQLite.

Thanks again for your help and a good job with SQLite.

Edit: Graph plugin from SQlite (sql: .... group by field ...) doesn't work for me. SQLite source cannot split the data for charts in groups.

[fr-ser]

I checked the issue and I also cannot get the pie chart working correctly with the SQLite plugin (same query works with PostgreSQL)

SELECT 1 as time, 5 as value, 'sth' as label
UNION ALL
SELECT 1 as time, 10 as value, 'else' as label

I don't have anything to go on right now. I am guessing it has to do with SQLite using the new DataFrame format. It might be worth asking in the pie-chart repo :shrug:

[JohnnyBee2]

I think the pie chart works with dataframes. See screen shot.

I think that the grafana-sqlite-datasource plugin should create N (for each name) group dataframes for a grouping sql query: select name, value from table group name (maybe N arrays in dataframes) - these are just my suggestions. The same problem with grafana-sqlite-datasource occurs with other charts. Can you add this functionality?

[JohnnyBee2]

The same is true for line charts.

[fr-ser]

I think the pie chart works with dataframes.

At least you got it working with separate queries. That is good :ok_hand: Of course, it is not as convenient as a group by (maybe you can hack it together with "repeating queries" over a query variable :shrug:

I think that the grafana-sqlite-datasource plugin should create N (for each name) group dataframes for a grouping sql query

I am not so sure about that. There is not much documentation about dataframes to be found online, but in general if you have multiple return columns for your SQLite query (doesn't matter if it is grouped or not) it would make sense to put it just to another column in the dataframe. I don't know of any other plugin using dataframes yet, where I could take a peek, but returning multiple responses for just one query does not seems the right way here. I would really question the pie-chart plugin here. Dataframes are not around that long and maybe they just have not been implemented yet :shrug:

Also, the example of a graph is good because the below query works just fine (as I would expect)

WITH sth(time, value, other) as (VALUES
(1,2,3), (2,3,4), (3,4,5)
) select * from sth

[JohnnyBee2]

No, no, that's not good. This is just an example of how dataframes graphs work. I need different data series for charts. series A time1A, nameA, value time2A, nameA, value time3A, nameA, value ..... timeNA, nameA, value

series B time1B, nameB, value time2B, nameB, value time3B, nameB, value ..... timeNB, nameB, value

...... ..... .....

series N time1N, nameN, value time2N, nameN, value time3N, nameN, value ..... timeNN, nameN, value

time timexA does not match timexB does not match timexN ... A separate plot is drawn for each series. I do not know the number of series and their names. This all makes me a SQL query ".... GROUP BY name ..."

Separate graph lines can only create separate data series.

Data should not be placed in one dataframes without additional information about which data is related to each series. Currently, the SQLite plugin is very good for tables, but not very useful for charts. I will wait for the next versions - thanks for the work.

[theodor-n]

I don't know of any other plugin using dataframes yet, where I could take a peek, but returning multiple responses for just one query does not seems the right way here.

Hi, I opened a separate thread/issue ("Support for multiple time series") related to time series in general. Inside there is a sample working* implementation of multiple time series using dataframes. Please bear with my code (I'm a golang novice) - but this should give you an idea of how to put multiple time series in dataframes.

*working - yeah, I did test it locally with a couple of visualization panels requiring multiple time series data. Also - looking at "Query Inspector" -> "Data" it appears that Grafana is handling the generated data frames properly: indeed it took some experimenting to get there - as you say there is not much documentation available for dataframes

[fr-ser]

Data should not be placed in one dataframes without additional information about which data is related to each series.

I would love to see some examples or documentation to back this up. Only then it makes sense to take action.

@JohnnyBee2 Maybe you could provide a more reproducible case to take a further look (something like a small test database and query and an exported dashboard).

[fr-ser]

I think with the workaround from above and the further discussion in this issue, this issue can be closed https://github.com/fr-ser/grafana-sqlite-datasource/issues/16#issue-752140710