How to insert nested data to an existing record using bigquery streaming

Question

I am trying to understand bigQuery and see if it fits our needs. One of the basic requirements we have is to store a nested structure such that the nested part needs to be stored separately than the main record.

e.g. Let's say we have a record of an employee, after storing the main data for the employee, let's say a minute after, another record would arrive with employee previous work place (and then another such record may arrive)

So we need to store te first employee record, and then update the structure to add a detail about the employee, this detail is also inserted as new record and does not overwrite an existing record.

How can this be done in bigQuerY? Assuming we may have different sources of the data?

Pentium10 · Accepted Answer · 2017-04-18 17:24:39Z

2

The preferred and recommended way to store that in BigQuery is append-only. That means that you are limited to do update/delete, and you constantly instant new rows.

By having a stream of rows from the same user, you need to write your queries in a such way to pick the last row, to obtain the most recent profile. But you have all the 'versioning' of all the stream that came in.

In other words you use Streaming Insert functionality to constantly add new rows. Then you have your SQL queries usually with Window Functions to pick last row.

You cannot update a row, or append to a record as BigQuery limits DML statements to 96 per table.

answered Apr 18, 2017 at 17:24

Pentium10

209k124 gold badges435 silver badges513 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Elliott Brossard Over a year ago

Another option could be to maintain separate tables with the various pieces of information and associate them based on an employee ID, for instance. Analytic functions might be complicated since the OP wants a combination of the information across rows.

Collectives™ on Stack Overflow

How to insert nested data to an existing record using bigquery streaming

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related