Comments (9)
Hey @hhromic, yeah, it makes total sense. 👍
Currently, I have several thoughts in mind:
- to make environment variables automatically generated from the yaml config (similar to how they do it in Grafana for example, and having the same format you've shared above)
- probably limit to
global
for the time being and maybe totarget
as maximum - avoid introducing fat dependencies, minimal and small are good, though.
Also, the password from file might be a neat feature, let's give it a try. 👍 The problem we have is that some drivers (e.g. libpq for PostgreSQL) have builtin environment variables that override connection parameters on a driver level (like PGUSER
, PGPASSWORD
, etc), while the others don't. It'd be great to have it consistently implemented on a driver side, but probably won't happen. 😞
But it was one of the reasons I decided to migrate to dburl
package to achieve some consistency at least there. In this case, we can parse the DSN and apply/modify username, password, etc via envs.
from sql_exporter.
Hey @hhromic, thanks for such a descriptive proposal! 👍
While the env variable system of Grafana is quite fancy and powerful, I think something like that for the SQL exporter would be overkill. Grafana has a way bigger set of configurations and therefore understandable why they went that way.
Yeah, agreed. My thought was that once we introduce such a way to generate environment variables, we don't need to think about that anymore. I also agree that the number of potential parameters in sql_exporter is way smaller, and it's finite. I would even say, we want to be small and finite.
I'm planning for the implementation to use standard Go only, no libraries for now. 👍
Yeah, in the case we go simple I also think there is nothing to worry about.
Overall, I believe we're on the same page and your design looks great. 👍🚀
from sql_exporter.
Hi folks!
May be our solution helps some one.
A week ago we with sql-exporter. It a great tool. But it can't configure with enviranment variables. ((( In our case we use jobs to aggregate access to several data bases (near 100 db)
Soluton
We add all env variables which we need in configs
Then when pods run first we start initial container with tool envsubst
which replace all env variables by value
That's all
When we look at the config in UI we saw
from sql_exporter.
Hey @abogdanov37, thanks for your suggestion! 👍 Happy to hear you're using
sql_exporter
against ~100 db. 😮The goal here is to make such a configuration supported natively, so you don't even need to run
envsubst
. Until then, though, we have populate the config aside.
Hi! Yep. I understand the idea in that issue. We also wait implementation. I share our solution may be someone need use env just now.
And simple comment in discussion I don't see env variables support for jobs. It's critical for us.
from sql_exporter.
Support for separate target DB config variables is not strictly necessary and can be left with the current SQLEXPORTER_TARGET_DSN
of course. That would still greatly simplify our boilerplate entrypoint script regardless.
Anyway let me know if you would still be interested in that part, especially for the "password from file" feature.
from sql_exporter.
Hi @burningalchemist !
I got a bit of time now to design/plan the implementation of this feature.
Apologies in advance for the long post! Take your time to read it and to think about it.
Let me know if you are okay with the proposal below and if you have further input/feedback before implementation.
First some comments to your thoughts mentioned above:
- to make environment variables automatically generated from the yaml config (similar to how they do it in Grafana for example, and having the same format you've shared above)
I'm very familiar with Grafana's approach to this as we use Grafana fully with env variables :)
Grafana was indeed the inspiration for the env variable formatting I proposed.
While the env variable system of Grafana is quite fancy and powerful, I think something like that for the SQL exporter would be overkill. Grafana has a way bigger set of configurations and therefore understandable why they went that way.
For the SQL exporter I would go with a simple static set of recognised env variables.
- probably limit to
global
for the time being and maybe totarget
as maximum
As shown in the initial issue, yes I was thinking on global
, target
and collector_files
to be configurable (see below).
- avoid introducing fat dependencies, minimal and small are good, though.
I'm planning for the implementation to use standard Go only, no libraries for now. 👍
The problem we have is that some drivers (e.g. libpq for PostgreSQL) have builtin environment variables that override connection parameters on a driver level (like
PGUSER
,PGPASSWORD
, etc), while the others don't. It'd be great to have it consistently implemented on a driver side, but probably won't happen. 😞
I don't think we should worry about that. The scope of the SQL exporter env variables should be only to prepare the DSN to send to dburl.Parse()
. After that, any other variables that each driver supports independently are responsibility of the drivers themselves and the end-user is free to mix'n'match if they know what they are doing.
I think the most important thing is to document the behaviour clearly so users know exactly how it works.
And yes, I also agree drivers will never be consistent. It hasn't been consistent for decades 😅.
But it was one of the reasons I decided to migrate to
dburl
package to achieve some consistency at least there. In this case, we can parse the DSN and apply/modify username, password, etc via envs.
Parsing the DSN and then overriding components looks a bit tricky to implement from what I have seen.
I propose to instead optionally build a DSN on the fly from separate env variables (see below).
Proposed Set of Recognised Variables
I propose to recognise the following env variables for the SQL exporter itself.
All variables are optional, but using some variables override others. See below.
Some variables populate array configs. For these, I propose to separate elements using commas (,
).
This, of course, limits each element to not be able to use commas in them, but I think is a reasonable separator character to use for typical configurations done using environment variables.
- Variables for the
global
top-level configurationSQLEXPORTER_GLOBAL_MIN_INTERVAL
SQLEXPORTER_GLOBAL_MAX_CONNECTION_LIFETIME
SQLEXPORTER_GLOBAL_MAX_CONNECTIONS
SQLEXPORTER_GLOBAL_MAX_IDLE_CONNECTIONS
SQLEXPORTER_GLOBAL_SCRAPE_TIMEOUT
SQLEXPORTER_GLOBAL_SCRAPE_TIMEOUT_OFFSET
- Variables for the
target
top-level configurationSQLEXPORTER_TARGET_DSN
(see below)SQLEXPORTER_TARGET_COLLECTORS
(comma-separated elements)
- Variables for the
collector_files
top-level configurationSQLEXPORTER_COLLECTOR_FILES
(comma-separated elements)
Due to complexity, no support for the collectors
and jobs
top-level configs is proposed at this time.
I don't think support for these is really necessary as more complex configurations should not use env variables anyway.
Proposed Target DSN Variables
The user can provide SQLEXPORTER_TARGET_DSN
(as currently is possible), but alternatively can provide the components of the DSN in separate env variables to build a DSN on the fly instead. This allows for (1) more readable configurations and (2) the ability to read password secrets from files if required.
The proposed variables follow the URL format for dburl
:
SQLEXPORTER_TARGET_DSN_OPAQUE
(optional, boolean to indicatescheme://
orscheme:
-opaque-, defaultfalse
)SQLEXPORTER_TARGET_DSN_PROTOCOL
(required, also known as "driver" or "scheme")SQLEXPORTER_TARGET_DSN_TRANSPORT
(optional, default to empty)SQLEXPORTER_TARGET_DSN_USER
(optional, default to empty)SQLEXPORTER_TARGET_DSN_PASSWORD
(optional and only used if the above is provided)SQLEXPORTER_TARGET_DSN_PASSWORD_FILE
(optional and overrides the above if provided)SQLEXPORTER_TARGET_DSN_HOST
(required, can optionally contain a port number)SQLEXPORTER_TARGET_DSN_DBNAME
(optional)SQLEXPORTER_TARGET_DSN_OPTIONS
(optional comma-separatedk=v
elements for the DB driver, default to empty)
If none of the "required" variables above is provided, then SQLEXPORTER_TARGET_DSN
is used as before.
Full Example
With a single DSN variable:
SQLEXPORTER_GLOBAL_MIN_INTERVAL=0s
SQLEXPORTER_GLOBAL_MAX_CONNECTION_LIFETIME=0s
SQLEXPORTER_GLOBAL_MAX_CONNECTIONS=3
SQLEXPORTER_GLOBAL_MAX_IDLE_CONNECTIONS=3
SQLEXPORTER_GLOBAL_SCRAPE_TIMEOUT=10s
SQLEXPORTER_GLOBAL_SCRAPE_TIMEOUT_OFFSET=500ms
SQLEXPORTER_TARGET_DSN=sqlserver://prom_user:[email protected]:1433
SQLEXPORTER_TARGET_COLLECTORS=prices_*,users
SQLEXPORTER_COLLECTOR_FILES=/path/to/colectors/*.collector.yaml
With separate DSN variables:
SQLEXPORTER_GLOBAL_MIN_INTERVAL=0s
SQLEXPORTER_GLOBAL_MAX_CONNECTION_LIFETIME=0s
SQLEXPORTER_GLOBAL_MAX_CONNECTIONS=3
SQLEXPORTER_GLOBAL_MAX_IDLE_CONNECTIONS=3
SQLEXPORTER_GLOBAL_SCRAPE_TIMEOUT=10s
SQLEXPORTER_GLOBAL_SCRAPE_TIMEOUT_OFFSET=500ms
SQLEXPORTER_TARGET_DSN_PROTOCOL=sqlserver
SQLEXPORTER_TARGET_DSN_USER=prom_user
SQLEXPORTER_TARGET_DSN_PASSWORD_FILE=/run/secrets/db-password
SQLEXPORTER_TARGET_DSN_HOST=dbserver1.example.com:1433
SQLEXPORTER_TARGET_COLLECTORS='prices_*,users'
SQLEXPORTER_COLLECTOR_FILES='/path/to/colectors/*.collector.yaml,/more-collectors/*.collector.yaml'
Another example with separate DSN variables:
SQLEXPORTER_TARGET_DSN_PROTOCOL=sqlserver
SQLEXPORTER_TARGET_DSN_USER=prom_user
SQLEXPORTER_TARGET_DSN_PASSWORD_FILE=/run/secrets/db-password
SQLEXPORTER_TARGET_DSN_HOST=dbserver1.example.com:1433
SQLEXPORTER_TARGET_DSN_DBNAME=mydb
SQLEXPORTER_TARGET_DSN_OPTIONS='encrypt=true,keepAlive=15'
from sql_exporter.
Hey @burningalchemist !
I've not forgotten about this feature request and my promise to implement it. I just got a bit stuck with other things!
As soon as I get the time, I'll dive into this. 👍
from sql_exporter.
Hey @hhromic, sure thing, no worries. 🙂👍
from sql_exporter.
Hey @abogdanov37, thanks for your suggestion! 👍 Happy to hear you're using sql_exporter
against ~100 db. 😮
The goal here is to make such a configuration supported natively, so you don't even need to run envsubst
. Until then, though, we have populate the config aside.
from sql_exporter.
Related Issues (20)
- `up` metric to show that database connection was successful HOT 5
- parameter to set log file location HOT 8
- Add custom metrics to expose MSSQL server hostname HOT 1
- can we have the Postgres collector yml file example
- Is it possible to distinguish between the sql exporter process status and the target database? HOT 3
- Scrape each job separately HOT 2
- [helm] Support getting data-source-name from an existing secret HOT 12
- Ignore no rows returned HOT 6
- Better handling of NULL return values in certain cases HOT 6
- Environment variable substitution for DSN's HOT 3
- Cache Mechanism for sql exporter HOT 2
- clickhouse `context deadline exceeded` HOT 1
- Helm chart improvement: additionalContainers HOT 5
- Can it support the collection of cloud databases HOT 1
- unable to use -web.listen.address flag HOT 4
- SIGHUP config reload HOT 4
- Clickhouse requests dosn't work after upgrade clickhouse driver to v2 HOT 6
- Scrape errors not clearing without restart HOT 3
- Config interpolation (secrets) HOT 6
- Can't use UNIX socket for postgres connection HOT 5
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from sql_exporter.