Multiple DBs

[danield72]

I need to run different queries against different databases. The databases are all running on the same server (MS SQL Server).

I know I could explicitly include the DB name right in my raw SQL query (as a prefix to the table name that follows FROM), but that means I have to repeat it for every query. Not ideal if somebody else has the same databases, but they use different names--this is something the user can customize when installing the third-party product that creates the DBs.

So what I'm trying to do, to keep things clean, is to create 3 config files: One for the common items (sql_exporter.yml), one defining all the queries that will run against DB1 (db1.yml) and the other defining the queries that will run against DB2 (db2.yml). These queries aren't interchangeable, meaning, DB1 and DB2 don't have the same schema; for all intents and purposes, they're completely separate databases.

I thought I'd be able to define my sql_exporter.yml so it looks something like:

global:
[...]
collector_files:

• "db*.yml"

Then my DB1.yml should contain:
target:
name: "DB1"
data_source_name: 'sqlserver://user:pwd@servername:1433/?database=TheFirstDB'
collectors: [db1]

collector_name: db1
metrics:

• metric_name: name1
  [...]
  query:
  SELECT whatever...
• metrics_name: name2
  [...]
  query:
  SELECT whatever...

And finally, DB2.yml should be very similar to DB1.yml, except for data_source_name having /?database set to the second DB, collector_name: being set to db2, and then the individual metrics.

Couple of observations:

It looks like the primary .yml file has to have a target: defined, it can't wait until it's defined in db1.yml or db2.yml. So I tried to define multiple target: entries, each with its unique name:, data_source_name: and collectors: values. But I'm then told there can only be a single target:. That's when I tried to move each target: to the secondary config files (one per DB), but no matter what I try, I always end up getting errors suggesting I'm not allowed to define some things like target: in DB1.yml and/or DB2.yml, or if I skip "target:" altogether, it doesn't know what data_source_name is (makes sense), etc.

Surely this is feasible, and I tried everything I thought was intuitive...but I can't get this to work. And surely the solution is NOT to have two completely separate instances/copies of sql_exporter.exe each running in its own folder with a single target.

Could I see sample config files that demonstrate how to do this? Again, to summarize, the goal is very simple: I need to run different queries against different databases, and I want to avoid duplicating the database names in each individual query. If a DB name changes, then ideally I only want to change it in one location, in the data_source_name.

Thanks.

[danield72]

Edit:

I found some prior discussions that seem to suggest splitting targets by job, so I tried this in my primary config file, but sql_exporter crashes:

jobs:

• job_name: db1_job
  collectors: [db1]
  static_configs:
  • targets:
    name: 'target1name'
    data_source_name: 'sqlserver://blah/?database=TheFirstDatabase'
• job_name: db2_job
  collectors: [db2]
  static_configs:
  • targets:
    name: 'target2name'
    data_source_name: 'sqlserver://blah/?database=TheSecondDatabase'

The crash:

ts=2024-10-17T19:28:07.920Z caller=klog.go:118 level=warn func=Warningf msg="Starting SQL exporter (version=0.15.0, branch=HEAD, revision=cb883e06441c462b41295361d6b71623bc957188) (go=go1.21.12, platform=windows/amd64, user=root@c031fda141fa, date=20240708-20:31:14, tags=netgo)"
panic: runtime error: invalid memory address or nil pointer dereference
[signal 0xc0000005 code=0x0 addr=0x0 pc=0x24aa44a]

goroutine 1 [running]:
github.com/burningalchemist/sql_exporter.NewJob(0xc0006b11f0, 0x10?)
/app/job.go:36 +0x1ea
github.com/burningalchemist/sql_exporter.NewExporter({0x292d3a3?, 0x1b?})
/app/exporter.go:76 +0x3dd
main.main()
/app/cmd/sql_exporter/main.go:81 +0x4d9

[burningalchemist]

Hi @danield72, could you please post the configuration yaml (full if possible, but without user-sensitive data of course) in a code block as it's hard to inspect?

As per my current observation, the configuration snippet for jobs is incorrect - it's also what the panic reference points at.

[danield72]

As per my current observation, the configuration snippet for jobs is incorrect - it's also what the panic reference points at.

That's what I figured, and I did look at the code (job.go line 36) but I'm completely unfamiliar with Go and couldn't quite figure out from the loop what to try to structure differently to get it going.

Here's my sql_exporter.yml.

global:
  scrape_timeout: 10s
  scrape_timeout_offset: 500ms
  min_interval: 0s
  max_connections: 3
  max_idle_connections: 3
  max_connection_lifetime: 5m

jobs:
  - job_name: db1_job
    collectors: [db1]
    static_configs:
      - targets:
          name: 'target1name'
          data_source_name: 'sqlserver://myusername:mypwd@mysqlserver:1433/?database=db1'
            
  - job_name: db2_job
    collectors: [db2]
    static_configs:
      - target:
          name: 'target2name'
          data_source_name: 'sqlserver://myusername:mypwd@mysqlserver:1433/?database=db2'

collector_files: 
  - "*.collector.yml"

...and here's my db1.collector.yml:

collector_name: db1

metrics:
  - metric_name: db1Metrics
    type: counter
    help: 'DB1 Metrics'
    key_labels:
      - col1
      - col2
      - col3
    static_labels:
    values: [SampleValue]
    query: |
      SELECT col1, col2, col3, SampleValue, TimeSampled FROM MyTable -- undoubtedly the query itself is not relevant at this point

I also have a db2.collector.yml file which, for all intents and purposes, is so far identical except for db1 -> db2 and of course the query (which again shouldn't come into play yet)

Your feedback is very much appreciated.

Thanks

[danield72]

Anything I can do to help, just ask.

My first response contains the exact files I have. So really, the only thing that I changed based on your response was to eliminate my "name" values under "targets:" (that was me grasping at straws) and renaming data_source_name to targetXname. So structurally my file's still pretty much the name.

I'm new to YML also. Since there's a loop iterating over "targets:" (as best as I can read the code), shouldn't there be something surrounding the child entries (a list one in this case) that appears below it, like []? I had played around with that with no success.

I'm also assuming comments are properly ignored. I have them peppered liberally all over the place (but always on a new line on their own).

[burningalchemist]

Yeah, no worries at all, I have all the setups, and I've been developing this project for the last 5 years.

I'll analyse the config once again once I'm back home, and come up with some ideas.

Sorry for the inconvenience 😉

[danield72]

Take your time, I can still make some progress while trying to find a solution for this.

I've only recently been introduced to Grafana and Prometheus, and I've gotta say your SQL exporter unlocks so much potential it's mind-blowing. I look forward to using it in many, many scenarios. :-)

I am using it experimentally right now, it's just in this one case I have a need to gather data from unrelated databases. As mentioned in my first message, I could hardcode the DB name explicitly in each query, but repeating that all over the place is just a bad practice I'd like to avoid.

Thanks again. Now I'll let you focus on what you have to do. :-)

[danield72]

Oh. My. God. You will hate me.

I just realized I had a stray hyphen character in my sql_exporter.yml. Do you see it?

jobs:
  - job_name: db1_job
    collectors: [db1]
    static_configs:
      - targets:
          name: 'target1name'
          data_source_name: 'sqlserver://myusername:mypwd@mysqlserver:1433/?database=db1'
      -
            
  - job_name: db2_job
    collectors: [db2]
    static_configs:
      - target:
          name: 'target2name'
          data_source_name: 'sqlserver://myusername:mypwd@mysqlserver:1433/?database=db2'

I swear I missed that, and it just looked like a spec of dust my on my monitor.

With it gone, the crash is gone. Not only that, but the collection works beautifully.

I'm so, SO sorry for wasting your time on this. But, you did get me the correct syntax right from the get go. So I thank you for that.

I don't know what you're relying on for the YML parser (your own, or somebody else's?)...but I guess in the end this is an opportunity to look out for more crappy mistakes an end user can introduce. At least that's something you can try to anticipate and avoid an outright crash. So...win-win? I hope? :-)

So things work now. Thank you again a million times. Once I have developed something useful, I'll share what I'm using your exporter for. As I said, it unlocks some tremendous potential.

• Dan

[burningalchemist]

Hey Dan, no worries at all. 👍

This is one of the common inconveniences of the YAML format. Just like Python syntax (not to blame it ofc), user have to take care of indentation, and whitespaces, and all the control characters and stuff. In a large file it's sometimes easy to miss all these small bits.

I'm glad you sorted it out. 👏 On my end, I still have a task to improve config validation, so instead of crashing harshly it would point out a proper reason. 👍

[danield72]

Right on.

I knew that in Python, indentation, whitespaces and the like are relevant. I wasn't sure about YAML - now I know. I also fully realize that a good parser is hard to write.

I'm very much coming from the Windows world, where users (my eventual customers) aren't exactly used to hand-editing configuration files. Because the expectation will be there, I'll probably end up writing a small frontend UI that'll just take a few simple input values from the user and generate the config file(s) in code to avoid that potential mess. Of course the user would expect to be free to change anything or write everything from scratch, but that also means my UI will have to be able to take in whatever's there - no matter how bad - and try to make the most of it...and somehow not lose any of the user's hand-edited items, even if my UI can't do anything with them. That means I'll have to get myself familiar with YAML and maybe be on the lookout for an already-made parser. I'll cross that bridge when I get there.

In the meantime, I thank you again. Now, onto the exciting part. 👍