GoogleCloudPlatform / GoogleCloudPlatform/DataflowTemplates
[Bug]: SpannerChangeStreamsToBigQuery does not support null primary key column values.
- 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
Assessment
This issue has not been assessed yet.