Generic DB Query Plugin (DBI plugin)

[leonty]

Is it possible like in collectd to perform an arbitrary sql query that forms a metrics?

[sparrc]

are you talking about the collectd DBI plugin?

There is nothing like that in Telegraf, but I can make it a feature request

[leonty]

Exactly. Actually I thought it was done already when read the plugin list...

[JulienChampseix]

Comment from #448 :

An usefull feature will be to have a mysql_query plugin which will be able to run an provided mysql query (returning 1 field only) against one mysql database for store result in influxdb.

See bellow an idea of the config of this plugin which could be present in telegraf.conf:

[mysql_query]
query=["mysqlhost;mysqluser;mysqlpwd;mysqlquery;measurementinfluxdb"]
query=["mysqlhost;mysqluser;mysqlpwd;mysqlquery;measurementinfluxdb"]

Requirement for this plugin :

- have a mysql access to the database
- the query must return only 1 field for be able to post return to influxdb

The goal :

- Able to get data metrics from mysql engine to be able to inject same on influxdb
- Able to set several query "subsection" inside this plugin
- Able to inject those datas into an defined measurement per query (using the database/user/pwd influxdb defined in the main telegraf section)
- Able to provide an mysql query having simple or double quote inside the query

[bbczeuz]

Hi,

as proposed in #831, I started working on a universal SQL database query plugin. Is there currently anybody else working on such a thing, so we could combine efforts?

• I use it to periodically get data from an Oracle DB via golang's database/sql (Driver go-oci8, but code is universal and should support any database/sql compliant DB)
• I need to dynamically add tags based on selected columns.
• Should be able to handle >10k series (every 1h or 24h)

Current config options:

[[input.sqlquery]]
  ## Measurement name
  name = "sqlquery" # required. Name of the output metric

  ## DB Driver
  driver = "oci8" # required. Options: oci8 (Oracle), postgresql, sqlite3, mysql

  ## Server URL
  server_url = "user/pass@localhost:port/sid" # required

  ## Queries to perform
  queries  = ["SELECT * FROM tablename"] # required
  tag_cols = ["location"] # use these columns as tag keys (cells -> tag values)

[menardorama]

Hi

That's a very good idea but how do you manage the third party driver integration ?

I know that Postgresql have a pure go drivers and Oracle not.

For each database driver you have specific parameters ?

[bbczeuz]

Yeah. The oracle driver is kind of ugly. Really ugly. It starts with "yum localinstall oracle-instantclient..." which is kind of semi-nice (no yum repo, but atleast this allows uninstallation)
It installs everything in its own directory structure neglecting any Posix standards. Maybe this was intentionally, but it was annoying and I had to manually fix library and binary pathes. sqlplus doesn't support readline (No backspace, nor arrow keys. Hey, that's 21st century!). But people still use their software (and I have to interface to it...) Finally it worked.

So:

• I don't think there's an easy and automatic installation of the oracle driver yet
• At least mattn's go-oci8 checks whether the Oracle OCI driver is installed
• Mariadb, Postgresql, sqlite3 should work out of the box

I use golang database/sql's Open(...) call with the driver's name and parameters, so it's up to the user to choose the correct parameters for his DB (=there's no parsing; 1:1 forwarding to the driver).

• Current alpha code is in my zseng_dev_sqlquery

TODO:

• Drop 'name' parameter, as telegraf already supports this.
• Instead of converting everything to strings, allow the user (or DB?) to define datatypes per column
• Use more efficient algorithms? In my application, I grab 70k columns at once. At least, it works.
• Maybe, move the DB object out to the global struct. Might be more efficient, but mysql nor postgresql plugins do that.

[JulienChampseix]

@sparrc is there some progress about a plugin for mysql db query ?

[sparrc]

@JulienChampseix no, I would update the case if I was working on it. But I wouldn't expect to see it anytime soon, this is a large & difficult feature to implement.

[jeromegit]

I would love to see this feature come to life too...

[ScubaDrew]

This would be really useful. Are there any 'generic' plugins or ways to have telegraf log a custom metric? I could execute a DB query in a script and then provide the value as a parameter or write it to a file?

Thanks,
Drew

[ScubaDrew]

I think the answer to my own question is: https://github.com/influxdata/telegraf/tree/master/plugins/inputs/exec

[sparrc]

@ScubaDrew exec works, there is also a tail plugin if you prefer writing it to a file

[JulienChampseix]

@sparrc a dedicated query inputs for postgresql is out (inputs.postgresql_extensible.query) maybe easier to do the same for MySQL ? (instead of using exec inputs as workaround with mysql command)

[menardorama]

@JulienChampseix : It shouldn't be so hard to do it. Is this is really needed ?

If yes and if nobody is working on it I can try to port what I did on postgres ?

[JulienChampseix]

@menardorama That will be great! By this way we'll have inputs for postgres & mysql based on the same inputs "model".

[menardorama]

@sparc : What if I just evolve the postgresql_extensible to a generic plugin that include those DB drivers : https://github.com/golang/go/wiki/SQLDrivers and having a parameter do define the sql driver name ?

I just wonder if I can put the plugin more than once in the config file to have one instance of the plugin for postgresql and another one for mysql ? Just in case we have several database instances

[sparrc]

yes, you can have multiple instances of the same type of plugin

[menardorama]

@sparc : so transforming postgresql_extensible to a generic_db_query implementing for example :

• postgresql
• mysql
• sqllite

I'll avoid Oracle driver mostly because of oracle licensing, I don't see an easy way for now.

Well at least I'll try but it shouldn't be so difficult hopefully

[bbczeuz]

@menardorama : Are there any options to avoid licensing issues and still be able to include support for Oracle? Some months ago I implemented a generic sqlquery plugin that is based on go's "database/sql". That project needed to interface with an OracleDB cluster. I can imagine that there are other users that put their data to Oracle DBs ;) There is no source dependency from Oracle, but to build the plugin (more precisely: "github.com/mattn/go-oci8"), the Oracle instant client SDK needs to be installed.
Using this code, the final telegraf binary will then link to libclntshcore.so which is part of Oracle's instant client and therefore bound to Oracle's Distribution License](http://www.oracle.com/technetwork/licenses/distribution-license-152002.html) which I (IANAL) think is too restrictive for inclusion into telegraf.
Alternative: ODBC?

[menardorama]

To be honnest, I would be glad to add it but I'm not working for influxdata.

But if I can make the plugin work with other Dbs like mysql; adding oracle would be quite easy.

[JulienChampseix]

thanks @menardorama for your support on MySQL (and also Postgres)

[menardorama]

Just a work in progress : #2286

[sparrc]

@JulienChampseix please stop pinging us about this, it's not helping

[JulienChampseix]

thanks @sparrc for your update on this feature. Just trying to know what's the status and what's blocking on the PR (because we dont have any useful comment or update). Maybe you could understand this point. Anyway we'll be patient.

[danielnelson]

Another pull request to keep an eye on is #2785

[menardorama]

Hi,

I have a question to the maintainers of this project.

Having a Generic SQL Plugin is for me too difficult to code as there is many corner cases depending on the database you want to include.

As a first approach, is a mysql_extensible plugin is a valid option for approval ?

It would be just a copy from the postgresql_extensible, even if it would have code duplication it would unblock lots of people.

[danielnelson]

@menardorama I would prefer having a single plugin that can handle it for all basic databases, if there are reasons why we need a specialized plugin then we can consider them, but I want the bulk of the code to be shared. This is primarily for maintenance and ease of use.

In the meantime, I encourage you to use a custom build with one of the exiting pull requests or your own custom code. If you have a mysql_extensible you can open a pull request and others can use it as well.

[conet]

It's 2020 seems like this plugin not going to happen, am I wrong?

[hershdhillon]

Telegraf can become really viable if it was also able to query a database or capture changes and send it to other sources such as kafka. That would have cut out so many unnecessary middle men from my data pipeline. Please pick this up Influx!