GoogleCloudPlatform / GoogleCloudPlatform/DataflowTemplates

[Bug]: SpannerChangeStreamsToBigQuery does not support null primary key column values.

Open
#831 10 comments 0 reactions 0 assignees View on GitHub
bug p2
Dominant language
Java
Stars
1.3k
Forks
1.1k
Avg merge
2d 20h
Merged PRs (30d)
60

Description

### Related Template(s)

SpannerChangeStreamsToBigQuery

### What happened?

In Spanner it is valid to have nullable columns in a primary key.

However, the way the pipeline reads values out of json [here](https://github.com/GoogleCloudPlatform/DataflowTemplates/blob/1276f71f34e783fa5fc6c05aec9e953f7331c305/v2/googlecloud-to-googlecloud/src/main/java/com/google/cloud/teleport/v2/templates/spannerchangestreamstobigquery/FailsafeModJsonToTableRowTransformer.java#L219) it seems like it is extracting `null` when a column in the primary key is null and passing that value to bigquery.

Bigquery does not seem accept this and throws "This field: XXX is not a record."

This is blocking us rolling out a new feature that relies on a null primary key column.

Full example:
```
CREATE TABLE TestTable (
Col1 STRING(40),
Col2 STRING(40)
) PRIMARY KEY (Col1, Col2)
```

say we delete row with the following values:
```
Col1="123",Col2=NULL
```

keysJson will be:
```
{\"Col1\":\"123\",\"Col2\":null}
```

error is:
```
"location":"col2","message":"This field: col2 is not a record.","reason":"invalid"}],"index":0}}
```

### Beam Version

Newer than 2.46.0

### Relevant log output

```shell
{"message":{"keysJson":"{\"XXX\":null,\"XXX\":\"XXXX\",\"XXX\":\"XXX\"}","newValuesJson":"{}","commitTimestampSeconds":XXX,"commitTimestampNanos":XXX,"serverTransactionId":"XXX","isLastRecordInTransactionInPartition":false,"recordSequence":"XXX","tableName":"XXX","modType":"DELETE","valueCaptureType":"OLD_AND_NEW_VALUES","numberOfRecordsInTransaction":2,"numberOfPartitionsInTransaction":1,"_metadata_error":{"errors":[{"debugInfo":"","location":"XXX","message":"This field: XXX is not a record.","reason":"invalid"}],"index":0},"_metadata_retry_count":1},"error_message":{"errors":[{"debugInfo":"","location":"XXX","message":"This field: XXX is not a record.","reason":"invalid"}],"index":0}}
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.