Add "key/value" mode for SQL module

[jsoriano]

SQL generic module currently collects one event per row, being each field the title of the column, and the value the value in the row for this column.

So for a result like this one:

a	b
0	42

An event containing these fields is generated:

...
"a": 0,
"b": 42,
...

This is a problem for queries that return the values like this:

variable	value
a	0
b	42

Because they would generate in this case two events containing fields like these ones:

...
"variable": a
"value": 0
...

Enhancement

Add a mode that can be selected with a configuration option and can be used so for the previous example an only event is generated containing fields like this one:

...
"a": 0,
"b": 42,
...

Use case

Some queries, specially some used for monitoring or configuration, use this "key/value" format in responses, for example MySQLs SHOW STATUS or SHOW VARIABLES.

This feature would allow to have a configuration like the following one, that would generate a single event mapping each variable to a field:

- module: sql
  metricsets: [query]
  hosts: ...
  driver: mysql
  sql_response_format: variables
  sql_query: "SHOW STATUS LIKE 'Key%'"

Would generate a single event per fetch with these fields:

"Key_blocks_used": 14955, 
"Key_read_requests": 96854827,
"Key_reads": 162040,
"Key_write_requests": 7589728,
"Key_writes": 3813196,

The possible values for sql_response_format would be:

• sql_response_format: table for current behaviour (default).
• sql_response_format: variables for the "key/value" case.

[rvillablanca]

I would like to help in this and doing a little investigation in the code I thing I should make the changes around here

beats/x-pack/metricbeat/module/sql/query/query.go

Line 65 in 0dab517

func (m *MetricSet) Fetch(report mb.ReporterV2) error {

am I right ? 🤔

[jsoriano]

@rvillablanca yes, changes should be done around this Fetch method, we will need two ways of doing the conversion from rows to events, the current one, and another one for responses with variables in "key/value" format.

I am going to assign this issue to you by now. Ping me if you have a PR to review or any other question.

Thanks!

[rvillablanca]

The only thing I need is to know how I can check the events, do I need kibana or only query elasticsearch ? I have x-pack/metricbeat with sql module enabled and configured but not sure how I can see the current events reported @jsoriano

[jsoriano]

@rvillablanca you can also configure your metricbeat with the console output, so it writes the events to the standard output.

It'd be also great if you could add a test here with the new setting: https://github.com/elastic/beats/blob/0dab5171f22a5d5e7a2336f8573055b9a889036e/x-pack/metricbeat/module/sql/query/query_integration_test.go

These tests can be run with go test -tags integration ./x-pack/metricbeat/module/sql/query/

[rvillablanca]

I'm done with this @jsoriano 👍