Generic SQL input by srebhan · Pull Request #8735 · influxdata/telegraf

srebhan · 2021-01-22T17:46:48Z

Required for all PRs:

Associated README.md updated.
Has appropriate unit tests.

This PR adds a generic SQL input plugin that can be used to query SQL servers and convert the returned data into metrics. In a first attempt only a handful of drivers are supported (see SQL_DRIVERS_INPUT.md) but there are more on the list.

Currently a test-suite is missing, therefore I'm marking this as DRAFT. Reviews, comments, suggestions and tests are welcome!

Takeover of PR #2785
Fixes #352

telegraf-tiger

Thanks so much for the pull request!
🤝 ✒️ Just a reminder that the CLA has not yet been signed, and we'll need it before merging. Please sign the CLA when you get a chance, then post a comment here saying !signed-cla

srebhan · 2021-01-26T09:06:53Z

!signed-cla

plugins/inputs/sql/README.md

ssoroka

A work of art. well done

plugins/inputs/sql/sql.go

srebhan · 2021-01-26T18:47:49Z

!signed-cla

telegraf-tiger

🤝 ✒️ Just a reminder that the CLA has not yet been signed, and we'll need it before merging. Please sign the CLA when you get a chance, then post a comment here saying !signed-cla

sspaink · 2021-01-26T21:20:48Z

!signed-cla

telegraf-tiger

🤝 ✒️ Just a reminder that the CLA has not yet been signed, and we'll need it before merging. Please sign the CLA when you get a chance, then post a comment here saying !signed-cla

srebhan · 2021-01-26T22:35:41Z

For running the tests, please create a testdata directory in the sql-plugin dir. Then download and unzip the MariaDB sample data and put the nation.sql file in the testdata directory. I skipped this as I cannot find out what the License of this dataset is.

If those preparations are successful, run go test -args -spinup in the sql-plugin dir. Keep fingers crossed! ;-P

srebhan · 2021-01-27T18:13:47Z

!signed-cla

telegraf-tiger

🤝 ✅ CLA has been signed. Thank you!

sspaink · 2021-02-17T23:57:11Z

!retry-checks

aslgithub · 2021-04-29T16:55:52Z

@srebhan Hi Sven, any chance you could get this pull completed so SQL queries can be run as a Telegraf input please ?
Got excited when you took this PR over in January that someone would be able to complete this functionality..
Thanks !

telegraf-tiger · 2021-05-11T13:37:30Z

Looks like new artifacts were built from this PR. Get them here!

Artifact URLs

srebhan · 2021-05-11T13:40:47Z

@aslgithub your wish is my command. ;-P
Testing this is very much appreciated! You can use the artifacts built by CI by expanding telegraf-tiger's comment. :-)

aslgithub · 2021-05-11T20:32:05Z

@srebhan nice one, thank you, much appreciated :-)
Will test this against Microsoft SQL Server.

aslgithub · 2021-05-19T13:00:06Z

@aslgithub your wish is my command. ;-P
Testing this is very much appreciated! You can use the artifacts built by CI by expanding telegraf-tiger's comment. :-)

@srebhan
Hi Sven,
I've started testing against Microsoft SQL.

It appears to work as expected in that I am able to connect to the SQL server and complete a basic query that does a select count from a table to return an integer.

Specifying a database in the dsn string within inputs.sql doesn't seem to have any impact?
However I believe that might be as intended given you can have multiple inputs.sql.query each targeting their own databases and tables on the same server connection ?

Would it be possible to support the tags feature at the query level please?
It works at the top level :
[inputs.sql.tags]
environment="dev"

however it doesn't work at the query level :
[inputs.sql.query.tags]
environment="dev"

This would be really helpful as I can have each query tagging its data as appropriate under the same connection.

Thanks,
Paul

srebhan · 2021-05-20T11:55:42Z

@aslgithub nice to hear it's at least not failing completely! :-)
TBH we do not filter the DSN in this PR. Can you please double check your DSN with the driver documentation.
Regarding the tags, I would like to add this in another PR (you are welcome to beat me at it :-)) once this is in. Complicating the code will make it only harder for the reviewers. Hope that's ok for you!?

aslgithub · 2021-06-01T22:05:03Z

@srebhan
Hi Sven,
Certainly not failing completely, this build has now been running for 12 days and has collected 227,249 data points from a Microsoft SQL Database using 2 inputs.sql.query sections.

Sorry, I'm going to admit doing a PR to add the tag would be beyond me appreciate your support in doing this.

Thanks,
Paul

… explicit type conversions for fields.

telegraf-tiger · 2021-06-13T19:45:27Z

Looks like new artifacts were built from this PR. Get them here!

Artifact URLs

docs/SQL_DRIVERS_INPUT.md

telegraf-tiger · 2021-06-14T10:27:14Z

Looks like new artifacts were built from this PR. Get them here!

Artifact URLs

srebhan · 2021-06-15T19:17:40Z

@aslgithub give master a test if you can... Thanks to @reimda this plugin will (hopefully) make it into 1.19

aslgithub · 2021-06-16T16:15:54Z

@srebhan I've downloaded the nightly build and it seems to be okay giving the same result as the previous artifact that I have been running on test. Thanks for your efforts on this.
@reimda Thank you so much for merging this :-)

(cherry picked from commit 908ad2f)

telegraf-tiger bot requested changes Jan 22, 2021

View reviewed changes

srebhan force-pushed the sql branch 4 times, most recently from 5e21e55 to e411f69 Compare January 22, 2021 19:23

ssoroka reviewed Jan 26, 2021

View reviewed changes

plugins/inputs/sql/README.md Outdated Show resolved Hide resolved

ssoroka reviewed Jan 26, 2021

View reviewed changes

plugins/inputs/sql/sql.go Outdated Show resolved Hide resolved

plugins/inputs/sql/sql.go Outdated Show resolved Hide resolved

telegraf-tiger bot requested changes Jan 26, 2021

View reviewed changes

srebhan mentioned this pull request Jan 27, 2021

add input odyssey plugin #8671

Closed

3 tasks

telegraf-tiger bot approved these changes Jan 27, 2021

View reviewed changes

helenosheaa added feat Improvement on an existing feature such as adding a new setting/mode to an existing plugin new plugin plugin/input 1. Request for new input plugins 2. Issues/PRs that are related to input plugins labels Feb 4, 2021

srebhan force-pushed the sql branch from e4a040a to 6d4b3f2 Compare May 11, 2021 11:33

srebhan marked this pull request as ready for review May 11, 2021 13:19

srebhan requested a review from ssoroka May 17, 2021 15:19

Sven Rebhan added 20 commits June 11, 2021 14:58

Register SQL input plugin.

32b1e1c

Add SQL plugin to list of input plugins in README.md.

83ecdbc

Conditional driver includes for windows and non-windows OS.

6e4db0c

Remove call to SetConnMaxIdleTime() as it requires go >= 1.15.

f9f8d9d

Exclude sqlite driver from darwin builds.

34f4b88

Reorganize plattform specific driver includes.

ae086d4

Make the build constraints sqlite specific and try to constrain further.

88bb317

Fix formatting.

c9b08b1

Add testcontainers dependency for SQL tests with docker containers.

9e137c9

Add SQL test for MySQL/MariaDB using Docker containers.

f3cb6cd

Fix plugin's usage of old internal.Duration.

39e0a65

Add testcontainers dependency for SQL tests with docker containers.

a8f09a6

Extend the internal date-parsing with more exotic types.

fd3f10f

Implement internal type-conversion methods.

325ae65

Rename *_col(s) to _column(s) in the config options and implement the…

41e6895

… explicit type conversions for fields.

Fix linter issues.

71f77e5

Cleanup modules after rebase.

78f991b

Fix timestamp parsing for raw byte-array format (and others).

f32b057

Bump postgres version v4 to use what others use.

932bf12

Use testdata to implement rudimentary testings.

873f59c

srebhan force-pushed the sql branch from 006bc7f to 873f59c Compare June 13, 2021 19:29

Hipska reviewed Jun 14, 2021

View reviewed changes

docs/SQL_DRIVERS_INPUT.md Outdated Show resolved Hide resolved

Improve driver readme as suggested by Hipska.

1c0e7c5

reimda merged commit 908ad2f into influxdata:master Jun 15, 2021

srebhan mentioned this pull request Jun 16, 2021

Genericsqlplugin #2785

Closed

3 tasks

reimda pushed a commit that referenced this pull request Jun 17, 2021

Generic SQL input (#8735)

91b6211

(cherry picked from commit 908ad2f)

Conversation

srebhan commented Jan 22, 2021 • edited by sjwang90 Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Required for all PRs:

Uh oh!

telegraf-tiger bot left a comment

Choose a reason for hiding this comment

Uh oh!

srebhan commented Jan 26, 2021 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Uh oh!

ssoroka left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

srebhan commented Jan 26, 2021

Uh oh!

telegraf-tiger bot left a comment

Choose a reason for hiding this comment

Uh oh!

sspaink commented Jan 26, 2021

Uh oh!

telegraf-tiger bot left a comment

Choose a reason for hiding this comment

Uh oh!

srebhan commented Jan 26, 2021

Uh oh!

srebhan commented Jan 27, 2021

Uh oh!

telegraf-tiger bot left a comment

Choose a reason for hiding this comment

Uh oh!

sspaink commented Feb 17, 2021

Uh oh!

aslgithub commented Apr 29, 2021

Uh oh!

telegraf-tiger bot commented May 11, 2021

Artifact URLs

Uh oh!

srebhan commented May 11, 2021

Uh oh!

aslgithub commented May 11, 2021

Uh oh!

aslgithub commented May 19, 2021

Uh oh!

srebhan commented May 20, 2021 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

aslgithub commented Jun 1, 2021

Uh oh!

telegraf-tiger bot commented Jun 13, 2021

Artifact URLs

Uh oh!

Uh oh!

telegraf-tiger bot commented Jun 14, 2021

Artifact URLs

Uh oh!

srebhan commented Jun 15, 2021

Uh oh!

aslgithub commented Jun 16, 2021

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

7 participants

srebhan commented Jan 22, 2021 •

edited by sjwang90

Loading

srebhan commented Jan 26, 2021 •

edited

Loading

srebhan commented May 20, 2021 •

edited

Loading