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

[rob-pomelo]

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 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

{"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}}

[rob-pomelo]

As a followup i dug deeper and i think the issue is that

val jsonObject =
      JSONObject("{\"Col1\":null,\"Col2\":\"123\"}")
val v = j.get("Col1")
val isNull = v != null  // TRUE, because JSONObject.NULL and not java null

Details on null: https://stleary.github.io/JSON-java/org/json/JSONObject.html#NULL

This differs from non PK fields where java null is used and works

DataflowTemplates/v2/googlecloud-to-googlecloud/src/main/java/com/google/cloud/teleport/v2/templates/spannerchangestreamstobigquery/schemautils/SpannerToBigQueryUtils.java

Line 142 in 1276f71

return null;

I think an explicit isNull check and skip similar to the following would fix it

DataflowTemplates/v2/googlecloud-to-googlecloud/src/main/java/com/google/cloud/teleport/v2/templates/spannerchangestreamstobigquery/schemautils/SpannerToBigQueryUtils.java

Line 209 in 1276f71

if (!newValuesJsonObject.has(columnName) || newValuesJsonObject.isNull(columnName)) {

[rob-pomelo]

hi any updates here?

[github-actions]

This issue has been marked as stale due to 180 days of inactivity. It will be closed in 1 week if no further activity occurs. If you think that’s incorrect or this pull request requires a review, please simply write any comment. If closed, you can revive the issue at any time. Thank you for your contributions.

[rob-pomelo]

this still is not fixed

[github-actions]

This issue has been marked as stale due to 180 days of inactivity. It will be closed in 1 week if no further activity occurs. If you think that’s incorrect or this pull request requires a review, please simply write any comment. If closed, you can revive the issue at any time. Thank you for your contributions.

[rob-pomelo]

still not fixed