Solved: Nested Pubsub message to Bigquery - Page 2

itsm3_vishnu · 01-02-2024 11:09 AM

Is it possible to flatten the nested pubsub payload and insert to bigquery?

Eg payload :

{"data": [
{
"field1": "some_value1",
"field2": "some_value2",
"details": {
"field3": "some_value3",
"field4": "some_value4"
}
}
]
}

I want the Bigquery columns to be field1,field2,field3,field4 etc

kolban

If the message being written to the PubSub topic was nested ... for example:

{
  "A": "Hello",
  "B": {
    "X": "Val1",
    "Y": "Val2"
  },
  "C": 123
}

And the table you want to populate looks like:

A	X	Y	C
Hello	Val1	Val2	123

Then I can't think of a way to "automatically" flatten the structured content of the topic message. What you appear to be describing is a mapping / transformation. Technology such as Dataflow, Data Fusion, Workflows, App Integration, Cloud Functions or Cloud Run could be used to sit between the topic containing messages and the insertion into the table. Alternatively, land the structured data into BigQuery directly and then perform ELT to transform within the data warehouse.

To answer your question .... my answer to "Is it possible to flatten the payload directly using schema and sink to bigquery?" is then "no" ...

View solution in original post