[receiver/sqlquery] Add support for logs

[andrzej-stencel]

Component(s)

receiver/sqlquery

Is your feature request related to a problem? Please describe.

At Sumo Logic, we have customers wanting to retrieve logs from SQL databases (MySQL, PostgreSQL, MS SQL Server, possibly others) into OpenTelemetry Collector. The SQL Query receiver can currently create metrics based on SQL queries. It seems natural to extend this feature to logs as well.

Describe the solution you'd like

Given the following example that creates metrics, taken from the SQL Query receiver's README:

receivers:
  sqlquery:
    driver: postgres
    datasource: "host=localhost port=5432 user=postgres password=s3cr3t sslmode=disable"
    queries:
      - sql: "select count(*) as count, genre from movie group by genre"
        metrics:
          - metric_name: movie.genres
            value_column: "count"
            attribute_columns: [ "genre" ]
            static_attributes:
              dbinstance: mydbinstance

the following configuration seems to make sense for logs as a starter:

receivers:
  sqlquery:
    driver: postgres
    datasource: "host=localhost port=5432 user=postgres password=s3cr3t sslmode=disable"
    queries:
      - sql: "select * from logs_table"
        logs:
          - # properties to be defined

The features that could be considered for logs are:

• defining the columns for body, severity, timestamp etc. (similar to value_column for metrics)
• creating structured body from the result set
• keeping track of the rows in the table that were already read/processed, e.g. by storing the highest row ID in persistent storage
• keeping consistency with metrics, e.g. with attribute_columns and static_attributes
• other? "let me know in the commens" 🙂

I'd be happy to work on the implementation if this feature gets accepted.

Describe alternatives you've considered

I don't see any alternative other than creating a custom binary that does this and sends the data to the collector via OTLP.

Additional context

Here's a past proposal to achieve similar goal by adding a new component:

• New component: receiver/mysqlrecordsreceiver #12082.

I believe adding the functionality in the SQL Query receiver is the right approach, as it already does the same thing for metrics. Also re-using its configuration and code for connecting to DBs, running queries etc. makes sense.

[github-actions]

Pinging code owners:

• receiver/sqlquery: @dmitryax @pmcollins

See Adding Labels via Comments if you do not have permissions to add labels yourself.

[atoulme]

creating structured body from the result set

Can you explain what the log record would look like? Would it be one record per row or one log record as the complete output of the query execution? Would it be represented as a JSON document or something else if so? Is that something you have thought about or are you asking for feedback and implementation ideas about it?

[andrzej-stencel]

I borrowed this idea from the previous mysqlrecordsreceiver proposal. The idea is to make each row of the result set into a separate log record, where the body of the log would be a map of column_name => row_value. Let me show an example (based on the one in that original issue):

Example SQL query and the result from the database:

> select * from Persons;
| PersonID | LastName | FirstName | Address   | City     |
| -------- | -------- | --------- | --------- | -------- |
| 3        | Kalita   | Raktim    | Hatigaon  | Guwahati |
| 4        | Person2  | Someone2  | Somewhere | Else     |
2 row(s) in set (0.01 sec)

For a receiver configured like this (subject to change of course):

receivers:
  sqlquery:
    driver: postgres
    datasource: "host=localhost port=5432 user=postgres password=s3cr3t sslmode=disable"
    queries:
    - sql: "select * from Persons"
      logs:
      - structured_body: true

I would expect the following logs:

Resource SchemaURL:
ScopeLogs #0
ScopeLogs SchemaURL:
InstrumentationScope
LogRecord #0
ObservedTimestamp: 2023-03-23 19:56:22.838756143 +0000 UTC
Timestamp: 2023-03-23 16:53:35.546008 +0000 UTC
SeverityText: 
SeverityNumber: Unspecified(0)
Body: Map({"Address":"Hatigaon","City":"Guwahati","FirstName":"Raktim","LastName":"Kalita","PersonID":"3"})
Trace ID:
Span ID:
Flags: 0
LogRecord #1
ObservedTimestamp: 2023-03-23 19:56:22.838760884 +0000 UTC
Timestamp: 2023-03-23 16:53:35.546012 +0000 UTC
SeverityText: 
SeverityNumber: Unspecified(0)
Body: Map({"Address":"Somewhere","City":"Else","FirstName":"Someone2","LastName":"Person2","PersonID":"4"})
Trace ID:
Span ID:
Flags: 0

Does it make sense to you @atoulme?

Perhaps @JKashyap96 (as the author of the original proposal) could help with the rationale for this - is this what customers/end users have actually requested?

[JKashyap96]

This was intended as to when we use the sumologic exporter, and define the log_format: otlp in the collector config, we a single json record or message ingested into Sumo Logic.

@astencel-sumo We get a json log message for each record which sort of makes sense.

[atoulme]

Thanks, this is helpful! How do you preserve the order in the result set or show that the records come from the same result set? Are you using the order in scope logs and placing all results in the same resource logs? Does that get lost with batching?

[JKashyap96]

The current feature scope only reads the data for the set of query record sets and stores it in a map with the key(index) value as a unique combination of queryNo and recordNo. All the data read during a collector run are converted into logs from a single result set. This index value could be used to separate the records and then also logs if required.
Currently the receiver converts each record from a query run into a log record, if I understand your question about the scope. So, if Q1 results in 100 records coming from db, Q2 another 100, then the receiver would create 200 log records for the pipeline ahead.
Batching is something which is yet to be implemented/tested.
cc @astencel-sumo

[atoulme]

I meant batching as using the batch processor to batch records, and therefore lose ordering or the grouping of rows in a resource log.

[JKashyap96]

Yes, the batch processor for this receiver needs to be tested. And then how to maintain grouping of rows needs to be decided. @astencel-sumo We should keep this in mind during development/testing of this part of the receiver.

[github-actions]

This issue has been inactive for 60 days. It will be closed in 60 days if there is no activity. To ping code owners by adding a component label, see Adding Labels via Comments, or if you are unsure of which component this issue relates to, please ping @open-telemetry/collector-contrib-triagers. If this issue is still relevant, please ping the code owners or leave a comment explaining why it is still relevant. Otherwise, please close it.

Pinging code owners:

• receiver/sqlquery: @dmitryax @pmcollins

See Adding Labels via Comments if you do not have permissions to add labels yourself.