[receiver/sqlquery] Improve error handling for missing columns in result set

[Grandys]

Component(s)

internal/sqlquery, receiver/sqlquery

Describe the issue you're reporting

I have two suggestions how to improve error handling:

1. The value and body columns do not match the column names in the result set, handling is different for logs and metrics. Here is the problematic configuration:

  sqlquery:
...
      - sql: "select max(datapoint) as datapoint from logs_data group by log_type"
        metrics:
          - metric_name: logs.datapoint
            value_column: "datapoint_ne"
      - sql: "select 'Max datapoint value for ' || log_type || ' is ' || max(datapoint) as body from logs_data group by log_type"
        logs:
          - body_column: body_ne

When I run the Otel collector with this configuration, logs are generated (with an empty body), but metrics are not. Below is the collector's output:

{
  "kind": "receiver",
  "name": "sqlquery",
  "data_type": "metrics",
  "error": "row 0: rowToMetric: value_column 'datapoint_ne' not found in result set\nrow 1: rowToMetric: value_column 'datapoint_ne' not found in result set\nrow 2: rowToMetric: value_column 'datapoint_ne' not found in result set",
  "scraper": "sqlqueryreceiver/query-0: select max(datapoint) as datapoint from logs_data group by log_type"
}

Notably, there is no error entry for "data_type": "logs". Ideally, the handling of missing body columns for logs should be consistent with the handling of missing value columns for metrics. If we choose to fail on a missing body column, it would be a breaking change.

2. The attribute_columns do not match the column names in the result set, error is logged only for the first miss. Here is the faulty configuration::

  sqlquery:
...
    queries:
      - sql: "select 'Max datapoint value for ' || log_type || ' is ' || max(datapoint) as datapoint, log_type from logs_data group by log_type"
        logs:
          - body_column: datapoint
            attribute_columns: [ "log_type_ne_1", "log_type_ne_2" ]

When the collector starts, the following error is logged:

{
  "kind": "receiver",
  "name": "sqlquery",
  "data_type": "logs",
  "error": "rowToLog: attribute_column not found: 'log_type_ne_1'\nrowToLog: attribute_column not found: 'log_type_ne_1'\nrowToLog: attribute_column not found: 'log_type_ne_1'",
  "query": "query-0: select 'Max datapoint value for ' || log_type || ' is ' || max(datapoint) as datapoint, log_type from logs_data group by log_type"
}

Although both attribute columns are missing from the result set, the error is logged only for the first missing attribute column. Providing more detailed error messages would enhance the debugging experience.

[github-actions]

Pinging code owners:

• internal/sqlquery: @crobert-1 @dmitryax
• receiver/sqlquery: @dmitryax @crobert-1

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

[Grandys]

@crobert-1 @dmitryax
If you agree with my points, here is the PR: #35189

[crobert-1]

This seems like a good idea to me, removing needs triage.