vishalmore90 opened a new issue, #41431:
URL: https://github.com/apache/beam/issues/41431
### What happened?
In `apache_beam.transforms.enrichment_handlers.cloudsql`
(`sdks/python/apache_beam/transforms/enrichment_handlers/cloudsql.py`, lines
350–377), `CloudSQLEnrichmentHandler._process_batch_request` contains multiple
critical flaws in batch response correlation:
#### 1. Silent Data Loss on Unmatched / Missing Database Rows
In single-request mode (`_process_single_request`, lines 337–349), when an
input record has no matching row in the SQL database, `result.first()` returns
`None`, yielding `(request, beam.Row())`. `Enrichment`'s `join_fn` (e.g.
`cross_join`) then preserves the original request in the output `PCollection`.
However, in batch mode (`_process_batch_request`, lines 350–377):
```python
result: Union[list[dict[str, Any]], dict[str, Any]] =
self._execute_query(
raw_query, params=param_dict, is_batch=True)
for response in result:
response_row = beam.Row(**response) # type: ignore[arg-type]
response_key = self.create_row_key(response_row)
if response_key in requests_map:
responses.append((requests_map[response_key], response_row))
return responses
```
The handler only iterates over rows returned by the SQL query. Unmatched
requests in `requests_map` are never tracked or yielded. As a result, any input
record whose join key does not exist in the database is **silently dropped**
from the pipeline output without warning or error.
#### 2. Silent Data Loss on Duplicate Join Keys in the Same Batch
In `_process_batch_request` (lines 361–364):
```python
for req in requests:
current_values = self._extract_values_from_request(req)
values.extend(current_values)
requests_map[self.create_row_key(req)] = req
```
`requests_map` is a standard `dict` mapping each key to a single `req`. When
a batch contains multiple records sharing the same join key (common in
event-stream processing, e.g., multiple clicks from `user_id=101`), each
subsequent record overwrites the earlier one in `requests_map`. When the query
returns the row for that key, only the last request is emitted; all preceding
records are **permanently lost**.
*(In contrast, `BigQueryEnrichmentHandler` in
`sdks/python/apache_beam/transforms/enrichment_handlers/bigquery.py`, lines
201–242, uses `requests_map: dict[Any, list[beam.Row]] = defaultdict(list)` and
emits `(req, beam.Row())` for all unmatched requests.)*
#### 3. `KeyError` Crash When `column_names` Omits Join Column or Column
Names Differ
Line 374 invokes:
```python
response_key = self.create_row_key(response_row)
```
Where `create_row_key` (lines 601–611) extracts fields using
`self._query_config.where_clause_fields` (or `where_clause_value_fn`):
```python
def create_row_key(self, row: beam.Row):
if isinstance(self._query_config, TableFunctionQueryConfig):
return tuple(self._query_config.where_clause_value_fn(row))
if isinstance(self._query_config, TableFieldsQueryConfig):
row_dict = row._asdict()
return (
tuple(
row_dict[where_clause_field]
for where_clause_field in
self._query_config.where_clause_fields))
```
- If a user configures `column_names` to fetch specific enriched fields
(e.g., `column_names=['email', 'tier']`) without explicitly including the join
column (`id`), `response_row` will only contain `email` and `tier`.
`create_row_key(response_row)` fails with `KeyError: 'id'`.
- If the input record uses a different field name than the database table
column (e.g. input record has `customer_id` while the table column is `id`),
`create_row_key(response_row)` looks for `customer_id` in the database row and
raises `KeyError`.
- If `TableFunctionQueryConfig` is used, `where_clause_value_fn` expects an
input `Row` and may reference fields that do not exist in the database table
schema.
#### 4. Cache Key Mismatch in `get_cache_key`
In `get_cache_key` (lines 632 and 649–650):
- Batch mode formats keys with `repr`: `key = ';'.join(map(repr,
current_values))` -> `'alice'`
- Single mode formats keys with `%s` / `str`: `key = ";".join(["%s"] *
len(current_values)) % tuple(current_values)` -> `alice`
This causes cache divergence between batched and unbatched executions.
---
### Code Pointers / Steps to Reproduce
The flaw can be verified directly with a mocked `_execute_query` or against
an external SQL instance:
```python
import apache_beam as beam
from unittest.mock import MagicMock
from apache_beam.transforms.enrichment_handlers.cloudsql import (
CloudSQLEnrichmentHandler,
ExternalSQLDBConnectionConfig,
DatabaseTypeAdapter,
TableFieldsQueryConfig,
)
config = ExternalSQLDBConnectionConfig(
db_adapter=DatabaseTypeAdapter.POSTGRESQL,
host='localhost', port=5432, user='u', password='p', db_id='db'
)
query_config = TableFieldsQueryConfig(
table_id='users', where_clause_template='id = :id',
where_clause_fields=['id']
)
handler = CloudSQLEnrichmentHandler(
connection_config=config, query_config=query_config, min_batch_size=2,
max_batch_size=10
)
# Reproduction 1: Missing DB row -> silent data loss
handler._execute_query = MagicMock(return_value=[{'id': 1, 'name': 'Alice'}])
reqs = [beam.Row(id=1, val='A'), beam.Row(id=2, val='B')] # id=2 is missing
from DB
responses = handler._process_batch_request(reqs)
assert len(responses) == 2, f"Expected 2 responses, got {len(responses)}"
# AssertionError: Expected 2 responses, got 1 (id=2 was dropped!)
# Reproduction 2: Duplicate join keys -> silent data loss
handler._execute_query = MagicMock(return_value=[{'id': 1, 'name': 'Alice'}])
reqs = [beam.Row(id=1, event='click'), beam.Row(id=1, event='buy')]
responses = handler._process_batch_request(reqs)
assert len(responses) == 2, f"Expected 2 responses, got {len(responses)}"
# AssertionError: Expected 2 responses, got 1 (reqs[0] was overwritten in
requests_map and dropped!)
# Reproduction 3: column_names omits join column -> KeyError
handler_cols = CloudSQLEnrichmentHandler(
connection_config=config, query_config=query_config,
column_names=['name'],
min_batch_size=2, max_batch_size=10
)
handler_cols._execute_query = MagicMock(return_value=[{'name': 'Alice'}])
handler_cols._process_batch_request([beam.Row(id=1), beam.Row(id=2)])
# KeyError: 'id'
```
---
### Impact
- **Silent Data Loss:** When batching is enabled (`min_batch_size > 1`), any
input rows lacking a matching record in the database table are dropped from the
pipeline. Furthermore, multiple input rows sharing the same join key within a
batch are collapsed into a single row, permanently discarding data.
- **Pipeline Crashes:** Restricting fetched fields via `column_names` or
using different field names between the input schema and SQL table causes
unhandled `KeyError` crashes in batch mode.
---
### Proposed Solution
1. In `_process_batch_request`
(`sdks/python/apache_beam/transforms/enrichment_handlers/cloudsql.py`, lines
350–377):
- Use `requests_map: dict[Any, list[beam.Row]] =
collections.defaultdict(list)` so duplicate keys map to all matching input
requests.
- Maintain an `unmatched_requests` set or copy of `requests_map`.
- When iterating over `result`, pop matched keys from
`unmatched_requests` and append `(req, response_row)` for each request in the
bucket.
- For remaining entries in `unmatched_requests`, append `(req,
beam.Row())` (or optionally respect `throw_exception_on_empty_results` if
added), preserving standard outer/left join semantics.
2. Ensure join fields are always included in the `SELECT` projection (or
create row keys using the SQL table's join column definitions rather than
relying on input field names).
3. Align `get_cache_key` between single and batch modes to use consistent
string formatting.
---
### Issue Priority
Priority: 1 (data loss / total loss of function)
---
### Issue Components
- [x] Component: Python SDK
- [ ] Component: Java SDK
- [ ] Component: Go SDK
- [ ] Component: Typescript SDK
- [x] Component: IO connector
- [ ] Component: Beam YAML
- [ ] Component: Beam ML
- [ ] Component: Beam examples
- [ ] Component: Beam playground
- [ ] Component: Beam katas
- [ ] Component: Website
- [ ] Component: Infrastructure
- [ ] Component: Spark Runner
- [ ] Component: Flink Runner
- [ ] Component: Prism Runner
- [ ] Component: Hazelcast Jet Runner
- [ ] Component: Google Cloud Dataflow Runner
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]