How to aggregate multiple rows into one in BigQuery?

Viewed 2683

Suppose you have a de-normalized schema with multiple rows like below:

   uuid    |    property    |    value   
------------------------------------------
  abc      |   first_name   |  John
  abc      |   last_name    |  Connor
  abc      |   age          |  26
...

The same set of properties for all rows, not necessarily sorted. How to create a table such as using BigQuery (i.e. no client):

Table user_properties:

   uuid    |    first_name  |    last_name   |    age
 --------------------------------------------------------
  abc      |   John         |    Connor      |    26

In traditional SQL there is the "STUFF" keyword for this purpose.

It would be easier if I could at least get the results ORDERED by uuid so the client would not need to load the whole table (4GB) to sort -- it would be possible to hydrate each entity by scanning sequentially the rows with same uuid. However, a query like this:

SELECT * FROM user_properties ORDER BY uuid; 

exceeds the available resources in BigQuery (using allowLargeResults forbids ORDER BY). It almost seems like I cannot sort a large table (4GB) in BigQuery unless I subscribe to a high end machine. Any ideas?

1 Answers
Related