String as x-axis in Grafana xy plot
My goal is to plot a xy scatter plot in Grafana. X-axis is 'setting', and the potential values are 'off', 'idle', 'standby', and 'on'. On the y-axis I have 'performance' and the data type is real. My issue is that xy scatter plot only supports numeric values and not strings on the axis, https://grafana.com/docs/grafana/latest/panels-visualizations/visualizations/xy-chart/.
My data source is sqlite db, and the data is currently stored as
| id | setting | performance |
|---|---|---|
| 1 | 'idle' | 11.4 |
| 2 | 'off' | 2.5 |
| 3 | 'idle' | 10.6 |
I've tried to transform 'setting' to enum in Grafana (https://grafana.com/docs/grafana/latest/panels-visualizations/query-transform-data/transform-data/) but it was not possible to use enum on x-axis.
Is there any other way to transform the string to numerical value in Grafana? Or is there a better way to store the data in the database?
Is there any other way to transform the string to numerical value in Grafana? Or is there a better way to store the data in the database?
A third option is to convert the data from the database when extracting it. A fourth option would be to store the setting in a table that has both values, the table being related to the main table i.e. utilising the relational aspect of a relational database.
It is relatively easy to convert the text to numerical representations when extracting the data. e.g. using the CASE WHEN THEN ELSE END construct.
e.g. perhaps the following would suit:-
SELECT
id,
CASE
WHEN setting LIKE 'off' THEN 0
WHEN setting LIKE 'standby' THEN 1
WHEN setting LIKE 'idle' THEN 2
WHEN setting LIKE 'on' THEN 3
ELSE 4
END AS setting,
performance
FROM mydata
ORDER BY id;
The following demonstrates the above:-
/* Cleanup just in case */
DROP TABLE IF EXISTS mydata;
/* Create the table */
CREATE TABLE IF NOT EXISTS mydata (id INTEGER PRIMARY KEY, setting TEXT, performance REAL);
/* Populate the table for the test */
INSERT INTO mydata (setting,performance) VALUES
('idle',11.4),('off',2.5),('idle',10.6),('on',17.1),('standby',1.4), ('rubbish',666.66) /* etc*/;
/* Extract the data converting the setting to a numeric representation */
/* note values could be as required */
SELECT
id,
CASE
WHEN setting LIKE 'off' THEN 0
WHEN setting LIKE 'standby' THEN 1
WHEN setting LIKE 'idle' THEN 2
WHEN setting LIKE 'on' THEN 3
ELSE 4
END AS setting,
performance
FROM mydata
ORDER BY id;
/* cleanup test environment*/
DROP TABLE IF EXISTS mydata;
The result being (note the additional data inserted to test all permutations of the setting value, including a non-conforming value rubbish):-
An alternative approach, would be to store the setting as the numeric, you could extract the text values from the numeric value when/if required in a similar way.
Yet another alternative approach, a which would be considered more consistent with normalisation, would be to have the setting types in a table that has a relationship to the core table. This then stores just the single value per setting.
- this approach would certainly pay off if the intention is to store many rows.
- see https://en.wikipedia.org/wiki/Database_normalization
The following demonstrates this alternative approach:-
/* cleanup test environment*/
DROP TABLE IF EXISTS setting;
DROP TABLE IF EXISTS mydata;
/* Create the related setting table */
CREATE TABLE IF NOT EXISTS setting (setting_as_text TEXT, setting_as_numeric INTEGER PRIMARY KEY);
/* Create the alternative mydata table that instead of storing the setting (text) value for every row
the value stored is a reference (map/association) to the setting value(s)
*/
CREATE TABLE IF NOT EXISTS mydata (id INTEGER PRIMARY KEY, setting_map REFERENCES setting(setting_as_numeric), performance REAL);
/* Populate the tables for the test */
INSERT OR IGNORE INTO setting VALUES ('on',3),('off',0),('standby',1),('idle',2);
INSERT INTO mydata (setting_map,performance) VALUES
(2,11.4),(0,2.5),(2,10.6),(3,17.1),(1,1.4);
/* As both the textual and numerical version of the setting as stored in the setting table that is referenced (joined)
the query is simple
*/
SELECT * FROM mydata JOIN setting ON mydata.setting_map = setting.setting_as_numeric;
DROP TABLE IF EXISTS setting;
DROP TABLE IF EXISTS mydata;
- Note that the
REFERENCES ....is not required (this is a FOREIGN KEY constraint), it does enforce referential integrity and allow automated maintenance of referential integrity (ON UPDATE CASCADE and ON DELETE CASCADE). The constraint (rule) prevents a row in the mydata table not referencing a setting (so therubbishrow could not exist unless there were a row in the setting table to allow such a row).
The results from this are:-
This alternative method also makes it easier, if ever needed, to add additional settings, without the need to rewrite the query(ies) to cater for the additional setting(s). Should the need arise to amend the setting description, it could just be changed in the setting table, again no need to amend the query(ies).
The following is the demonstration above extended to show new and updated settings being applied and that the query that extracts the data remains the same:-
/* cleanup test environment*/
DROP TABLE IF EXISTS setting;
DROP TABLE IF EXISTS mydata;
/* Create the related setting table */
CREATE TABLE IF NOT EXISTS setting (setting_as_text TEXT, setting_as_numeric INTEGER PRIMARY KEY);
/* Create the alternative mydata table that instead of storing the setting (text) value for every row
the value stored is a reference (map/association) to the setting value(s)
*/
CREATE TABLE IF NOT EXISTS mydata (id INTEGER PRIMARY KEY, setting_map REFERENCES setting(setting_as_numeric) /* may be benifical ----->*/ ON DELETE CASCADE ON UPDATE CASCADE, performance REAL);
/* Populate the tables for the test */
INSERT OR IGNORE INTO setting VALUES ('on',3),('off',0),('standby',1),('idle',2);
INSERT INTO mydata (setting_map,performance) VALUES
(2,11.4),(0,2.5),(2,10.6),(3,17.1),(1,1.4);
/* As both the textual and numerical version of the setting as stored in the setting table that is referenced (joined)
the query is simple
*/
SELECT * FROM mydata JOIN setting ON mydata.setting_map = setting.setting_as_numeric;
/*=======================================================================*/
/* Adding a new setting*/
INSERT INTO setting VALUES('blazing away', 100);
INSERT INTO mydata (setting_map,performance) VALUES (100,25.6);
SELECT * FROM mydata JOIN setting ON mydata.setting_map = setting.setting_as_numeric;
/* examples of updating setings*/
/* as setting_as_numeric is the referenced column then it being changed will be CASCADED as per the ON UPDATE action */
UPDATE setting SET setting_as_numeric = setting_as_numeric / 4 WHERE setting_as_numeric > 10;
/* the setting_as_text is not cascaded as the value used is always from the setting table*/
UPDATE setting SET setting_as_text = 'slow' WHERE setting_as_text = 'idle';
SELECT * FROM mydata JOIN setting ON mydata.setting_map = setting.setting_as_numeric;
DROP TABLE IF EXISTS setting;
DROP TABLE IF EXISTS mydata;
The two results (SELECTS being):-
and
One pitfall with this additional table, is that a table takes up at least 4k (by default) so storage will be increased (but probably not at the expense of runtime efficiency).
However, perhaps consider the potential advantage of a query such as:-
SELECT
*,
performance / (1 + setting_as_numeric) AS adjusted_performance /*<<<<<<<<<< ADDED*/
FROM mydata JOIN setting ON mydata.setting_map = setting.setting_as_numeric;
- Not intended to be useful in itself
This would produce (i.e. a new value derived from the existing values and no need for a CASE WHEN THEN ELSE END construct):-
Additional re the comment
I haven't had time to test it yet, but you have convinced my to go with the addtional settings table. Really appreciate such an answer, since databases are not one of my competences
Assuming that you are going to use the FOREIGN KEY constraints then one issue you may have is the need to ascertain the setting_as_number value for the setting text value when insert a row into the mydata table.
- Not an issue if you maintain hard coded equivalents e.g. ENUMs.
As an example say you had the performance data as 7.7777 and this was for an idle setting (now changed to slow if using the above) then if you know that slow(idle) is 2 fine. However, if you don't want to rely on knowing both you could think that you have to use slow(idle) to find out before inserting so have two invocations, one to get the number for slow(idle) then the actual insert.
This can be done in a single interaction by using CTE's (Common Table Expressions which are temporary tables that exist just during the interaction).
The following demonstrates (bar binding the values as you would/should do when using the SQLite API, of course you could build the SQL without binding by replacing the values slow or ouch (noting that ouch is just used to demonstrate an incorrect value) and 7.7777).
/* cleanup test environment*/
DROP TABLE IF EXISTS mydata;
DROP TABLE IF EXISTS setting;
/* Create the related setting table */
CREATE TABLE IF NOT EXISTS setting (setting_as_text TEXT, setting_as_numeric INTEGER PRIMARY KEY);
/* Create the alternative mydata table that instead of storing the setting (text) value for every row
the value stored is a reference (map/association) to the setting value(s)
*/
CREATE TABLE IF NOT EXISTS mydata (id INTEGER PRIMARY KEY, setting_map REFERENCES setting(setting_as_numeric) /* may be benifical ----->*/ ON DELETE CASCADE ON UPDATE CASCADE, performance REAL);
/* Populate the tables for the test */
INSERT OR IGNORE INTO setting VALUES ('on',3),('off',0),('standby',1),('idle',2);
INSERT INTO mydata (setting_map,performance) VALUES
(2,11.4),(0,2.5),(2,10.6),(3,17.1),(1,1.4);
/* As both the textual and numerical version of the setting as stored in the setting table that is referenced (joined)
the query is simple
*/
SELECT * FROM mydata JOIN setting ON mydata.setting_map = setting.setting_as_numeric;
/*=======================================================================*/
/* Adding a new setting*/
INSERT INTO setting VALUES('blazing away', 100);
INSERT INTO mydata (setting_map,performance) VALUES (100,25.6);
SELECT * FROM mydata JOIN setting ON mydata.setting_map = setting.setting_as_numeric;
/* examples of updating setings*/
/* as setting_as_numeric is the referenced column then it being changed will be CASCADED as per the ON UPDATE action */
UPDATE setting SET setting_as_numeric = setting_as_numeric / 4 WHERE setting_as_numeric > 10;
/* the setting_as_text is not cascaded as the value used is always from the setting table*/
UPDATE setting SET setting_as_text = 'slow' WHERE setting_as_text = 'idle';
SELECT * FROM mydata JOIN setting ON mydata.setting_map = setting.setting_as_numeric;
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
/* ADDITONAL */
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
/*Example Insert using setting text (assuming setting row has been added). Not actually inserting rather showing how it works*/
WITH
/* This CTE accepts the values (instead of actual values you would have ?,? for values that are bound, hardcoded for the demo)*/
cte_insert_data(setting_text_value, performance) AS (
SELECT 'slow',7.7777
),
/* This CTE uses the first CTE (the values passed) and adds the setting_as_numeric i.e. the primary key that is to be referenced*/
cte_with_setting_as_numeric(setting_text_value,performance,setting_as_numeric) AS (
SELECT setting_text_value, performance, (SELECT setting_as_numeric FROM setting WHERE setting.setting_as_text = cte_insert_data.setting_text_value) FROM cte_insert_data
)
SELECT * FROM cte_with_setting_as_numeric;
/* same as previous BUT with invalid (non-existing) setting text value i.e. ouch */
WITH
cte_insert_data(setting_text_value, performance) AS (
SELECT 'ouch',7.7777
),
cte_with_setting_as_numeric(setting_text_value,performance,setting_as_numeric) AS (
SELECT setting_text_value, performance, (SELECT setting_as_numeric FROM setting WHERE setting.setting_as_text = cte_insert_data.setting_text_value) FROM cte_insert_data
)
SELECT * FROM cte_with_setting_as_numeric;
/* Same as above EXECPT this actually INSERTs*/
WITH
cte_insert_data(setting_text_value, performance) AS (
SELECT 'slow',7.7777
),
cte_with_setting_as_numeric(setting_text_value,performance,setting_as_numeric) AS (
SELECT setting_text_value, performance, (SELECT setting_as_numeric FROM setting WHERE setting.setting_as_text = cte_insert_data.setting_text_value) FROM cte_insert_data
)
INSERT INTO mydata (setting_map,performance) SELECT setting_as_numeric,performance FROM cte_with_setting_as_numeric WHERE setting_as_numeric IS NOT null;
/* Same as the previous BUT due to ouch not being an existing setting text and thus the setting_as_numeric is null, the insert is skipped*/
WITH
cte_insert_data(setting_text_value, performance) AS (
SELECT 'ouch',7.7777
),
cte_with_setting_as_numeric(setting_text_value,performance,setting_as_numeric) AS (
SELECT setting_text_value, performance, (SELECT setting_as_numeric FROM setting WHERE setting.setting_as_text = cte_insert_data.setting_text_value) FROM cte_insert_data
)
INSERT INTO mydata (setting_map,performance) SELECT setting_as_numeric,performance FROM cte_with_setting_as_numeric WHERE setting_as_numeric IS NOT null;
/* Show the data */
SELECT * FROM mydata JOIN setting ON mydata.setting_map = setting.setting_as_numeric;
DROP TABLE IF EXISTS mydata;
DROP TABLE IF EXISTS setting;
- Note that the previous SQL is included, the new SQL starts at
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/ /* ADDITONAL */ /*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/ - Basically the same code is replicated 4 times (4
WITH ....)- The first show the data that is being generated for use by the INSERT
- one using
slow(idle), the other using the invalidouch
- one using
- The last two actually use an INSERT
- again valid for
slow(idle) (which inserts the row), the invalidouchis not inserted as it is excluded from the INSERT's SELECT by the WHERE clause.
- again valid for
- The first show the data that is being generated for use by the INSERT
The data generated (the 1st WITH/SQL) as per:-
- i.e. given the values
slowand7.7777the2has been obtained
The 2nd as per:-
- note that the setting_as_numeric values is null (i.e. there is no row in the setting table with the setting_as_text value of
ouch)
The 3rd and with don't produce output, however the log/message do get the number of rows that have been updated (which you should be able to obtain via the SQLite API) as per:-
/* Same as above EXECPT this actually INSERTs*/
WITH
cte_insert_data(setting_text_value, performance) AS (
SELECT 'slow',7.7777
),
cte_with_setting_as_numeric(setting_text_value,performance,setting_as_numeric) AS (
SELECT setting_text_value, performance, (SELECT setting_as_numeric FROM setting WHERE setting.setting_as_text = cte_insert_data.setting_text_value) FROM cte_insert_data
)
INSERT INTO mydata (setting_map,performance) SELECT setting_as_numeric,performance FROM cte_with_setting_as_numeric WHERE setting_as_numeric IS NOT null
> Affected rows: 1
> Time: 0.033s
and then (for ouch)
/* Same as the previous BUT due to ouch not being an existing setting text and thus the setting_as_numeric is null, the insert is skipped*/
WITH
cte_insert_data(setting_text_value, performance) AS (
SELECT 'ouch',7.7777
),
cte_with_setting_as_numeric(setting_text_value,performance,setting_as_numeric) AS (
SELECT setting_text_value, performance, (SELECT setting_as_numeric FROM setting WHERE setting.setting_as_text = cte_insert_data.setting_text_value) FROM cte_insert_data
)
INSERT INTO mydata (setting_map,performance) SELECT setting_as_numeric,performance FROM cte_with_setting_as_numeric WHERE setting_as_numeric IS NOT null
> Affected ro
ws: 0
Time: 0s
Finally the query at the end:-
- Check for unique characters in a string
- How to split strings in C++ like in python?
- Replacing a character if it is preceded by double quote and several digits
- TypeError: unsupported operand type(s) for +: 'SQL' and 'str'
- Formatting Phone number in Swift
- How to split Devanagari bi-tri and tetra conjunct consonants as a whole from a string?
- Conversion to int with Unicode strings
- How to use the contents of a text file(tab delimited format) to rename files in a folder?
- Calling PHP functions within HEREDOC strings
- Pad zero OPE ID
- Int to string conversion with leading 0s
- Remove specific characters inside parentheses using regex
- How to indent the contents of a multi-line string?
- Multiline C# interpolated string literal
- use regex in java with non printable chars
- How to return a vector of strings (or chars) when input data is an object with chars (not concatenated chars)
- Converting string to title case
- PHP: create string with values separated by comma BUT without comma at the end of string
- How to check whether a pattern contain multiple words present in a string in java
- python ConfigParser: read configuration from string
- How to replace case-insensitive literal substrings in Java
- fprintf with string argument
- Convert string to Enum in Python
- Java Pattern for Word without Spaces
- Weird problems with Zig Array List(s)
- Using str_replace so that it only acts on the first match?
- How can I convert a string to an integer in JavaScript?
- Partial match for regular expression in Java
- The function doesn't replace inputs with spaces
- Escape a string for a sed replace pattern