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?