BigQuery subscription issue: I get "JSON is missing required field: subscription_name, message_..."

[jldupont_sora]

Here is my setup:

Kinesis --> pubsub ingestion --> topic "T" --> BigQuery Subscription "S"

The destination table has the required fields:

and is partitioned:

The subscription "S":

Result: no data is flowing to the table (yes there is traffic on topic "T"):

----

When I add a "dead letter" service and I pull messages from there through gcloud, I get the following:

  which clearly contradicts the BQ schema (see above).

What is weird: (1) if I turn off the "write metadata" and make the "publish_time", "attributes", "message_id" and "subscription_name" NULLABLE, it works.  Now, (2) I turn back ON the "write metadata" and it still works !!

How can I make this work reliability please ?

[marckevin]

Hi @jldupont_sora,

Welcome to Google Cloud Community!

The error “CloudPubSubDeadLetterSourceDeliveryErrorMessage” generally means that a Pub/Sub message couldn’t be written to BigQuery table, which contain the actual reason of “JSON is missing required field:” due to mismatch on the data type fields between the Kinesis through Pub/Sub and BigQuery table. 

As per my understanding from the behavior you observed, the subscription is expected to work since you only disabled/enabled the “Write Metadata” while maintaining the mentioned metadata fields as “NULLABLE”.

As quoted from the documentation on Use table schema: “If there are BigQuery fields that are not present in the messages, these BigQuery fields must be in mode NULLABLE”, as BigQuery has the ability to still accept values and store it as “null”, as opposed to when you set it as “required” as BigQuery expects the incoming data to match the exact data type specified. In addition, Write metadata only enabled Pub/Sub to add additional columns to write metadata to your BigQuery table.

Here are some suggestions that may help resolve the issue on BigQuery Subscription:

• Focusing on the error you encountered, inspect your source message, ensure your data type fields are aligned for your Pub/Sub message fields and expected BigQuery table schema.
• Ensure the Pub/Sub topic and BigQuery Subscription is properly configured and aligned. You can refer to the documentation on subscription to write to BigQuery, including a guide on how to use table schema, Write metadata and other fields.

If the issue persists, I recommend reaching out to Google Cloud Support for further assistance, as they can provide insights into whether this behavior is specific to your project.

I hope the above information is helpful.