Nested rows using STRUCT are not supported in Dataflow SQL (GCP)

Question

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

Can you join a BQ table with your Dataflow SQL pipeline? like a simple query in fact. — guillaume blaquiere, May 07 '21 at 12:22
yes i can. Like: SELECT message_table.device as device, "some_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, May 07 '21 at 12:30
And, is it what you want to achieve? Else, I didn't understand yoru blocking point — guillaume blaquiere, May 07 '21 at 13:16
what I get: {"event_timestamp":1619784049000, "device":{"ID":"some_id"}, "NAME":"some_name" } — Marko, May 07 '21 at 13:28
what is want: {"event_timestamp":1619784049000, "device":{"ID":"some_id", "NAME":"some_name" }}. — Marko, May 07 '21 at 13:28
Ok, your Dataflow SQL output is a PubSub, not a BigQuery, correct? Can you share your request? — guillaume blaquiere, May 07 '21 at 13:47

score 1 · Accepted Answer · answered Jun 03 '21 at 11:02

Unfortunately, Dataflow SQL does not currently support STRUCT/Sub queries, but we are working on it. Since there are some Apache Beam dependencies preventing its progress (Nested Rows Support, Upgrading Calcite), we cannot provide an ETA at the moment, but you can follow its progress on this issue tracker.

score 0 · Answer 2 · answered May 07 '21 at 14:16

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

answered May 07 '21 at 14:16

guillaume blaquiere

66,369
2
47
76

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
1

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

Nested rows using STRUCT are not supported in Dataflow SQL (GCP)

2 Answers2

Linked