…and will be removed in Home Assistant 2022.6; Your existing configuration has been imported into the UI automatically and can be safely removed from your configuration.yaml file
Dear Community,
I have several Questions about the SQL-Integration, and the (will-get-depreciated) configuration.yaml:
What i’m currently working on: For each Energy Measure Device, Query the last Value, 24h and 96h ago.
Like in the Logs said - it seems each Query from configuration.yaml gets “copied” to the SQL-Integration as well.
While the Queries stay in the yaml, they aren’t active, but the ones in the UI-Integration. Correct?
I played a bit around with the values (eg. solve failures) in configuration.yaml. It seems, everytime i change a value in one Query (in the Yaml), the Entities don’t get updated, but (in the SQL-Integration) another entity with a upcounting suffix gets created.
How can i just update the existing Query, am i forced to use the UI? (Why this wouldn’t be useful see below). Btw: This can be really harmful! I had a wrong query, leading to high CPU use. Altered the Values in the configuration.yaml to solve it - but the “wrong” one still existed and still run. Multiply that with Devices x Attempts to fix it. Happy i had a look at the Logs early enough.
I set up 36 Queries in configuration.yaml, with scan_interval: 900 not to overload it.
How can i access the Interval in the UI-Integration, for the, from the config cloned, Query?
It looks the Queries get processed one by one. Is there a way to control the order?
If i have a look at the Entities, some of their Values (Interval: 900) are 10 Secs old, some (literally) 10 Minutes. To compare the Values, at least per Device they need to be as close as possible.
How can i edit all my SQL-Queries from the “new” SQL-Integration in one Place / one File? While i generally don’t hate beeing things done in the UI - I have 12 x 3 Queries. I want to alter one Value, and roll that out to the corresponding other 11. Its best and fastest done in Notepad with search and replace. Or - create a Template, play around with it, and after all Copy&Paste that.
One will definitly loose overview over all Queries in the UI:
I’m suffering from the same. I try to keep my configuration (and myself) sane by organizing as much as possible into packages. That means that everything that belongs to a particular device (e.g., a weather station) is contained in a single place where it is easy to maintain and to maintain consistency since its is all there in that file.
Using the UI to set up SQL sensors is nice if you have just a couple of them. If you have more, and complex, queries it quickly becomes painful. For example, if I change my database password I have to change ALL the queries… Tedious and prone to errors.
So please, reinstate the YAML facility for SQL sensors. Thanks.
Yes, I just realized this as well. It is a lot harder to edit the sensors now. I also did the mistake of editing in YAML which just created new sensors. And now I am unable to delete these extra sensors.
Please revert to YAML editing of SQL sensors, SQL sensors are advanced anyway.
Also, does anyone know how to delete a SQL sensor?
OK, this change from yaml file to integration is a biiiig mess, if you (me) have a lot of sql queries… horror movie… i’d like to have yaml back, too.
But, to my question: am i just me or is it impossible to edit sql query? I have recorder on MariaDB on Synology and if i create new sql it’s ok, but if i want to chang existing one i get an error:
User input malformed: template value is None for dictionary value @ data[‘value_template’]
So only option is to delete old one and create a new one.
Is there a different way?
@cp1983 on the integrations page, select the query, then the 3 dots, there you can delete
@Protoncek i’m able to edit the sql query in UI, with HAOS running on a Proxmox VM
but i’m with you. Just dabbling into HA and need 30 odd sensors which are impossible to create as templates or history stats. Doing this in the UI will be nearly impossible to manage vs yaml … wonder what the rational is to remove this from yaml?
my scan_interval settings were not preserved (not possible to add / edit / view afterwards). Causing my old expensive SQL statement that was running once per hour to run more than once per minute and overloading the system. How do you change the scan_interval with the new GUI format? This is a breaking change, but was not marked as one back then (I think).
Did i missed something or has this been re-introduced? I’ve read a bit about sql sensor on HA pages after a while and noticed this sentence in officical page for SQL integration:
This integration can be configured using both config flow and by YAML.
So i decided to try and i inserted my SQL’s into sql.yaml file and added “sql: !include sql.yaml” into configuration.yaml. It works, HA doesn’t “import” my sql’s and there’s no complains from HA’s side…
Ok, only downside is that i have to restart HA after each sql change, while this is not necesarry if i insert each sql manually…
Just found this thread. Updated HA to find my sql sensor wasn’t working anymore. No mention of it in the log. I had to add it manually through the UI. yaml based sensors are deprecated?
