BigQuery: Provide handler for schema evolution for custom events and contexts about data-models HOT 2 OPEN

It makes most sense to my mind to structure this as two separate stored procedures, one for events and one for contexts.

Structs and arrays can be handled by creating new objects of the same type, or flattening - not sure which makes most sense at the moment.

I think it's acceptable to pear it down to a minimal implementation first (eg. only top-level structs and arrays), since the vast majority of users don't heavily use structs or arrays. Those fields not handled can be omitted or included but not coalesced - perhaps the latter makes most sense since that allows people to handle those cases themselves.

from data-models.

A possible approach:

• Javascript UDF takes an array of struct or array field paths as input, outputs the superset key-value pairs it finds at those paths (prioritising latest version) - as a struct.
• Call that UDF within the stored procedure on any array/struct fields found.

Need to check whether the key is always present even if one row has no value for a given key. If not, we may need something more complex in the UDF.

Also the output can change across runs, so it could only be used to produce scratch tables (but I don't think there's any way to avoid this).

from data-models.