2

With Dataflow SQL I would like to read a Pub/Sub topic, enrich the message and write the message to a Pub/Sub topic.

Which Dataflow SQL query will create my desired output message?

Pub/Sub input message: {"event_timestamp":1619784049000, "device":{"ID":"some_id"}}

Desired Pub/Sub output message: {"event_timestamp":1619784049000, "device":{“ID":"some_id", “NAME”:”some_name”}}

What I get is: {"event_timestamp":1619784049000, "device":{"ID":"some_id"}, "NAME":"some_name" }

but I need the NAME inside the “device” attribute.

SELECT message_table.device as device, devices.name as NAME 
FROM pubsub.topic.project_id.`topic` as message_table
  JOIN bigquery.table.project_id.dataflow_sql_dataset.devices as devices 
  ON devices.device_id = message_table.device.id
Marko
  • 21
  • 2

1 Answers1

0

You need to create a struct in the projection (SELECT part)

SELECT STRUCT(message_table.device.ID as ID , devices.name as NAME) as device
FROM pubsub.topic.project_id.`topic` as message_table
  JOIN bigquery.table.project_id.dataflow_sql_dataset.devices as devices 
  ON devices.device_id = message_table.device.id
guillaume blaquiere
  • 33,758
  • 2
  • 11
  • 37
  • Thank you, guillaume. I tried it but during creation of the Dataflow Job it gives an error: "exec.go:64","message":"Invalid/unsupported arguments for SQL job launch: Query uses unsupported SQL features: Does not support expr node kind RESOLVED_MAKE_STRUCT"} – Marko May 07 '21 at 14:47
  • My bad, I was sure it will work because it worked in SQL... Maybe a day my answer will be valid! But not today. I fear that there isn't solution for this case... – guillaume blaquiere May 07 '21 at 15:05
  • Thanks for you help, I appreciate it! – Marko May 07 '21 at 15:12